Automating Google Analytics Reports

If you followed my advice in my last post, you now have your web site traffic segmented perfectly in Google Analytics using custom segments.  While this gives you tremendous insight into where your customers are coming from, it can take some work to spit out meaningful reports each week.  While Google does have built-in reporting tools, you can only look at a few segments at once and frankly, the output of the reports can be a bit limited.

My preferred approach for analyzing Google Analytics data is to use the tool that so many office workers rely on every day - Excel.  Excellent Analytics offers an excellent tool for pulling Google Analytics data into Excel, and last time I checked, it was free.  What I will show you how to do is to use Excellent Analytics to build a spreadsheet that automatically pulls your weekly Google Analytics customer traffic data by segment into Excel.

The first step is to install Excellent Analytics.  I won't cover that here as the software provides instructions for that - it is a plug-in for Excel so it is pretty straightforward. Once you get it installed, you should see an extra tab in Excel like this.  Note the extra tab called "Excellent Analytics" on the right.

Next you will click on New Query and you will need to sign in with your Google Analytics account information.  You now can pick the metric you want to report on.  Let's pick "visits"...

Now pick the segment you want to report on.  Use the custom segments that we set up last week.  You will have one query for each segment...

Finally pick the start and end dates. When you are ready, hit the execute button.  This will bring the data back into Excel.  You basically will repeat this process for each segment you want to track.  You can summarize the data in a table or in a chart.  Each time you need to refresh the data, just hit "update query" for each segment, change the start and end dates if you like, and hit execute.  You will almost instantly have a powerful chart showing where your traffic is coming from that you can show to your management.