Pulling Data from Google Analytics, Pt 4: ShufflePoint

Part 4 in a 4 part series looking at different tools for accessing the Google Analytics API to pull data into Excel.

Part 1: VBA Macros | Part 2: Tatvic | Part 3: Excellent Analytics

Part 4: ShufflePoint

ShufflePoint.com has a set of tools for “making presentations manageable”, including “dynamically associated Google Analytics data with Excel, Powerpoint, and Google Gadgets.  Unlike the other solutions reviewed in this series, there is a cost, which is $29-$199/mth depending on volume.  You can take a free 7 day test drive – BUT – you will have to provide a credit card to get set up (and then remember to cancel your account within 7 days if you don’t want to pay for the service).

Once you register and login, you open a new workbook in Excel and go to Data > (Get External Data) From Web and enter “https://www.shufflepoint.com/feed” into the New Web Query address bar.

Launching access to ShufflePoint from within Excel 2007

Launching access to ShufflePoint from within Excel 2007

Clicking on ‘New query in GAQL Studio‘ gets you to the handy interface below, where you can set up your Google Analytics query (you may have to log in first):

ShufflePoint Query Window

ShufflePoint Query Window

The available choices for Metrics and Dimensions correspond to the available options in Google Analytics, although with different terminology in some cases.

I found this to be a very user friendly interface with drag-n-drop functionality, some helpful notes in the ‘Help’ panel, and even definitions that show up when you roll-over metrics, etc.  It looks easy enough to change date range, but you actually have to select ‘Given‘ from the list in the ‘Date Range‘ dropdown first.  Otherwise, you just go with the provided date range options.  If you hit ‘Get Results‘ you get a preview of your report.  You’d expect that clicking the ‘Import‘ would then import your data from GA into your spreadsheet, but you’d be wrong.  In fact, the next step is to hit ‘Continue‘ and go through a couple more clicks to get the data imported.

You add some rich text formatting prior to import and here’s what it looks like for the query shown above:

GA Data Imported into Excel

GA Data Imported into Excel

Now, it’s true that in this case, you can create a custom report in Google Analytics to display the same data:

GA New Visits by Month

GA New Visits by Month

The benefit of using a tool like ShufflePoint to get the data easily into Excel is that you can then extend the data to get something like this, with accompanying chart:

Comibining GA + Excel

Comibining GA + Excel

And, with a few clicks, you can update the data from within Excel – for example for the next month.

So another tool worth checking out if you have a need to import data from Google Analytics into Excel.  As for me, I’m going to get busy figuring out how I can get build some loyalty and increase return visits!

Advertisements

Pulling Data from Google Analytics API, Pt. 2: Tatvic

Part II in a series looking at getting data from Google Analytics into Excel via the API

Part 1: VBA macros

Tatvic GA Excel Plug-in

The Tatvic GA Excel Plugin requires users to download and install a plugin in order to extract data from the Google Analytics API, unlike the VBA macro approach.  In exchange for this small inconvenience, you get a more robust tool with a built-in interface that makes it easy to grab the data you want.

Installation is intuitive and trouble-free.  Once installed, all you need to do go to the ‘Add-Ins‘ tab in Excel, hit ‘Login‘ and enter a GA username and password.  Click on ‘Add New Data Block‘ and all the accounts and profiles associated with that username will be available for downloading. Pick an account and profile, date range and range breakdown.  This breakdown feature is very handy, as it allows you to, for example, get a year’s worth of data broken down by month – something you can’t do within the GA interface.

Tatvic GA Plug-in screen

Tatvic GA Plug-in screen

After selecting the account, profile and date range you want to report on, next step is to pick your dimensions and metrics.

Tatvic GA Plug-in data selection screen

Tatvic GA Plug-in data selection screen

Hit next and the data lands on your spreadsheet, starting in a cell of your choosing.

Tatvic GA Plug-in data results

Tatvic GA Plug-in data results

Note that in this case I choose to breakdown the data by week, with the weeks being identified as numbers in the output. Not exactly optimal, but with some fiddling with Excel functions, the date for the first day of the week can be derived.

Some of the real convenience factors here include:

  • drop-down list of all available accounts and profiles associated with the username – easy to grab data from multiple profiles in the same spreadsheet file.
  • ability to breakdown time period by day/week/month or year
  • built-in list of all the available GA dimensions and metrics
  • easily add multiple dimensions and metrics, with preview pane showing generally what it will look like
  • can edit reports after the fact if you want to add a metric, dimension, etc.
  • ability to select starting cell, so you can add to existing data over time.

Tatvic GA Plug-in is currently available as a free demo.  Not sure what the future plans are, but at the current price it offers excellent value. Check it out at http://gaexcelplugin.tatvic.com and keep up to date at the Tatvic blog.

Pulling Data from Google Analytics API, Pt 1: VBA Macros

As mentioned back in April, the release of the Google Analytics Data Export API was bound to lead to a variety of options for accessing/manipulating GA data without going through the interface.  More recently, the Google Analytics Blog did a quick list of some of the approaches that are now available for extracting GA data into Excel.  There are some real advantages to being able to pull Google Analytics data into Excel – especially for larger sites with lots of rows of relevant data.  Being a ‘roll up the sleeves’ kinda guy, I thought I would test these various methods and assess some of the pros/cons of each.  There are 4 tools mentioned in the Google Analytics Blog:

1. VBA Macros (with template by Mikael Thuneberg) – described in this blog post

2. Tatvic Excel Plugin

3. Excellent Analytics

4. ShufflePoint

I plan to look at them each in some detail, so I will do a separate blog post on each one, starting with…

VBA Macros (demonstrated by Mikael Thuneberg)

This is the most straightforward approach and requires no additional tools other than Excel.  Mr. Mikael Thuneberg has provided a good explanation of how it works in his blog Mikael’s Excel Page and – even better for those of us not well-versed in VBA – a spreadsheet template that can be used to fetch data.  So the easiest way to get started is to download GA_data_fetch.xls from Mikael’s page and fill in the boxes.  Below is a screenshot of what I got with my login info and the default report settings:

GA-data-fetch1

You can use Google’s Dimensions & Metrics Reference to find other dimensions and metrics that can be added (using & between them), in which case the possibilities are almost endless.

GA-data-fetch2

The worksheet uses an array formula to display multiple dimensions and metrics. It is set up to display 34 rows of data but the VBA module can fetch up to 10,000 rows (which I think might be the max allowed by GA in 24-hr period – see the API Quota Policy).  In order to display a larger set, you will have to expand the area of the array formula.  This can be easily done:

1) select the entire expanded area (including existing data)

2) F2 to enter edit mode

3) CTRL+SHIFT+ENTER to copy array formula to empty cells.

A very flexible solution that quickly gets data into Excel for further manipulation.  You can set it up to pull data from different profiles into different worksheets in the same Excel file to have easy access to them. And it’s free!

Next up: Tatvic Excel Plugin

Floodgates Open: Google Analytics API Launched

Google Analytics Blog announced on April 21 the long-anticipated release of the Google Analytics Data Export API, opening it up for developers to get in and start building applications that can extend the power and availability of GA.  From the blog:

Developers can integrate Google Analytics into their existing products and create standalone applications that they sell. Users could see snapshots of their Analytics data in developer created dashboards and gadgets. Individuals and business owners will have opportunities to access their Google Analytics information in a variety of new ways.”

Big news, and we can expect to see lots of cool applications being developed in the coming months.  More info in the blogpost about what this all means and how to take advantage of it.