9-MINUTE READ · By Supermetrics
UPD: This article tells how you can 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 I’m happy to share the next public fruit of this labor – a Paid Channel Mix template with Adwords, Facebook Ads, Twitter Ads and Bing Ads data.
To briefly recap, by far the Google Sheets connector is the only option for pulling non-Google data, such as Facebook, Twitter and Bing, into Data Studio. With Supermetrics, you can set up a feed of data to be easily imported into Google Data Studio.
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!
You may also want to add campaigns, ad groups, and even audience variables as a secondary dimension. This will let you view data split by these categories. You can add and remove dimensions in Google Data Studio when completing your graphs. You can also set up a filter in Data Studio to select which dimension value to show data for.
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.
Find the data sheet from here:
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.
Follow instructions to connect the data sheet to the Adwords, Facebook, Twitter and Bing accounts you want to report on.
In the end, click on the blue Insert Template button.
Last but not least, remember to set up scheduled refreshing to always keep your data up to date.
Don’t know how to use Custom Template? Find it out in this previous post.
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.
And remember to change field type of CTR and Conversion rate to Percent.
Then hit on the blue Create Report button on the top right corner.
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.
Then select the right calculated field in the Metric Picker
Repeat it for other charts, scorecards and data tables with missing metrics. Eventually you’ll get your own awesome-looking report!
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.