How to use the Google Analytics Query Explorer to export data

The Google Analytics Query Explorer tool is a powerful way to export Google Analytics data for your website. There is a version of Query Explorer available for both Universal Analytics and Google Analytics 4 (GA4). You can export data from Google Analytics using the Export function available from within the application itself; however, using the Query Explorer is preferable if you're looking to automate data retrieval or connect it to business intelligence or analytics software.

In this post, I'm going to walk you through the basics of using this tool to export data from Google Analytics UA.

What is the Google Analytics Query Explorer?

The Google Analytics Query Explorer is an interface that lets you construct API queries which you can use to retrieve data from your Google Analytics account. You can build queries that collect data from a specific account, property, and view. The query tool lets you specify data based on predefined metrics and dimensions. As you get more familiar with Query Explorer you can create custom filters and apply segments to export the exact data you need for your marketing reporting.

What's the benefit of using Google Analytics Query Explorer?

As mentioned earlier, you can export Google Analytics data using the Export function. You can export formats such as a PDF, Google Sheet, Excel file, or a CSV. This is useful for doing manual reporting and for putting together Excel dashboards or quickly sharing simple reports.

The benefit of using Query Explorer is that you can directly query Google Analytics to get detailed reports with the precise dimensions and metrics you need. More importantly, this tool allows you to craft a query that can be used to automate data retrieval from Google Analytics. As your marketing reporting becomes more sophisticated and manual reporting becomes more time consuming, you will likely want to connect to an analytics tool. The Query Explorer tool gives your team the ability to set up automated reports for your analytics.

You can create comprehensive, in-depth web analytics reports using this data. Our customers use Query Explorer to fetch data and develop automated dashboards and metrics. Here's an example of what you can build using PowerMetrics.

This is the tip of the iceberg, too. Once you have your Google Analytics data exported via API queries, you can start to combine that data with other sources, such as HubSpot, to create calculated metrics, such as Web Users to Leads.

How to use the Google Analytics Query Explorer for Universal Analytics

I am going to use the UA version of Query Explorer for this tutorial, but the skills you learn in the UA version will be transferable to the GA4 version, so I encourage you to follow along. Of course, make sure to open Query Explorer in another browser tab and explore.

Here's how to export Google Analytics data from Query Explorer:

1. Select a view
2. Select a date range
3. Select metrics
4. Select dimensions
5. Run query and export data

Select a view

To begin, you will need to select the account, property, and view from which you'd like to export data. For most Google Analytics users, a view is analogous to a website. You may have multiple “views” within a web property used for testing, experimentation, or with different filters applied. Make sure you're selecting the main view your company uses for its website metrics and reporting.

Step-by-step

1. Click the Account menu to bring up a list of accounts.
2. Select an Account
3. Click the Property menu to bring up a list of properties associated with your account
4. Select a Property
5. Click the View menu to bring up a list of views associated with the selected property
6. Select a View

Select a date range

Next, you will need to select a data range for your query. You have a lot of options for the time period or date values used to export your data. Check out Google's documentation to learn more. You can use 3 types of date values:

• Calendar date such as 2021-01-31
• Relative date such as today or yesterday
• NdaysAgo value such as 30daysAgo or 7daysAgo

Step-by-step

• In the start-date field, enter the value: 7daysAgo
• In the end-date field, enter the value: today

Select metrics

Every API query you craft with the Query Explorer needs to include at least one metric. This brings us to an important question:

What is a metric in Google Analytics?

Metrics are quantitative measurements that supply the statistics for user activity on your website, such as goals completed, number of users, or page sessions. When requested using an API query, metrics are presented as aggregate metrics. For example, if you made a request for users for the past 7 days, your result would look like this:

Step-by-Step

1. In the Metrics field, select one or more metrics
2. You will want to test your query to ensure the metrics you selected can be queried together
3. Click the Run Query button
4. If the query runs smoothly, move on to Step 4. If the query returns an error, swap out incompatible metrics

Select dimensions

Dimensions are optional when crafting an API query, but excluding them defeats the purpose of using the powerful Query Explorer tool. Common dimensions I use are Date, Channel Grouping, Source/Medium, Country, and Goal Completion Location.

What is a dimension in Google Analytics?

Dimensions are qualitative values that describe the data or metrics you're retrieving for your website, such as date, source/medium, or user types. For example, if you made a request for users for the past 7 days and applied the date dimensions your results would look like this:

Step-by-Step

1. In the Dimensions field, select one or more metrics
2. You will want to test your query to ensure the dimensions you selected can be queried together
3. Click the Run Query button
4. If the query runs smoothly, move on to Step 5. If the query returns an error, swap out incompatible dimensions

Export your Google Analytics data

You have two options for exporting your Google Analytics data from the Query Explorer. You can export it as a .tsv file and open it up in Excel. Or, you can use the API request to export the data.

The API request may be outside your comfort zone if you're not a developer, but it's actually quite usable. First, if you select the option to “include access token” you can copy the query to your clipboard and paste it into your browser to see the raw data.

The data exported from the API Request is in JSON format and not useful to most of us out of the box. However, many analytics platforms such as PowerMetrics can take the query and use the data exported to create metrics and reports. As your reporting becomes more mature, you will find this knowledge helpful to building sophisticated reports without needing a developer or an analyst.

Step-by-step

1. Double-check all fields to make sure your request is complete
2. Click the Run Query button
3. Export the query as a TSV or by copying the API Request to your clipboard

Using Filters and Segments in Query Explorer

Two additional features of Query Explorer that you will use quite a bit are Filters and Segments. They allow you to slice and dice the data to get very specific results. For example:

• Analyzing traffic from a single traffic source such as Twitter or Reddit
• Looking at hourly traffic patterns to determine best time to push website updates
• Analyze and bucket visitors based on average time on page and by number of visits

Using Filters in Query Explorer

Applying a Filter to a query is useful for refining the data you get back in your call. You can apply Filters to Metrics or Dimensions. It's important to note that there are 6 unique operators for Metrics and 6 unique operators for Dimensions. Check out the filter operators documentation for more information.

For example, if you're tracking Facebook Ads with Google Analytics, you may want to take a closer look at user behavior and how they interact with your website. Perhaps you'll want to create an automated metric. The filter you would use would look like this:

ga:sourceMedium==facebook / cpc

This only scratches the surface of what you can do with the Filters feature. For example, you could use it to analyze traffic that spends a certain amount of time on your site, or to only look at pages with a certain number of users.

Using Segments in Query Explorer

All the segments available in your Google Analytics view will also be accessible in the Query Explorer. This includes built-in segments and custom segments. You can even use dynamic segments to apply logic on the fly. Here's the documentation for your reference.

Google Analytics Query Explorer and marketing APIs

The Google Analytics Query Explorer is a powerful tool for exporting data for marketing reports. As far as marketing APIs are concerned, the Google Analytics API is made immeasurably easier to work with because of this tool. It provides a clean interface for compiling a query, and gives you the ability to test queries in real-time.

The benefit of using this tool is that you can automate data exports from Google Analytics into 3rd party analytics tools. Hopefully you found this article a useful introduction to the Query Explorer tool.