Tracking the number of unique keywords by month

A recent discussion with a client and between some Google Analytics Authorized Consultants I know inspired me to summarize a technique for tracking the number of unique keywords referring traffic to a site and analyzing the trends between keyword growth and traffic growth.  So, this short post describes just that and provides a Custom Report link and workin Excel pivot table sample to build your own reports from.

Update (11/18/2010): I’ve made the Excel example and Custom Report in this post available for download here.

Part 1: Custom Report for analyzing Traffic by Month

This is the first part to creating the unique keywords trend report is a Custom Report that will group data by month.  This is a realtively simple thing to do and I’ve provided a link for this custom report here:

Get the Custom Report and Excel File for Unique Keywords by Month here

This report will allow you to see trafic by month and set the Secondary Dimension to Keyword, which will provide the means of analyzing Months X Keywords.

Part 2: The Spreadsheet Pivot Table

The second part to this is exporting all the data to Excel for analysis.  Once you’ve created your Custom Report, drilled into the “organic” Medium and set the Secondary Dimension to Keyword, export this report into CSV.  To export more than 500 rows right-click on the “CSV” export option, copy the link, then paste it into a new window and append “&limit=XXXXX” where the XXXX is the total number of rows you want to export.

Once exported, copy the data sheet into the Excel file I’ve uploaded here for analysis, or make your own Pivot Table in Excel setting the data source to the Months / Keywords / Visits portion of the export from Google Analytics and set the Pivot Table primary field to Month and the values to Count of Keywords.  Want a copy of the spreadsheet? Click here!

The results: here’s an example chart from the spreadsheet!

Part 3: Other Options

You could automate and improve this using the Data Export API for Google Analytics, or get more rows of data more easily using a query with the Month and Keywords dimensions and Visits metric.