In today’s article I’ll be teaching a new skill in pivot table mastery which will help make you more efficient and effective when optimizing your Bing Ads or other digital marketing campaigns. The specific focus is uncovering low CTR search terms – as the most granular level of keyword match-based ad campaigns, search terms are where the majority of performance trends are ultimately created. Specifically, I’ll teach you how to use the pivot table calculated field and the design ribbon to figure out exactly where a negative keyword is needed and what the expected impact will be.

Pre-pivot prep:

• To begin, run a search term report from the Bing Ads reports tab by clicking the reports link at the top-level bar navigation, selecting “search term” as the report type, and specifying your report unit of time & date range; in most cases, summary unit of time and past 30 days will provide the best view. Run a shorter date range if you have made recent optimizations/changes and run a longer date range if your campaigns have lower impression levels.
• Since we’re putting the data straight into a pivot tablet and we don’t need the report to be a table itself, CSV format will suffice.
• Select the account, campaign and ad group you want to optimize, add any additional attributes (such as device type) or statistics (such as conversions) you want to see and then click run.
• You can use the filter option to exclude data from the report (for example exclude a particular device type or country), which can make your report run faster and return a more precise dataset.

Once you’ve run the report, download and open it in excel and add a filter to the columns. In order to exclude your explicitly bidded keywords (exact matches) from the analysis and keep only the search terms that you matched to via phrase or broad match, filter out delivered exact match types and copy the filtered results to a new sheet. If you do want to include your bidded keyword set in the analysis as well, you may skip this step. From the new sheet, highlight all the broad/phrase data, load them into a pivot table and get ready to work some magic!

Pivoting

1)      Begin by adding account, campaign and ad group, one after the other as applicable, into the rows labels; stop at the level which you want to add your negatives to. For example, if you want to add ad group-level negatives then ad group should be the last addition; for campaign-level negatives campaign should be the last addition. Once you have your level determined, add search term as the final and lowest-level row label.

2)      Next, we’ll specify the data values we want to analyze. Since we’re improving CTR, we’re concerned with a search term’s impressions, clicks and CTR, so insert impressions and clicks as values. In order to calculate a search term’s correct CTR (some search may terms appear more than once in your dataset), we’ll want to create a calculated field, which will calculate the correct value for each row label, rather than averaging the average CTR values per each instance found in the performance report. To do this, click into the pivot table analyze ribbon, find fields, items & sets and select calculated field. Here we can write the formula for CTR, which is clicks/impressions. Be sure to give this calculated CTR field yourself, since “CTR” is already a taken field name; alternatively, you can exclude CTR from the pivot table dataset.

3)      Now that we have the CTR of each search term, it’s time to create our negative insertion datasheet. Click into the pivot table design ribbon and find report layout – adjust this to show in tabular form, and repeat all item labels. Then, in the design ribbon turn grand totals and subtotals off for rows and columns. What we have left is each search term, aggregated per ad group, with the account, campaign and ad group listed for that particular, ad group-level search term instance.

4)      At this point I prefer to copy the pivot table as values-only into a new sheet so that I can edit it and not have the column sizes reset when sorting, but now we begin the analysis. Make sure to create a column or other identifier to indicate which search terms you want to add as negatives so that you can estimate the CTR pre-and-post optimization. The least time-consuming and highest impact method is to filter search terms by an impression threshold (say 1000 or more impressions), and sort by CTR lowest to highest.

5)      Once you’re done reviewing and selecting negative keywords, sum the total impressions and clicks from those proposed negatives and calculate the new, estimated CTR from the total impressions and clicks, should those negatives be added. Voila! You have just spent a few minutes to drive significant and instantaneous improvements your account’s performance!

Improving CTR is one of the best ways to organically improve your account all-up, and improves metrics such as ad rank, quality score, conversion rate, CPC and CPA. In one branded CTR optimization case study, the results were astounding: an improvement in CTR of 80% in the week pre and post optimization, leading to an improvement in CPC of 15% and an improvement in CPA of 21%.

One very important recommendation I would be remiss not to mention, is to always be sure to use your business judgment when adding negatives. Since we are only looking at a single dimension (search term) to analyze performance, it’s entirely possible that we may mistake poor performance at the search term level as a symptom of one or more of a host of other potential factors wherein the real problem lies (e.g. ad, position, day of week, hour of day, device, country, etc.). Also, always be sure to record the date of changes so that you can measure the performance pre and post-optimization, which will come in handy for recaps and inform future optimizations.

That’s all for today – thanks for following along, feel free to ask questions, comment and add your own flavor of insight. Happy optimizing!

-Gabe