Quantcast

How To ‘Excel’ At Search Marketing


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

Google Secures User Data: Mixed Messages, Mixed Feelings

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’veImage of red and green traffic lights representing mixed signals 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.”.

A Combined Approach To Search & Conversion Optimisation

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.

5 Tips for New Web Analysts / Strategists

I often receive emails from new web analysts/strategists who are fresh out of University or a year into their Analytics careers asking what they can do enhance their career and how they can fine-tune their skills. Below are 5 tips definitely worth looking into:
  1. Use iGoogle: Using iGoogle will allow you to stay on top of industry trends/tips all in one central place – and the interface rocks! It’s a great way to read what thought leaders have to say across multiple industries. I personally make sure I stay on top of what the following blogs have to say: Search Engine Land, SEO Moz, Search Engine Journal, Marketing Pilgrim, Search Engine Watch and for fun: Tech Crunch, Engadget, GigaOm, ReadWriteWeb and Scobelizer.
  2. Learn complimentary skills: Go beyond analytics because it will allow you to have a more complete understanding of your clients’ online strategies. Knowledge in areas which compliment web analytics quite nicely include: SEO/PPC, Software Development, User Experience, Social Media and Mobile Apps/Web.
  3. Participate in the Community: Join the Web Analytics Association, be active on groups in Facebook/LinkedIn, attend eMetrics, provide feedback on prominent blogs and even offer to guest blog (PS we’re always looking for good bloggers).
  4. Love Technology: I have been very grateful to have used dozens of different types of online technology in my career because it now allows me to quickly provide recommendations on what solutions will provide my clients with the most value (and at what price tag).
  5. Experience the Industry: It’s very useful to have different experiences such as: at an agency, in-house at a large corporation or as a consultant. A colleague of mine put it in words that really made sense to me: One of the main reasons senior strategists (or even senior management) make the big bucks is because of their experiences, they have a multitude of real-life scenarios they call pull inspiration/strategy from and this is a key separator from their junior/intermediate counterparts.
  6. Explore our Jobs Sections for Industry information and definitions on Internet marketing related jobs, Analytics oppertunities and seo jobs

Keyword Zoom from ClickEquations

This is a guest post from Alex Cohen, Senior Marketing Manager at ClickEquations.

People tend to think of paid search through the lens of keywords: You pick keywords, bid on keywords and figure out which keywords are good and bad.

Searchers, the people you’re trying to reach, don’t type in keywords. They type in search queries. The keywords you purchase and match types you choose, along with your bids and Quality Score, determine how many search queries you get exposed to. The relevance of your text ad determines how many click.

That relationship between search queries, keywords and text ads is one of the truths of paid search. But, analyzing that relationship is actually very cumbersome with most paid search tools. There’s one report for keywords, another for search queries and yet another for text ads. You, the analyst or paid search manager, are left to do the heavy lifting to stitch the pieces together in order to target the right queries with the right message.

Connecting these pieces is a technology problem and one that we at ClickEquations have solved with our latest feature, Keyword Zoom.

Keyword Zoom makes it possible look inside the performance of any keyword and directly manipulate the queries that have driven up cost or lifted revenue and tune the relationship between those queries and specific ad copy.


Keyword Zoom allows you to see:

  • The search queries that the keyword attracted and how each performed
  • The ad copy that was shown to the people who entered these queries
  • Complete performance statistics and metrics for that keyword

You can easily:

  • Turn a search query into a new negative keyword so don’t waste money on irrelevant search queries
  • Add a search query as a new keyword of any match type to attract more searches like that, boosting sales
  • Edit existing ad copy or create new ads or variations to improve the alignment of queries to text ads

“Paid search advertisers bid on keywords but money is actually spent and earned on search queries.” said Craig Danuloff, President. “Keyword management is no longer enough. Only by looking inside the performance of any keyword, and mining search queries to stop ineffective clicks and increase targeting can accounts truly be optimized in today’s competitive and expensive PPC marketplace. Keyword Zoom is the first and only tool that brings keywords, search queries, text ads and detailed analytics data into one screen with editing tools. It’s the best way to target the best prospects with the most relevant message.”

Keyword Zoom is integrated directly into the ClickEquations paid search platform, which enables full reporting, editing, optimization and automation of PPC campaigns.

Watch this video to see Keyword Zoom in action:

Who's Taking Credit for Your Sales Leads?

There used to be a time when website owners didn’t bother keeping track of where all their leads or sales came from. This caused much angst in the Internet marketing industry as hard working marketers were not receiving the acknowledgment they deserved.

If you asked clients where their leads came from or which marketing initiatives provided the best return on investment, many would have no idea. Even those who had set up website analytics, or asked prospective callers how they found their contact, the information was mostly incomplete. No one could provide an accurate measure of detailed lead or sale generation metrics.

