How to build the ultimate pivot table in Google Data Studio

As digital marketers, we’re often experiencing data overload. And that’s why having a clear process for analyzing your paid campaigns’ performance is critical. 

This process will help when you’re asking questions, like:

• What data do I need to look at to make informed decisions that will improve campaign performance?
• Am I attributing conversions and/or revenue to the right campaigns?

To answer these questions, pivot tables come in handy.

What are pivot tables?

Pivot tables allow you to group data in a meaningful way and extrapolate a data-driven narrative that attributes performance back to specific campaigns, products, or other key dimensions over a given period of time.

In this case, we’re going to pivot data on the campaign dimension but note that the same principles apply to pivoting data on any other dimension.

Why pivot cost and purchase metrics on the campaign dimension?

Understanding the cost of each transaction is a good starting point. However, understanding the cost of acquiring a customer, and the lifetime value of that customer, is even better.

If you can identify what specific campaigns drive high-value customer acquisition over time, you’ve hit a gold mine.

In this example, we’ll map your Facebook Ads cost data back to your customer performance data from Shopify. But do note that the same principle applies to any advertising network and any ecommerce or CRM platform.

By now, you’re probably wondering: How’s this possible given that the digital marketing ecosystem is super fragmented?

One option is to pay for a third party analytics tool. Although some are pretty good (and pricey), it can still be tricky to find a cohesive way to report on customer acquisition costs and lifetime values; especially if you want to segment your data down to specific Facebook campaigns.

What are your other options? Spending a ton of time manually piecing together data from different sources?

The good news is that you don’t need to pay top dollar for a third party analytics tool or spend hours piecing data together. Instead let’s go through a one-time set up to create an automated process using a combination of free and low-cost tools.

But before we jump into the actual step-by-step process, let’s quickly talk about the LTV:CAC ratio, the importance of a feedback loop, and why we’re building a campaign-level pivot table.

Understanding the LTV:CAC ratio

What is the LTV:CAC ratio?

The LTV:CAC ratio lets you know if acquiring each new customer is profitable, breaking even, or costing you money.

If you spent $10 and generated $10 in sales, your LTV:CAC ratio would be 1. Or, in other words, it would break even. A healthy LTV:CAC ratio is typically considered to be 3:1. So in the example above, you’d want your $10 spend to generate $30 in sales over that customer’s lifetime.

Being able to identify the LTV:CAC ratio on the campaign level is an important metric in defining the value of specific campaigns.

For example, if you’re seeing a high cost per purchase with an LTV:CAC ratio of 5 or more over a 45 day period, you might want to keep that campaign active.

Why? Because, even if the short term KPIs, cost per purchase or ROAS, look like they are ‘underperforming’, the campaign is driving high-value customers over time via repeat purchases.

Note: In this report, I’m working with gross sales. You need to establish if reporting the CAC:LTV ratio on gross or net sales makes more sense in your context. In this example, gross sales only account for marketing costs but won’t account for cost of goods, shipping, overhead etc.

The importance of a feedback loop

A feedback loop ensures that the conversion data you are seeing in the Ads Manager actually translates to valuable customers in your system. In this case, we’re working with a Shopify store to give you an ecommerce example. However, if you’re working on a Facebook Ads lead generation campaign, you’d want to join cost data from Facebook with your CRM data. This way, you can understand which campaigns are driving qualified leads as opposed to relying on simple form submissions.

Why campaign-level pivot tables?

The goal of the pivot table we’re going to build in Google Data Studio is to measure first purchase and lifetime purchase performance.

The three primary tables are:

• Table 1: Daily reporting. This is a simple campaign.level report that includes a short-term daily reporting table to measure CPA and ROAS.
• Table 2: Customer acquisition. This pivot table helps you track the daily cost per new customer and the ROAS of new customers.
• Table 3: LTV:CAC ratio. This table helps you keep track of your LTV:CAC ratio and the lifetime ROAS of each campaign so that you don’t end up making shortsighted optimization decisions.

We’re more likely to make shortsighted optimization decisions with the increased use of ad blockers and the aggressive rollout of privacy settings across browsers and how they handle first and third-party cookies. 

