Web Analytics World

Analytics, Mobile, Social Media and Digital Marketing Strategy

  • Home
  • About Us
  • Online Courses
  • Current Bloggers
  • Contact Us
You are here: Home / landing page optimization / How To ‘Excel’ At Search Marketing

How To ‘Excel’ At Search Marketing

December 16, 2011 by David Fothergill 6 Comments

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.

Excel Mug

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):AdWords Using Len Formula

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:

Using VLOOKUP

One Even More Useful Data Set:

Tables without vlookup

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:

Example of using the SUMIFS function

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:Example Table

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:

Chart showing category metrics across 2-axis

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

Filed Under: landing page optimization, SEM, SEM Training, Tips, Web Analytics, Web metrics, Website Optimization

About David Fothergill

Gill is a Project Director at SEM Agency QueryClick with a wealth of experience in Paid Search, Conversion Otimization and Web Analytics. Gill's BSc in Mathematics and expertise with Excel allows him to tie Analytics with business goals and derive insights to improve Marketing Strategy.
Read more of Gill's articles.
Connect with David via QueryClick
Or on one of his Social Channels:
Twitter
Google+
LinkedIn

Comments

  1. Annie Cushing says

    December 16, 2011 at 9:05 am

    Great post, David. But can you explain the X axis in the last chart? What metrics are you using to get the “share” metric? Posts like this get more bang if you provide a downloadable Excel file.

    Reply
    • David Fothergill says

      December 19, 2011 at 3:00 am

      Thanks for your comments, Annie. The share metric is essentially ‘1 – (clicks/potential traffic available)’ giving an ascending scale with which to do the analysis (i.e. maximum potential = 1; no potential = 0).

      The follow up post will be a few detailed breakdowns of sheets, including this one, in which I’ll be posting them alongside the blog.

      Reply
  2. Koozai Mike says

    December 16, 2011 at 10:34 am

    Great article (shame about the pun). I second the Excel love, even for doing basic to do lists it’s ideal. We’ve recently started using the Google API with Excel and it reveals a 2nd layer of awesomeness.

    Reply
    • David Fothergill says

      December 19, 2011 at 3:05 am

      Sorry Mike, I’ve never been able to avoid a pun when it presents itself – glad you enjoyed the post despite it!

      I’m with you on the Google API – we’re currently using NextAnalytics a lot to populate reports. Less time spent on creating reports, more time analysing and getting the all important insights.

      Reply
  3. Stine Hulvej says

    December 22, 2011 at 11:47 am

    Thanks David for sharing your Excel tips it is very appriciated – Great post keep going!

    Reply
  4. TechTwitt says

    December 23, 2011 at 1:50 am

    Great Help David thanks for sharing

    Reply

Leave a Reply to Koozai Mike Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Accelerate Your Career

Business Blockchain

Never miss another post!

Entering your email address in the field below will subscribe you to our RSS to Email list. This means that when we publish a new post, you'll get an email with a synopsis of the post and links to the full article on this site.

  

You can unsubscribe from this service at any time by following the instructions within the notification email.

© 2021 Web Analytics World • Privacy • Cookies