Quick Start - Google Analytics with Excel using IQY Files

Excel IQY files are supported on the Mac and Windows. They are used to dynamically import Google Analytics data through ShufflePoint into an Excel cell range. These cell ranges can then be bound to charts and formatted tables.

Setup step: Associate your ShufflePoint account with Google Analytics

If you have not already done so, click the Google Analytics link on the right side of the “My ShufflePoint” page, and follow Google's instructions. Upon granting access and returning to the ShufflePoint site, you should see a list of Google Analytics accounts and profiles which may now be queried using ShufflePoint.
View the tutorial movie.

Step 1: Open the Analytics Query Tool

Log into ShufflePoint, and click “Launch Analytics Query Tool” on the “My ShufflePoint” page.

Step 2: Configure IQY File

Click on the “Excel” tab in the center panel. The query tool should appear as follows. The default settings will be used for this Quick Start.

Step 3: Download IQY File

Click the “Download” button and select a save location for the IQY file. We recommend you save the IQY file in the location (directory) that Excel defaults to when adding Web Queries. Within Excel click Data / Import External Data / Import Data and note the location.

Step 4: Create a Query

Click on the “Query” tab in the center panel and then copy the following query and paste it into the “Query” area. Once the query has been pasted, click the “Get Results” button on the right.

SELECT
   METRICS ga:uniquePageviews, ga:visits, ga:newVisits ON COLUMNS
   DIMENSIONS ga:country ON ROWS TOP 25 BY ga:uniquePageviews DESC
FROM default
WHERE
   TIMEFRAME default

The query tool should appear as follows. Click the “Copy to Clipboard” button in preparation for pasting the query into Excel.

Step 5: Open Excel

Create a new workbook within Excel. The IQY file downloaded for this Quick Start will prompt for three parameters; query, profile, and timeframe. All queries will use the same cell references for profile and timeframe, and individual cell references for each query.

Paste the query copied to the clipboard in Step 4 into cell B4. Type in the labels in cells A1, A2, and A4 and adjust the cell formatting on row 4 to wrap text and set the vertical alignment to “Top”. Replace “www.shufflepoint.com” with a profile name associated with your ShufflePoint account.

Excel should appear as follows except your default profile will be shown in place of www.shufflepoint.com.

Step 5: Create Excel Query

Select cell B6, and then click Data / Import External Data / Import Data and select the shufflepoint.iqy file downloaded in Step 3. Accept the default for the data location.

When the “Enter GAQL query” prompt appears, click the icon to the right of the entry area and select cell B4. Check “Use this value/reference for future refreshes” and click the “OK” button.

When the “Enter default timeframe” prompt appears, click the icon to the right of the entry area and select cell B2. Check “Use this value/reference for future refreshes” and click the “OK” button.

When the “Enter default profile” prompt appears, click the icon to the right of the entry area and select cell B1. Check “Use this value/reference for future refreshes” and click the “OK” button.

Excel should appear as follows except www.shufflepoint.com will be your profile.

Returning to a previously saved workbook

Refreshing Queries

When you open your spreadsheet with Excel at a later date, click Data / Refresh Data. This will refresh all the queries with the latest data.

Changing Defaults

You may put any profile associated with your ShufflePoint account in cell B1. Click the “Google Analytics” link on the “My ShufflePoint” page to see a list of your available profiles. The timeframe in cell B2 can be changed to any valid value listed in the Analytics Query Language (GAQL) reference.