10.5. Data Blending: De-Aggregation

This lesson will cover an exciting topic: using left join to de-aggregate auto-aggregated metrics. You might wonder why this would be useful, but let's start with a simple question that's difficult to answer: What was our average revenue for source medium from Google Analytics over the last date range (last month, last quarter, or last seven days)?

Breaking Down Aggregated Metrics

To answer this question, we need to break down an already aggregated metric and then calculate the average. This is not possible with auto-aggregated metrics coming from a data connector like Google Analytics. When you apply revenue to a scorecard, the metric type is set to auto, and you can't choose to average it. The reason for this limitation is that Looker Studio doesn't know the appropriate breakdown for calculating the average (e.g., source medium per day, user type, channel, device, browser).

De-Aggregating Metrics with Data Blending

To calculate the average, we first need to decide which dimension we want to de-aggregate by (e.g., source medium, day). One way to do this is by using data blending. In this example, we'll de-aggregate by source medium, as that's what the client asked for.

We'll start with a table from Google Analytics showing only the source medium without a metric. This table will list all source mediums seen in the selected date range. We want to look up the actual revenue for each source medium using a left outer join. The right table should be the revenue per source medium.

The blended table will show the revenue for each source medium that had revenue, and null values for those that didn't. The resulting blended table is no longer connected directly to Google Analytics, and the metric type will be set to sum instead of auto. This allows us to perform calculations on the individual values.

Now that we have access to the breakdown, we can choose other types of aggregation, such as averaging the values. The final result will give us the average revenue per source medium during the selected date range.

Alternative De-Aggregation Method: Extracted Data Sources

Another way to de-aggregate auto-aggregated metrics is by using extracted data sources. This method is easier to implement and doesn't require creating a blend. With extracted data sources, Looker Studio extracts and de-aggregates the data for you. However, this method only updates once a day, so it's not ideal for real-time data analysis.

In summary, data blending is a powerful technique for de-aggregating metrics when working with live connections, while extracted data sources are a convenient alternative when you can accept a one-day delay in your data.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn