Quick Start - Excel reports using ShufflePoint

ShufflePoint creates Excel reports by leveraging the built-in data import features. No Excel add-in is used, and you have the full capabilities of Excel at your disposal. ShufflePoint presents a web datasource to Excel by means of an IQY file. The IQY file is a text file which tells excel how to find the ShufflePoint datasource.

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. Select a default profile and timeframe. These defaults will be used in queries where "DEFAULT" is specified for the profile or timeframe respectively.

Setup step B: Download appropriate IQY File

ShufflePoint's IQY-based datasources are supported both on Windows and Mac version of Excel. We provide two IQY files. A Windows-only file which is optimized for Excel on Windows, and a cross-plaform IQY file which will work with both Windows and Macintosh. If your users who will be refreshing reports may have Macs, download and use the Mac &l Windows IQY file.

The generic IQY files have four parameters:

Key - authentication key
Timeframe - query timeframe
Profile - Google Analytics profile
Query - AQL query to retrieve data

When importing data, you will reference Excel cells to get values for these parameters.

Windows only (save to 'My Document/My Data Sources' *)
Mac & Windows (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 specific labels in column A are not important - they merely help document the IQY paramerters in cells B1, B2, B3 & B5.

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”.

* Scoped vs. unscoped keys are explained on the key generator page.

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”.

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

Step 4: Create Data Query

Select cell A7. This will be the top-right-most cell of the imported data table. The Excel menu and ribbon selections you make next will depend upon your version of Excel.

For Excel 2003 on Windows, use the Data menu to select Import External Data / Import Data... and select the IQY file downloaded in Step 3. You can limit the list of available datasources to only show IQY files by selecting "Web Queries (*.iqy)" in the "Files of type" dropdown.
For Excel 2007 or 2010 on Windows, select the Data tab and click Existing Connections in the ribbon. Select "Connection files on this computer" to limit the connections list to IQY files. Select the IQY file downloaded in Step 3.
For Excel 2008 or 2011 on Mac, use the Data menu to select Get External Data / Run Saved Query.... Change the "Enabled" dropdown at the bottom of the diaog to "Query Files" and select the IQY file downloaded in Step 3, then click "Get Data".

You are presented with a dialog titled "Where do you want to put the data?". "Existing (work)sheet" should be selected, and the textfield should have the address of cell A7 which you had selected earlier. If you hadn't selected the cell, you may click on it while the dialog is displayed and the textfield will update to reflect the selection.

Click the "Properties..." button. The "External Data Range Properties" sub-dialog displays. UNCHECK "Adjust column width", and CHECK "Overwrite existing cells with new data, clear unused cells". These two settings prevent columns from resizing or shifting around as you add additional queries to a sheet. We also recommend to UNCHECK "Enable background refresh" because otherwise Excel may error when trying to update many queries in parallel.

Click the "Ok" button.

You are now prompted for four parameter locations.

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 down and to the right of cell A7. Excel should appear similar to the following figure:

Step 5: Add another query

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

Select cell A19 and repeat Steps 3 through 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 which contains the second query.

Rather than repeat the above steps for additional queries, we can suggest this shortcut. Select the cell range of the first query result table (cells A7-D15, or whatever is the full range), select "Copy" from menu or context menu, select cell A19, and then "Paste". By doing so, you can avoid going through the above steps again and again.

However, you will have to change the query parameter's cell reference. Right click on cell A19 and select "Parameters..." from the context menu. Select the "Query" parameter and change the cell address to reference A17. After you click "Ok", the cells in range A19-C24 (or there abouts) will get the query results. Whew - lots of clicks saved!

Want to save even more clicks? Have a look at this blog posting which gives a short macro you can use to automate the process of adding queries.

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 all 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.

Refreshing a single queries

Right-click the top-left cell of a query result table. Select Refresh Data from the context menu. The data for the selected query table will refresh.

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 aren't limited to one column.