Overview: Google Analytics Query Builder

In this article, we will explain elements and functionality of the  Query Builder for Google Analytics through an example. Our goal is to filter organic search values by country.

IN THIS ARTICLE

1. Basic Options

A
  Custom Query Name: Write a name for your custom query in this field. You will find this name/title as a metric option in the designer after saving the query.
We chose "New users / organic by country" for our query name.
B
  Metric (required) : Select a metric from your connected google analytics data source (the aggregated statistics for user activity to your site), such as users, new users, clicks or pageviews. A metric can be used in combination with other dimensions or metrics, but only where valid combinations apply for that metric. 
C
  Dimensions (optional) The dimensions parameter breaks down metrics by common criteria, such as by  browser or  country (like in our example). When dimensions are requested, values are segmented by dimension value. If a query has no  dimensions parameter, the returned metrics will provide aggregate values for the requested date range, such as overall pageviews or total bounces.
D
  Date range (required) To display your data the way you like, you will need to choose a date range. Just click on the "Date range" field. Selected intervals will be enabled for a new metric.
E
  Compare With (optional) : Select a date range to compare your previously selected interval to. This date range will be used for "previous line" on line and bar charts, and for calculating change %. Options are: None, Previous interval and Same interval last year.

2. Advanced Options

A
  Segment (optional) A segment is a subset of your Analytics data. For example, out of your entire set of users, one segment might be users from a particular country or city. Another segment might be users who purchase a particular line of products or who visit your page and came from organic search sources, as in our example.
B
  Metric Sort Order: Sort results by Ascending or Descending. Metrics, where lower values are better (e.g. Bounce Rate) the sort order needs to be set to Ascending.
There's a limit of how many rows (dimensions) we can pull from Google Analytics, per request (currently 2000). By default the sorting is set to "Descending", which means that if you have more than 2000 rows you won't be able to see the ones above 2000. For Metrics like Bounce Rate the lowest values are the best performing ones, but wouldn't be pulled because of the limitation. For Metrics where the lowest values are best performing it's necessary to change the sorting to "Ascending".
C
  Filters (optional) : You can filter results by dimension or by metric. Filters can be combined using  OR and  AND boolean logic. Filtered queries restrict the rows that do (or do not) get included in the result. Each row in the result is tested against the filter; if the filter matches, the row is retained, and if it doesn't match, the row is dropped.

Filter Operators

Dimension Filters:

  • Exact match: Aggregate metrics where the country is United States
  • Does not match: Aggregate metrics where the country is not United States
  • Contains substring: Aggregate metrics where the country contains United States
  • Does not contain substring: Aggregate metrics where the country does not contain United States
  • Contains a match for the regular expression: Aggregate metrics where the country starts with United States
  • Does not match regular expression: Aggregate metrics where the country not start with United States

Metric Filters:

  • Equals: Return results for users where number of sessions is exactly 5
  • Does not equal: Return results for users where number of sessions is not 5
  • Greater than: Return results for users where number of sessions is strictly greater than 5
  • Less than: Return results for users where number of sessions is strictly less than 5
  • Greater than or equal to: Return results for users where number of sessions is 5 or more
  • Less than or equal to: Return results for users where number of sessions is 5 or less
In our example we set filters to get data only for three selected countries: United States, Australia and Germany
When everything mentioned above is set, click "Run query" to show  Data preview.

3. Data preview

In Data Preview you will see all values for selected metrics and date ranges. At the bottom you can check total values. Just click on tabs for switching date range.

If you want to sort values by date/time just click on the arrow next to "Date"

When your query is ready, click "Save query." Now a new metric has been created, with the metric name the same as the custom query name.

By default, a metric will be added in the datasource where data is pulled. It is possible to change the target datasource by clicking on "Change target data source" on the top right.

Transformations

Rename Dimension:

It is possible to rename dimensions (best practice is to rename them before saving the query) by clicking  "..." next to "Dimension" and then selecting "Rename dimension".

Skip Dimension:

This function is useful when you want to exclude one or more dimensions. (For example if you want to show data for all Countries except one). Click on  "..." next to "Dimension" and select "Skip dimension".

Round values:

Click on  "..." next to "Value" and select "Round values." Enter the number of decimal places to be rounded to into field in popup, and values will be rounded accordingly.

Still need help? Contact Us Contact Us