Automating reports with Data Studio and Super Metrics
If you’ve read my last blog you know that Data Studio and Super Metrics are great tools to automate reporting. Unfortunately, the support docs and tutorials can be a little intimidating when you are a beginner. There are so many features in DataStudio that it can be hard to figure out what to focus on to be productive. In this blog I’ve created a brief how-to on the features I think are most important to get started.
Creating a budget pacing table is a good exercise to learn the basics because making one involves several useful skills that can be applied to many other reporting builds:
- Using Super Metrics to pull Data into Google Sheets
- Creating tables in DataStudio
- Connecting DataStudio tables (or anything else) to a data source
A budget pacing table is used to ensure your spend is on pace to your monthly goal. It simply divides your budget by the number of days in the month so that you can ensure that you are on track with your monthly goal Note that this can be used for any time period if you have shorter or longer budget windows.
A budget pacing table, pulls the spend data automatically and on a daily basis, to show you if you are over or under your goals on any given day.
Check out the example below:
As you can see, you can use this to track the spending of multiple platforms.
Having this table on DataStudio allows you to easily stay up to date on how your account budget is pacing, so that you can make changes to not spend over or under your target budget. You can also share this report with your client should they find it useful for their own planning.
It actually starts outside of Data Studio, on Google Sheets. Data Studio can connect to Google Sheets to pull data that can be set to refresh daily. This allows you to take advantage of the features within Google Sheets to make formulas so that your table not only has the spend data, but can calculate whether you are over or under pacing on each day.
Here’s an example of one of ours in action:
We even use conditional formatting to highlight whether we are over pacing and need to slow down (red) or are under pacing and need to speed up (green).
Before you get started, you will need to go to supermetrics.com and try the free trial for Google Sheets and download the SuperMetrics add-on by clicking this link that will take you directly to the web store. Simply click the Free button and the add-on will be added to your Google Sheets account. If you find supermetrics as useful as I do, you’ll definitely want to buy a monthly or annual subscription.
Creating the budget pacing table is simple, there are 3 main steps
1. Pull the Data from the platforms using SuperMetrics
2. Fill in your budget and divide the pacing to get your daily goals.
3. Make equations to calculate totals and the delta (how you are pacing)
Below I’ll show you how to create this table in 3 steps, then afterwards how to easily connect it to DataStudio so that it is client ready. Let’s get started.
From within a blank Google sheet you can use Supermetrics to pull Adwords information. To do this, start by launching Supermetrics by clicking on “Add-ons,” then “SuperMetrics,” then “Launch:”
After clicking “Launch” the sidebar will open up, and the next step is to simply fill the following information: Sata source, Select accounts, Select dates, Select metrics, Split by, Filter, and Options. I like to have: “no header rows” checked on my options menu so that I can customize the header as needed. Then click “get data to table.” The end result should look something like this:
After creating this you simply fill in your budget on row C, for this example I am going to choose $10,000 for the month and divide it by day. We are not running ads on weekends so after inserting the budget, the Google Sheet looked like this:
The final step to create the budget pacing table is easy, just fill in equations to calculate the Totals and cost – the budget for the Delta.
You will be using =SUM(B2:B32), =SUM(C2:C32), and =SUM(D2:D32) For the totals.
You will also be using =IF(B2=””,0,B2-C2)on D2 for the Delta. To create the Delta you’ll want to copy this formula in each row of your table, you can do this by simply double clicking the blue square on the bottom right of the cell with this formula once it’s selected. Or you can click-and-drag on the lower right corner down to the end of the table.
And that completes your live pacing table in Google Sheets!
Adding this table to Data Studio is fairly simple as well. It can be broken down into three parts:
1. Create the table
2. Connect the data & Ensure settings are correct
3. Format table
First: Create a new data studio report. Then choose the Table option from the dropdown menu named “Add a chart:”
To connect your Table to the Pacing Google Sheet data, click on the option under Data Source, and afterwards select CREATE NEW DATA SOURCE:
Then scroll down and select Google Sheets.
.jpg)
After that, ensure the right Google sheet is selected and click Connect or Reconnect on the top right corner:
When your sheet is connected you will have to set the data type to Currency (USD – US Dollar), because it defaults to Number.
Also, make sure to change Date to Text so it doesn’t get an error for the Totals row. The setup should look like this:
Check to make sure your data matches your data table on Google Sheets. Then click ADD TO REPORT in blue on the top right.
By dragging and dropping the available fields to different locations you will be able to manipulate the table.
And to remove the number columns on the left side of the table click on STYLE:
And unselect the Row numbers box under Table Body.
Final Note: if you want to fix the nulls on the spend column that appears for days you haven’t spent you can add an if null formula, but the easiest way to fix this is to just go back to your Google Sheet and fill in 0s for the days that you have not spent.
This concludes our DataStudio and Supermetric how-to. With the skills you learned here you should be able to do most reporting easily.
Hope it helped
-Chris
