How to join your CRM and GA4 event data in BigQuery
This tutorial provides a step-by-step guide on how to join a simulated CRM data set with a GA4 ecommerce sample data set.
Get ready to dive deep in this step-by-step tutorial and experience the game-changing power of a seamless join as you use a simulated CRM data set to join with the GA4 ecommerce sample data set.
Connecting your CRM data with Google Analytics is a smart move for your digital marketing strategy. By bringing together data from both sources, you get a better picture of how customers interact with your website.
What is CRM data?
CRM data is essentially a digital record of all interactions and transactions a business has with its customers. It includes things like contact information, purchase history, communication records, and more.
CRM data is considered first-party data, meaning it is collected directly from the customer through interactions with the company. This data is often more reliable and accurate than third-party data, which is collected from external sources.
Having access to CRM data can be incredibly valuable for a business, as it allows them to better understand their customers and tailor their interactions with them. Especially when privacy legislation and technical limitations continue to impact the use of web analytics data.
For example, by knowing what products a customer has purchased in the past, a business can make more personalized recommendations or promotions.
Additionally, by tracking customer complaints, businesses can identify and address problems more quickly, leading to happier customers and increased customer loyalty.
Benefits
One of the biggest benefits of integration GA4 and CRM data is the ability to segment your users based on information that is available in your CRM or transactional systems.
You can analyse cross device behaviour, compare segments of known customers versus anonymous visitors, see what actions they take and how they move through the customer journey.
To join your CRM and GA4 event data in BigQuery, you will first need to export your CRM data into Google BigQuery. You can use an ETL tool or create a custom pipeline to push your data into BigQuery. Once the data is in BigQuery, you can use SQL queries to join the data from the two sources.
Example CRM data set
This tutorial will show you how to join GA4 and CRM data, using - with permission - a fake CRM data set that is created by Mark Edmonson, the author of Learning Google Analytics and creator of googleAnalyticsR (a library for working with Google Analytics data in the programming language R).
This simulated CRM data matches the sample ecommerce data set in BigQuery in some instances, using a cid that overlaps with GA4’s user_pseudo_id. This identifier will be the key to join our two data sets.
In the use case Edmonson described in his book, the cid value is acquired from a website when users log in, and is added to the HTML form as a hidden entry, subsequently being read into the CRM system.
Another popular approach is to make a CRM user id available in the dataLayer when a user logs in, so the value can be collected as user_id or user property in GA4. In that case the CRM user id would be the key to join the two data sets.
Let’s start this tutorial with exploring the CRM data set.
In this tutorial you will learn how to:
- explore a simulated CRM data set
- integrate your CRM data with GA4 ecommerce data in BigQuery by using SQL queries to join the data
