David Fothergill

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

Google Secures User Data: Mixed Messages, Mixed Feelings

October 31, 2011 By David Fothergill 2 Comments

Google recently announced changes which will see them redirect all logged in users to https://www.google.com. The reason given for this change given by Google is that it will make search data more secure for it’s users, ensuring private data remains that way.

From a web analytics point of view the main impact is that no keyword referral data will be passed for organic searches in the above circumstances. Although Google played down the impact, stating that this would affect less than 10% of data, there was much uproar from the whole SEM community (I don’t think I’ve ever seen so many comments on an GA blog post or reaction blog posts), with much of the ire being directed towards what has been dubbed a highly hypocritical move: not applying the same user protection to keyword data for users clicking on AdWords ads. If user data protection is priority number one, then Google should have the courage to stick to it’s principles, especially when money is involved.

The reason for doing this is obvious – a great selling point of advertising through AdWords is the ability to accurately measure ROI and optimize performance at keyword level. To take this away would surely lead to a huge drop in revenue from the cornerstone of Google’s financial strength.

This lack of consistency is at the root of the frustration for the SEO community: by not committing fully to the cause which they give as the reason for the change, it clearly shows that Google is merely paying lip service to need to to show they are serious privacy issues. The move has all the hallmarks of a compromise; finding a middle ground to keep everyone within the Google team happy.

I specifically choose to say ‘reason’ rather than ‘defence’ in previous paragraph – in the big picture this is a positive move as you cannot deny that any move towards increasing the security of data around the web is hugely important. For anyone outside the SEM industry, it would be hard to find any negatives to this change. Ill feeling at this point from a minority voice is surely collateral damage that was expected and accepted before the changes was publicly announced.

With my web analytics hat back in place, I have to draw the conclusion that this change merely pays lip service to the idea that they are prioritizing the protection of user data.

This is a real shame, but I get the impression that no amount of outcry will change the stance Google has taken.

Change happens all the time and at the end of the day the best thing to do is to accept it, adapt and continue to do the best job possible with what data we do have. As Stephen Hawking wisely said “Intelligence is the ability to adapt to change.”.

Filed Under: analytics, Google Adwords, Google Analytics, SEM, Web Analytics

A Combined Approach To Search & Conversion Optimisation

September 26, 2011 By David Fothergill 1 Comment

Too often the components which make up internet marketing are treated as individual silos, even when there are direct links between them. When the digital marketing mix is in synergy, all the elements can sit in the pockets of each other and provide incremental increases in sales numbers and most importantly, the bottom line.

This goes for the day to day interaction between operational team members in each field – only by discussing the digital marketing strategy as a whole, from as basic a principle as knowing what each other are doing and when, can the benefits be reaped. Identifying the key areas which lend a hand can lead to finding secret sauce which results in the optimal ROI. Pulling in the same direction

What I want to discuss in this article is the tight relationship between organic search and conversion optimisation. To illustrate this, let’s do a little dissection of the goals of SEO and CRO:

SEO: gain well placed rankings by proving to Google (or other engines) that a site or page is highly relevant to the user’s search query.

CRO: increase the ratio of visitors converted into customers by providing an experience which fulfils their intent to buy/sign-up etc.

(Note: For the purpose of this article the above are simplified, individual goals which make up part of SEO or CRO strategies)

Despite the difference in who is the judge of success in these cases (robots vs. humans, in a non-Terminator way), the key aim of both is to provide a relevant user experience which a) makes it easy to find what they want to purchase and b) makes it an easy decision and process to purchase the item.

From a conversion optimisation perspective, I find that often there are a number of common areas within SEO strategies for which a sharing of knowledge is mutually beneficial:

Informing Keyword Strategy

It is essential when reviewing a site’s conversion metrics that entrance source is taken into account, and is factored into the strategy for improvement. Specifically, by deriving (as you may guess, my favourite SEM word) intent from the the search queries that are driving visitors to pages we can gain vital insight into what messages are (or as importantly, are not) working for these user groups. To give a simple example, if most visitors are coming through ‘value’ focussed keywords, then we should make sure the space is used wisely by tailoring calls-to-action and copy to fulfil this intent.

The result of this is that we build up picture of performance metrics for a number of keyword categories. Plugging this into the SEO strategy provides great deal of insight about the profitability of potential keyword targets, allowing informed decisions to be made about which directions to take and what the required course of action is to meet any given targets.

Converting From The SERPs

In a CRO strategy, we need to look at the whole user journey, from the first query to the completion of a purchase and beyond. It’s important to remember that, if you have a good level of organic search visibility, then it’s likely that your first interaction will be with your meta description. This is the chance to give your elevator pitch – in less than 155 characters you need to appeal to the intent (told you!) of the user type that will be provided a relevant, useful experience by your site/service. By focussing on finding these potential customers, all the onsite CRO work will prove even more beneficial by having a tailored audience. Only by working hand in hand with the SEO team can the meta description be tailored in such a way, whilst still ensuring it is highly optimised from an organic search perspective.

Profitable Pages

When investigating conversion performance, another key area to look at is which pages are getting the most organic traffic, and how efficient they are as landing pages at converting visitors into customers. If you can find pages which have a) high conversion rate for the traffic coming through them and b) a realistic potential for increasing traffic by optimising for the keywords driving visits, then the SEO strategy should be tailored accordingly. Even if it is a highly competitive area, the foundations provided by positive metrics on both sides of the coin should ensure that even slow progress will provide worthwhile uplifts in revenue.