And with Facebook planning on shortening the 28-day attribution window, it’ll even further reduce any long(ish) term attribution that Facebook has been able to provide until now.These privacy changes will push digital marketers back to relying on last click attribution. The campaign-level pivot table counteracts these limitations and will allow you to attribute campaign value over a longer period of time, moving you past the over-dependence and limited insights of a last click model.

Say hello to pivot tables in Google Data Studio! ?

As long as you can create a common join key that ties back to a customer, you can use pivot tables to identify the LTV:CAC ratio and lifetime ROAS of any campaign.

In this use case, we’ll create a join key of date and campaign (date_campaign) to identify the cost per purchase based on a last click model. Then, we’ll pull the lifetime revenue of all customers that fall within that given date_campaign cohort.

Note: This model is not perfect and it’s important to keep in mind that the same customer can fall under more than one date_campaign cohort.

Tools you need to build the ultimate pivot table

Google Sheets: This is where we’ll build our final join table, stitching together cost and customer data with a combination of formulas.

Supermetrics for Google Sheets: Supermetrics is a big time saver and will allow us to automate the data calls for campaign cost data. With their recent release, you can also use Supermetrics to pull Shopify data. Say goodbye to messy CSV exports of customer data.

Google Data Studio: Data Studio allows us to build pivot tables for data visualization. In this example, I’ll show you how to organize and format your data for Google Data Studio and build some basic tables. However, there’s a whole lot more you can do to visualize your data once it’s been formatted for Data Studio.

Consistent UTMs: the key to being able to stitch data together on the campaign level is to have a consistent UTM structure. For this exercise your utm_campaign and Facebook campaign name need to match; once you go through this exercise you will start seeing how having additional UTM fields mapped back to campaigns, ad sets, and ads can be super valuable for data analysis.

You can manually create UTMs using dynamic Facebook parameters (although they have some limitations) or rely on the Google URL Builder. To automate Facebook UTMs and keep them consistent you can use a tool like EasyAutoTagging.

But now, let’s get started with building the report.

First, pull your data into Google Sheets

Pull your Shopify order data into Google Sheets with Supermetrics

Note: You can access the final Google Sheet here for reference. All cells in red consist of data pulled with Supermetrics and all cells in green are cells that contain a custom formula. We’ll go through each Google Sheet formula in the steps below. 

I recommend you duplicate this sheet and keep it open as a reference when we walk through each formula below.

Using Supermetrics’ Shopify connector in Google Sheets, we’ll pull the following metrics:

• Gross sales
• Total sales
• Items
• Orders

Next, we’ll split the data by the following dimensions:

• Customer ID
• UTM source
• UTM campaign name
• Date
• Account age in days

Note: If you have a large set of data, make sure to expand the # of rows to fetch.

Pull your Facebook cost data into Google Sheets with Supermetrics

Next up, you’ll want to pull the cost metric from Facebook Ads, split by date and campaign name.

Note: In the example spreadsheet, I included Google Ads data to give you a channel agnostic view of how to build a holistic LTV:CAC report. If you want to do the same, you’ll want to pull cost data from Google Ads and split it by campaign and date. The same goes for any other ad data sources.

Schedule a data refresh with Supermetrics

Under “Add-ons” > “Supermetrics” > “Schedule & refresh” in Google Sheets, you can create triggers to refresh your data hourly (requires an Enterprise plan), weekly (requires a Super Pro license), or monthly. If you don’t want to pay for a higher cost license, you can keep manually updating the report by going to “Add-ons” > “Supermetrics” > “Refresh all”.

Next, clean and join your data

The goal of this sheet is to create a final_join tab that will act as the data source when we connect the Google Sheet to Google Data Studio.

Note: All cells highlighted in red consist of dimensions and metrics pulled from Supermetrics. All cells highlighted in green are our own custom formulas. All cells highlighted in blue are reorganized columns referencing existing values.

Let’s quickly look at each tab before we jump into details:

shopify: This tab contains our raw data pulled from Supermetrics, our primary join key (date_campaign) and custom columns necessary for filtering and aggregating values.

facebook: The facebook tab contains cost, campaign, and date pulled with Supermetrics, a static column to define the source and, of course, our primary join key (date_campaign).

