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.

Key steps

  1. Grant Access. If you have not already done so, click Manage Datasources and then Google Analytics on the right side of the "My ShufflePoint" page, and follow instructions. Upon granting access, you will see a list of available accounts and profiles.
    Select a default profile and timeframe.

  2. Download the IQY file. You will use this file in Excel to connect to ShufflePoint. You only need to do this step once. Click here to download IQY file.

    * To find Excel's default IQY folder: click Data / Import External Data / Import Data and note the location.

  3. Get Key. Log into ShufflePoint, and click "Get Excel IQY files and keys" on the "My ShufflePoint" page. Select "Generate Un-scoped Key". Scoped vs. unscoped keys are explained on the key generator page. You can get a new key any time.

  4. In Excel:
    1. Create a worksheet and paste into different cells:
      • The key generated above
      • A timeframe (eg. lastMonth)
      • Your Google Analytics profile id or name
      • Your AQL query

      Note: If your query is longer than 256 characters, Excel will not allow you to run the query (you may see a "bad parameter" message). You can split your query into parts and paste each of those parts in multiple adjacent rows. Or you can use our query shortener.

    2. Selected a destination cell, then
        Windows:
      • Click on Data > Get External Data > Existing Connections.
      • Select or browse for the IQY file you downloaded. Click Close.
        Mac:
      • Click on Data > Get External Data > Run Saved Query.
      • Select or browse for the IQY file you downloaded. Click Close.
      At first prompt, if not already selected, choose where you want the data to appear.
      Click "properties..." and in sub-prompt:
      • UNCHECK "Background refresh" (important to avoid "refresh all" errors)
      • UNCHECK "Adjust column width"
      • CHECK the third radio "Overwrite existing cells with new data, clear unused cells"
      Then at each of the following prompts, point to the cell that contains your key, timeframe, profile, and query.
      Check the boxes that say Use this value/refrence for future refreshes.
      Note: If you split your query earlier, select all of the cells that contain your query.
    3. To run a different query, simply change the query in Excel. Likewise for timeframe or web profile.
    4. To run additional queries, follow all of the steps above and choose a different location where you want the data to appear.
    5. Press Data > Refresh All to run all queries.

Details and screenshots

Here are two sample queries you can paste into the query cell.

query 1: key metrics by browser

query 2: pageviews and bounce rate by continent

If you use these two queries, and organize your IQY parameters the same, your resulting worksheet would look like:

Data Range Properties

The sub-prompt dialog should have these settings:

Working faster

Rather than repeat the above steps for additional queries, we can suggest this shortcut: copy an existing web query.

  1. First, select the cell range of the first query result table, select "Copy" from menu or context menu
  2. Select a new cell (make sure there is room below), and then "Paste".
  3. Change the query parameter's cell reference. Right-click a cell in web query result table. Choose "parameters...", then "query".
  4. Select a different query cell location

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.