PPC Excel Tips For Every Level: Part 1, Faster Campaign Analysis For Intermediates

This installment of PPC Excel tips focuses on intermediate level techniques for speeding up campaign analysis. Paid search managers will benefit, but really anyone using Excel for data analysis will find good information or a helpful refresher here.

Intermediate Tip: Get more out of Pivot Tables with calculated fields and by fixing #DIV/0 errors.
Pivot tables are truly a must-have Excel skill for marketers. If you haven’t used pivot tables yet, don’t be timid. You can’t damage your data set when working with pivot tables because they simply aggregate the data you are working with in a separate area, typically in a separate sheet.

Calculated Fields: For those with pivot table experience, you know that once data such as impressions, clicks, spend are aggregated, you still need to calculate metrics like CPA and CTR as you do with regular campaign exports. In other words, you can’t Sum cost/conversion or click-through data, you have to calculate them from the aggregated cost, click and impression data. This is what Calculated Fields do for you with just a little set up work. I have to admit, for years I calculated these metrics in cells outside of pivot tables not knowing about the magic of calculated fields.

A great thing about calculated fields is the data columns you want to use in your formula do not have to be included in your current pivot table. For example, in the demo below, i be showing how to calculate CPA to find the total cost per conversion, but only spend is included in his pivot table, not cost/conversion.

One note, your calculated fields can’t use duplicate names from fields already in your data set. Thus the “CPA” field name in this example.

The calculated field will append a new column to the right in your pivot table. You can then sort and filter based on this new column.

Fixing #DIV/0 Errors: Whether you’re using calculated fields or not, if you divide a number by zero in Excel it returns the dreaded #DIV/0! error. This can obviously skew your analysis if you’re looking at CPA data.

“If you ignore the error, you’re missing out on valuable information — a keyword can spend thousands and still not convert a single time,”

The solution: Use the IFERROR() formula in the calculated field to return “spend” or “cost” when there are 0 conversions. Open the Calculated Fields dialog again, go to the CPA field in the Name dropdown, and change the formula to: =IFERROR(Spend/Conversions,Spend)



I am using the 2013 version of Excel. If you’re using Excel 2010, you’ll find calculated fields under the
Options tab in PivotTable Tools.



