If you’re running ads on platforms like TikTok, X or Facebook, you probably use UTM parameters to track the performance of your campaigns. But did you know that you can quickly calculate the total number of conversions attributed to each UTM parameter directly in Google Sheets? This guide will show you how to use a simple formula to count conversions based on your UTM tags—like utm_source=tiktok and utm_source=facebook.
In just a few steps, you’ll have a dynamic way to track and report conversions from all your digital campaigns.
Step 1: Set Up Your Google Sheet for Tracking
First, ensure your Google Sheet has the relevant data, such as conversion IDs and UTM parameters. You can easily include these values on your Sheet Monkey form with a little Javascript or automatically on the Carrd platform.
Your spreadsheet should look something like this:
| Conversion ID | UTM Parameter |
|---|---|
| 1 | utm_source=tiktok |
| 2 | utm_source=facebook |
| 3 | utm_source=tiktok |
| 4 | utm_source=facebook |
| 5 | utm_source=tiktok |
| 6 | utm_source=tiktok |
Each row represents a conversion, with the UTM parameter listed in the second column. This setup will allow us to count the number of conversions per UTM source.
Step 2: Use the COUNTIF Function
Now that your data is in place, it’s time to calculate how many conversions are associated with each UTM parameter. Google Sheets provides an easy formula called COUNTIF that can do this.
Let’s start with calculating the total conversions for utm_source=tiktok.
- Click on an empty cell where you want to display the total count (for example, D1).
- Enter the following formula:
=COUNTIF(B:B, "utm_source=tiktok")
This formula will search through column B (where your UTM parameters are) and count every occurrence of utm_source=tiktok.
Step 3: Repeat for Other UTM Parameters
Next, repeat the process for utm_source=facebook (or any other UTM parameters you’re using):
Click on another empty cell (for example, D2).
Enter this formula:
=COUNTIF(B:B, "utm_source=facebook")
Now, you’ll have the total number of conversions for both utm-x and utm-y displayed in their respective cells.
Step 4: Automate and Monitor Your Conversions
As you continue adding new conversion data into the Google Sheet, the COUNTIF formula will automatically update the totals for each UTM parameter. This makes it incredibly easy to keep track of how your ads are performing without needing to do manual calculations.
Tracking conversions with UTM parameters is essential for understanding the effectiveness of your campaigns. With Sheet Monkey and the COUNTIF function, you can easily calculate how many conversions each UTM parameter has driven, all in real-time. Now you can monitor your digital campaigns efficiently without needing third-party tools or complicated setups.