One of my primary duties at Bamboo is to monitor traffic across our Web sites. I use a couple of different services for monitoring traffic, including Hitslink and Google Analytics. I like Google Analytics a lot. The user interface is intuitive, and Google does a good job of making the most important metrics easy to find. I also find Google Analytics performs better than other services, providing virtually instantaneous results to most queries.
However, when reporting site traffic to the company at large, I can't ask people to log into Google to see how things are going. I need to pull out the most important Key Performance Indicators (KPIs) and summarize them. Since all of our company operations are built around the SharePoint portal, it was natural for me to try to marry Google Analytics with SharePoint.
Unfortunately Google does not yet offer an API or web service that would enable us to dynamically pull in data from Google Analytics. Therefore my starting point was an import of historical data. In Google Analytics, I selected the metric I wanted to include in my dashboard (in this case Total Site Visits). I then set my timeframe to include all available historical data. Next I chose the data interval I wanted to track. For my purposes, I selected "weekly" data, this gives me a good exhibit to reference in my weekly team meetings. I'm able to report to the team how our activities from the previous week positively or negatively affected overall site traffic.
If you export the data as a CSV file, it opens automatically in Excel. There are some column headers and other comment data included in the export, I simply deleted those and trimmed the file down to two columns, date range and site visits.
I was initially concerned about the date range data that Google provided. The raw data didn't look extremely readable (e.g. compared to "Week of June 1st - June 8th"). I was afraid I might have to go through all the historical back data and do some kind of manual conversion. My concerns were unfounded however. At least for weekly data, Google's concatenation of Year, Month, Date turns out to be a very handy, sortable format to work with. When I later configured a data sheet view, I was able to sort the list in reverse chronological order showing the most recent data first.
Next step was to import the Google data into SharePoint. I simply created a new list, choosing the "Import Spreadsheet" option from the options under Custom Lists [A trick I learned reading SharePoint Blank, Working with Spreadsheets in SharePoint?] I selected the table range, hit import and viola, my Google Analytics data had made it into SharePoint.
Naturally a two column list in SharePoint wasn't the sexiest addition to my department site. I wanted to represent the data visually and make it easy to spot trends. To accomplish this, I used two of Bamboo Solutions most popular Web Parts, Chart Plus and Data-Viewer.
I simply created a new Web Part page, added an instance of the Bamboo Chart Plus Web Part and the Bamboo Data-Viewer Web Part. I configured Chart Plus to generate a simple bar chart, and beneath it set up Data-Viewer to show 6 weeks of the most recent data points. The result was a very satisfying display that has become a critical feature of my department site:
How will I update the data? Again, until Google Analytics makes an API available, it's going to be a manual process. I could either import a new list every week, or simply add a new data point each week. I will likely just get in the habit of adding a new list item each week while I'm compiling my status report.
This was an incredibly simple exercise. I'm sure there are dozens of other ways to accomplish the same display. As a use case, I think this is a great example of how SharePoint can empower even novice end users, especially when armed with powerful easy-to-use Web Parts like Bamboo's Chart Plus and Data-Viewer.
As I evolve my department dashboard, I plan to leverage Bamboo's List Rollup Web Part in conjunction with Chart Plus and Data-Viewer to plot multiple data sources on the same chart. I'll even combine sales and download data and plot them against our site traffic metrics.
I hope to hear from others who have found more creative ways to leverage Google Analytics within their SharePoint environment. Please share your creative applications with the rest of Bamboo Nation!
Posted
Sep 08 2008, 03:31 PM
by
Steve Gaitten
My name is Steve Gaitten, I am Director of Online Operations at Bamboo. My primary mission is to make Bamboo Nation the most useful SharePoint community site on the web. I am also focused on ensuring a world class shopping experience for customers who visit the Bamboo Solutions Online Store. Prior to Bamboo, I spent over a decade at America Online. At AOL my most recent roles included Director of Product Management in the Messaging & Social Media division as well as Managing Editor of AOL Money & Finance. I am a patented inventor, a bad golfer, an enthusiastic horticulturalist and a dog lover.