Our latest build includes the ability to have two timeframes present in the query. ShufflePoint will interpret the second time period as a previous period against which you wish to compare the first period.
A simple example of such a query is:
select metrics ga:visits on columns dimensions ga:medium on rows from default where timeframe lastMonth, lastMonth2
In English, is
"Get visits by medium for last month and the month before,
and get the values and the change in values"
Since only Excel imports using IQY files currently support this new feature, the first thing I'll do is get an IQY datasource file so that I can write queries in Excel. This is done by loggin in and on the My ShufflePoint page clicking the 'Download for Windows' link:
I save the file on Windows in "My Documents/My Data Sources" then start Excel. Following the Excel IQY Quick Start, I also go and create an access key using the "Get Excel IQY files and keys" link on the My ShufflePoint page:
I'll create a key which can only access the shufflepoint.com web property by selecting that radio button in the list.
Using the IQY datasource allows you to put the information about your queries into Excel cells. The screenshot below shows Excel setup for doing the data import. It is just a convention of mine to have the parameter labels in the first column.
After using the Excel "import external data" command, selecting the IQY I generated, and binding the datasource parameters to the appropriate cells, Excel will have the results in the sheet
A query with more columns is then addded into another cell and another "import external data" operation results in the following:
select metrics ga:visits, ga:avgPageviews, ga:timeOnSite, sp:newVisitsPct, ga:bounceRate dimensions ga:medium on rows from default where timeframe lastMonth, lastMonth2
The new two-period logic also allows you can create a pivot table:
select dimensions ga:medium on columns dimensions ga:month on rows from 70442 where metrics ga:visits timeframe lastCalendarYear, lastCalendarYear2 filter ga:medium != "cpc"
For this query I'll use a key to a GA account which has data going back a couple years. So using the generate key page again, I generated a key which will allow queries against my Active Interface GA account.
Since I had already run the query using the first key, I need to chang the key parameter (I could have just cleared the query results and started over, but it is good to know how to change query parameters). I right click in the results and choose "Parameters..." and for the key select the new cell (Note that with IQY files, you cannot right-click and choose "Edit query" since this will attempt to make a web query instead of using the datasource).
Editing parameters doesn't rerun the query, so I also click the "Refresh data" button in the data toolbar. The results are shown below. Notice that the columns dimension still gets split into three sub-columns even though it now has a dimension instead of metrics.
