This blog is NOFOLLOW Free!

Using Excel to analyze Analytics data

Posted on May 11, 2009
Filed Under MS Excel tips and tricks |

Many of us rely on visualizations to understand and analyze data.  While there are many tools that assist in analyzing data, Google analytics is one of the better tools used to measure website perfomance and matrices.

Google analytics can be a bit overwhelming and some portions are downright alien to many users.

A client recently ask how they could use excel to anaylze some Google analytics data.

The Problem:

Download monthly Google analytics data, import the data into Excel and display a user friendly interface for a customer in less then 8 hours (due to budget and time restraints).

The solution:

We created an excel spreadsheet that the customer could use to import google analytics data in csv format.

The spreadsheet contained the following:

Note: Each of these could have been placed in their own spreadsheets to segregate the data further.  We opted instead to place them in the same spreadsheet and hide the worksheets the customer did not need to see.  In this case the 20 traffic sources were hidden.

Each month, the customer will download a csv file from their analytics account and import the data into the spreadsheet (this could easily be automated but again the project was time restrained).

Graphs were created which are automatically updated to reflect the new monthly data and the customer will have a visual representation of their traffic sources for tracking purposes.

The monthly worksheets are raw csv data imported from the file that Google analytics generates.  Nothing special here.

The Graphs are also fairly straight forward - Displaying the site counts for the selected 20 referring sites, graphs for the bounce rate etc.  Here is an example of one of the graphs:

The worksheets for the site counts however, involved the use of vlookup Excel function (we could have used a few other funtions, but vlookup was quick and easy).

The design of the site count sheets required the use of 1 label to identify the site to be counted.  Each row was labeled Jan-Feb in col A.

There are 10 columns of data in the csv file which can be analyzed as needed:

Each cel in the site count reflects the value from one of these data elements using the following function:

=VLOOKUP(string-to-find!$col$row,where-to-find-it!$col:$col,Column-to-return,FALSE)

string-to-find!$col$row - Used to identify the name of the referring site in the current sheet.  Example:  If the referring site to count is Google and the name as it appears in the raw data is located in Col E, Row 1, then this entry would have the following value: Google!$E$1  which says look inthe sheet called Google at col E row ! for the string to look up. (Note: The “!” is required since we are using this formula to reference other worksheets in the same spreadsheet)

where-to-find-it!$col$row - This is used to identify which monthly sheet we want to find the string identifying the referring site.  Example: If we have 12 sheets, labeled Jan - Dec for the raw data, this entry would have the following value for January: Jan!$A:$Z which says: Look in the sheet called ‘Jan’ in the range of cells beginning at A and ending at Z.

Column-to-return - This is the column number whose data is to be retrieved if the string is found.  In the example, we are bringing back the First Column or the searched column.  If we wanted to bring back the second columns data, we would change this value to 2.

FALSE - This final element is telling the function whether to use a case sensitive search or not.  In our example we are not using a case sensitive search.

Here’s an example with some test data for Jan - May:

Overall, an easy spreadsheet for visualizing referring sites so the customer can compare their targeting site traffic.

Promote this article
SubscribeBlinklistBloglinesBlogmarksDiggdel.icio.usFacebookFurlMa.gnoliaNewsVineStumbleUponTechnorati

Comments

Leave a Reply