If someone contacted you by phone instead of through your online contact form, you would logically assume that the caller was not a referral from your website. Failing to recognize the potentially effective web lead or more specifically SEO, SEM/PPC Advertising, email, etc., credit would likely go to another marketing initiative.

After years of working on many Internet marketing projects and running into this particular issue, the Toronto based web design and SEO company, 9th sphere, decide to find a solution that would help their clients get a better sense of where to spend their marketing budgets.

So here’s a sample scenario. A prospect reads an online news article about you, clicks onto your website link in the article, then visits your site briefly. Since you are also optimizing your website for search engine ranking, a few days later the prospect searches on Google for your keywords and sees your company name again. They read a few pages of your website and then call you to learn more about your services. The sales representative asks the prospect where they heard about the company. The prospect may mention the initial article or the search on Google. As a marketer you’d want to know every touch point the prospect had with your brand. This would include the article, web pages on your site and why they decided to call instead of filling out a web form or purchasing your product online. Because the prospect decided to make the call by phone instead of fill out a web form, the tracking of their encounter with your brand becomes much more challenging.

What 9th sphere did to resolve this challenge is build a system that tracks the entire experience of the prospect caller; from first finding you in the article, to making the call, as well as any interaction with the website thereafter. 9th sphere’s solution is REF:CODE Analytics, which works similarly to Google Analytics in the background. When a prospect calls, the sale representative simply asks them for the reference code that is found next to the company phone number on the website. This reference code has been tracking the entire online experience of the prospect caller. Once the unique code is referenced by the caller the sales representative can connect the action to that particular lead. In this way you can keep track of your leads as well as the effectiveness of your website and Internet marketing initiatives, like SEO and PPC campaigns.

If your lead comes from an online source, next time you will know. And you’ll be able to confidently give your hard working online marketer the credit they’ve no doubt earned.

Why the Adwords Top Spot Should be Your Bottom Priority

In pricing PPC keywords, competing for the top spot could be your biggest mistake. Capturing the #1 position is not always realistic, and it is a likely waste of your money. The waves of information we see for search marketing has clued us in to smart concepts that pay off when put into practice. We’ll show you exactly how to stretch your ad dollars without sacrificing exposure to your domain.

Often, search marketers spend hours carefully pouring over data in a concentrated effort to hit that trophy—the top ad position. There is value to that position, but that value usually means more to a deep-pocketed advertiser, fighting to close the gap against an aggressive competitor.

For most advertisers, shooting low is smart. Position 6 or 7 is the better answer. Not only does a lower bid still bring in respectable traffic, but it stretches your budget once clicks start adding up.

Devote your attention instead to building your click through rate (CTR) with relevant ads for the keyword. This carries more weight with Google and allows your ad to show up at a higher position than what your bid alone would have delivered.

By analyzing SpyFu data, we found that advertisers getting clicks in these middle positions are usually paying between 35-45% of the top domain’s bid. (You will want to determine the difference in your specific niche between the two, but 35% can be our working model.)

When you reset your bid prices to reflect this play on the top money-guzzlers, you have a more competitive strategy that keeps you in line with a mid to low range position.

Follow through to the end of the post to see how to get a better idea for the range of keywords that you use.*

Experienced Adwords managers are starting to cringe at where this is leading. The idea of calculating new pricing for dozens or even hundreds of terms might start to sound like a task that isn’t worth the pain.That could be, but there is an easy solution.

SpyFu has constructed a new interactive keyword viewer to manage strategies just like this. With a built-in export function created specifically for Adwords users, it takes the pain out of practices that savvy search marketers rely on most.

You can enter your pricing pattern as a multiplier of the max bid price.In one move, your entire ad group is individually priced and ready to start getting you results. For our example, your multiplier is “.35″ for 35% of the max bid price. Don’t worry about one size fits all.S hould you spot few keywords where you’d like to rank higher, you can make individual updates. The built-in multiplier just helps you buy time when you are setting up your group.

As you start to get clicks and results, you can always measure your CTR to determine if you need to adjust your bids up to take advantage of more competitive keywords and ads. Starting low and raising your bid is a better position to be in than wringing your hands over lost dollars you’re trying to salvage.

Try it for yourself:

Enter your own domain into SpyFu to pull up your paid keyword list, or choose about 5-8 PPC keywords that represent the niche where you plan to advertise.

Choose a term where you ranked about 5, 6, or 7th. In our example, candyfavorites.com ranked 5th for saltwater taffy.

Select the keyword to retrieve its stats, and find the cost per click in the top left corner.

Divide the top end of the range by the low end of the range. Here, the CPC ranges from .28 to .60. The low end is about 47% of the high end. Continue this pattern on selected terms until you come up with an average percentage of low range bid trends you would like to use.