Combining Google Sheets Add-Ons for Optimal Budgeting

Within Google Sheets there are tools that are really rarely used despite the power behind them. You can find these tools in Add-ons. There is a plethora of them available, but the one I use the most is the Supermetrics add-on. Supermetrics has the capabilities to bring data in from all platforms, and from there you can make the reports you’re looking for. But it doesn’t stop there. I use Supermetrics for things such as: Bidding Processes, Budgeting Processes and even projecting lag revenue.

There is plenty of different optimization docs, reporting docs, etc. that you can create by using advanced SUMIF and Data Validation tactics along with the utilization of Supermetrics.

Taking that one step further, what if we combined the use of Add-ons in order to create one powerful tool that is easy to use. The tools we’ll be speaking to today include, Supermetrics as mentioned above, and the OpenSolver

Another tool often used at Hanapin Marketing, specifically by the Analyst team, is the Excel Solver. The Solver is often used to maximize Leads or Revenue (for eCommerce clients) with the budget at hand by using Impression Share data in order to analyze the growth capabilities side-by-side with performance. Jacob Fairclough was the mastermind behind how you get the most out of the Excel Solver in the Online Advertising world. The Analyst work together to continue evolving how the Solver is used – and help to show the PPC world how to best use the Excel Solver in order to max out revenue.

One of the biggest annoyances with using the Excel Solver is assuring all of the information needed is downloaded and in that right order before getting everything setup. So, the thought here was: How Can we eliminate that Step for the Team?

The answer? Supermetrics and the use of the Open Solver directly located in Google Sheets.

Step 1 – Bring Data in From Supermetrics

The first step to this was to assure all the proper metrics are being brought in through Supermetrics. You’ll need to bring in the Campaign Name in the “Split By” section, and in terms of metrics the following metrics would be necessary:

• Clicks
• Impressions
• Cost
• Conversions
• Conversion Value
• Lost IS Due to Rank
• Lost IS Due to Budget

Step 2 – Setup the Solver Information

Once this is in place, you setup the Solver information – similar to what was shown in the Excel Solver posts seen on PPC Hero. In the advanced version there are two separate maxes: Est Max Spend w/ Current Bids and Est Max Spend w/ Bid Increases. This gets a little complicated but below shows the columns the Solver would entail:

Estimated Max Spend: Spend/(1-Lost IS Due to Budget)

Estimated Max Spend Increase w/ Bids: Clicks Available Due to Bid Increase*CPC Demands w/ Bid Increases

Total Max Spend: Estimated Max Spend + Estimated Max Spend Increase w/ Bids

Allocated Budgeted Spend: 0

Allocated Bid Increase Spend: 0

Total Allocated Spend: Allocated Budgeted Spend + Allocated Bid Increase Spend

Allocated Budgeted Leads: (Allocated Budgeted Spend/Current CPC)*Conv Rate

Allocated Bid Increase Leads: (Allocated Bid Increase Spend/CPC Demands with Bid Increase)*Conv Rate

Total Allocated Leads: Allocated Budgeted Leads + Allocated Bid Increase Leads

Current CPC: Cost/Clicks

CPC Demands w/ Bid Increases: Current CPC*(1+Lost IS Due to Rank)

Current Conv Rates: Conversions/Clicks

Clicks Available at Current Price: Clicks/(1-Lost IS Due to Budget)

Clicks Available Due to Bid Increase: Clicks/(1-Lost IS Due to Rank)-Clicks

Average Order Value: Revenue/Conversions

Allocated Revenue: Total Allocated Leads*Average Order Value

These columns are filled out with 0s because these are the columns the solver will be solving for in order to max out revenue volume with the budget at hand.

Step 3 – Setup the Solver Itself

Finally – once the information is all in the Solver Data tab, it is time to run the solver.  In order to run the solver properly, you’ll need to set a budget and assure it is set to maximize revenue. A setup as seen below where the projected columns sum the allocated columns in the Solver Data sheet is what your Totals tab may look like:

From here you should be able to set up the Solver as seen below:

Once the solver is setup on the Solver Data tab as seen above, you can run the Solver and get the results. (Note: the Budgets tab is an added step that is not needed in this version).

Once the Solver is ran you should be able to go in the Solver Data tab and visualize what your Campaign Daily budgets should be according to the solver, along with what campaigns you should focus Bid Adjustments on. You’ll also see projected totals:

Potential Extra Steps

As stated above, there are some extra steps that can be taken when using this tool. One of those added steps is to add campaign categories that are included in your naming convention and assure certain categories hit a max of minimum spend threshold. That is where the budget sheet constraints come into play – for example maybe we would want to set max and minimums on how much we spend on Shopping campaigns as seen below:

Other additional steps could include formulating if bid movement is allowed or only budget movement allowed, auto-changing the objective metric based on if the client is Lead Gen or eCommerce, formulating the date range you want to look back on (Monthly, Quarterly, Yearly) or formulating the aggression you are willing to take on campaign spend pullbacks.

How we would make would be to add formulas based on the data validation in certain columns or within the Supermetrics query itself in the case of the Budget Time Frame. Below is an example on how Bid Movement Allowed would be formulated – showing that if Bid Movement Allowed is selected the formula is in place, if not then the Est Max Spend Added w/ Bid Adjustments is set to $0:

Conclusion & Takeaways

From this post – the hope is that you learned:

• More about Supermetrics and understand the value Supermetrics can bring to your account due to the efficiency levels it can bring to reporting and management
• About Excel Solver & Open Solver and the capabilities to use the tool in order to perform budget analyses, and how you can get creative with the tool
• That creativity with tools, such as Google Sheet Add-Ons can lead to much more efficient account management

Get creative with your budgeting recommendations and how to approach them – and make sure that you are using the right tools to do so!

Jake has been in the PPC Industry since June 2012. He has worked in agencies and in-house at an electronic components company as a Search Engine Marketing Specialist. He started working at Hanapin Marketing in November 2013.

Follow Jacob on Twitter @jakebrownppc