Excel Formulas Every PPC Professional Needs – Anu Adegbola’s Benchmark 2017 talk
Anu Adegbola, Zoopla’s Paid Search Manager and self-confessed Excel geek shared some of the great formulas that had been invaluable to her career in paid search over the past ten years
She began her Benchmark 2017 presentation by admitting she used to be “that little girl who used to play on my dad’s laptop creating tables and graphs while everybody else was outside playing”, and could talk about Excel all day, but instead managed to squeeze an impressive host of useful formulas and tips with the Benchmark audience.
Anu established that Excel could be used by PPC professionals for a number of things, including campaign creation, reporting, budget modelling (to allow you to determine which parts of your accounts you want to target spend depending on your return you’re getting), competitor tracking and ad scheduling. But Anu’s talk focused on campaign creation and reporting.
- Consider your high intent keywords – the ones that are more likely to lead conversion and revenue. How many of them are there, and how big do you want your campaign to be? This will be determined by your budget.
- How can you make your ad copy as relevant as possible as, in terms of Quality Score? You need to make sure your keyword matches your ad copy and landing page.
- Match type and how your site is actually structured.
Anu demonstrated how Excel can be used to optimise for this.
Starting off with keyword formula, Anu recommended using the concatenate formula (which is designed to join different pieces of text together or combine values from several cells into one cell) for keywords and match typing.
For Zoopla, the different aspects that make a very high intent keyword are: number of bedrooms, type of property, whether it’s to rent or for sale, and location.
If you apply concatenate properly, for these keywords you can generate 96 possible keyword combinations, as the slide below demonstrates:
Take the first line in the slide, for example, by putting A1 at the front and end, you can set your match type, you automatically set up your phrase match for all of these keywords.
This can be used for keywords and match typing, but particularly for exact match and phrase.
For a match type of broad match modifier (BMM), Anu recommended using a formula such as substitute, because for BMM you need a plus sign in front of each word. To do this easily in Excel, you need to insert the plus sign before the first keyword, and then do substitute from space to space + (to make sure there’s a space in between each of the words).
In this example, ‘1 bed hotel to rent in London’ becomes the BMM version of ‘1 bed hotel to rent in London’:
Anu moved onto how to use Excel to create and optimise ad copy. She recommended using the proper formula to ensure each word was capitalised, as she finds this format increases CTR.
In this case, the keyword becomes the headline copy of the ad as: ‘1 Bed Hotel To Rent In London’:
Moving onto the description line, where you have more characters to play with to include features, USPs and CTAs, you can use concatenate by putting find at the beginning, using the & sign to refer to your keyword, then adding extra copy after that.
Anu pointed out that it’s a good time to bring the trim function into play so as not waste any characters spaces:
You can also use the LEN, which allows you to see how many characters you have for each line to ensure you’re working within the limit and maximising the space available.
We are always looking for an instant return from our paid search; Excel can help with this through several must-use formulas
Zoopla’s Anu Adegbola
When she first started as Paid Performance Manager at Zoopla, Anu was disappointed that the agency they were using wasn’t providing daily or weekly Excel reports – so decided to create her own.
With reporting, you need to ask yourself:
- Who is it for?
- How often does it need to be done? For example, your internal digital team might want to have more frequent visibility of performance, such as daily or weekly; while your CEO may only want reports monthly or quarterly.
- How large is the account (therefore what size does the report need to be)?
PPC reporting: a case study
Anu showed us the report she created for Zoopla:
The tabs show daily, weekly and monthly activity. It has a tab for raw data, as well as data pulled from DoubleClick, Google Analytics and AdWords, and all of Anu’s formulas.
She admitted that putting such a detailed document together is very time consuming at the beginning but, once the template is set up, maintaining it can be quite straightforward.
Easter egg incoming!
Anu shared a tool not many people know about yet: the SEOTools extension, which provides additional formulas for manipulating your data.
Formulas used for reporting
The text formula allows you to control the format of your data.
Sumifs is a good way of summing up your data, whether this is impressions, clicks or costs, according to different groupings. For example, if you have several campaign reports over several days, but you only want to see performance over a weekly or monthly basis, you could use this formula to aggregate your costs column, and then a particular date or date range.
The search and isnumber formulas are a good way to categorise something that’s in a cell and there are a few variations. The search formula identifies what you put in the bracket and generates a number where it is found.
Now the isnumber formula determines whether data in a certain cell is a number. Combining this with the previous search formula, which returns a number if the formula can find an instance of the query, means you can use these formulas together to apply rules only when a certain query appears in a string:
Anu uses the SEO formula RegexpFind to find cells containing particular words and replace the contents of that cell with that term written out in full, regardless of what else the cell contains. A limitation with this is that there is a limit to the amount of arguments you can use in the formula. A way around this is to add more options using concatenate.
While Vlookup is a popular formula, Index Match opens up more options to look up values in a table based on data in other rows and columns.
Lean up your file
As formulas take up a lot of space, it can take a long while for your file to load, for you save or do anything else while the calculations are working.
So, save all the formulas on one line, in one tab, then call use it when needed to manipulate your raw data by copying down the formula then copying and saving the values produced in another tab.
Easter egg incoming! Part two
ASAP Utilities is a downloadable tool (currently only available for PCs) that allows you to implement many of the formulas discussed here without remembering them.
One way Anu likes to use it is for reformatting the dates, as she doesn’t like the default format when downloaded from Google Analytics:
The tool can also be used for manipulating text, formulas, removing or adding characters. It makes finding large batches of landing page tracking URLs less painstaking than doing manually.
- The ‘concatenate’ and ‘substitute’ formulae are vital for keyword list creation.
- The ‘trim’ formula can resolve any potential problems with wasted space.
- Reporting takes time, creating a great reporting template takes longer – but crucially the latter needs to be done only once.
- The ‘SEO Tools’ extension for Excel can be extremely useful.
- To maintain a lean file, save all formulas on one line in one tab and work your data around this to prevent excessive loading times.
- ASAP Utilities is a must to save time on various repetitive tasks (such as date representation conversion from US to UK).
Prefer to watch the talk? Here’s Anu’s video in full:
Want to see what the agency behind the successful Benchmark Search Conference can do for your brand? Contact us today, or check out our library of resources. For more Benchmark goodness, all talks and slide decks are now also available on the Benchmark 17 page.