July 17, 2017
I don’t know about you, but I am always looking for ways to make my job easier. Anything that makes my day-to-day tasks take a little less time so I can focus more on learning and strategizing PPC is good to me.
Recently, I began to utilize Supermetrics, an add on to Google Sheets. Supermetrics can automatically pull in data from across your PPC platforms, (AdWords, Bing, Yahoo, Facebook, etc.) letting you analyze the data all in one place. If you have never used Supermetrics before, I suggest giving it a go by downloading it as an Add-On in your Google Sheets account, as seen below. There are so many uses for Supermetrics, but for myself, the latest use I have found is for projecting budgets.
Manual Projections
We’ve all been there – about to report to our client or boss and needing to figure out how on-pace we are for spend. Maybe you pull your numbers manually across your platforms and project in Excel, maybe you even have a program where you only have to paste the data in. With Supermetrics, you set it up once and it automatically pulls the data in daily. You can see projections across all your advertising platforms based on the last 7 days and month-to-date data without having to do anything but open a Google Sheet.
If you’re wondering how to get this done, I’m here to show you.
Supermetrics Budget Projection Steps
We are going to be pulling cost by account for Google, Bing, and Facebook for the last 7 days and month-to-date data. We will then combine the data into one sheet for easy-to-see projections and insights.
Launch
The first step is to open Supermetrics by launching the sidebar. There you will input the data you need for your projections. You do this under the Add-Ons tab in Google Sheets.
Data Source
We would then need to choose our data source. We will start with Google. This is where you will switch between your platforms as you move to Bing and Facebook as well.
Account
Then we choose the account we will be working with. When building out the projections for Bing and Facebook, be sure to pick the correct account for accurate projections.
Metrics
Next, you choose your metrics. In this case, we really only need cost but we will pull in the other basic metrics as well.
Date Range
Here you will choose your date range. For this data pull, we are choosing this month to yesterday. When pulling last 7 days, this is where we would change the date range.
Split By
The split by section is where you choose how to sort the data. I recommend splitting by campaign. While we are doing account projections, it is nice to have the data by the campaign in case you want to get more granular with the data in the future.
Completing The Data Pull
Once you have your settings in place you launch Supermetrics and it will pull the data in. You now have month-to-date data by the campaign for your Google account! For each new query, you will want to have a new sheet created, as seen below. By having each data pull on a new sheet you will ensure the data does not overwrite itself incorrectly and it makes automating the report much easier.
Gathering data for Bing and Facebook is very similar to Google. You only need to change the Data Source, make sure you are on the correct account, and then change the date range when pulling for last 7 days versus month-to-date. The only major difference between the platforms is for Facebook. While spend is the same, ensure you have the proper conversion metric in place; Be it leads, website conversions, etc. Once pulled, we move on to consolidating the data into one spreadsheet and setting up the projections.
Projection Setup
For setting up the projections, we first need to create a data table. In the table, we will have the projections, our goal cost, percent to goal, and then the last 7 days and month-to-date cost.
Adding Supermetrics Data
Next, we sum the data from the sheets we’ve created. In the L7D column, we sum the last 7 days data, and month-to-date is summed in the MTD Cost column. We want to sum down the entire cost column in case we add new campaigns in the future.
After that, we add the projection formulas. The Normalized Projection formula is:
MTD Cost+((L7D Cost/7)*Days Left in the Month)
And the MTD formula is:
(MTD Cost/Days Past in the Month)*Days Left in the Month+MTD Cost
Automation
Since we want the report to be automated, we need a few formulas to fill in the days of the month automatically. First, in the Today section use =Today():
Then we fill in the month start with =Date(Year(Today())), Month(Today()), 1):
Then Month End with =EOMonth(today(),0):
Finally, we subtract Today from the Month Start for Days Past in Month:
And Month End from Today plus One for Days Left in the Month:
We will use the Days Past and Days Left in our projection formulas and they will update daily. The final step for automation is for Supermetrics. For that, we go to any tab pulling in Supermetrics data. Then select Supermetrics under the Add-Ons tab in Google Sheets, and click on the schedule Refresh and Emailing tab. There you can choose when to refresh the data and if you want an email reminder for when the refresh triggers. I have my data set to update at 7 AM every morning, but you can choose the time that works best for you.
Finalizing The Data
Once you have completed the above steps, the projection sheet is done. For additional insights, one the thing to do is to add Goal Cost and % to Goal columns. With these, you can easily see how close you are to your overall account projections. The % to Goal column is calculated by dividing the normalized projected cost from the Goal Cost.
Conclusion
With the goal set up completed, you now have an automated projection report that updates daily. While there are several steps, once you have the process down you’ll be able to quickly set this up for all your accounts saving you time in the long run. Supermetrics is a great tool for automating daily tasks, and this is just one example of how to utilize the tool. Do you have other uses for it already? Let me know how you are using Supermetrics by reaching out to me on Twitter.