Building An Automated PPC Spend Tracker In Google Sheets
We'll showcase an application of Google Sheets to solve a problem in a way that saves time while revealing actionable insights.
I’d like to start off this post with a big hat tip to Kevin Klein, a fellow account manager at Hanapin, for his thoughts, feedback, and assistance. All of which were all essential for bringing this post together. Now back to your regularly scheduled programming.
“Taken out of context, I must seem so strange.” – Ani DiFranco
Early this month we stressed the importance of teaching metrics to clients, and not simply bludgeoning them with data, and then expecting them to take everything at face value. It’s an important lesson, and one that account managers— the ones wielding the blunt force data— would do well to internalize.
Today I’m going to show you a metric, and its suggested application practices. Then I’ll provide an insightful alternative.
The Lin/Rodnitzsky ratio, also called The Aggressiveness Scale, refers to the following formula:
CPA of total search queries / CPA of search queries that have converted.
Then, based on the number resulting from this formula, the L/R ratio comes equipped with a suggested plan of action. A 2 on this scale, supposedly, is the sweet spot of consummate account curation. A 1 means you’re too conservative, and should probably undergo some keyword expansion. A 4 means the opposite. Time to cut back.
Here’s the problem: the L/R ratio doesn’t consider any account specific intangibles besides converted keywords (such as goals and performance), and makes the assumption that if there are performance issues, that those issues are caused, or even correlated to, excess keywords.
But we can take one aspect of the L/R ratio, extract it, and apply it in a more constructive way. The L/R ratio measures the amount of an account’s spend towards keywords that have not converted. However, it presents this information in an abstract way (1, 2, 3 etc, on a limitless scale). So why not just calculate the exact % of your non-converting spend? Seeing that 60% of your account’s spend is going to non-converting keywords is a whole lot more meaningful than seeing your account is a 4.2 on The Aggressiveness Scale, right
In order to find the % of Non-Converting Cost (NCC) in your account, filter out keywords with at least 1 conversion.
Now, take the cost of your converting keywords ($12,792.48), and subtract it from your total cost ($87,437.93), to find your NCC ($74,645.45). Then divide NCC by total cost, and multiply it out by 100 to find your NCC% (85.3).
Once you have a solid grasp on your NCC— and a number to represent it that is meaningful rather than represented in a non-intuitive way— you can than begin to figure out if it truly has any impact, positive or negative, on your account.
Here are a few different examples.
The above stacked line graph measures an account’s rolling 14-week NCC% versus its Return on Ad Spend numbers. As is starkly illustrated in the data visualization, there is a very clear (and seemingly dependent) relationship between NCC% and ROAS. In fact, whoever manages this account can look at the graph and note that when the account has an NCC of 86% or greater, it tends to fall short of its ROAS goal, and vice versa. That’s a pretty fantastic piece of information to have about your account, and one that the L/R ratio does not provide.
Here’s another account, this one possessing a very different NCC/ROAS relationship than the one above.
Here you’ll see that over the course of 12 months, ROAS and NCC stuck together like your upper and lower teeth after a withdraw from the bank of Jujyfruit. This relationship defies conventional wisdom— you’d think that super efficient spend would translate to an increased return. For whatever reason (and whoever runs the account can hypothesize on this), that’s not the case here. But what’s important is that you can use NCC% to identify that relationship, and then set your course of action.
If the trends are any indication, and they are— this is a massive account, with a trove of history, and a ton of data— cutting back on NCC%, which is what the L/R ratio would advise, is not what this account needs. See? Context. Valuable stuff.
A final example, this time utilizing a CPL goal from a lead-gen account.
This is example is interesting because there does not appear to be a tangible relationship between the goal metric (CPL) and NCC%. While in example 1 there was a clear inverse relationship, and in example 2, both data sets mirrored each other, example 3 possesses elements of both, making it difficult to use NCC as a benchmark, because performance has been volatile.
So in 3 different accounts, we see 3 different NCC/goal relationships. Looking at the data this way allows the account manager to operate with the aid of perspective, instead of blinding reducing or building out.
“Taken out of context, I must seem so strange.” – Ani DiFranco
^ See what context can do for comprehension? Bet it now makes a lot more sense why I started with that quote.
The primary point of any metric should be how it relates to business goals. Performance should improve after removing wasted spend is a straightforward concept. By cutting down on excess you can focus on high converting queries. If NCC is high you can dig into the two major problem areas. Are you bringing in poor quality traffic or is it a CRO/site design problem?
The issues come into play when relying solely on a number. NCC normalizes this ratio to something more manageable. On an account specific basis it reveals hints for maximizing gains and orienting an overall strategy. Of course you’ll always need to account for seasonality, market changes, and new strategies but looking beyond the CPA and into your NCC helps in understanding where your money is going. It may alarm you at first if it is a high number but remember PPC is full of managers who would be ecstatic for a 1% conversion rate.
For example, in e-commerce bids are often cheap, and the queries plentiful. While it’s important to hone high traffic keywords with exact matches, there will be too many queries to always have exact matches. So long as these are quality queries and enough users convert, you can feasibly increase spend for higher and higher ROAS, even if you’re technically increasing your overall NCC.
For accounts with high average order values, the increases in revenue will quickly overcome the higher NCC. It’s up to you to find the correct level for your accounts. You may find that you can’t sustain or profit from a high NCC%, or you may find that by attempting to quickly bring down the NCC, you slash too much potential revenue.
For a lead gen example, NCC allows you to justify and analyze just how much you will pay for those extra conversions. For example, you may have 20 more seats open in a university class, which you try to fill via paid search. While you usually optimize for a $250 CPA, filling those excess seats may justify a much higher CPA (since the relative value of tuition and credit hours is so high, you really don’t want seats to go to waste). While optimizing towards a specific ratio would tell you not to do this, understanding that you are devoting an extra 50% of spend for additional conversions provides a deeper perspective for allocating your budget.
Do you utilize any metrics such as the Lin-Rodnitzsky ratio, Aggressiveness ratio, or something like the NCC? What situations have you found it most useful or where did you find it not so useful? Feel free to leave a comment and share your ideas with us and fellow readers.
Browse By Category
Learn 3 easy ways to quickly test out variants on low-traffic landing pages to improve performance.
Get a client services view on 10 traits of successful PPC account managers and see which are on your list!
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Hero Conf Los Angeles is one short month away. And now we get to share the good stuff. Join us at Hero Conf Los Angeles, April 18-20, for 10 of the most fun and unique networking opportunities in the industry.
In light of the March Madness season it was thought that we could have some fun and review some of the previous PPC Hero articles on the best ways to utilize Excel in order to more efficiently and effectively manage PPC accounts.