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.
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.
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.
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
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…
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>"
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.
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.