Quick Start - Google Analytics with Excel using Web Queries

Web Queries created within Excel are only supported on 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: Start a new workbook in Excel

Step 2: Log into ShufflePoint using a Web Query

Within Excel, click Data / Import External Data / New Web Query to create a new query. This opens a browser window within Excel, with which you go to http://www.shufflepoint.com/feed. This will bring up the login screen show below. Click “Yes” to the “Do you want to display the nonsecure items”. Enter your ShufflePoint username and password and click the Login button.

Upon logging in, you will see the ShufflePoint web query "welcome" page.

The "Current session settings" section depicts your login name and your current default Profile and Timeframe. If this is a new session, these values will be those selected in the Google Analytics page linked off the My ShufflePoint page.

Step 3: Change session defaults

Change the default profile and timeframe to be used for the workbook you are editing by clicking the "Change session defaults" link, which will display the page below.

The selected profile will be used for all queries defined without a profile. The selected timeframe will be used for all queries that use the "default" timeframe. The advantage of using these defaults is that you can create one spreadsheet template report and easily use it for multiple profiles and timeframes without changing the underlying queries.

We recommend that you import this settings information at the top of the first tab of your spreadsheet. This is accomplished by clicking the yellow box just above the "User:" label (not the yellow box at the top), and then clicking the "Import" button at the bottom on the right. This special query is referred to as the "Login Status Query", and will appear within Excel as shown below.

Step 4: Create a Data Query

Now you are ready for your first Google Analytics query! Within Excel, click Data / Import External Data / New Web Query to create a new query. This opens a browser window within Excel, with which you go back to www.shufflepoint.com/feed. You will see the same screen as when you logged in. Click "New query in GAQL Studio" to launch the query builder.

Use drag-and-drop to select the metrics and dimensions of interest. Note that only one profile can be selected unless you have chosen the "GA Profile" dimension. If you leave the "Profile" box empty, then the default profile will be used. See the "Session Defaults" section below for further details. Click "Get Results" under the Profile box in the upper right to display the query results in the grid at the bottom. Once you are satisfied with the results, click the "Continue" button at the bottom right (do not click the "Import" button yet). This will bring up the following page.

Step 5: Web Query Options

If you click the "Options..." button in the upper-right corner of a web query window, the dialog box show below appears. Under the Formatting section, choose "Rich text formatting only" to have your imported GA data be properly formatted. It is not likely that you would have a reason to import the data unformatted (it would not cause any harm, but you would have to apply cell formatting from Excel after importing the data). Unfortunately, it does not seem to be possible to tell Excel to remember this formatting selection, so for each new web query you will have to remember to open the Options dialog and select "Rich text formatting".

None of the other settings in the options dialog are relevant and the default values can be kept.

Click the "Ok" button to dismiss the Web Query Options dialog.

Step 6: Import the data

Click the yellow box above "data table selector". The yellow box will become a green checkbox (see screenshot "Query Import" above). Then click the "Import" button in the lower right. The refreshable query results will then appear within Excel.

Returning to a previously saved workbook

Refreshing Queries

When you open your spreadsheet with Excel at a later date, right click on the "Login Status Query" and select Edit Query. This will bring up the login screen. Follow the steps described in the "Step2: Log into ShufflePoint" section above.

Upon login, you will be shown the current session settings screen. It is a good practice to select the status table and import it into Excel since it has the last refresh date.

Once the login process has been completed, you can refresh all of the spreadsheet's queries at once by clicking “Data / Refresh Data” within Excel. When you close Excel, your login session will be ended.

Changing session defaults

Follow step 3 instructions to change the defaults for the workbook. After changing the defaults, clicking "Refresh All" will refresh all queries in the workbook. Queries set to use a default profile or timeframe will be refreshed using the new defaults.