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.

Follow these steps to begin creating Excel reports with your Google Analytics data.

Setup step A: 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.

Setup step B: Download generic IQY File

The majority of ShufflePoint users will be best served by NOT generating their own IQY files and instead using the generic IQY file which can be downloaded here. These files use a parametized key which can be placed in a cell in the Excel workbook. Use the “Key Generator” link on your My ShufflePoint page to generate keys for use with these generic IQY files.

for Excel on Windows (save to 'My Document/My Data Sources' *)
for Excel on Macs (save to 'Microsoft Office X/Office/Queries' *)

* Or save in the directory that Excel defaults to when adding Web Queries.
Within Excel, click Data / Import External Data / Import Data and note the location.

Step 1: Start Excel

Create a new workbook within Excel. Populate the cells as show in the screenshot below.

The generic IQY file will prompt for four parameters; key, profile, timeframe, and query. Often a report will use the same key, profile, and timeframe. By pointing all data queries at the same cells, these values can be changed in a single place.

Step 2: Generate an access key

Log into ShufflePoint, and click “Key Generator” on the “My ShufflePoint” page. Select “Generate Un-scoped Key”. Select key in textfield and copy to clipboard. Paste the key into the Excel cell to the right of the cell containing “key”.

Step 3: Enter a Query

You would normally use the Analytics Query Tool to create your queries. For the purpose of the quickstart, just copy the query below and paste into the cell to the right of the cell containing “query 1”.

You may find it helpful to adjust the cell formatting of the cell with the query to wrap text and set the vertical alignment to “Top”.

Excel should now appear as follows except your key will be present.

Step 4: Create Data Query

Select cell A7, and then from menu click Data / Import External Data / Import Data and select the IQY file downloaded in Step 3.
Accept the default for the data location.

When the “Enter access key” prompt appears, check “Use this value/reference for future refreshes” then click cell B1 and then click the “OK” button.

When the “Enter default timeframe” prompt appears, check “Use this value/reference for future refreshes” then click cell B2 and then click the “OK” button.

When the “Enter default profile” prompt appears, check “Use this value/reference for future refreshes” then click cell B3 and then click the “OK” button.

When the “Enter query” prompt appears, check “Use this value/reference for future refreshes” then click cell B5 and then click the “OK” button.

The query should now run and populate the cells to down and to the right of cell A6. Excel should appear similar to the following figure:

Step 5: Add another query

In Cell A17, type “query 2”. In the adjacent cell, copy and paste this query:

Select cell A19 and repeat Step 5 for this second query. For the first three IQY parameters, select the sames cells as was done for the first query. For the query parameter, select cell B17 whic should contain the second query.

After the query runs, Excel should appear similar to the following figure:

Step 6: Refresh all queries with a different profile and timeframe.

Change profile value from default to one of the profiles listed on your Manage Google Analytics page. And change the timeframe value from lastMonth to yearToDate. Then under the Excel Data menu, select Refresh Data. Both query result tables should refresh to reflect running those queries with the new timeframe and profile.

Returning to and refreshing 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 Google data. Note that the IQY file does not have to be distributed to users. Users can refresh existing queries without the IQY file.

Managing Query Profiles and Timeframes

The defaulting logic in queries can be a bit confusing. So here they are in a nutshell:

Rule 1: If a query contains a profile ortimeframe, then that profile or timeframe will be used to satisfy the query.
Rule 2: If a query has the word “default” in place of the profile or timeframe, then the cell referred to for the IQY parameter for the profile or timeframe will be used to satisfy the query.
Rule 3: If a query has the word “default” in place of a profile/timeframe, and the cell referred to for the IQY parameter contains “default”,then the session defaults selected on the My ShufflePoint / Manage Google Analytics page will be used to satisfy the query.

Thus there is a three-level precedence model for profiles and timeframes. And here are some suggestions on how to make use of these rules for your reports.

In general, it is best to NOT use specific profiles in queries. Instead use "FROM default" in the query and reference cells containing the profile IDs.
Specific timeframes in queries are can make sense. If the report title is "Last month's web analytics", then having most of the queries use lastMonth will give the desired results. However, if you think you may sometimes run the report for the month before last, then leave the timeframes as "default" in the query and put the value lastMonth in a worksheet cell referenced by the query. Then to run the report for the previous month, simply change that cell to lastMonth2 and then refresh all data.
Employing Rule 3, where the defaults come from your ShufflePoint account settings, can be used to manage the refresh of a collection of related Excel report files and it gives you a single place to change the profile used for the whole collection. However it can lead to unintended side-effects if you or someone else using the same ShufflePoint account change the account defaults.
Our strong suggestion to ease the creation, reuse, and ongoing maintenance of Excel reports it to creat a “Settings” sheet as the first sheet in the workbook. Place in that sheet all of the keys, profiles, and timeframes for the queries which will be present on other sheets in the workbook.

Changing the access key

For this first tutorial, you generated a global or un-scoped key. The queries used in this report can access any profile under any account visible using the credential to which you granted ShufflePoint access.

You may wish to delegate report definition or refresh to a business department (if you are an in-house web analysts) or a customer (if you are a web analytics consultant with many clients under your GA credential). Scoped access keys allow you to control who can query or refresh what data. The key generation page contains a tree view of available GA accounts, web properties, and profiles. When generating a scoped key, select from the tree the desired scope and then click the "Generate Key" button. Paste this key into the key cell of a report. Recipients/maintainers of that report will only be able to query profiles below the selected scope entity.

Preventing report refresh

If you do not want a recipient of the spreadsheet to be able to refresh the report, delete the contents of the key cell before delivering the report.

External Data Properties dialog

If you click on the "Data Range Properties" button in the data toolbar, Excel will popup the “External Data Properties” dialog show below.

A good review of the settings in this dialog can be found here and here. Some of the settings you may wish to review include:

Name: The named range created with the table. When you create an external data range, Excel automatically creates a named range that covers the table. You can type a new name here which changes the named range.
Adjust Column Width: Checked, this causes the column widths to adjust to the data on refresh. Unchecked, the column widths stay the same.
"If the number of rows..." (Data Range properties): These options determine how the range is updated when the data changes and can affect how other data in your spreadsheet is handled. See the second link above.
Fill Down Formulas: If you put formulas in the column adjacent to the table, the will expand and retract as the range does. You arent limited to one column.