How to Merge GA4 Data with Campaign Manager in BigQuery

[agarcia1]

Hello everyone,

I'm facing a challenge with Google BigQuery and I need your expertise. My current project involves combining Google Analytics 4 (GA4) data with that from Campaign Manager. Both data sets are already in BigQuery, transferred directly, but my challenge is in how to properly join them.

Has anyone worked on something similar? What I'm looking for is to create a table that integrates GA4's campaign performance data with the data from Campaign Manager. The key question is: What field or variable could I use to effectively join these two tables?

Any shared experiences on this topic would be of great help, especially examples of SQL queries in BigQuery that have been effective in this type of task.

Thank you in advance for any advice or suggestions you can provide.

Thank you for your time and help!

[fgarcia-1637593]

For Google products (integrated with CM360) you could use their respective IDs (Campaign, Adgroup, etc). For other channels, you will likely need to rely on information sent via the UTMs (found in the landing url). Usually most, at minimum, send the source/medium in the URL that can be used, but usually, it ends up only giving you the ability to join data at a very high level. For more advanced cases, including the following UTMS in the URLs:  utm_id, utm_campaign, utm_campaign and date (along with your standard source/medium) can go a long way to help in joining data from these platforms. 

Also, GA4 has a Product link option for floodlights with CM360, but there are some caveats to keep in mind: https://support.google.com/analytics/answer/12325075?hl=en

The tricky part is that nothing is retroactive for both platforms, and if they haven't included any of these, it will be very challenging to combine them at a deep level.