Google Sheets for Marketers Part I – Let’s Build a URL Builder!

Welcome to a new series at Chili Piper – Google Sheets for Marketers – where you can learn how to use Google Sheets to build marketing reports, dashboards, and how to discover actionable insights to bring back to your team.

To cover the basics of Google Sheets, we’ll jump right into our first tutorial on creating a URL builder – an essential tool for any marketing team to create links with UTM parameters for tracking. 

UTM parameters are tags appended to URLs so tracking software (like Google Analytics) can know where traffic is coming from. 

Here’s an example:

www.chilipiper.com/?utm_source=facebook&utm_medium=social&utm_campaign=instant-video&utm_content=feature-video&utm_term=a-prospects

The example above would be a link to our homepage, but the parameters of utm_source, utm_medium, utm_campaign, and so on designate where the traffic is from, our internal campaign name, the content was used, etc. Here’s a deep dive on UTM parameters from Autopilot if you want to learn more.

By using a tool like a URL builder and having strict naming conventions for channels, campaigns, and content, you and your team will have much cleaner data in Google Analytics (and even your crm) for finding actionable insights.

The benefit of using a spreadsheet to build your tracking URLs instead of an online tool, is in a spreadsheet all the URLs you build will be saved in case you need to diagnose an issue or make sure a URL was even made in the first place. You can even make a limited version of this spreadsheet for each person’s role. For example, you can give the social media manager a URL builder that only lets them use the channels they’re using. This will help limit errors and keep your data consistent and reliable.

In this tutorial, I’ll walk you through how to build a “bulletproof” URL builder that your whole team can use to create trackable, campaign links.

Download the finished spreadsheet

Covered in This Tutorial

Absolute vs Relative References

An absolute reference is used when you want to target a specific cell that doesn’t change as you move your formulas across your spreadsheet. Relative reference are used when you want to reference a row or column of values that you want to reference in a formulas as your formulas moves across your spreadsheet. 

Quick tip: Use ‘F4’ (use fn + F4 on Mac) to cycle through the 4 variations of relative and absolute references.

Navigation

CMD + directional arrow key to jump to the end of the dataset.

CMD + Shift + directional arrow key to highlight all called from your current selection to the end of your dataset.

CMD + D to fill in your formula down a range. Start with selecting a cell with the formula you want to copy. Use CMD shift down/up arrow key to select the range you want to fill with your formula, then use CMD + D to fill.

Formulas

Start with an “=“ to designate to Google Sheets you’re using a formula.

CONCATENATE to append two or more strings (text) together. You can also use the operator ‘&’ to append two values together, but can get too repetitive for longer concatenations. 

IF to tell GSheets to plan A or plan B based on whether a statement is true or not. In this tutorial, we used IF formulas to tell GSheets to show BLANK until all required cells were filled out for our URL builder.

Formatting & Making it Useable for a Team

Data Validation can be used to create a drop down list of values, which can help prevent errors or misuse. We use a drop down values to ensure everyone on our team is using the appropriate naming convention for their UTM links.

Protecting a Range is useful for sections of a spreadsheet that you’ve set up to be all automated with formulas. To protect it from accidentally getting messed up, you can lock part of the sheet so only you can make changes while still providing edit access to others.

Hiding Results Until Requirements Met – To make a spreadsheet more like a tool, I like to hide the final output until the user has done everything required to avoid mistakes or incomplete data. In the URL builder, we hide the final URL using IF formulas until all UTM parameters have values.

What Do You Want to Learn Next?

I want to hear from you! In this tutorial, we just covered some of the basics. Next week, we’ll have a more advanced report or dashboard to build where we can dive into more complex formulas.

What would you like to see a tutorial on? Leave a comment to let me know.