I love Excel. There you go, I’ve said it. It’s not even a love/hate type thing – I genuinely find that my life is a better place because of this wonderful, agile, willing gem of a piece of software.
Gushing, maybe, but in the 7 years I’ve worked in search engine marketing (SEM), I have almost certainly used Excel every day for one purpose or another. So what I really wanted to do in over the course of a couple of posts was share some of the key formulas that are most useful in this line of work and then expand these into examples of how I would use them on a daily basis to analyse and optimise campaigns.
Formulas With Specific Benefits For SEM
LEN(text)
What does it do?
Counts the number of characters used in a given piece of text
Why is this useful for SEM?
Whether writing advert copy for PPC or meta descriptions/titles etc for SEO getting a visual aid within Excel can really help you make the most of these characters available in these situations (especially combined with conditional formatting):
VLOOKUP()
What does it do?
Uses a given text or numeric input to reference related information, grabbing it out of a table or list.
Why is this useful for SEM?
There are endless instances when you will be given data from two sources which need matched up. As an example, it could be your web analytics tool data and customer details from your offline sales team. This is probably the formula that’s given me the most satisfaction, and is probably one of the most useful single formulas in the toolbox:
2 Semi-Useful Data Sets:
One Even More Useful Data Set:
SUMIF(), SUMIFS()
What does it do?
Allows you to specify conditions on which to add data from a specific range or array of data. For example, turning it spoken word logic you could have:
“Sum values from the specified column IF they match criteria 1 AND they match criteria 2”
Why is this useful for SEM?
With so many sources, mediums and keywords we will often find ourselves with raw data sets which are no use to man nor beast and are left thinking “If only there was a quick way to tidy this up into some meaningful order”. There are often many ways to tidy up data, such as creatimg subtotals, but I find SUMIFS to be the quickest and most flexible way:
The Real Magic
Now, all these formulas are well and good, but the real power comes in learning to combine them in the right way, at the right time, to get actionable insights. I’ll go into this further with some walkthroughs of sheets which I regularly use for SEM analysis, but here’s a quick example which illustrates what I mean:
My ‘Keyword Category Potential’ Analysis Sheet:
For this report, we take data from our Google Analytics account, regarding current traffic levels and a success metrics, such as the ecommerce conversion rate, and match it against traffic estimate data pulled from the Google Keyword Tool.
The key requirement of actionable data is that there is significant context created by the chosen metrics. This is achieved by combining the click and traffic estimate data to get a rough ‘share’ of potential traffic. Pulling this into the table below is done on the fly by typing a keyword into the ‘Category’ column, and relying on the SUMIFS in to pull the relevant pieces of information from other sheets which use VLOOKUPs (amongst other formulas) to tidy up raw output from the tools used:
The final piece of the puzzle is to make it even easier to get the actions to take away from the analysis. I like to do this by populating a 2-axis scatter chart. A quick glance at the below tells me that there are a few keyword areas that fit the bill of having both a high potential to get more traffic, and a higher than average conversion rate:
Quickly and easily we can take from this that there are 3 categories of good converting keywords which have potential to drive more traffic
—————–
Note: all data sets have been arbitrarily created for the purpose of the post, so no client information is included whatsoever
