Building an Adwords and Facebook Ads Dashboard in Data Studio

No one disputes that Data Studio, Google’s free dashboarding tool, produces downright beautiful reporting.

There’s a good reason it’s quickly become the queen of digital marketing dashboards.

But — let’s not pretend that it’s perfect. Far from it.

Three Google Data Studio Obstacles To Work Around

In my analytics work with agencies and startups, I’ve run into two key shortcomings of Data Studio:

1) Out of the box, it only connects to Google services and databases

Yes, Data Studio integrates directly with your Google Analytics, Search Console, Adwords and YouTube data, which is fantastic.

But, since this is a Google product, connectors for Twitter, Facebook, Mailchimp and other services were naturally left out of the roadmap.

2) You can’t compare metrics across data sources

Data Studio doesn’t allow for cross-referencing of data sources — instead keeping each one in a silo.

So if you want to view FB Ads and Adwords campaigns side-by-side in the same chart, you’re SOL.

3) You can’t connect two or more accounts of one data source simultaneously

Say, you would like to pull data from two different AdWords accounts, to compare the same metrics.

Google Data Studio allows you to pull data from only one account at a time, and in case you want to see which account is performing better you would need to build a whole new report, which requires more time and effort.

Supermetrics to the rescue

Thankfully, these shortcomings can easily be remedied with a little Google Sheets elbow grease.

It turns out that, Supermetrics and Data Studio, *when paired together together*, form a really solid working friendship.

Pairing them up allows you to:

1) Pull data from 3rd-party sources like FB and Twitter Ads into Data Studio, and

2) Merge your campaign data from multiple sources, so that you can compare apples-to-apples across all of your acquisition channels

Pulling this pairing off gives you infinite flexibility: you can pull data from virtually any service’s API, and visualize it in any way you like.

It makes you a shoo-in to be nominated as your team’s data linchpin.

Feel Frustrated with Data Studio’s Limitations? Directly Pull All Marketing Data into Data Studio Here.

Putting the pairing into action

At Coding is for Losers, we publish Google Sheets templates that allow you to quickly get up and running with Data Studio & Supermetrics.

But let’s dive deeper, and walk through how our FB Ads + Adwords Data Studio template is put together.

If you get the hang of how it’s built, you’ll be able to customize it to match with the acquisition channels you’re running at work — Twitter Ads, Bing Ads, etc.

The final product is a Google Sheet that merges your campaign-level data together, and a Data Studio dashboard that reads from the Google Sheet.

Pulling raw data

The first step is to pull raw data from Adwords, FB Ads and Google Analytics into the Sheet using Supermetrics.

This data is split by month out of the box, so that you can track campaign and channel performance over time.

If you’re familiar with Supermetrics, this step will be easy-mode for you.

The one power user feature you may want to use: scheduling your reports to auto-refresh daily, so that you always have fresh campaign data in Data Studio.

Rolling data up into one tab

The ‘Aggregated Data’ and ‘Campaign Setup’ tabs are where the Sheets magic happens.

In order to view campaign data across FB Ads + Adwords in Data Studio charts, they have to live together in the same Google Sheets tab.

So two things happen in the template:

1) The ‘Campaign Setup’ tab allows you to set parameters for each of your campaigns: a Channel, Name, and UTM parameters to lookup Google Analytics data.

2) The ‘Aggregated Data’ tab reads those campaigns, and pulls data in from each source: Google Analytics for sessions and conversions, and campaign data from either FB Ads or Adwords depending on its configuration.

If you’re not too familiar with Google Sheets formulas, there are a few key ones to note in the Sheet:

QUERY

Used to pull in all Google Analytics data that matches the campaigns you’ve set up.

=query ( ‘Google Analytics’!A1:G , “select A, B, C, D where D > 0 and (C = ‘All’ ” & concatenate(arrayformula(” OR C = ‘” & ‘Campaign Setup’!D2:D99 & “‘”)) & “)” , 1)

If you’re not yet familiar with it, put a pin in this Google Sheets query function tutorial for later.

VLOOKUP

Used to lookup campaign data (spend, impressions, CPC, etc) from either FB Ads or Adwords data tabs.

=if ( A2:A=”Facebook” , arrayformula ( vlookup( P2:P , { Facebook!A:A & Facebook!B:B , Facebook!C:G }, {2,3,4,5,6} , 0 ) ), if( A2:A=”Adwords” , arrayformula ( vlookup ( P2:P ,{ Adwords!A:A & Adwords!B:B , Adwords!C:G}, {2,3,4,5,6} , 0 )),””))

ARRAYFORMULA

Used throughout to avoid copy-pasting formulas — allows you to write a formula once, and copy it down to every populated cell.

=arrayformula ( if ( D2:D<>”” , D2:D & E2:E , “” ))

To scan through how these formulas are used in the template, you can browse through the formulas in rows 1 and 2 of the ‘Aggregated Data’ tab.

Pushing data up to Data Studio

Once that aggregation is done, connecting your Sheet to Data Studio and building charts is a piece of cake.

Simply make a copy of the Data Studio template (from the link in the Sheet), connect your Sheet as a data source: every chart or table in the template should get data from the corresponding tab in your Google Sheet document. .

If you run into problems while connecting the data sheet with the template, don’t worry: he Data Studio sidebar is a super-intuitive interface for changing up the charts and tables and corresponding data sources of the template.

I recommend playing around with it to your heart’s content, until you’ve arrived at the perfect report for your team.

If you’re looking to dive deeper on dashboarding skills, put a pin in this CIFL Data Studio tutorial.

That’s all!

With a little time spent merging together the superpowers of Supermetrics and Data Studio, you’re able to ascend into dashboard-building bliss.

In case you missed it earlier, you can make a copy of the template here.

Make it your own — these tools are designed to be playful, and to be molded exactly to your workflow. And if you need more inspiration and tips, check out Supermetrics’ articles on calculated metrics in Data Studio and how to create a Paid Channel Mix DS template.

Add new paid acquisition data sources, new Data Studio charts, and most of all: have fun!