The Negative Impact Of Working in Silos

Results of poor teamwork

Needless to say, there are a huge number of ways in which different channels can support each other, the above only serving as an illustrative example of the myriad of ways and means. When there is a common goal between parties, sharing information can always serve to improve the results for all, so it’s vitally important that silos aren’t created which could lead to conflict or cannibalisation of efforts – e.g. when bidding there is a PPC bidding on keywords with an organic presence, duplicating the sales message would be cannibalistic, but taking the opportunity to give exposure to an alternative set of USPs increases the opportunity to appeal to a broader range of potential customers.

Filed Under: Conversion Rate Optimization, SEM, SEO

The Conversion Optimisation Toolbox

August 17, 2011 By David Fothergill 7 Comments

Conversion Optimisation Toolbox A bad workman blames his tools. A good workman makes sure his tools allow his abilities to shine.

Every person who wants to be at the top end of their profession has to have a great set of tools: for a Michelin starred chef it could be the sharpest knives money can buy, for a Premiership footballer it’s boots which employ all the latest performance enhancing technology, for a concert pianist it’s the Steinway or Bosendorfer grand piano.

Conversion rate optimisation is all about a fine blend of science and art, analysing opportunities and finding the most creative solution to help boost a site’s efficiency. It requires a broad skill base, which covers both the creative and analytical aspects of web marketing, and as such, the tools which can help an optimisation project can cover a whole host of areas.

Visitor Insight

As long as commerce has existed, being able to provide exactly what your customers want has been key to business success. Thankfully the web lends itself perfectly to being able to analyse all sorts of behaviour, allowing us to build a hugely insightful picture of the interaction with the site:

User Survey KissInsights – Advanced visitor survey tool. Target relevant questions at users in chosen scenarios. Great for finding answers to questions such as ‘what is preventing user from signing up?’ and ‘why are potential customers exiting at this stage of the process?’

4Q – Set 4 simple questions for your visitors and get insightful feedback to help steer improvements to your site. No frills tool which gets actionable data.

ClickTale – Record visitor mouse movements and create heatmaps of user activity on the site. Great for getting insight into how users interact with your site.

Google Analytics – [BBC style disclaimer: other tools are available] The closest thing we have to a ‘standard’ for web analytics, by progressing past the standard reports and get stuck into Advanced Segments and Custom Reports, you can lose yourself for days digging into your visitor, content and referrer data.

crazyegg – Provides some really insightful visualizations of users interacting with your site, from the standard heatmaps through to a referrer by referrer confetti view.

UserTesting – Quick and easy user testing. Get 15min videos of users performing tasks you set and answering questions about your site from a user point of view.

A-B/Multivariate Testing

Once the research and analysis is done and the creative variations have been decided on, it’s time for the most important part of the process. Only by testing new variations against the existing versions can you measure the value of the work carried out and get insight for future site iterations on what does and doesn’t work. There are a huge range of tools available to do this, and the choice is usually influenced most heavily by a combination of personal preference and available budget:

Google Website Optimizer – Another free tool from Google. Helps you test and measure the impact of changes made to your site. Easy, intuitive set-up of a/b testing and multivariate testing.

Visual Website Optimizer – marketing itself as a “dead simple test designer”, VWO will allow you to set-up advanced a/b and split testing without the need for a huge amount of technical knowledge or support. Has some great features including the ability to edit content ‘in place’.

SiteSpect – As a more enterprise-level solution, SiteSpect integrates a-b/multivariate testing with advanced behavioural targeting and visitor segmentation.

Personalisation

When it comes to strategies, one great way to give your conversion rate a boost is to use the information available to deliver a custom site for the user (based on search keywords, page viewed, referring source etc). By ensuring that the most useful information is displayed to a visitor you can provide a hugely relevant user experience, and thus improve the conversion rate:

BT Buckets - Used to create user segments (buckets) for the purpose of serving personalised content to create more relevant user experiences. Easily integrates with Google Analytics, AdWords, Facebook and Twitter.

Personyze – Advanced site personalisation tool. Helps to deliver relevant content to users based on a huge range of factors including keyword usage, pages viewed, internal search use, geographic location and visit duration.

Miscellaneous

YSlow – Numerous studies have confirmed what common sense tells us – page load speed affects conversion rates. Time is money, literally. YSlow lets you measure any page either on the site or through a convenient browser extension and will then provide recommendations to help you improve load times (Note: Google has recently launched their Page Speed Service to help sites deliver content in a more timely manner).

Olark – This would cross boundaries in a Venn diagram with the customer insight tools. Olark is a live chat tool which allows you to monitor and engage with customers as they travel through the purchase process. By doing so you can help improve your conversion rate and get an insight into the mind of your visitors (find obstacles etc.).

Notable – Most conversion projects include a number of stakeholders and the involvement of team members with different skillsets (designers, marketers, analysts). This screenshot tool is really useful for annotating webpages in a collaborative manner.

ConceptFeedback – Submit your mockups and design elements for some expert analysis. Useful for getting quick, impartial feedback.

So, those are some of the tools and services that I find allow me to get some great results from conversion optimisation projects. It’s always a case of personal preference so it would be great to hear what other people find helps them when carrying out site optimisations.

Filed Under: Conversion Rate Optimization