How to Use Query Explorer to Get Any Data Out of Google Analytics
What is Query Explorer?
You have probably encountered situations in which Google Analytics interface didn’t allow you to get the data in the dimensions you wanted to. The easiest example would be: try getting the percentage of website visitors by Operating System -> Browser -> Browser Version in a single view in Google Analytics (quite a frequent request from QA teams, isn’t it?).
Most likely, you will end up using a Custom Report, but you will still hit a wall, as Google Analytics interface isn’t designed to present the data sliced by more than 2 dimensions (i.e. getting the number of users by Operating System -> Browser is easy), and that’s why Google created Reporting API. Not happy with the limitations of the UI? Just use the API.
API is all fun and games for engineers, but for regular mortals, like us, the marketing folks, Google created Query Explorer. Elevator pitch for Query Explorer would be “explore the power of Google Analytics Reporting API without writing a single line of code”.
If you are an advanced user of Google Analytics (and not an engineer), then I suggest you start with visiting and bookmarking the Query Explorer page, and for those who don’t trust clicking links, here is screenshot of Query Explorer:
If you’re on Mac, use Chrome of Firefox, as there is no “Get Data” button if you’re using Safari. Not sure if this is by design, but the most important button is just not there for Safari users.
I’ll explain each field in more details below, but for now, in order to get the data out of Google Analytics, you need to do two things:
- Select the correct Google Analytics View that contains the data;
- Formulate a data query using several fields, such as dimensions, metrics, filtes, etc.
If you’re successful with the above, you will get the data out in a form of a table and a link to a .TSV file (don’t worry, it’s almost the same as CSV, which stands for Comma Separated Values, just that the values in a TSV file are separated by TABs).
Selecting Google Analytics View
This part is really straightforward: if you are logged in, then the list of Google Analytics Accounts, Properties and Views will be automatically populated. Therefore, everything you have to do is to select, using drop downs, the correct Google Analytics view that contains the data you want to get.
If you have selected one of the Views, the field “ids” (not sure why the use of plural) will be autocomplete with this view’s ID.
Formulating Queries
Formulating a query boils down to telling Query Explorer which metrics you want to get, sliced by which dimensions, in which date range. You can also get the data for a specific segment of users, as well as apply filters and sorting.
Dimensions and Metrics
If you have never worked with Google Analytics Reporting API, the concept of dimensions and metrics might be confusing at first. Let me use a screenshot from Google Analytics to explain the concept:
The above image shows standard Acquisition -> Channels report from Google Analytics. In this case the dimensions are Default Channel Grouping, and the metrics are Sessions, News Sessions, New Users, etc. Let’s take another example, Acquisition -> All traffic report:
In the above example, dimensions are Source/Medium, while the metrics are Sessions, New Sessions, New Users, etc.
Once you are comfortable with the difference between dimensions and metrics, you can formulate a query for Query Explorer. A nice thing about Query Explorer is that it provides a detailed explanation of the each dimension and metric once you hover over it in the drop down menu.
Similarly to dimensions, hovering over a specific metrics in the drop down menu gives you an explanation of it meaning, i.e. in the example below the metric ga:adClicks is explained, as “the total number of times users have licked on an ad to reach your property”.
You can specify multiple dimensions or metrics using a comma as a separator (although there is a limit of 7 dimensions and 10 metrics), and I am pretty sure that over time you’ll lean their names by heart (just don’t forget that all dimensions and metrics start with “ga:”).
Segments
Similarly to the regular Google Analytics interface you can query the data for a specific segment (i.e. “Mobile Traffic”). If you expand the drop down, you will see a list of both “Default Segments” and the “Custom Segments” that you have defined. Apparetly, it is possible to formulate a custom segment right in the Query Explorer, but honestly, I never had a need for that.
Filters and Sorting
You can filter the resulting data using one or several dimensions as filters. For instance, the below will show filter for ogranic Google traffic:
ga:sourceMedium==google / organic
and the following will show organic OR paid traffic from Google:
ga:sourceMedium==google / organic,ga:sourceMedium==google / cpc
and finally, use this filter to get all ga:sourceMedium combinations that contain “Google”:
ga:sourceMedium=~google
As you can see from the above, I used “==” to filter for a specific ga:sourceMedium (i.e. “google / organic”), I used “=~” to filter for ga:sourceMedium containing a word “google”.
Also, I used “,” to specify several filters, in which case I got the results that include ga:sourceMedium of “google / organic” OR “google / cpc”. The final combination would be to list filters separated with “;”, which stands for “AND” logical operator:
ga:sourceMedium=~google; ga:sourceMedium=~cpc
Thus, the above will filter ga:sourceMedium that contains (“=~” operator used) “google” AND “cpc”, and would be identical to
ga:sourceMedium==google / cpc
Try it out as filters are a super powerful feature of the API.
In addition, you can sort the resulting data by the dimensions you specify and you can specify multiple dimensions to sort with (just separate them with commas). In case multiple dimensions are specified, the resulting data will be sort by the first specified dimensions, then by the second, then by the third, etc.
Start-date & End-date
Selecting date range is straight forward; however, it is important to pay attention to “This result is based on sampled data” warning. In case you receive this warning (next to “Get Data” button as in the image below), I suggest you to try selecting a shorter date range until you get rid of the warning. In some cases it want help, as you might be operating with a large dataset (i.e. trying to get each pageview by minute) and yet, shortening the date range can help quite often.
You can read more about Data Sampling in Google Analytics here.
Max-result and Start-index
Query Explorer will return the number of rows limited by the “max-result” field (10,000 in the example below). Similarly, to the Reporting API, the maximum number of rows to be returned is 10,000; however, you can use “start-index” field to get more than 10,000 rows.
Take a look at the example below: my query matched 23110 results, and as the “max-result” field was set to 10,000, Query Explorer returned exactly 10,000 rows, from 1 to 10,000. So how do I get the rows 10,001 to 23,110? By specifying the “start-index” parameter: if I set it to 2, then the rows 10,001 to 20,000 will be returned, if I set it to 3 then the rows 20,001–23,110 will be returned (there are just 23,110 results in this query, otherwise rows 20,001 to 30,000 would be returned).
Therefore, if you query returns more than 10,000 results, use “start-index” to iterate through those.
That would be pretty much it on formulating queries, so just hit the “Get Data” button to see your data. Don’t worry about making mistakes, Query Explorer error warnings are really helpful in formulating the query correctly.
Once you get comfortable with Query Explorer you will be able to get any data out of Google Analytics sliced by the dimensions you want.
Exporting Data and Sharing Queries
Once you have formulated the query and validated that it outputs the correct data, you can export the data into .TSV file (tab separeted values) by clicking “Excel TSV” button on the right.
In addition, you can share the result with your colleagues or clients: by clicking the “Share Report” button (as shown in the image below), you’ll get a link to Query Explorer with the query you formulated.
It is important to note, that sharing a link will only share the query (the values of the dimensions, metrics, segments, filters, etc fields that you have specified) and will not share any Google Analytics data.
In order, to run the query, a person receiving the link will need to have access to the correct Google Analytics Account, Property and View.
Finally, as Query Explorer is ideal for building regular reports (i.e. weekly AdWords campaign performance), you can save a link to the formulated query in a text file or a spreadsheet, and then reuse it whenever you have to run this report again.
Alternative, you can build a whole library of queries for all use cases:
- Users shown by Operating System, Browser and Browser Version
- AdWords Impressions, Clicks, Cost, CPM, CPC shown by date
- Users referred by Facebook shown by date
- and so on…
Hope this helps and don’t forget to visit my other tutorials:
