Using Google Analytics to show our Top 100 Searches

We have just updated our August 2010 Top 100 searches from Google and other Internet search engines. This post provides an overview of how we do this and the Excel file(s) which we use.

It’s done manually at the beginning of each month but doesn’t take long to do, once the initial files were set-up. We just:

  • Extract the data from Analytics
  • Use Excel to produce an Top 100 searches in HTML
  • Copy the HTML data into a Top 100 searches page

Getting Started with Google Analytics

In Analytics, Keywords can be found under Traffic Sources from the left hand option menu. Set the date range which you want to report on and GA will display an overview of the numbers searches and the number of keywords used. It will show the first 10, we change this to the top 250 to get an overview of the searches.

Keywords Traffic for Enlighten for August 2010

Keywords Traffic for Enlighten for August 2010

The keywords are exported using the Export > CSV for Excel file format. This exports daily search counts as well as the keywords and their visits.

Exporting as CSV for Excel

Exporting as CSV for Excel

From Analytics to Excel

In Excel we trim the data from Analytics into just two columns keywords and visits. At its most basic, we could have just made this data public but we felt it was more useful to provide links back into Enlighten which would enable us to this data.

Excel with Keyword and Visits Columns

Excel with Keyword and Visits Columns

To use these, we add two additional columns, one for the keywords to be used by the link (Keyword_Plus) and the other for the links which will be generated by our Excel formula (Link). In the new Keyword_Plus column, we replace all the spaces with + these will be used as the search terms by Google.

In the Link column, we use a formula in Excel to create an HTML formatted list which combines the first two columns. This is set-up to pass the search terms back to Google. To ensure relevance we use our local custom Google Search but results could be limited using site:.

We use the former because it gives us more control over the sites searched and the “look and feel” of the search and results pages. We do definitely recommend applying an option like this and not just passing results to Google…

Excel with Keyword Plus column

Excel with Keyword Plus column

The formula which we use for links is this (the hardest part was sorting out the & and ” syntax!):

="<LI><A HREF=""http://www.lib.gla.ac.uk/enlighten/search/results.html?cx=008133519044995412890%3Ai9xbikqzcrc&cof=FORID%3A9&ie=UTF-8&q="&C1 &"&sa=Search"&""">"&B1&"</A> "&"("&B2&")"&"</li>"

An alternative version just going directly to Google and limiting by site:

="<LI><A HREF=""http://www.google.co.uk/search?q="&C2 &"+site:eprints.gla.ac.uk"&""">"&B1&"</A> "&"("&B2&")"&"</li>"

A copy of our August 2010 Excel file, including these formula can be found as GA_Custom_Search_Example.xls and GA_Site_Search_Example.xls on the Enlighten website.

Once the formula is in the first row of the search terms, we scroll down to add this for all 100 searches. The column variables are replaced with the keywords and visits and prepped with <LI> so they can be used as an ordered list. This was initially an unordered list but we changed it quite early on to become ordered since that made more sense for a Top 100 listing.

From Excel to the Top 100 Searches web page

The results of Column D are copied into our Top 100 Searches web page between ordered list tags to show their ranking. The introductory text is updated to provide an overview of the number of searches for that month.

This is uploaded as index.html into our /top100searches directory. and the finished page is available from the Top 100 Searches link which is part of our default left hand navigation bar.

Enlighten - Top 100 Searches (August 2010)

Enlighten - Top 100 Searches (August 2010)

Monthly and Yearly Counts

Over the last couple of months of we have also started to provide monthly as well year to date search counts. This gives new search terms (and papers)  an opportunity to be seen.

See:  2010 to date

A word about John Wayne…

Searches for John Wayne continue to rank at the top of both our annual and monthly statistics. Using the Advanced Filter for August 2010 we can see that there were 68 variations on keywords with John Wayne sending a total of 392 searches. These accounted for 2.28% of our search traffic.

The paper, “Is that you John Wayne? Is this me?”: myth and meaning in American representations of the Vietnam war by Professor Simon Newman is freely available from the publisher link in the record in Enlighten.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s