Step 1: Run a Search Term Report and Export the Data to Microsoft Excel
Search term reports can be ran at the Keyword, Ad Group and Campaign level. To achieve a better understanding of the relationship between the overall Ad Groups and Search Terms, I prefer pulling this data at the Campaign level:
- Sign into your AdWords account
- Click on the “Campaigns” tab.
- Click the specific campaign you would like to run the Search Term Report for
- Click the “Dimensions” tab.
- Select “Search Terms” from the “View” drop-down menu to analyze the search terms for all keywords in your Campaign that have received a click.
- Then click the “Download Report” button to export into Excel as a .CSV
Now you’ll have the data in an easy-to-format spreadsheet. So the next question is what to do with this data. While segregating each Ad Group into it’s own Excel sheet and searching for high occurrences/CTRs of related search terms is an option for new Ad Group/Keyword ideas, this can be very time consuming (especially with large campaigns or ad groups that contain many broad match keywords). An alternative method is to utilize the PivotTable function in Excel, which will save time and effort.
Step 2: Separate the Search Terms Into an Easy-to-Read List of Individual Words
By breaking the list of Search Terms down into a “# of word occurrences” list, you can discover new Ad Group and Keyword divisions derived from Search Term word variations.
- Copy and paste the entire data set into a new sheet. This will give you the original Search Term Report data, along with the new sheet’s data, which we will be editing.
- On the new sheet, highlight the entire column of Search Terms – this will most likely be column “A”.
- Click on the “Data” tab in Excel, and then click the “Text to Columns”function.
- Choose the “Delimited” Radio button (characters such as commas or tabs separate each field) and click “Next”
- Then choose the “Space” Delimiters checkbox and verify the “Treat consecutive delimiters as one” checkbox is selected as well. Click “Next”
- Click the “Text” radio button under “Column data format”. Then click “Next >”, and “OK” when asked to replace the contents of the selected cells
The spreadsheet will then be reformatted into a similar manner to the screenshot below – each word for every Search Term will now be in it’s own cell. This reformatting will remove AdWord metrics (Clicks, Impr, CTR, etc), but don’t worry, as these are not the metrics we’re concerned with. We’re purely searching for the frequency of individual word occurrences in the Search Term Report, which will be used to find variations of these words (in relation to their original Search Term) later on.
The next sub-step is to combine all of the columns into a single column. This can be achieved by cutting/pasting each column beneath the first column or the subroutine below can be copied and pasted into Excel Visual Basic Editor (props to “Gary E” in the Yahoo! Answers Software Forum). Allow your computer a few minutes to render this script:
Dim iRow, iCol, iTargetRow, iMaxRow As Long
iMaxRow = XXXX
iTargetRow = 1
For iCol = 1 To XX
For iRow = 1 To iMaxRow
‘If Cells(iRow, iCol) > “” Then
Cells(iTargetRow, XX) = Cells(iRow, iCol)
iTargetRow = iTargetRow + 1
‘Else: Exit For
- Edit iMaxRow = to the maximum row for your data in the first column
- Edit For iCol = 1 To the maximum column for your data (e.g. if your longest Search Term goes to column F, then enter 6)
- Edit Columns(XX).Clear: To the next column after your longest search term (e.g. if your longest Search Term goes to Column F, then enter 7)
- Edit Cells(iTargetRow, XX) = Cells(iRow, iCol) to the next column after your longest search term (use the same # as the “Columns(XX).Clear” line above)
You should now have one column of data that includes every word from the Search Term report (see screenshot below). Sort the Column A-Z to view a clean list of each word.
Step 3: Use PivotTables to Reformat the Column of Individual Words
PivotTables are one of my favorite tools in Excel. They can be used to cross reference everything from Geographic Reports to Day of Week>Hour of Day reports. In this instance we will be using PivotTables to find the actual # of occurrences for each word in the Search Term Report.
- Click the “Data” tab in the Excel Toolbar
- Under the “Analysis” heading, click the drop-down menu for “PivotTable” and click “Create Manual PivotTable…”
- Then select “OK” for both radio buttons upon the “Create PivotTable” confirmation pop-up.
- The “PivotTable Builder” menu will then appear. Make sure you drag the “Field Name” into both the “Row Labels” and “Values”areas, which will then render a PivotTable similar to the one below – with the actual Search Term Report words in the left column and the Total # of occurrences in the right column. Ignore the title of “Count of 1st word” in the screenshot. This column contains the count of EVERY word in the report. There will obviously be many useless conjunctions and prepositions (e.g “and”, “the”) in this report, but ignore these and focus on possible Keywords.
- To highlight the words that occur the most frequently in the report, use the “Conditional Formatting” tool in Excel to highlight cells greater than “5” for example (in the “Total” Column). Doing this will allow you to pinpoint the words to possibly build Ad Groups or Keyword additions around. For example, I may want to focus on an “accessories” or “affordable” segment in this specific Campaign (per the “Total” column above), since these words appear in a variety of user Search Terms.
Step 4: Cross Reference the Original Search Term Report with the # of Word Occurrences PivotTable
The final step in this method is the most vital, as it will divulge the user Search Terms to focus on. Refer back to the original Search Term Report with the AdWord metrics to segment areas based on your PivotTable data.
- Isolate the highlighted, most searched-for words (per your PivotTable) in the Search Term Report by using the “Conditional Formatting” tool in Excel (“Highlight Cells Rule” > “Text that Contains” > “the word with high frequency”).
- Then apply a “Filter” to the column and filter by the highlighted cell color. This will then give you all of the Search Terms that contain the specific word(s) you want to focus on.
- Repeat for desired Word(s)/Search Term(s) and segment each into it’s own Excel sheet.
And there you have it – an organized spreadsheet with potential Ad Group and Keyword additions. As I stated earlier, this is by no means an end-all Search Term Report analysis, but more of an exploratory, quarterly report to clean up neglected terms. Shoot me your opinions and/or suggestions concerning this reporting method at [email protected]