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.
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:
Custom Report doe Data Analysis by Month
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? Just This e-mail address is being protected from spambots. You need JavaScript enabled to view it or use the box at-left.
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.
Comments(1)
