HOWTO: Web Analytics Pivot Chart in Excel
eMetrics Get Ready
I bet a small child could:
- Push a few buttons
- Create a chart with bars, lines, pies or whatever
- Export that and declare themselves ready to attend eMetrics
From “The Joy of Statistics”, Professor David Spieghalter of Cambridge provides my preferred guidelines for analysis:
Just looking at one thing at a time doesn’t tell you very much. You’ve got to look at the relationship between things. How they change over time, how they vary together, and that’s what correlation is about.
That’s how you start trying to understand the processes that are going on in the world and in society.
By the end of this post you will have the knowledge to clean up messy data in Excel, and then use that data to build a PivotChart to use in data exploration as part of your analysis.
I even throw in the code to fix the date exported from the Google Analytics Data Export API.
- Submittal Instructions
- Who Might Submit an Entry?
- What Day Is It?
- Excel Functions
- Google Analytics Export API Excel Date Fix
- Weekday Matters
- Excel PivotChart
- Simply Stated
- Trends and Ratios
- More Charts Tomorrow
For completeness, here are the instructions to submit an entry for the FREE eMetrics pass:
- Send an email to firstname.lastname@example.org
- With the subject “Submittal”
- ATTACH IMAGE FILE AND TEXT DOCS
- On or before Friday February 11, 2011 12:00 Midnight PST
- Winner notified Monday February 14 at some point BY EMAIL
- Required In The Email:
- Your Name
- Your Email
- Your Phone Number
- Your Physical Address
- FEEDBACK YES or NO
- Your Personal Website
- Your Twitter Account
- Your LinkedIn Profile
Who Might Submit an Entry?
- If your employer just can’t budget eMetrics
- If you are just getting out of the UBC program and would like to show off what you learned
- If you are transitioning into, or work tangentially with web analytics
- If you are a business owner/employee who wears many hats, one of them online optimization
- I am looking forward to your entry
Cleanliness is Next to Data Analysis
Data cleansing and preparation is key to many analysis, we are still missing two key items which will enable us to create a PivotChart in Excel that is pretty rockin’.
What Day Is It?
Every website has cycles of traffic related to some temporal quality, commonly seen are:
- Season of the year
- “Waaaiiiiiit a minute, you sell more shorts in the Summer?”
- Day of the Week
- “Waaaiiiiiit a minute, people have more time for the site on the weekend?”
- Hour of the Day
- “Waaaiiiiiit a minute, people are in a hurry around lunchtime?”
All common sense observations that we should attempt to implement here.
Google Analytics Export API Excel Date Fix
If you are using Google Analytics Data to provide a chart, the GA date format is “YYYYMMDD” when you export through the API. In Excel this can be fixed with a few very simple Excel function calls:
CONCATENATE combines the strings produced by the two MID and one LEFT functions, those functions slicing up the original string.
No more filling cells with replacement values!
If you add a column in between the date and hour columns, then use the Excel function WEEKDAY referencing the Date column you will get back the weekday.
The value returned is of the wrong format, still formatted as Date when it should be Number, right click and change it to an integer Number.
The Weekday option I chose was to start numbering the week on Sunday, so a 2 is Monday.
Data Analysis in a Spreadsheet
Select the Insert tab, PivotTable, and then PivotChart.
After confirming the range selection Excel drops you off in a new sheet that should look something like this.
The right hand menu is where the controls are easiest for me to use, altough drag and drop into the chart is an option.
Select the data you would like to analyze, I have set my initial report up with the axis fields of Date, filters of Weekday and hour.
Recall our summary statistics, the kurtosis, or “pointiness”, of New and Returning visitors was quite different. Here we can see the difference.
Instead of spending time flipping the variables around trying to find insights, we used a little statistics to guide our investigation while working more efficiently.
In the PivotChart the gray buttons are the items set up originally, the top right here are the filters. We could filter by weekday, hour or weekday and hour.
Are the behavior of people to your site the same at lunchtime Monday through Friday?
The result of our analysis beforehand leads us to this chart, here the different kurtosis of the two segments, New and Returning, can be clearly seen.
Curious about a specific date? Hover over it and Excel will give the source data.
Adding log trend lines to our chart is a simple task, explore the top lable PivotChart tools for that and other useful tools.
Now the kurtosis, or “pointiness”, from the summary stats makes more sense. The log trend lines show the differential increase in visits from New or Returning visitors.
We are getting more New visitors, but those New visitors may not be turning into Return visitors. If these increased New visitors are associated with a some paid advertising campaign it would probably be a good time to really examine the efficacy of that channel.
Trends and Ratios
This get us closer to the trends and ratios emphasized throughout “The Big Book of Key Performance Indicators.” Watching trends and ratios is likely far more instructive than raw data.
These data are limited, lacking a true business objective in a conversion.
It is entirely possible that you would end up posing questions as a result of analyzing these data, that is encouraged. Not every problem can be answered with the first set of data pulled.
Tumbling the data over mentally, in the spreadsheet and in graphs should provide a direction for your submittal.
More Charts Tomorrow
Tomorrow I will post some additional charts of data, not necessarily these data . . . I don’t want to give any answers out!