Measure Customer Acquisition Cost by Channel in Google Data Studio

Measure-Customer-Acquisition-Cost-by-Channel-in-Google-Data-Studio (1)

Customer Acquisition Cost is an important metric to understand the return on investment you’re getting from different marketing channels.

With some marketing channels you can see exactly how much it has cost to get a conversion. However, some other channels can be more complex. If a website visitor found your company’s blog post while searching on Google and then converted, how much did it cost to acquire that customer?

A simple and DIY way to work this out is to use a combination of Google Analytics data and your own marketing spend added to a Google Sheet.

In this blog post, I’ll show you a method for calculating the cost of acquiring customers in Google Data Studio.

1. Add your Marketing Spend data to a Google Sheet

So I’ve added in some hypothetical marketing spend to a Google Sheet.

It includes a Notes column to indicate how the money was spent.

You’ll notice there is no Date information in the table. Unfortunately this means that in the Data Studio report we won’t be able to adjust the date range so that the Spend changes.

Therefore this type of reporting is best suited to looking at a weekly, monthly or quarterly overview of your marketing spend and conversions.

2. Add your Google Analytics and Spend data sources to a Data Studio report

First, add your Google Analytics data to your new Data Studio report.

Select Google Analytics as a Data Source

Then add the Google Sheet which contains your Spend data.

Adding Google Sheets as a data source in Google Data Studio

You should now have both data sources added to your report, as shown below.

3. Blend the Google Analytics and Spend data together

The next step is to blend the Google Analytics and Google Sheet spend data together. This will give you a Blended data source.

To do this, click Resource > Manage blended data > Add a data view.

We can use Channel from the Spend sheet and Default Channel Grouping from Google Analytics as the join key to blend the data.

I’ve added in a ‘purchase’ goal completion from the Google Analytics data source.

Blending together google analytics and marketing spend data to calculate customer acquisition cost by channel

When we’ve blended the data this is what our new data source should look like.

The new blended data source using data from Google Analytics and google sheets.

Click save and now we can move on to creating charts with our new blended data source.

4. Create a table showing Spend, Purchases and Customer Acquisition Cost

Using our new blended data source, which I’ve named Spend & Purchases, we can now create a table displaying this data.

Shown below is what the table data tab should look like.

It should have the following dimensions and metrics;

Dimensions: Channel, Notes
Metrics: Purchases completed (SUM), Spend (MAX) and a new calculated metric CAC per purchase

A data tab in data studio to work out Customer Acquisition Cost

Customer Acquisition Cost per Purchase shows the amount of money spent on marketing for each purchase on the website.

The calculated metric CAC per Purchase is created as follows.

MAX(Spend)/SUM(Purchase Completed (Goal 1 Completions))
Showing how to create the Customer Acquisition cost per purchase in the blended data source

Now that we’ve created our CAC per purchase metric, we can create the table.

A table showing channel, spend, purchases completed, and customer acquisition cost per purchase in data studio

5. Create a chart showing Spend, Purchases and Customer Acquisition Cost

Next, we can create a line and bar chart showing Spend, Purchases and Customer Acquisition cost per purchase.

We set up the data tab in the following way;

Dimension: Channel
Metrics: Purchases completed (SUM), Spend (MAX), and CAC per Purchase (created again using the same formula shown above).

We can have Spend and Purchases as bars and CAC per purchase as a line on the chart. Have Spend on the right axis and Purchases and CAC per purchase on the left axis.

A breakdown of how the data and style of your chart should look like

Our finalized chart should look similar to what is shown below.

A chart showing Customer Acquisition Cost per purchase, purchases and spend per marketing channel

6. Looking at the Data Studio report

And now we can talk a look at our completed Data Studio report!

What does this tell us? It allows us to see that the cost of acquiring a customer via Organic traffic is $11.68 while the cost of getting a customer through Paid Search is a whopping $57.69!

This suggests a number of possibilities, such that our blog posts are really effective at converting website visitors, our paid adverts are useless at converting website visitors or a number of other scenarios.

Anyways, I hope you found this useful. Obviously this is a simple overview and doesn’t take into account a lot of the complexities of digital marketing and attracting customers to your site. But it does show how you can use Data Studio to report on your data and as a starting point for analysis of the data.

My Other Blog Posts

If you liked this article you might like my other blog posts.

I’m Michael


I’m a digital analyst with seven years experience in digital marketing. I’m skilled in web analytics, tag management and data visualization.

Get in touch with me!
LinkedIn
Twitter
Email

I'm Michael
Michael

Author Michael

More posts by Michael

Join the discussion 2 Comments

  • Hi Michael. I see that you updated your blog post based upon our conversation on Twitter. Nice job. I think it adds some more context and understanding to how the data works with this particular data blend.

    • Michael says:

      Hi Yehoshua – Thanks for the advice, I think its better to make it clear to readers that the example I give can only be used for a static time period.

Leave a Reply