Blog

Wow, that’s a super specific title, isn’t it? Well, this is a challenge that has been bugging me for quite some time, and I finally solved it! As such, I felt it warranted a blog post in the hopes that it could help others.

The Challenge

I have a Google Data Studio report for a client that, among other things, has two key pieces of information it.

1. The number of pageviews coming from ad traffic

2. The number of pageviews coming from organic traffic

This is easy enough to display using filters. I created the following filters and applied them to each field above, respectively:

1. ‘medium’ contains ‘cpc’

2. ‘medium’ does not contain ‘cpc’

This gives me the numbers I need. Awesome. Here’s the challenge:

• I want to show the proportion of all the pageviews that the ad traffic is responsible for.

In other words, I want to show ‘Pageviews from ad traffic’ divided by ‘All Pageviews’ (i.e. no filter). This proved very challenging as I essentially wanted to divide a field by itself, but with the first instance having a filter. Tricky. But I persevered and found a solution.

The Solution

I ended up using the following steps to create the scorecard field I ultimately wanted:

• Duplicating the ‘Pageviews’ field in my Google Analytics data source

• Creating a blended data set

• Creating a custom field

Let’s go into greater detail on each of these steps.

Duplicating the Pageviews field

Here’s what to do:

• In the Google Data Studio menu, go to Resource > Manage Added Data Sources

• Click ‘Edit’ next to your Google Analytics data source

• Find the ‘Pageviews’ field, click the three dot icon next to it, and select ‘Duplicate’

• Rename the new field from ‘Copy of Pageviews’ to ‘Pageviews from CPC’

This is just a copy of the Pageviews field, but it’s important that we give it a unique name. If we don’t, you won’t be able to tell your two Pageview fields apart when it comes time to build your custom field, and, in fact, you won’t be able to create the custom field at all.

Blend the Data

The easiest way to do this was, for me, to select both of the Pageview scorecards that were on the page, right click, and then choose ‘Blend data’. There are, however, other ways to create a blended data set, but this is one of the easiest.

When you select both scorecards and choose ‘Blend data’, you will get a new scorecard, called ‘Pageviews’, using a new blended data source. Here’s what you do next:

• Edit your new blended data source

• In the first data source, choose a join key of ‘Date’

• Add in the duplicated metric that you created, ‘Pageviews from CPC’

• Apply your filter ‘medium contains cpc’

• In the second data source, add the metric ‘Pageviews’

• Rename your blended data set to something more memorable

• Save your changes

Create a Custom Field

• Select your newly created ‘Pageviews’ scorecard

• Click on the ‘Pageviews’ metric in the right column

• Click on ‘Create field’

• Give it a name

• Enter the following into the Formula section:
  sum(Pageviews from CPC) / sum(Pageviews)

• Set the ‘Type’ to ‘Percent’

• Click ‘Apply’

Boom! Done. You should now have your scorecard that shows the proportion of Pageviews from ad traffic (i.e. CPC medium only) relative to all Pageviews.

Now go forth and blend some data, friends!