google: The google tab is the same as the facebook tab. It contains cost, campaign, and date pulled with Supermetrics, a static column to define source and our primary join key (date_campaign).

not_paid: this tab looks for any rows from the shopify sheet using the primary join key that don’t exist in the facebook or google tabs. In other words, it looks for any rows that are not related to paid campaigns.

total_cost: The total cost tab aggregates all rows from the facebook, google, and not_paid tabs and we create our primary join key of date_campaign.

customer_ltv: This tab pivots the shopify data on customer id with a secondary dimension of our primary join key so that we can calculate LTV and lifetime orders.

final_join: The final join tab joins our dimensions and metrics based on our final join key (date_campaign) and extrapolates the data so that it is formatted and can be used as a data source for Google Data Studio.

Let’s get started.

Merge Facebook and Shopify data using your join key

Let’s go through the formulas used in each tab in detail.

Note: Most formulas or cell references will be preceded with an =arrayformula( )Using the flexible array formula will dynamically populate rows for the entire column. This way when you refresh your Supermetrics data, any additional rows will automatically have updated formulas or referenced values without requiring you to manually paste down formulas or cell references.

shopify

Column J

• Column name: Customer ID
• Formula: =arrayformula(A$2:A)
• Description: Here we’re just referencing existing values so that columns are sorted in a way that we can use them for other formulas and vlookups.

Column K

• Column name: date_campaign
• Formula: =arrayformula(D$2:D&C$2:C)
• Description: This gives us our final join key by concatenating date and campaign name. We are not using the concat formula since it doesn’t work inside of an array formula.

Columns L & M

• Column name: Facebook Ads Filter
• Formula: =arrayformula(iferror(vlookup(K$2:K,facebook!E$2:E,1,false)=K$2:K,0))
• Description: This formula performs a vlookup and returns True if the date_campaign is present in the facebook or google sheet and a 0 (this is why we are preceding the vlookup with an iferror) if the value is not present in the respective sheets.

Columns N–P

• Column names: source, UTM campaign name, and Date
• Description: Here we are just referencing existing values so that columns are sorted in a way that we can use them for other formulas and lookups.

Column Q

• Column name: First Time Orders
• Formula: =arrayformula(if(E$2:E=0,1,0))
• Description: This checks the Account age in days column and returns a 1 if it equals 0. This way we can identify first time orders.

Column R

• Column name: First Time Order Revenue
• Formula: =arrayformula(if(Q$2:Q>0,F$2:F,0))
• Description: If the first time order is greater than 1 (also reads as if first time order is true) then return the respective value from the Gross sales column otherwise return 0

facebook

Column D

• Column name: Source
• Formula: =arrayformula(if(A$2:A=A$2:A,”facebook”,”facebook”))
• Description: Whether the if statement returns true or false the value returned will be the source value. Doing this instead of static texts allows us to embed the if statement in the arrayformula()

Column E

• Column name: date_campaign
• Formula: =arrayformula(A$2:A&B$2:B)
• Description: This gives us our final join key by concatenating date and campaign name. Again, we are not using the concat formula since it does not work inside of an array formula.

google

Column D

• Column name: Source
• Formula: =arrayformula(if(A$2:A=A$2:A,”google”,”google”))
• Description: Whether the if statement returns true or false the value returned will be the source value. Doing this instead of static texts allows us to embed the if statement in the arrayformula()

Column E

• Column name: date_campaign
• Formula: =arrayformula(A$2:A&B$2:B)
• Description: This gives us our final join key by concatenating date and campaign name. Again, we are not using the concat formula since it does not work inside of an array formula.
• Note: For any additional paid channels you would create sheets the same as I have done above for Facebook and Google Ads. Just don’t forget to add a filter column on the shopify tab for additional channels.

not_paid

Columns A–C

• Column name: Source, Campaign name, and Date
• Formula: =FILTER(shopify!N$2:P,shopify!L$2:L<>True,shopify!M$2:M<>True)
• Description: We’re going to return the source range from the shopify tab filtered down to any campaigns and sources where paid Facebook or Google campaigns do not equal True. Remember the paid filters we set for Columns L & M on the shopify sheet?

Column D

• Column name: Cost
• Formula: =arrayformula(if(A$2:A=A$2:A,0,0))
• Description: Whether the if statement returns true or false the value returned will be 0. Doing this instead of static texts allows us to embed the if statement in the arrayformula()

