SEM Account Audits

Often, when taking on a new client or new job, SEM marketers are faced with the daunting task of familiarizing themselves with a new account. To help ease this process, I’ve put together a framework for conducting SEM account audits. This is a deep dive into the accounts and is useful for both understanding the account and identifying new opportunities.

Account Structure

A good starting point with any new account is to examine the overall account structure. What are the themes for the various campaigns? Do the campaign names easy to understand? Are the campaigns organized in a logical structure (such as mirroring the structure of the website)?


Next, I like to pull a summary keyword performance report for the past twelve months. The columns I use are campaign, ad group, keyword, match type, keyword state, status, average position, quality score, impressions, clicks, cost, conversions, and revenue. You do not need any calculated metrics (such as CTR, CPC, etc.) because you will create these in your PivotTable. Once I have the raw data, I add my own columns for engine (Google vs. Bing), distribution (Search, Content, Mobile, Display, Remarketing), type (Brand vs. Non-Brand), impression weighted position (impressions x average position), and impression weighted quality score (impressions x quality score).


Once you have the data, click Insert > PivotTable. Once the PivotTable has been created, click PivotTable Tools > Options > Fields, Items, & Sets > Calculated Field. Here is where you create the formulas for calculated fields such as CTR, CPC, Conversion Rate, CPA, and ROI. You’ll also want to create calculated fields for average position (impression weighted position/impressions) and quality score (impression weighted quality score/impressions). This may seem like a lot of work, but it’s crucial for your data to display correctly as you slice and dice it.


Now that your PivotTable is set, we get to the quantitative analysis. First, pull the campaigns and ad groups into your PivotTable to get a count of the number of campaigns and ad groups. Then pull the keywords into the ‘∑ Values’ box and change the field value setting from ‘Sum’ to ‘Count’. If you highlight the ‘Count of Keyword’ column you just created, you can see some useful metrics in the lower left hand corner; the average number of keywords per ad group and the total number of keywords. The average number of keywords per ad group is a useful indicator of how tightly themed your ad groups are. A good average is 5 – 20. Anything larger and you’ll want to break up some ad groups into smaller, more tightly themed groupings.

Pull the ‘Campaigns’ field out of your PivotTable and you can now sort by the number of keywords per ad group. This will help you zero in on ad groups that are too large. In the example below, you can see that some ad groups with over 400 keywords. Not good.

Another red flag to look for in the Account Structure is overlapping campaigns. Are there multiple versions of the Brand campaigns? Are there ad groups for a product that appears in multiple campaigns?

Keyword Management

Since we have the keyword data handy, let’s take a look at the keyword distribution. Remove the ‘Ad Group’ field out of your PivotTable and pull in ‘Conversions’. Sum the data based on the number of conversions to give yourself a sense of the distribution of head, torso, and tail terms. For example:

Here you can see that the account has far too many long-tail keywords. Over 94% of keywords had zero conversions in the past twelve months and over 43% of the budget was wasted on these non-converting terms. By pausing out these non-performers, you can have a significant impact on ROI.


Next, we’ll look for warning message by pulling in the ‘Status’ column to your PivotTable. Of all of your active keywords, how many are below the first page bid? How many have low quality score or are disapproved?

Match Types

Just like we examined the distribution of keywords between the head and tail, you should look at the distribution of keywords amongst the different match type. By pulling the ‘Match Type’ into your PivotTable, you can get a good feel of how the volume is balanced, just be sure to set your PivotTable filters to Non-Brand and Search so that you get the most accurate picture.

As a rule of thumb, I like to see at least 50% of Non-Brand Search traffic coming from Phrase and Exact match keywords. In this example, the Brand distribution looks good, but over 90% of non-brand costs are on Broad match terms (which also had the poorest ROI of the three match types). There’s a big opportunity here to expand the match types on high performing keywords and then bid back aggressively on Broad to improve ROI.

Negatives

Negative keywords and placements can be a great way to improve ROI by excluding irrelevant traffic. Simply pull a Search Query Report and Placement Performance report, sort by volume, and see if there are any keywords or sites you want to exclude.

Budget and Delivery

Now we can start diving into the campaign settings. In AdWords, simply click on the ‘Settings’ tab, highlight the campaigns and settings, and use Paste > Paste Special > Text to paste the data into Excel. The settings you will want to review are the Budget, Location, Language, Network, Devices, Bid Type, End Date, Ad Scheduling, Delivery Method, and Ad Rotation. For Bing it’s a little trickier and you’ll probably have to manually review the settings using the AdCenter Desktop Tool.


The first thing we’re going to look for are campaigns that are hitting their daily budget and pausing out early. Ideally, you should never be hitting your daily budget. Using bids to manage daily spend will increase your volume for the same given budget.


