Auto-generating Google Campaign URLs with Smartsheet?

Trish Harrison

I would love to create and manage Google Campaign URLs with Smartsheet. Ideally, I would enter the URL and associated variables, and in the final column it would generate the complete campaign URL, just like the official Google URL builder (https://ga-dev-tools.appspot.com/campaign-url-builder/).

Specifically, I suppose the final column would check a cell to see if it has a value, and if it has the value, input into the URL formula appropriately. The output would look something like:

(A1)[?utm_source=(B1)][&utm_campaign=(C1)][&utm_medium=(D1)]

Does anyone know how I might go about making something like that happen? Thanks in advance.

JamesR

You would need a column for each element, then a cloumn for the full address which you would construct with the Concaternate formula including any HTML charachers required.

J. Craig Williams

Here's an example:

="www.google.com/maps/place/" + SUBSTITUTE(JOIN([Street Address]1:Zip1, "+"), " ", "+")

This takes 4 columns (starting at [Street Address] and ending as [Zip]) to create a Google Maps link. (The other two are [City] and [State] in case you were wondering)

The SUBSTITUTE function replaces spaces with the plus (+) sign.

Craig

Trish Harrison

Thanks - I was just trying to find documentation on the sytanx to use but it's simplier than I thought; simple =[Cell1]R# + "string" + [Cell2]R#

I ended up using this formulate and it works exactly as intended:

=[URL*]21 + "/?utm_source=" + [Campaign Source*]21 + "&utm_campaign=" + [Campaign Name*]21 + "&utm_medium=" + [Campaign Medium*]21

(Columns: URL*, Campaign Source*, Campaign Name*, Campaign Medium*, and the formula goes into a colum "Final URL" )

Now, what I'd really like to do is add logic to the formula - "If([Campaign Medium]is NOT null then "+&utm_medium"+"[Campaign Medium]" so that if there is a term in the cell it adds it to the URL, but if there is not a term, it doesn't -- is that possible? I've found some documentation on "IF" but not "not null."

Edit: I figured it out! The "ISBLANK" statement is formatted to provide "If blank = statement" and "If not blank = statement" already. The documentation is just really unclear on that.

So by entering the statement "IF(ISBLANK([Campaign Source*]), "", "&campaign_source" + [Campaign Source]) in my overall statement it worked as intended. Woo!

Here's the full statement, hope it helps!

Columns (* indicates required according to Google URL's campaign builder)

URL*

Campaign Source*

Campaign Name*

Campaign Medium*

Campaign Term

Campagin Content

Formula:

=[URL*]1 + "/?utm_source=" + [Campaign Source*]1 + "&utm_campaign=" + [Campaign Name*]1 + "&utm_medium=" + [Campaign Medium*]1 + IF(ISBLANK([Campaign Term]1), "", "&utm_term=" + [Campaign Term]1) + IF(ISBLANK([Campaign Content]1), "", " &utm_content=" + [Campaign Content]1)

minord

This was really helpful.

Any suggestions on how the formula might change if you sometimes have URLs that already have a ? (question mark) as part of the original destination URL?

For example, say your URL looks like this:

https://mydomain.com/register/?id=c17b446a-8743-4825-8ae3-cc615c0b2ebe

when I add my UTM codes using the formula provided in this discussion thread above to my Smartsheet, then the URL will fail given that it has two question marks in the URL.

https://mydomain.com/register/?id=c17b446a-8743-4825-8ae3-cc615c0b2ebe?utm_medium=email&utm_source=crm&utm_campaign=event-ll&utm_term=adm-sp19-ll

Am I able to build any logic into the Smartsheet formula that would spot the ? in the original URL and instead insert a & in the URL

https://mydomain.com/register/?id=c17b446a-8743-4825-8ae3-cc615c0b2ebe&utm_medium=email&utm_source=crm&utm_campaign=event-ll&utm_term=adm-sp19-ll

Any suggestions on how to handle this. Unfortunately our event registration URLs have a ? in the URL.

Thanks for your help.

Doug

tksampson

This has been hugely valuable - but I get a break after the IF(ISBLANK([Campaign Term]1),.

I think the "" is giving a space? Also - what is the 1 for after each column title?