IFERROR can be used outside of calculated fields as well. It’s a good function for marketers to know, particularly for cost analysis. The logic of the formula basically says, if the calculation returns an error (#DIV/0! in this case) then return X, and X can be your total spend, or it can be a specific number or even text. Essentially, you can customize what the formula returns by putting what you want after that comma in the formula.

How To Understand And Optimize AdWords Search Partners Data

What Are Google Search Partners?

When it comes to buying clicks from Google AdWords, the Search Partner network remains one of the least transparent and hardest to optimize areas to work with. The official Google help page for Search Partners is pretty vague about exactly what you get from Search Partner traffic:

“On our search partners, your ads can appear on search results pages, on site directory pages, or on other pages related to the person’s search.”

In layman’s terms, depending on how liberal Google want to be with “other pages related to the person’s search,” you really can’t control anywhere near as much of your search partner traffic as you would like. I had always been under the mistaken assumption that Search Partners referred to search results pages for AOL or Ask.com and the like — search engines that decided to use Google’s algorithm and in turn get a share of the advertising profits.

An example of a Search Partner results page.

However, the scope of the Search Partner network is actually much broader than this. Both internal search results and product pages on sites like eBay, Amazon, Walmart or Target can be part of the network. Let’s take a look at whether Search Partners are right for your AdWords account and then dive into how we might start to identify the sources of your traffic.

Should You Be Using Search Partners?

From analysis across the accounts we manage, there really is nothing inherently wrong with Search Partner traffic. It tends to run at a similar (sometimes slightly higher) cost per conversion to the Google Search Network, and CPCs can be cheaper, too. As a rule, I tend to leave Search Partners turned on when starting a new campaign.

If my account is severely budget restricted from the get-go, I might consider turning them off — but otherwise the additional 20-60% of clicks that Search Partners bring is worth it (at least until you have data to suggest otherwise). If you have a more mature account and you’ve been running with Search Partners long enough to generate statistically significant data, you’ll want to judge the results for yourself. For a quick analysis, you can try using the segment button in your “all campaigns” menu and segmenting by “Network (with search partners).”

To manipulate this data, click segment by “Networks (with Search Partners)” when downloading a campaign report and then use a pivot table that has Campaigns for rows, network for columns, and a custom formula for either CPA or ROAS in your values. Here’s an example I pulled from one of my accounts earlier:

Notice that there really isn’t all that much difference CPA-wise here and that Search Partners are actually performing better in five out of eight campaigns. You might use this data to pause Search Partners anywhere that they are heavily underperforming by going back into your settings and switching to Google Search Network only. The bad news is that despite all of the recent upgrades to bid adjustments, a Search Partner bid adjustment is still nowhere in sight. Also, as a sidebar, don’t worry about your CTR from this traffic dragging down your overall as Google claims it has no impact on your keyword quality scores.

What Are These Weird Queries In My Search Term Report?

Ever seen something in your search term report that looks like this?

If so, you might have been confused as to why someone could possibly be searching for such a strange term. What’s even weirder is that the term apparently generated 571 impressions. A lone person copying and pasting that into Google might fly with me, but 571 people? Something weird is going on.

These queries are actually the result of Search Partner “searches” — it kind of shook my world to learn this (I’m a little slower than the rest of you who figured this out years ago.) As Google counts product pages on sites like Amazon as Search Partner pages (rather than just regular display), they have to pull a search query from somewhere. In this case, they are pulling the search query from the links that users are clicking to get to that page.

If you want to follow along in your own account here’s how: First, pull a Search Term report and filter for “clicks=0″. Then, sort by impressions and segment by search partners. If you find a query with 0 impressions on the Google Search Network and a ton on the Search Partners Network, you’ve probably found one of these terms. Weird formatting is another giveaway for these. In this case, I’m going to take that search query and type it into Google:

The organic search results should pull up the page your ad was triggered on as that combination of phrase and formatting is normally fairly unique. In this case my search turned up a page on Gumtree:

Notice in the above that Google is pulling the navigation options for “Cars, Vans & Utes” into my search term report? A quick scroll down the page also reveals the placement of the Search Partner ads:

This page seems fairly logical as a Search Partner to me. Someone was clearly searching Gumtree for Nissan vehicles and was shown one of my related ads. Makes sense, right? Well, what about on product results pages (which I find a little more dubious to label as “Search” rather than “Display”)? Here’s a search term from another one of my accounts:

Once again, via the Google search results page, I was able to tie the query to this page where my ad was shown:

Now I don’t know about you, but to me, this really isn’t a search results page — it’s a product page. Yet the ads shown in this page are clearly being counted as Search Partner results (as the user was searching to get here and Google followed them down the rabbit hole):

I’m not entirely sure how much Display and Search Partner traffic are overlapping in these placements. I know that they work a little differently in AdSense, so my guess is that they are still kept separate.

How Can I Optimize My Search Partner Settings With This Information?

Short answer: with a great deal of effort and patience that might not even be worth it. If you have the time, you could categorically go through your search term report and start spinning queries with lots of impressions out into their own Search Partner ad groups. Take a look at an example I’ve created below:

Through some tireless checking of pages, you can separate out these strange terms by the site they appear on and start to learn more about CPAs by Search Partner. However, I don’t really like the structure of an account organized in this way and the volume of traffic will probably still be too low for the cost of the time you would have to sink into this.

Another hack attempt that I’ve seen before is duplicate campaigns, one with both Google & Search Partners, and one with just Google. By having the Google one set to slightly higher bids, the Google-only campaign tends to win the auction, which leaves just Search Partner traffic for the campaign with both. I don’t really like this method either, as it kind of defeats the point of wanting to optimize search partners on a keyword basis and any CPC changes have to be meticulously recreated for both campaigns. (Accidentally bidding up the Search Partner-only one would break everything.)

For now, we’re left with two real options:

• Use negative keywords to remove poor performing search partner pages.
• Turn search partners off entirely if the CPA/ROAS is bad.

Of course in an ideal world, Google would be up front with us about Search Partners. It remains a fevered dream that we will be able to set up Search Partner-only campaigns, segment by specific search partners, and bid adjust for search partners. (It is worth noting that Bing already offers all these things with their partner network, so perhaps we’ll see Google catch up to them eventually.)

Google Showing Missing Keywords On Long Tail Queries

Google is now showing the keywords Google did not match on for your long tail search. The example below shows how Google did not match on a specific keyword within the search phrase and noted that under the search results snippet.


I suspect this is part of the Hummingbird algorithm doing its job.

Google prefers to show some search results even when there isn’t an exact match, especially if the searcher is being so specific.

Google’s way of informing the searcher that it wasn’t an exact match is by detailing which keywords Google could not match on, within each returned result.