PPC Keyword Research with Pivot Tables and Word Clouds

Anyone that’s worked on pay per click campaigns knows the search term report is a valuable tool. By knowing what terms are triggering your ads, you can find new ad groups and keywords to target while also adding negative keywords to refine your campaigns to be more focused with less spend. 

However, one problem I’ve found when working with larger campaigns is in dealing with large numbers of search terms. Once search terms get to the point where there are thousands of terms per keyword, it can be a difficult task to sift through all of the information in order to find the right terms to maximize your spend. One great method I've found to wade through a large list of search terms involves using Microsoft Excel pivot tables and a word cloud generator.

Word Cloud Generators for PPC Keyword Strategy

I use a word cloud generator to find common themes in a keyword list which have driven a certain number of clicks to a website. TagCrowd is a personal favorite tool of mine which helps me accomplish this task. I tend to set the TagCrowd tool to a setting so a phrase needs to show up at least 3 times, as this minimum requirement helps me find trends in the keywords. I'll use an Adwords tool called the "Search Term Report" as my starting point, exporting these terms and plugging them into Tag Crowd to generate my refined word cloud. Once I have an idea of what the popular search terms are thanks to the word cloud I generated, I move my focus to raw data from Adwords and Google Analytics. I pull data from Adwords and Analytics into an Excel document, and combine the data from both using V-Look Ups to gather and aggregate impressions, clicks, and conversions from the website's Adwords account and visit duration, bounce rate, and percentage of new visits from the website's Google Analytics account. I'm now left with a clean sheet of data which holds all of the relevant information needed from the two sources in one easily searchable worksheet.

Pivot Tables Help Narrow Down Keyword Lists

From here I create a pivot table based on the combined information. When the pivot table is done, I enter the terms I generated from the word cloud into the pivot table filter. This shows me all the data I need from both sources, and will help me identify which trends are working and which are not. When researching to find terms I might use as negative keywords to filter out of my ad campaign, I'll sort for terms which may have a high bounce rate, zero or low conversion rates, or a low average visitor time on site, and compare these metrics to the site average. Alternately, if I'm trying to find terms which pay have ad campaign potential, I'll look at phrases which have produced a low bounce rate, a high rate of traffic conversions, or a high average time on site for visitors. It's really a tool which can work well both ways, depending on how you use the report. Once you are finished you will have a solid list of common keyword terms to either use as a new ad group or as keyword ideas, or even as list of negative keywords to add to and improve your campaigns or account.

With PPC You've Got Options
There are so many different approaches you can use when working to improve your PPC campaigns. Not every type of business will have success using the same strategy. Whether your business is a small shop working with OEMs or a large Industrial B2B, Ecreative has a team of professionals dedicated to improving your online presence. Contact ECreative for help on getting started.