Automate Your Budget Projections Using Supermetrics

By Tanner Schroeder | @TannerSchroeder | Account Manager at Hanapin Marketing

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.

 

Supermetrics Add-On

 

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.

 

Supermetrics Launch

 

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.
Supermetrics Account Menu

 

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.

 

Accounts

 

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.

 

Metrics

 

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.

 

Supermetrics MTD

 

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.

 

Supermetrics Split By

 

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.

 

Supermetrics Tabs

 

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.

 

Set Up

 

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.

 

L7D Formula

 

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

 

Projections

 

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():

 

Today Formula

 

Then we fill in the month start with =Date(Year(Today())), Month(Today()), 1):

 

Beginning of Month

 

Then Month End with =EOMonth(today(),0):

 

End of Month Formula

 

Finally, we subtract Today from the Month Start for Days Past in Month:

 

Days Past in Month Formula

 

And Month End from Today plus One for Days Left in the Month:

 

Days Left in Month Formula

 

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.

 

Supermetrics Refresh

 

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.

 

Goal Percent Formula

 

Conclusion

 

Supermetrics Budget Projections

 

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.

Our Attendees Have Spoken - Why You Can't Miss London

At Hero Conf London, 23-25 October, we've created a schedule that can be tailored to any PPC role. In-depth, actionable sessions for the PPC Specialist and high-level strategies and tactics for the Marketing Manager looking to generate new opportunities. 

Adding Facebook Ad Block Lists

Get tips on how to add a Facebook ad placement block list and start blocking your ads from appearing on certain websites.

Optimizing Display Advertising in an Omni-Channel World

We’re teaming up with DialogTech to tell you all about display tactics you can put in place for conversion success, both online and over the phone. DialogTech’s Blair Symes and Hanapin’s Stephanie White will show you the advanced strategies that will have a big impact...