How to DIY PPC Budget Pacing Report in 10 Minutes (by Lunametrics)

BUDGET PACING · 3-MINUTE READ · By Andrew Garberson on September 27 2016

 

Saying goodbye to expensive advertising reporting tools was easy. No more sales calls. No more closed code report limitations. Way more budget for other fun tools.

But I really missed the budget pacing calculator.

Budget pacing calculators help digital advertisers meet ad spend targets each month. Companies would not have budgets in a perfect world, but most companies don’t live in a perfect world. A portion of the annual operating budget is set aside for marketing and the SEM manager gets a slice of that for digital advertising. Divide it by 12 and the monthly ad budget is born.

AdWords and other digital ad networks that set budgets at a daily level benefit the most from pacing tools. It can be difficult to meet monthly goals with complex accounts that evolve quickly. Adding new match types, pausing poor performers, launching new campaigns, and testing ad formats all influence account direction and spend. A way to pace the budget is essential.

 

The good news: You’re 10 minutes from your own in Google Sheets. Supermetrics makes it easy.

Screen Shot 2016-07-21 at 3.12.25 PM.png

Several formulas are required but no serious spreadsheet wizardry. It is easy to design and I have done most of the work for you (although feel free to talk all of the credit with your boss).

 

Start by importing cost using Supermetrics or the Google Analytics plugin for Sheets. In Supermetrics, it might look as simple as this.

 

Screen Shot 2016-07-21 at 3.43.12 PM.png

 

Now it’s time to add some formulas to either this tab or another tab that references this one. I like to use another tab so I can adjust the rhetoric, but either will work.

 

Add the formulas in row 16 to 19 to begin. Don’t worry about the data in rows 20 to 24. We will get to that later.  

 

Screen Shot 2016-07-21 at 3.54.33 PM.png

 

NOTE: Don’t use the periods at the beginning of the formulas. I had to add those to expose it for you. Drop the periods on the far left.

 

You now need to update the red formula to match your sheet and budget. The first part, “Budget!D27,” is simply referencing the cost that we imported from AdWords. The second part, “25000,” is the monthly budget. Update that to reflect yours.

 

Once those changes are made, it should look like this.

 

Screen Shot 2016-07-21 at 3.57.18 PM.png

 

The next (and most rewarding) step is visualization. I used a horizontal bar chart that compares current monthly AdWords spend to current portion of the month. The chart settings and final product look like this.

 

Screen Shot 2016-07-21 at 4.01.19 PM.png

 

Finally, let’s return to the strange “Monthly Spend” data that I placed in rows 21 to 24. I like to do a second data pull from Supermetrics to catch historical spend from previous months. I add it as an additional graph in my report to show spend over the 3 prior months.

 

 

Screen Shot 2016-07-21 at 4.02.52 PM.png

 

You’ve done it. Just 10 minutes (or so) and you have your own budget calculator. Now go show your boss and coworkers the cool thing that you built!

P.S. I’ve just shared with you this Google Sheets, which can give you a better view of my report building process. However, if you are looking for something that can be used immediately, check out this Client Budget Tracker & Alert . It automatically calculates your clients’ budgets, notifies whether you are under-spending or over-spending and gives you more time to focus on the things that matter!

 

 

About Andrew Garberson

Andrew GarbersonAndrew Garberson is Manager of Search at LunaMetrics, a Google Analytics partner and search marketing consultancy. In addition to leading the SEO and PPC teams, Andrew is an analytics junkie with a special interest in conversion optimization. For more from Andrew, find him on Twitter, his personal site or the LunaMetrics blog.