Behind every Next Analytics dashboard and report is a series of script commands. These simple text strings start with a command name and are usually followed by comma-separated parameters. When we added the ability to make Google Analytics queries, we had to create a new script command that would translate into a full query behind the scenes. We called it uiGetGoogleAnalyticsData, and it is this script command behind our fancy UI that lets you pick the metrics and dimensions, profiles and segments, time period and unlimited row count.

[update: 2011-02-17 Next Analytics now supports all the new dimensions and metrics made available by the GA API as of Jan.25th, 2011, as well as offering support for adding sort, filter and custom advanced segments to your query. The command structure is the same as described here.]

To achieve a fully automated script, we had to include all the information needed to make the query, and that includes the Google Analytics user ID and password, the profile ID for the web property of interest, and a long series of parameters that form the core of the query. Since we save the results of the query direct to a file, we also needed the file name.

Wherever possible, we try to eliminate complexity and avoid tedious tasks for the user. Early in our development, it was clear that most queries would be based on today’s date. People want to know this week’s pageviews, this month’s visits, this year’s trends, and they don’t want to be continuously changing the dates in the query. So we included an optional parameter that identified a floating period of time, relative to today, as in the ‘Past 28 days’. The structure became:

uiGetGoogleAnalyticsData,<userID>,<password>,<profileID>,<period>,<file>,<parameters>

Let’s break it down and look at each part:

<userID>,<password>

The user ID and password are your Google Analytics login credentials, and there are several ways in which they can be used.

1. In the simplest sense, you can enter the values of your userID and password as plain text and the command will work. Anyone with just a smidgen of concern about security might be a bit worried at this approach, though, since you might inadvertently send your password to someone as part of a spreadsheet you were working on.

2. When you save a command from the user interface, you will notice that the password contains a long sequence of characters that starts with “encrypted_string”. This is an RSA-encrypted version of your password that only Next Analytics can use. Anyone with Next Analytics can run this command and it will get your website data, but they can’t login to Google Analytics (or your email account) with it. This scenario is useful if your distribute reports and dashboards to others and you want them to be able to refresh the workbook themselves.

3. For the extremely security conscious, you can leave the fields blank. When Next Analytics tries to execute the script, it will pop up a dialog box and ask you to log in. This is how we ship our dashboards – the fields are blank. (* see caution below)

4. Logging in every time can be tedious (and we avoid anything that is tedious, remember?), so we provide the option to save your login and (encrypted) password to a local file. When Next Analytics comes across a command with empty fields, it automatically looks in that local file for the credentials to use. The result feels automated, but it will only work that way on your PC. You can share the spreadsheet with others without worry of giving anything away, but if they want to refresh it, they will have to supply their own userID and password.

<profileID>

The profile ID is a unique identifier for the web property and a collection of filter and goal definitions. New Google Analytics users often find this confusing, but this is not the account ID that is used in the tracking code. You can have multiple profiles per account, but most new users only have one. See my previous article on how to find your profile id.

<period>

When defining the floating time period, the UI provides a limited number of choices but you have a lot more flexibility with the script command itself. Basically, anything of the form ‘Past N days’, ‘Past N weeks’, ‘Past N months’ or ‘Past N years’ where N is some number. In all these cases, the end date is Yesterday and the start date is calculated as that number of days, weeks, months or years prior.

[update: 2011-02-17 the period field now supports a number of new floating period types, including LastCalendar and ToDate. See the User Guide for more information.]

Since Google Analytics is not a real-time service – numbers may be delayed for a few hours as they are processed – it is common to only look at complete days of traffic, and hence, the floating periods by default end at yesterday. There are times, however, that people want to see the most recent activity, so we have included a special annotation to make the floating period end on today: “Past N days~today”.

If you choose to use specific dates for the query, enter ‘no’ (without the quotes) for the period.

<file>

The filename should be supplied with a “.csv” extension as it will be saved in a comma-separated-value format. Note that Microsoft Excel has a number of different CSV formats, and we recommend you ALWAYS use the “CSV (MS-DOS)” version or you may suffer from lost or corrupted data when working with any CSV files.

The file will be saved to the ~\My Documents\Nextanalytics\data\ folder, which is the default folder we use for reading or writing all data files. When interacting with the user interface, this action is almost transparent as it saves the query and loads the file automatically.

<parameters>

The parameters field is both the simplest and the most complicated. It is simple because, with a couple of exceptions, it is passed straight through to Google’s servers. You can enter anything you want, including filters, dynamic segment definitions, sort order, and any dimension or metric you want. Next Analytics will send the command as you entered it and Google will determine if it is accepted or not (see: GA Data Feed Reference).

It can be complicated because there are a number of rules about what dimensions can be used with what metrics (see: GA Valid Combos). The Next Analytics UI is programmed with the rules so you don’t have to guess what will work together, but if you are manually changing the command, you’ll have to figure it out on your own. An invalid command will result in a “400 Bad Request” error.

Also, while you can specify the start-date and end-date as part of the parameter list (e.g. &start-date=2010-07-01&end-date=2010-07-15), if you have used a floating time period the dates will be ignored and calculated values will be used in their place.

Finally, the raw Google Analytics API limits you to 10,000 rows per query and they let you set a ‘start-index’ so you can send multiple queries to retrieve larger result sets. We, of course, found that to be too tedious, so we let you enter any number you want for ‘max-results’, and Next Analytics will transparently get it all for you. As a result, if you choose to include a ‘start-index’ in the parameter list, you may get unpredictable results. Note: if you leave out the max-results parameter, Google will limit your query to only 1000 rows.

Example

Putting it all together, we end up with a script command that looks like this (wrapped to fit in the space):

uiGetGoogleAnalyticsData
,my-account@gmail.com
,encrypted_stringu3+gyS8CAfNotARealPasswordTkk2oL2MHI2FW4kyCKHyPIcKA="
,"12345678"
,"Past 30 days~today"
,"NextAnalytics.csv"
," &dimensions=ga:date,ga:source &metrics=ga:visits &segment=gaid::-1&max-results=1000000"

Footnote

* Caution regarding empty cells: Our script commands can be entered as comma-separated strings in the worksheet column A, or you can put individual parameters into adjacent cells (column A, B, C, D and so on) without the commas. All our dashboards use the latter method for the uiGetGoogleAnalyticsData commands to make it easier to include cell references for individual parameters of the command. When the command is entered in this way, it is important that the cells are not completely empty, or the command may not be reconstructed properly. “Empty” cells should contain a space character.