Reporting & Analysis
Mar 22, 2024
TLDR
To solve the issue of Klaviyo UTMs not showing up in GA4, a creative solution using spreadsheets was devised. By mapping Klaviyo flow names with GA4 metrics using spreadsheet formulas and pivot tables, the missing data was retrieved and presented in a comprehensive report. This process can be automated using Airboxr's tools, providing a streamlined solution for regular reporting needs.
Recently, we helped an ecommerce brand solve their problem for tracking Klaviyo email campaigns and flows within GA4.
Apparently, they weren’t the only ones facing this issue:
The solution for this involved multiple steps, but we eventually did manage to solve it (and even automate it!).
The best part is? We solved it using spreadsheets. Which means you can fix this for yourself, too.
Note that the solution for tracking Klaviyo campaigns in GA4 was different from tracking Klaviyo flows. We’ll show you how we solved for both of these.
This is what we did:
Change the global UTM parameters under Settings
The first thing you should do is to change your default UTM parameters, under the global Settings page in Klaviyo.
Here’s the proposed solution by Klaviyo:
For reference, here’s the default Klaviyo settings (from our own account):
We recently tested this with an ecommerce brand that was trying to automate their reports consolidating metrics from Klaviyo and GA4. After they set their utm_term to ‘Campaign’ and ‘Flow’ respectively, the campaigns started showing up this way in GA4:
(Note that utm_term shows up as Manual Term in GA4. Also note that campaigns that were sent prior to the change were showing with their campaign names, instead of ‘Campaign’ and ‘Flow’, under Manual Term.)
So… hurray! The UTMs for Klaviyo campaigns and flows are showing up. Now we can find the Campaign names - add Campaign and Campaign ID as rows, and filter Manual Term by ‘Campaign’. (The screenshots below show you how to do this for flows, but you can do it for campaigns instead.)
🥳🥳🥳
But wait - what about flows? Under ‘Flow’, campaign IDs refer to individual flow messages… not the entire flow itself.
To get the metrics attributed to the entire flow, you’ll need to use utm_id (It’s called Campaign ID in GA4). But there’s one major problem…
The problem: Klaviyo Flow IDs don’t show up in GA4
You’ll see in Klaviyo’s global Settings that we have indicated Flow ID to show under utm_id.
Unfortunately, this is what we see in GA4:
(Note: the flow ID is still called Campaign ID in GA4, which is why Manual Term is so important. Don’t get confused by the name.)
As you scroll down the report, you may find that many, many of these campaign IDs / flow IDs are missing. GA4 is not tracking the utm_id parameter well at all.
Well… that’s a bummer. But what can we do about it?
(We did write on Google’s support forums, by the way. You can guess how successful that was.)
Here’s what we did that actually worked:
Think like a pirate: The map is the key
We were able to solve this problem thanks to the power of spreadsheets, and some creative thinking.
For this, we took inspiration from the pirates. Yup, you read that right.
We thought:
“Well, we have flow message names from Klaviyo, which we can get the flow names for. We have flow message names showing in GA4 under Campaign. What if there was a way to map the two sets of data using flow message names?”
Pirates hunt for treasure, and to do that, they use maps. A map gives them the lay of the land (or seas), so they know how to find what they’re looking for. The map is the key (ahem) here to solving our problem.
Just like a pirate hunting for treasure, a single spreadsheet formula can hunt for the right flow to attribute GA4 metrics to. All it needs is a map.
And so we created a map of all the active Klaviyo flows and flow message names, onto a spreadsheet:
We also did a data dump of all the Klaviyo flows by Flow Name, and all the GA4 metrics by Message Name (referred to as Campaign in GA4), into two separate sheets.
Improve your DTC game. Sign up for weekly tips.
Combine the sheets with a formula, then create a pivot table
The next step was to put them all together. In a new spreadsheet, we used a QUERY formula to pull all the metrics from GA4 with the Flow Message Name (aka Campaign), and then an ARRAY formula to match the actual flow names from the Klaviyo sheet.
Once that was done, we used a pivot table to group the GA4 metrics by their flow names.
That’s it! We finally managed to get our GA4 metrics by Flows, and not just Flow Message Names!
Final step: Presenting the data
Once we had the data ready, we could present it in a format the client wanted.
They wanted to be able to see the GA4 metrics next to their Klaviyo metrics. So on a final spreadsheet (which is the actual report), we used another QUERY formula to bring in all the Klaviyo metrics we had pulled earlier by flows, then an XLOOKUP in an ARRAYFORMULA to match the GA4 metrics to the flows.
(For campaigns, we presented the consolidated Klaviyo and GA4 data in the same way, just without the need for the additional mapping step. any mapping needed to be done in between.)
Automating the process
So, this analysis works. You can use it to create the report you want.
The next step: automating this process, so that you don’t have to do this analysis over and over again.
Once this entire system is set up in one spreadsheet file, you can automate it by running Airboxr’s automated reports to regularly pull two sets of data onto two separate sheets on different tabs: the Klaviyo metrics, and the GA4 metrics.
When those two sheets are updated with fresh data, the final report will also be updated, thanks to the formulas linking the sheets together.
And if you need to update the map at any time, you can also use an automated report to pull in a fresh set of data on flow names and flow message names from Klaviyo.
Long story short, you can only partially solve the problem of Klaviyo UTMs not showing up in GA4, as some of them will still refuse to show up no matter what. But with some out of the box thinking, and a few spreadsheets, you can get the metrics you need - and with Airboxr, you can even automate your reporting, so you won’t have to refer back to this blog post every time you need this report done.
