Basic task: Re-create UTM Tracking Google sheet formula in AirTable

Hi Alyssa,

I would like to help, kindly contact me on S.k.y.p.e- cis(dot)ron or e.m.a.i.l me- ron(dot)cis40(at)gmail(dot)com

Regards,
Ron A.

Hi :wave: @Alyssa_Martin,

This is pretty easy with just two functions & and SUBSTITUTE. The first function we can use to join the text of multiple fields and the latter we can use to replace the spaces with ‘%20’.

You can read more about both functions here:

For your table, I came up the following formula:

SUBSTITUTE(URL & '?utm_medium=' & Medium & '&utm_source=' & Source & '&utm_campaign=' & Campaign, ' ', '%20')

With the table structure looking like this:

I didn’t add UTM tags for ‘Content’ or ‘Keyword’ but that should be easy enough. If you’d like to add removing a UTM tag if the field is blank I’d be happy to add some IF logic to the function above. Also feel free to copy out my table if you want to take a closer look:

SUBSTITUTE(URL & '?utm_medium=' & Medium & '&utm_source=' & Source & '&utm_campaign=' & Campaign, ' ', '%20')

This is amazing! I wouldn’t have been able to figure that out myself. Thank you @bdelanghe. I’m about to jump on a client call, but I’ll pop back in afterwards and take a thorough look.

See? I told you someone would almost certainly volunteer a solution before the end of the day! :slightly_smiling_face:

SUBSTITUTE(URL & '?utm_medium=' & Medium & '&utm_source=' & Source & '&utm_campaign=' & Campaign, ' ', '%20')

Thank you @bdelanghe. This is awesome. I would love those last few things you mentioned, if you’re able to please:

1. UTM tags for ‘Content’
2. UTM tags for ‘Keyword’
3. removing a UTM tag if the field is blank.

Those things would make this perfect.

Oh! And is there a way to make sure the generated URL is all lowercase?

Done!

Bonus, also done!

Before I give you the formula, let me explain what’s added:

1. IF functions are wrapped around each UTM tag. You’ll notice there is no =, this because any text (except ‘false’ or ‘0’) will return TRUE. So each if says, “if there is text in this field, then show the UTM tag.”
2. REPLACE, is used to add the ‘?’ to the beginning of the UTM tags replacing the first ‘&’. All tags now start with this because we don’t know which will be the first tag.
3. LOWER, this one is easy just wrap it around anything and it’ll change the case to lower.

And now for the formula:

LOWER(
  SUBSTITUTE(
  URL &
  REPLACE(
    IF(Medium,'&utm_medium=' & Medium) &
    IF(Source,'&utm_source=' & Source) &
    IF(Campaign,'&utm_campaign=' & Campaign) &
    IF(Content,'&utm_content=' & Content) &
    IF(Keyword,'&utm_keyword=' & Keyword)
    ,1,1,'?')
  , ' ', '%20')
)

Best of luck! :four_leaf_clover: If you need to add new UTM tags. I suggest copying the formula into notepad and adding mimicking the IF formula replacing it with your new tags name.

Hi,

This is great! I have one issue though, as I would like the formula not to print anything if I dont have a URL in the field, how can I add it to the code?

Thanks,
cs

@Chiara_Sozzi
You can simply wrap the whole formula in an IF() function, like so:

IF(
   {URL Field},
   << The rest of your formula >>
)

IF(
   {URL Field},
   << The rest of your formula >>
)

Hi @Jeremy_Oglesby you lifesaver, tried to fit it in my code, but get an error, can you check :slightly_smiling_face:

IF(

{Link},

LOWER( SUBSTITUTE( Link & REPLACE( IF(Platform,’&utm_medium=social’ ) & IF(Platform,’&utm_source=’ & Platform) ,1,1,’?’) , ’ ', '%20’))

)

I’m not entirely sure, but I think maybe some bad quotes got you… Airtable is picky about those. Try copy-pasting the formula below, where I’ve fixed all the bad quotes, and see if that works for you:

IF(
   Link,
   LOWER(
      SUBSTITUTE( 
         Link &
         REPLACE(
            IF(Platform,'&utm_medium=social' ) &
            IF(Platform,'&utm_source=' & Platform),
            1,1,'?'
         ) , 
         ' ',
         '%20'
      )
   )
)

thank you that works!

LOWER(
  SUBSTITUTE(
  URL &
  REPLACE(
    IF(Medium,'&utm_medium=' & Medium) &
    IF(Source,'&utm_source=' & Source) &
    IF(Campaign,'&utm_campaign=' & Campaign) &
    IF(Content,'&utm_content=' & Content) &
    IF(Keyword,'&utm_keyword=' & Keyword)
    ,1,1,'?')
  , ' ', '%20')
)

@Jeremy_Oglesby I would like to understand the benefits of using AirTable for UTM parameters. I understand it can create the UTM parameter but is there another reason why using AirTable is a best practice? We are currently using Google UTM builder https://ga-dev-tools.google/ga4/campaign-url-builder/ and collecting the data in a Google spreadsheet. But if we can get more if we use AirTable I would love to change but I would like to understand it better. Thank you!