UPDATE: This article will tell you how to pull data into a Data Studio dashboard using Supermetrics for Google Sheets. Now you can create reports much more effectively with Supermetrics for Google Data Studio connectors. Start a free trial here.
In our last post, we showed how to make a Facebook Ads dashboard in Data Studio. We posted it on Supermetrics blog. After 4 hours it got shared by Google and a few hundreds of likes/retweets.
Many of you asked for more. So we decided to keep producing Data Studio templates that you would enjoy. And we’re happy to share the fruit of this labor: a Paid Channel Mix template with Adwords, Facebook Ads, Twitter Ads and Bing Ads data.
In this post I’ll explain how to connect your data feeds from multiple platforms to Google Data Studio.
To learn how to copy this template, jump directly to here.
Decide what your feeds consist of and create a feed for each platform
To get started, let’s say you want a simple overview of top level data for the last 3 years across AdWords, Bing, Twitter, and Facebook.
For this to work, you will need a separate query for each platform. On the sidebar launcher, select a data source. Then, you want to select your date range:
Calculated fields vs. pre-calculated metrics
Next, select your metrics. Performance metrics that are pre-calculated (i.e. cost per click, click through rates, and conversion rates) should be set up as calculated fields in Google Data Studio. This set-up is needed to enable your data to be accurate when you use custom data ranges as the tool can only use AVERAGE, COUNT, COUNT DISTINCT, MAX, MIN and SUM functions for your data ranges.
Setting up calculated fields in Data Studio is fairly straightforward, just make sure you import key fields such as clicks, impressions, cost, and conversions so that you can use them in your equations.
Take full control of your date range
As you may know, Google Data Studio has a neat date range tool that lets you adapt the data to a specific date range (such as in Google Analytics). In order for this to work, we need to set the data import to include a breakdown by date. Make sure you set your number of rows to allow plenty of space for this!
Make sure you tick, ‘Show all time values’ in the options tab. Your timeline graphs will make more sense!
Multi-source report from one sheet
Now begins the arduous simple task of adding in your additional data source queries. You only need to:
- Add a column to label which platform the data is coming from
- Give your metrics common labels to allow aggregation. For example, Facebook and AdWords have different names for similar metrics (clicks and link clicks). But if you want these to be treated the same by Data Studio, change the header to read Clicks. Make sure to set your queries to ‘No header row’ so that this isn’t overridden.
- After leaving space between each platform to allow for changing number of dimensions, set up your query for the additional platform. Make sure your date range and primary dimension (date) remains constant.
And you’re done! You have your data ready to import to Google Data Studio.
And if you want to see end results from the steps above, check out our Paid Channel Mix template in Data Studio.
How to copy this template for your own accounts
Copy the data sheet with Custom Template in the Supermetrics Template Gallery
Copy the URL of the template sheet:
Go to Supermetrics Google Sheets add-on, open Template Gallery, add it as a Custom Template by pasting the copied URL.
Copy the Data Studio report
Connect the copied data sheet as the data source
Create calculated fields
After creating data source, there are only four metrics imported from the sheet, clicks, impressions, cost, and conversions.
Let’s add a few more, CTR, CPC, Conversion rate and Cost per conversion.
Connect your charts to calculated fields
Right after these steps, a lot of data will appear to be missing. At this point, you may think the mission has failed.
But no worries, it just takes a few clicks to make your report work.
And the reason behind is that Data Studio can’t link the newly created calculated fields to the original calculated fields in the template despite they share identical names.
Click on a chart, and go to Invalid Metric in the sidebar.
Have ideas for a new template?
Just let us know – we’re always working on new templates. If you have an idea for new Data Studio Template that can be created with Supermetrics, please feel free to contact us.
If you haven’t installed our Google Sheets add-on yet, get it for free to make sure you won’t miss our future templates.