We’ve been assisting a large e-commerce client with migrating to Google Analytics 4 from Universal Analytics and improving their campaign attribution to ensure they have accurate reporting on each of the mediums and channels they’re putting to use, including influencers. The best means of doing this is to ensure every distributed link to their online store has a UTM querystring appended to it for Google Analytics to capture the campaign information associated with the visit.
I built a friendly UTM Querystring app to do this on Martech Zone, but it doesn’t store any info… which your team may want to. So, for companies who wish to organize better and standardize their Google Analytics Campaigns, I created a Google Sheet where they could log all of their campaigns and ensure the information passed in their UTM Querystring is standardized. Here’s a rundown of the features:
- Campaign Date – If you double-click, you’ll get a nice calendar pop-up to help you with the date.
- Destination URL – A field to enter the destination URL. You can even have a querystring in it and the output will incorporate any of those parameters.
- Campaign Name – The campaign name you want to be passed to Google Analytics.
- Campaign Medium – A dropdown to select your medium. This references the My Mediums tab below, so you can add, delete, or enter the dropdown as you’d like. The current list is the standard channels currently listed in Google Analytics 4.
- Campaign Source – A dropdown to select the platform you’re sending from. Of course, you could also update this list using the My Sources tab sheet.
- Campaign Term – A dropdown to select the goal of your campaign. Of course, you could also update this list using the My Terms tab sheet.
- Campaign Content – A field to enter the content or offer of your campaign.
Keep in mind that many companies standardize the use of these parameters differently. That’s why I have the referenced tab where you can enter whatever information you want.
The output Campaign URL is properly formatted, trims any unnecessary space off each parameter, adjusts all the entries to lowercase, and properly encodes the parameters for use in a URL.
Rather than make an enormous formula for the output URL, you’ll notice that I have some hidden columns that display each variable, and then the formula concatenates all the fields. I did this just to make it easier to troubleshoot and for users who aren’t as savvy with formulas to be able to see how it was built.
Feel free to copy this Google Sheet to your own account for your own use. Just select File > Make a Copy and you can copy this to your own Google Workspace instance.
If you wish to download and work with the file locally in Excel, be aware of one limitation: it will only work offline on Microsoft Office for Windows. Excel for Mac doesn’t recognize the =ENCODEURL() function in Excel, so the formula will produce an error. This is true of Office365 online as well. Pretty crazy that Microsoft hasn’t got that functional at this point!
I hope you enjoy this freebie! I thought it might come in useful to our audience.
Related Martech Zone Content
