PPC Reports. They are 100% necessary if you provide paid advertising as a service. But they can be time-consuming to create and frustrating to maintain.
These problems are especially true when your reports cover advertising on multiple different systems… like Facebook Ads, Google Ads, and Bing Ads.
Trying to manually consolidate all your data into one report where the numbers add up is a giant headache no one needs. Thanks to Google Data Studio (GDS) you don't have to spend hours trying to hand-stitch your PPC reports together.
Instead, you can use GDS and third-party data connectors to create…
Automated PPC reporting!
In the video and post below, I am sharing how I created my automated, consolidated Google Data Studio PPC report with you. I'll also show you how I built the consolidated PPC report. And, I'll share a massive shortcut to consolidated reporting I discovered after I completed my report.
The evolution of PPC Reporting – chasing the dream of automated PPC reports
Before I share how to build a consolidated PPC report in Google Data Studio, I'd like to offer a little history on my reporting evolution. I shared this historical context during a presentation at PPC Hero Conference 2019, and I saw a lot of heads nodding along in the audience. So I thought you might also enjoy a little background on PPC reporting in the 21st century.
If you're not up for bonding over PPC reporting failures and triumphs, feel free to scroll down and click the link to grab my report template. But if you're like me and you've been “chasing the dream” of automated PPC reporting for a while, then you'll appreciate this prologue.
PPC reporting in the 21st century – the trials and tribulations
As long as I have been providing PPC services (since 2006), there's always been one constant thorn in my side. Reports.
Reporting is an absolute necessity if you provide PPC services. More often than not, reports are how your clients see all the results your advertising is producing.
The standard reporting equation in the PPC world works like this:
Reports = Informed clients = Continued budget for your services.
Caveman reporting – Screenshots
Back in 2006, when I first started sending my clients PPC reports, they looked something like this:
That screenshot represents PPC reporting circa 2006. A single screenshot was combined with a verbal report, something like “Hey, your campaign had 156 clicks, 12,291 impressions, and .64% conversion rate.”
Not very insightful.
Medieval PPC reporting – Excel charts
It didn't take long for me to evolve and realize I needed to do better than screenshots and inconclusive statistics.
So I stepped my game up and started creating my more comprehensive reports in Excel. I would copy all the data I needed from Google AdWords and Microsoft and paste it into a spreadsheet. Then I would work with the data manually to produce a chart that looked like this:
Now, this chart may not seem like much to you. But at the time, I was beaming with pride from this effort.
I also improved my analysis (but not really) by letting my clients know that big blue lines meant their campaigns were doing well.
Stepping into the light – Automated PPC reporting version 1.0
The next iteration of trying to make reporting easier involved automation. At that time, my agency's PPC business had grown to the point where reporting was almost a full-time job.
So we hired someone to build various databases we could use to create our reports. We used Excel until we maxed out the row limits. Then we tried Microsoft Access. We eventually built a proprietary reporting system using SQL server.
BUT, then the end result was usually the same. When it came time for reporting day, the system would break, and we would pour hours into building our reports manually.
Dark times – Building a data warehouse
Next, we moved to build an internal data warehouse for marketing reports. I am not going to spend a lot of time telling you how difficult and costly it was to create a Data warehouse. I'll just sum this learning up by saying… It did not make reporting easier.
The elusive dream – Automated monthly reports
Until recently, the dream of automatic reporting was just that, a “dream.” Why? Because automations break. Our automated reporting systems would usually break at the most inconvenient times.
Now you can see why I grew to hate reporting…
That is until Google Data Studio introduced third-party data connectors.
Is Google Data Studio the solution to automated monthly PPC reporting?
When Google introduced third-party data connectors into Data Studio, I started to dream of the possibilities.
You already know that Google Data Studio is a free, sophisticated reporting system we can all use with no fancy coding or costly data warehouses required. Connect Data Studio to your marketing platforms, and you can build a live dashboard that automatically reports on your latest results.
Add in the ability to connect with 30+ advertising platforms using Supermetrics, and you can report on Facebook, Bing, and Google Ads in a single consolidated report.
Of course, Data Studio still has its quirks, especially with third-party connections. And I wouldn't say we have arrived at the goal of comprehensive automated reporting yet. But you can see we are getting close based on what I was able to build using Data Studio and Supermetrics.
Building an Automated Consolidated PPC Reporting Dashboard in Google Data Studio
In the interest of sharing how I created an automated PPC reporting dashboard that combines data from Google Ads, Facebook, and Bing, I've outlined the steps I used to build this dashboard in the remainder of this post.
So if you want to see how the sausage is made, follow along. If you just want to download the dashboard (like I mentioned before) click the link below to join Data Studio Mastery.
Step #1 – Go analog and whiteboard it out
Before I design a new report, I always start with the trusty whiteboard. Why not just jump into Data Studio and start building? Because tinkering with the charts and graphs into Data Studio can suck up hours of your time.
I've found that whiteboarding your desired metrics and design is the quickest and most efficient way to get your report concept in place.
Step #2 – Add the tools you need to your tool belt
Data Studio is the primary tool we need to build our automated PPC report. But sometimes one tool isn't enough to get the job done.
(Kind of like when you put together IKEA furniture. They tell you that all you need to get the job done is that little Allen wrench. But come on! Try putting together a STUVA without busting out a hammer or screwdriver!)
Enter Supermetrics
While Google Data Studio can get you most of the way, the complementary tool you need to create an all-in-one PPC report is Supermetrics.
I am a big fan of Supermetrics because not only do their data connectors help me automate the connection to platforms that Data Studio doesn't natively connect to; they have an excellent team in Helsinki who is at the cutting edge of data liberation through their connector products.
Without Supermetrics, you can't automagically pull data from Facebook or Bing (unless you want to code your own data connectors or use one of a few other connector options).
Step 3 – Play in the sandbox
Next, it's time to jump inside Data Studio and start building the elements of your report. But you don't want to slow yourself down with design considerations until you're confident you can get the data you need into your report.
So at this stage, I recommend using what I refer to as a GDS “sandbox.” The sandbox is a blank reporting template where you only play with data, not the formatting.
I used a GDS sandbox to start building my automated PPC report. Experimenting with data blending and creating calculated metrics in my sandbox helped me see what was going to be possible in my final report.
During this experimentation, I ran into quite a few issues, which I'll show you how to avoid in upcoming steps.
One word of warning. You can't easily copy your blended data source into a new report. So once you're sure you can get at the data you need, move on from your sandbox and start building the final draft of your report.
Step 4 – Put your design template in place
Putting your design in place will help you see if you can re-create the whiteboard version of your report in Data Studio.
All my Data Driven reports follow the same design theme. For this step, I copied our Year-in-Review Website Marketing report and built my new report with a copy of that format.
Step 5 – Connect to your data sources
As always, when you set up a new report, you need to connect to your data sources. Using the native data connectors is an essential part of working in Data Studio. If you need a little more guidance on this step, check out my 4-part, free Google Data Studio Tutorial. Or if you want a comprehensive GDS guide, you can join my complete Data Studio Mastery Course.
The Consolidated PPC Report starts with the native Google Ads connector and then mixes in the premium Supermetrics connectors to connect to Facebook and Bing Ads.
Step 6 – Add your blended data
Data blending is how you create a single visualization or report in GDS using multiple data sources. If we want to see data from three ad platforms – Facebook, Google, and Bing in one chart or scorecard, we need to blend data.
When building our Consolidated PPC Dashboard, I discovered the hard way that dimensions from Google Ads do not always play nicely with Facebook and Bing.
To save you from the data blending frustrations I suffered, let me share what I learned.
How to blend data from Google, Facebook, and Bing into a single data source
To blend data, go to the resources menu then select manage blended data.
Next, select “Add Data View.”
Then add the data sources you want to blend.
Keep in mind, the data sources you're blending must have a dimension in common which you can use as a “join key.”
For example, when blending Facebook and Bing Ads data, you can use the Year & Month dimension as your join key.
Things get a little trickier when you bring Google Ads into the mix. Google's API naming conventions are different from the other two platforms. You need to use the dimension Month from Google Ads to make this blend work.
One thing I learned the hard way… Your consolidated data may break if you try to join Google using the “Day” dimension with Facebook and Bing's “Date” dimension. So be careful out there, folks, and always be sure to verify your data against the original source.
Since most reporting is monthly, I decided to stick with months as my join field.
Naming conventions are important
As you're blending your data, make sure to use naming conventions which identify your data source. For example, I'm using the following naming conventions for impressions: IMPR (FB), IMPR (GOOG), IMPR (Bing). If you forget to name your metrics, things will get very confusing when you move on to your GDS workspace.
The things you learn
Just a quick word to the wise… if you're looking for your Facebook Cost data, it's called “Amount spent.”
I “spent” a reasonable amount of time searching for Facebook's cost metric before I realized this nuance.
Step 7 – Add your calculated metrics
Using blended data to create calculated metrics is a feature that Google recently added to Data Studio. Without that capability, this report would not have been possible.
Here's an example of how this feature works. To create a scorecard that combines all your impressions for Facebook, Google, and Bing Ads, you can use the following function:
IMPR (FB) + IMPR (Bing) + IMPR (GOOG)
Here are some of the other calculated metrics I used with the blended data in this report.
Consolidated CTR = Clicks (FB) + Clicks (Bing) + Clicks (Goog)/IMPR (FB) + IMPR (Bing) + IMPR (GOOG)
ROAS = (((Conv (FB)+Conv (GOOG)+CONV (Bing))*300)-(Cost (FB)+Cost (GOOG)+Cost (Bing)))/(Cost (FB)+Cost (GOOG)+Cost (Bing))
In the Return on Ads Spend (ROAS) calculation, I chose $300 as my average conversion value. If you copy this calculation, you'll want to replace that number with your average conversion value. Not sure of your conversion value? Check out our Google Ads Budget Calculator or our Facebook Ad Budget Calculator for more insights.
Step – 8 Add charts and depth
To finish off the consolidated PPC report, I added a couple of combo charts to our Dashboard.
I also included three additional pages. These pages break out results from Google, Facebook, and Bing Ads into individual reporting dashboards where they can easily be customized.
What I learned from building the ultimate, consolidated, automated PPC report
Here are three big lessons I learned while building the consolidated PPC Dashboard.
- Every platform has different naming conventions. Figure out which dimension you can use a your “join key” using the platform APIs before you try to blend your data. Otherwise, the data might not blend correctly, and you'll spend hours trying to figure out why your calculations are off.
- GDS is still a bit buggy at times. If none of your metrics are displaying, refresh your workspace to see if you can reset the system.
- If someone else figures out a better way to get the solution you need, save yourself hours of frustration and borrow their solution.
- Literally just a few days after I built the Consolidated PPC Dashboard I was reminded of the Supermetrics Ad Data + Google Analytics connector, which made life sooo much more manageable.
Unless you're a reporting masochist, use the Supermetrics Ad Data + Google Analytics connector to consolidate you PPC data
The Ad Data + Google Analytics connector essentially blends your PPC Data for you, so you don't have to go through the arduous process I described in this post.
All I have to say about this is that while I love you Supermetrics, seriously, you could give a guy a heads-up when you release this brilliant time-saving tech!
Armed with the new technique, I plugged the Ad Data + Google Analytics connector into the Consolidated PPC Dashboard I created. This connector works great. The dashboard is much easier to use with the Supermetrics connector in place. And my only regret is that I wasted so much time without this connector in version 1.0 of this report. I could have saved myself 5+ sweaty hours otherwise.
Why bother with data blending when you can use a connector?
You may be wondering why I delved into data blending + calculated metrics in this post when you can use a Supermetrics connector to consolidate your data. Well, there are many other use cases for data blending. So, I wanted to share my success and mistakes in the hope that you might learn from my experience.
Also, I know that despite my warning, many of you will be tempted to DIY your dashboards and calculate your own metrics with blended data. For those of you who are reporting masochists, good luck with your PPC data blends! Hopefully, this post saves you time along the way.
You can download my Consolidated, Automated PPC Report Template
And for those of (who like me) are sick spending hours and hours building reports, you can download my Consolidated PPC Report when you join my Data Studio Mastery program on Data Driven.
Comments or questions
Alright, let's hear it! Bring on your comments, questions, and criticisms. And while you're at it, share what's in your PPC reporting. We're always up for improving our report templates. So let me know what you think.