ABSTRACT: The UTM Campaign URL Builder Spreadsheet template was built to address a big pain point for digital advertisers. Building and tracking UTM Campaign URLs is a time-consuming endeavor that can make marketers want to throw their computers against the wall. In having to manually generate each UTM link through Google Campaign builder and then hopping back and forth between platforms to enter all of that data into an Excel spreadsheet, there is room for error, anger, and lots of pain. This spreadsheet template addresses both of these issues by automatically generating (UTM) Campaign URLs and automatically tracking their results for the Facebook, Instagram, or Google channels.

The instructions below provide a step-by-step guide to building and managing all of your Campaign (UTM) URL links from one place. More information on the Facebook Campaign URL UTM Builder and Reporter spreadsheet template, click here: https://factivate.com/automated-spreadsheet-template-marketplace/facebook-ads-campaign-url-utm-builder-spreadsheet-report-tracker/

If you would like to go directly to the blank spreadsheet template, you can view it here: https://app.factivate.com/spreadsheets/75c8ee7791b141b08f987cb39738db4a . Note that if you do not have a Factivate user account, you will not be able to link this template to your Google or Facebook data.

Connecting your UTM Campaign URL Builder Spreadsheet in order to automatically report on UTM link performance

This spreadsheet is able to pull information from Google Analytics and Facebook in order to bring together UTM campaign data in one place. To do so, you will need to connect your spreadsheet so that it’s automatically updated with performance data from each channel.

To do so, you will need to make sure that you create a copy of the publicly shared spreadsheet and add it to your account. Go to FILE>Duplicate in the publicly shared spreadsheet and that should take care of it.

The template will be mostly blank because you have not added any UTM links and your “blue” tabs have not been enabled to pull data. Be sure to go to the GA_UTMCampaigns (pulls from Google Analytics) and the FBAdsAcct1 (pulls from Facebook Ads manager) in order to enable your automatic data pulls. If you’re not familiar as to how to do this, you can see how to connect your spreadsheet to third party sources in the video below:

How to use the UTMlibrary spreadsheet tab

The tab titled “UTMlibrary” is where you will build and maintain a record of all of your UTM Campaign links. If you’re not overly familiar with what some of the column titles mean (ie, campaign content), you can see a detailed explanation on UTM campaign links here: https://blog.factivate.com/the-best-guide-to-utm-campaign-tagging-for-digital-marketers/

Now that you got the basics out of the way, let’s look at the spreadsheet. There are 6 required items you will need to add in order to generate a good UTM link: the Landing page URL, Campaign Name, Campaign Content, Campaign Term, Campaign Source, and Campaign Medium. Each of these should follow a naming convention. For best practices, note that , the ad names or Keywords should never be repeated. You can look at tagging definitions to see or the article mentioned above to make sure this is done correctly.

A few don’ts for this tab:

• avoid spaces, symbols (+ or /) within the required parameters (ie Campaign Name). If you use them, the UTM link will not be generated correctly. If need spaces, use the “_” sign.
• Second, try to avoid CAPS on your parameters. This will make your data cleaner and will ensure your template works as desire.

Once you have entered your required fields in the row, a UTM link will be generated. Simply copy the link from your spreadsheet and paste it in your ad. This takes care of the link generation part.

If you’re generating a link for a FB Ads account – make sure that your campaign name, adset name, and ad name all match what you used for your UTM link generated in the spreadsheet. This will keep your data clean and will ensure that data is pulled and attributed to the same campaign.

So what happens after you’ve launched your ad and made sure your UTM link was set up correctly?

All you need to do is sit back and watch this automated spreadsheet work its magic. The UTM library will continue to aggregate data for each UTM campaign link built and will display these KPIs from columns L through X.

How do you calculate the ROI per UTM campaign?

In this case, we’re using Google Analytics Value Created as a KPI to determine your “revenue” estimation. If you have an e-commerce platform or can see revenue from your Google Analytics account, you will need to change this formula in cell M3 to read as follows:

=IF($K3<>””,SUM(DS(GA_UTMCampaigns!’Revenue’:’Revenue’,AND([@Feed Timestamp]>=$B3,[@Feed Timestamp]<=$C3,[@Campaign]=$F3,[@Ad Content]=$G3,[@Keyword]=$H3,[@Source]=$I3,[@Medium]=$J3))),””)

Once you’ve changed M3, remember to copy/paste that formula down so that all of column M formulas change to measure revenue and not Value Generated.

Understanding the MonthlyReport tab

The spreadsheet template also includes a tab that you can share with your team/clients for the month. This tab essentially measures the same KPIs as your UTMlibrary but will be specific for a specific time period (ie, Campaign A results only for the first 14 days and not for the lifetime of the campaign).

Using this spreadsheet is simple: simply COPY the UTMLibrary data for the campaigns you want to display (columns F through J) and paste that data in the MonthlyReport tab starting with cell B53. Once you’ve done that you will notice your cost per channel, unit economics, and performance KPIs automatically recalculate.

All you have to do now is decide who to share this file with and then receive all of the praise in the world! Go get them you genius marketers you!

Conclusion

The Campaign URL Builder spreadsheet template is probably the best tool for a digital marketer’s arsenal when used correctly. By automatically combining all of your channel and web performance KPIs into one place, you will start noticing amazing insights into your campaign spending and optimization. Please email us at [email protected] if you have any questions or leave a comment below.

For additional Facebook templates built for marketers, please visit the Factivate Spreadsheet Template Marketplace