10.3. Data Blending: Data Enrichment

Imagine you have an online business that acquires leads, and those leads later become customers. You use Google Analytics for web tracking and a CRM to track customer data. When a lead is captured through a form on your landing page, you store two additional pieces of information: the Google Analytics Client ID and the source medium for that session. This information is then passed to the CRM.

In Google Analytics, you have data on the number of sessions, transactions, and revenue for each client. You want to blend this data with your CRM data to understand how much revenue and how many transactions you're getting from each original source medium.

Blending Data for Enrichment

To blend the data, you perform a simple left join on the Client ID. This process enriches the left table by adding the name, email, and lead source medium from the CRM table.

You could have dropped the unnecessary columns (username and email) from the blend table and only kept the source medium column. This is just for demonstration purposes.

Now that you have this data, you can create a table that answers the question, "How much revenue and how many transactions did we get from each lead source medium?"

By removing the unnecessary columns (username, email, and client ID) from the table, the data will automatically aggregate based on the metric aggregation types (sum).

Adding Revenue per User

Suppose you want to add a column for revenue per user. You'll need to create a new custom field that calculates the sum of revenue divided by the count distinct of Client IDs. This will provide you with the revenue per user for each lead source medium.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn