ShufflePoint Query Setup and Organziation

Getting Started with ShufflePoint

/

GettingStartedWithShufflePoint

Overview

ShufflePoint is a paid application that uses Excel’s built-in “Web Query” function to pull data from Google Analytics into Excel. It is an extremely powerful tool and allows you to take advantage of Excel’s data manipulation abilities. This gives you the freedom to develop compelling visuals that will help you quickly assess the performance of a website. When I was developing my first ShufflePoint report, I found that thinking about and planning data organization took the most time. My hope is that this article will help you graph your Google Analytics data in Excel with as little trial and error as possible.

Setting up a ShufflePoint Query

Before you can pull data from your Google Analytics account you will need to log into ShufflePoint and generate a key for your Google Analytics Property and a Profile (View) ID for the view you want to pull data from. Once you have those two things you can start setting up your Excel Spreadsheet.

To get started, mimic the image below when setting your first spreadsheet. First, add your key and Profile ID and designate a spot where you’ll enter a ‘Start Date’ and ‘End Date’ for your data pull. Under the start and end dates, add a place for the ‘Timeframe’ function which will format the dates to be compatible with ShufflePoint. In the empty cells below, you can enter your first query.

Shuffle Point Set-Up

 

Here is an explanation of what all those things are:

  1. The Account Key- This key is specific to a single account in Google Analytics and in conjunction with the profile ID is what allows you to call data from the API. All you need to know is you need one. Once you put it in your workbook you’ll never touch it again.
  2. Profile ID- This is a number ShufflePoint generates based on the view you would like to pull data from.
  3. Timeframe- This is where to specify the period of time for the report. The most important thing to remember is that formatting matters. The timeframe format must look like this: yyyy-mm-dd:yyyy-mm-dd where the first date is the start date and the second date is the end date. That formatting is not very user-friendly- if you need to change the date on a regular basis it can become an inconvenience. Instead, we can use the formula below to format normal dates into the correct shuffle point range.

Here is what it looks like: =CONCATENATE(TEXT(F5,”yyyy-mm-dd”),”:”,TEXT(G5,”yyyy-mm-dd”)).

Example: I know my start data needs to be 01-01-2014 and my end data to be 07-31-2014. Instead of entering it into that wonky format you can just use the concatenate function to reference one cell that is the start date and another that is the end date. Seems like I’m making things more complicated than they need to be, right? When we look at its application, you’ll see why this is useful.

  1. Actual application: I open my Excel sheet on September 2nd with the intent to run an August report. Knowing that I’ll always open this document to run the previous month’s report, I create an equation that finds the end date of last month. That equation is: =EOMONTH(TODAY(),-1). To find the start date, I then reference that end date and count back however many months of data I want. *Be careful of sampling here. ShufflePoint doesn’t tell you if your data is sampled so it is a good idea to double-check that your preferred timeframe doesn’t show skewed data. I like to use 18 months when I can. This allows me to see year-over-year comparisons and an additional six months which is great if your website is affected by seasonality. The start date equation for 18 months of data looks like this: =EDATE(G5+1,-18) where G5 is not an airplane but a reference for to the end date.

After you have the Key, Profile, and Timeframe set up you are ready start building queries! Queries have four main parts: metrics, dimensions, a timeframe (FROM) and a filter.

Queries are outlined as such:

  1. SELECT
  2. METRICS
  3. DIMENSIONS
  4. FROM default
  5. WHERE FILTER

METRICS & DIMENSIONS: A great resource for metrics and dimensions is in Google’s API documentation. You can search for common metrics and dimensions found in the GA interface. Note: not all metrics and dimensions are in this list, the API can be restrictive in this way. Double-check before you go plugging-in metrics into your spreadsheet. You must have at least one dimension listed and you are able to use up to 10 metrics.

FROM: ‘From’ can be used to dictate a comparison timeframe. ShufflePoint has options for last week, last month, last year, etc. I prefer to keep this as ‘default’ and do comparisons by running a second query for a different timeframe. This makes for cleaner data sets which makes the data easier to work with in Excel.

WHERE FILTER: Here you can use include/exclude equations to slice and dice the data you want. Filters can be any metric or dimension that Google uses in their API- it is not restricted by the metrics and dimensions in your query. Just like filters in Google Analytics, you can use include, exclude and REGEX (regular expressions) when filtering data. Here are example equations:

  1. Include: ga:region==”Pennsylvania”
  2. Exclude: ga:region!=”Pennsylvania”
  3. Regex Include: ga:landingPagePath!~”/blog/.*” excludes blog traffic data
  4. Include Regex: ga:landingPagePath=~”/blog/.*” includes only blog traffic

Query Organization

Knowing how many rows of data will be pulled is crucial to how you will organize your queries. The basic rule of thumb is: if you don’t know how many rows will be pulled, run the query in its own tab. When ShufflePoint pulls data, it writes it into the rows of Excel. So if you’re unsure how many rows of data it’s going to pull, you have the potential for overwriting another query. For instance, I have a ShufflePoint workbook that pulls multiple sets of 18-month data which I pull in a single tab. That same workbook has multiple landing page queries where each are on their own tab.

To make the process simpler and more efficient, we have developed a macro to help you with setting up your queries. Download the workbook and have it open when you are setting up your queries. First select the cell where you want to data to begin. Then run the ShuffleStarter macro. This ShuffleStarter macro is in beta, so leave a comment if you have a problem with it.

Shuffle Point Query Set-Up

Now you can easily and quickly report on Google Analytics data in Excel! Once you can pull basic data, you can experiment with different filters, segments, and combinations of metrics. You may even gain unique insights and trends that may have been missed in the Google Analytics interface.

 

Sean McQuaide is a Senior Account Manager at LunaMetrics. As the technical search marketing lead he is the go-to for website audits, search analysis, and site performance. Sean also leads LunaMetrics A/B testing service where he uses his experience identifying user intent to find and test conversion opportunities. Sean spends his free time racing sailboats in the Gulf, constructing massive sandcastles on the beach, touring breweries, and running 5 & 10ks.

  • http://www.shufflepoint.com Chris Harrington

    Hi Sean,

    Chris from ShufflePoint here. First, thanks for the article and the “new query” macro. I have a “new query” macro in my personal.xsb file bound to the “control-q” key combination, which makes it fast for me to add new queries. But I like your approach too – which is basically a Web Query Wizard macro. We’ve not see that approach done by any customers. Here’s a MSFT page on personal macros – which are global in that they can be used with any workbook:

    http://office.microsoft.com/en-us/excel-help/copy-your-macros-to-a-personal-macro-workbook-HA102174076.aspx

    Here’s a link to a text file with the global macros I have there
    http://shufflepoint-media.s3.amazonaws.com/personal_xlsb.txt

    I also would like mention using macros is completely optional with ShufflePoint. The vast majority of our customers refresh their reports using Excel’s built-in “refresh all” button.

    The TIMEFRAME is a very powerful clause in AQL. If you want the last 6 months as you had done, then rather than use an excel formula, you can use

    TIMEFRAME fullmonths:6

    or to get the full months in the current year

    TIMEFRAME monthsToDate

    Comparing timeframes directly in the query is also possible by adding a second timeframe to the clause:

    TIMEFRAME lastMonth, lastMonth2 — compare last month to previous month

    TIMEFRAME lastMonth, lastYear — compare last month to same month last year

    Sampling. You can report the presence of sampling in a query by using the xx:sampling metric:

    SELECT
    METRICS xx:sampling, ga:visits ON COLUMNS BY ga:visits DESC
    DIMENSIONS ga:medium ON ROWS
    FROM default
    WHERE
    TIMEFRAME yearToDate

    Again, thanks for the great article. We’re always available here at ShufflePoint to assist with complex report automation tasks.

Contact Us.

LunaMetrics

24 S. 18th Street, Suite 100,
Pittsburgh, PA 15203

Follow Us

1.877.220.LUNA

1.412.381.5500

getinfo@lunametrics.com

Questions?
We'll get back to you
in ONE business day.