Columns E–H

• Column name: Date, Campaign Name, Cost, Source
• Formula: =arrayformula(C$2:C)
• Description: Here we’re just referencing existing values so that columns are sorted in a way that we can use them for other formulas and lookups.

total_cost

Columns A–D

• Column name: Date, Campaign name, and Cost
• Formula: ={facebook!A$2:D; google!A$2:D; not_paid!E$2:H}
• Description: Using this function pulls all final rows from the facebook, google, and not_paid sheet.

Column E

• Column name: date_campaign
• Formula: =arrayformula(A2:A&B2:B)
• Description: This gives us our common join key by concatenating date and campaign name. We are not using the concat formula since it does not work inside of an array formula.

customer_ltv

Columns A–B

• Column name: customer_id and date_campaign
• Formula: =unique(shopify!J$2:K)
• Description: Here we are pivoting on the customer ID with a secondary dimension of date_campaign, our common join key. This will allow us to identify the lifetime value of a customer by performing a lookup of all customer revenue and tying it back to a specific date_campaign.
• Note: Keep in mind that customer rows can appear across multiple campaigns so the customers LTV will always hold true but you cannot sum customer ltv across all rows in the final report since that will return an inflated number.

Column C

• Column name: customer_ltv
• Formula: =arrayformula(sumif(shopify!A$2:A,A$2:A,shopify!F$2:F))
• Description: The formula performs a vlookup on the customer ID and returns the total revenue for that customer. Again keep in mind that even though we are pulling unique customers they can display more than once since we are splitting the data by date_campaign.
• Note: To validate the formula filter rows in the shopify sheet down to a customer_id and compare the sum of that customers Gross sales column to the ltv value that the vlookup returned. You can do the same for Orders.

Column D

• Column name: lifetime_orders
• Formula: =arrayformula(sumif(shopify!A$2:A,A$2:A,shopify!I$2:I))
• Description: The formula performs a vlookup on the customer ID and returns the total number of orders for that customer.

final_join

Column A

• Column name: date_campaign
• Formula: =unique(total_cost!E$2:E)
• Description: Using this unique formula we are returning unique rows from the total_cost tab. Remember the total cost tab includes all paid and non paid campaigns.

Column B

• Column name: Date
• Formula: =arrayformula(text(left(A$2:A,5),”m/d/yyyy”))
• Description: With this formula we are extracting the date from column A

Column C

• Column name: Campaign
• Formula: =arrayformula(right(A$2:A,len(A$2:A)-5))
• Description: With this formula we are extracting the Campaign Name from column A

Column D

• Column name: Source
• Formula: =arrayformula(vlookup(C$2:C,total_cost!B$2:D,3,false))
• Description: We are performing a vlookup on the date_campaign column to pull in the source value for each row.

Columns E–I

• Column names: Cost, All Orders, Revenue, First Time Orders, First Time Order Revenue
• Formula:

• Description: For these columns we are using the SUMIF formula where the data_campaign is the criterion for summing each respective value from the shopify sheet.
• Note: To validate the formulas for columns E-I compare the sum of each column (cost, all orders, revenue, first time orders and first time order revenue) on the final_join sheet to each respective column on the shopify sheet. The totals should always match. If they do not then some rows are unaccounted for in either the facebook, google or not_paid tabs.

Columns J–K

• Column names: Lifetime Orders and Lifetime Revenue
• Formula:

• Description: For these columns we are using the SUMIF formula where the data_campaign is the criterion for summing each respective value from the customer_ltv sheet.

Summarizing the Google Sheets process

This should give you a final_join sheet that you can connect to Google Data Studio as a data source. Going through each formula can seem daunting but the process remains the same for a small or large data set. Once you’re familiar with the set up, the work involved to maintain the data is minimal.

Building your Google Data Studio Report

Link Google Sheets to Data Studio

Go to Data Studio and in the top left click Add data then select Google Sheets.

Next, select your spreadsheet then select your final_join worksheet and click “Add”. Make sure to select “Use first row as headers”.

Format your fields

In this step, you want to make sure all of your worksheet fields are properly formatted. For example, set cost to “Currency” and orders to “Number”.