Next, review the budget delivery method. All campaigns should be set to Accelerated Delivery so that you’re participating in every auction (in the AdCenter Desktop Tool, this is found under the ‘Additional Settings’ dropdown on the ‘Campaigns’ tab).
The next area to examine is the Ad Scheduling. Are you adjusting bids by day of week? What about time of day and day of week? Are you pausing your campaigns during unprofitable time periods? If not, then it’s time to dig into the data. In AdWords, click on the ‘Dimensions’ tab and select View > Time > Day of Week. Click the download icon and then click Add Segment > Time > Hour of Day. Once you’ve downloaded the data, create a PivotTable with the day of week on the horizontal axis and the time of day on the vertical axis. Then create a calculated formula for conversion rate. The end result should look something like this:

As a final step, create an index by dividing the day/hour conversion rate by the average conversion rate (2.9%/6.2% - 1).

This is the actual percentage that you should increase or decrease your bids for each day/hour combination. In this example, the advertiser sees strong performance on Tuesdays and Wednesday and weak performance on Sundays. Based on the data, I would also recommend pausing the campaigns between midnight and 4am Saturday through Monday.

Distribution

Naturally, campaigns will perform differently depending on the location, language, device, and distribution channel. The primary thing we’re looking for here is that the accounts are properly segmented so that each channel can be optimized independently.


Are the campaigns geo-targeted? Do you have separate campaigns for major geographical areas, such as US vs Canada? Are there opportunities to expand your geo-targets to capture new customers? Do the language settings compliment the geo-targeting? Is there an opportunity to reach new customer such as Spanish speaking Americans or French speaking Canadians?


For the network settings, check that you’re not mixing Search and Content in the same campaign (Content should always be broken out into its own campaigns). On Bing, is there an opportunity to split out the Yahoo!/Bing owned and operated sites from their search partners? From a high level, how does the performance differ between Search and Content? In the example below, the Content campaigns have an incredibly strong ROI, but only receive 3% of the budget. This is a major opportunity to grow volume.

Within your Content campaigns, are you using Remarketing to convert comparison shoppers? Are you using Interest Category Targeting to prospect for new sites to target (Interest Categories are a form of Remarketing based on your search query history. To see what Google thinks you’re interested in, go to www.google.com/ads/preferences)?

One of the biggest emerging opportunities is in Mobile Search, but like Content, these should be broken out into their own campaigns. Within the mobile campaigns, are you driving traffic to a mobile optimized landing page? Are you bidding aggressively enough to appear in the top two positions (you can use your keyword PivotTable to find out)? Is your call to action appealing to a customer on the road?

Ad Optimization

The first thing I look for here is how the ad rotation settings in the search engine UIs. Google AdWords offers three settings; optimize for clicks, optimize for conversions, or rotate evenly. Optimize for clicks works to maximize your CTR, which is good for Google, but clicks do not necessarily mean conversions (try putting ‘Free’ in your ads to see this in action). Optimize to conversions shows ads based on a blend of the CTR and conversion rate. This is an improvement, but doesn’t help you much when you’re trying to methodically test creative. Generally, I recommend rotate evenly for all campaigns where you are testing creative. This allows for the ads to get roughly even exposure so that you can make statistically significant decisions.


Next, I recommend pulling an ad performance report to get a feel for how many unique headlines and unique description lines you are actually testing. The approach is the same as the work you did for the keywords. Simply pull the raw data, pivot, and then count the number of uniques. It may also be worthwhile to see what percentage of impressions is going to the same headline or description line. This can give you a sense of how often new creative is being tested. If volume is concentrated on a small group of headlines, then you may want to increase the frequency of testing, particularly in high volume ad groups. Below are a few areas for potential testing:


Headline testing

Description line testing

Call to action testing (buy now, order now)

Value proposition testing (price, selection, service, speed, features, warranty, etc.)

Ad Extensions

Google ad extensions include Sitelinks, Location Extensions, Call Extensions, Product Extensions, and Social Extensions. On Yahoo!/Bing, ad extensions include their Rich Ads in Search ad format (basically, Sitelinks plus a companion image). Ad extensions can raise CTR and push competitors further down the page, so my best practice is to have at least Sitelinks on all Search campaigns. Location Extensions are good if you have brick and mortar stores and want to drive offline traffic. Call Extensions are best for Mobile campaigns, particularly when running click-to-call only. Product Extensions are primarily for ecommerce, though tend to get low visibility (try Product Listing Ads instead). Social Extensions are for building up your +1s, which in 2012 will likely become a factor influencing quality score and become a nice way for Google to make more money.

Summary

I hope this helps you to systematically evaluate some of the key performance drivers of SEM accounts. This is by no means comprehensive, but should serve as a good starting point for digging into the details and identifying areas of opportunity. Good luck!