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.
I have a Google Data Studio report for a client that, among other things, has two key pieces of information it.
The number of pageviews coming from ad traffic
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:
‘medium’ contains ‘cpc’
‘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.
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’
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!