Create a pivot table

Next, we’re going to create three pivot tables.

1. Daily performance, including money spent, all orders, and total revenue for any given day
2. First time orders only
3. Cost to acquire a new customer and their respective lifetime value

This is where the fun begins! 

We’re going to pivot each table on the campaign level. By pivoting the data this way we can quickly understand short term ROAS, the cost of acquiring first time customers, lifetime ROAS, and the LTV:CAC ratio.

Table 1: Daily performance

To get started click on “Add a chart” then select “Pivot table”.

Next we are going to want to pull in our available fields.

• Date range dimension: Date
• Row dimensions: Campaign, Source
• Metrics: Cost, All Orders, Revenue

Now we’ll want to create custom fields for “Cost Per Order (All Orders)” and “ROAS (All Orders)”.

Click on “Add metric” → “Create field”.

The formula for Cost Per (All orders) is: sum(Cost)/sum(All Orders)

Aggregation is set to “Auto” and the type is “Currency”.

The formula for ROAS (All Orders) is: sum(Revenue)/sum(Cost)

Aggregation is set to “Auto” and the type is “Number”.

Here’s what the daily performance table will look like:

Table 2: Customer acquisition

With this table, we want to look at performance with respect to first time orders only. In other words, we’re measuring the performance of customer acquisition, not all orders.

To get started click on “Add a chart” then select “Pivot table”.

Next we are going to want to pull in our available fields

• Date range dimension: Date
• Row dimensions: Campaign, Source
• Metrics: Cost, First Time Orders, First Time Order Revenue

Next, we’ll want to create custom fields for “Cost Per (First Time Order)” and “ROAS (First Time Order)”.

Click on “Add metric” → “Create field”.

The formula for Cost per (First Time Order) is: sum(Cost)/sum(First Time Orders)

Aggregation is set to “Auto” and the type is “Currency”.

The formula for ROAS (First Time Order) is: sum(First Time Order Revenue)/sum(Cost)

Aggregation is set to “Auto” and the type is “Number”.

Here’s what the customer acquisition table will look like:

Table 3: LTV:CAC

As opposed to the previous table that was focused on understanding our customer acquisition cost, this table helps us understand the lifetime value of each customer over a given date range.

To get started click on “Add a chart” then select “Pivot table”.

Next we’re going to pull in the following available fields:

• Date range dimension: Date
• Row dimensions: Campaign, Source
• Metrics: Cost, Lifetime Revenue

Now, we’ll want to create our custom fields: New Customers, Cost Per New Customer, ROAS (Lifetime) and LTV:CAC.

Click on “Add metric” → “Create field”.

We’re not creating a new customer field, rather we are just renaming the existing field “First Time Order” to “New Customers” for this table.

The formula for Cost Per New Customer is: sum(Cost)/sum(First Time Orders)

Aggregation is set to “Auto” and the type is “Currency”.

The formula for ROAS (Lifetime) is: sum(Lifetime Revenue)/sum(Cost)

Aggregation is set to “Auto” and the type is “Currency”.

The formula for LTV:CAC is: sum(Lifetime Revenue)/(sum(Cost)/sum(First Time Orders))

Aggregation is set to “Auto” and the type is “Number”.

Here is what the CAC:LTV table will look like:

Final tables

We’ve created three tables. One for short term analysis of blended performance, one looking at the cost to acquire new customers only, and lastly the money table which tells you what the value of each campaign is over time. As you can see, campaign ROAS and LTV:CAC update when you extend the date range:

Conclusion

Collecting and organizing both short-term and customer lifetime purchase data is critical when you want to make performance-based decisions about your Facebook campaigns.

This prevents you from having to guess or make preemptive decisions and instead, enables you to understand the impact of paid campaigns over time.

In the example above, we specifically talked about Facebook Ads for a Shopify ecommerce store. However, the same principle can be applied to any source of truth. Be that a CRM system for lead generation campaigns or another ecommerce platform.

Also keep in mind that the above tables pivot on the campaign level but as long as you have a common join key, you can pivot on any other dimension such as product.Did you find this report useful? How will you use the campaign pivot table to make better marketing decisions? Let us know on Twitter or LinkedIn.