Skip to main content

Creating Queries

Introduction

Query allows you to build queries, answer your questions, and create visualizations around your metrics and dimensions. The page has an easy-to-use menu for each selection. The following document highlights all the options available to create your queries.

Selecting Metrics

You can start by selecting one or multiple metrics from the menu on the landing page. You can also select any associated filters you want to apply to that metric. Once selected, you will be navigated to the main page with includes a visualization component and an editable menu on the right-hand side with all your selections.

You can also access Query directly from a metric page - underneath the metric chart, you will see an option that says "Create Query", which will take you to the Query page for that metric.

Selecting Dimensions

Selecting dimensions from the dimension menu dictates what we group your data by.

Metric Time

By default, we will plot metrics over the main time dimension for your metric, which is called Metric Time.

You can always remove this selection and query your metric without time, which will simply aggregate your data by the measure's aggregation type over the selected time range. You can also look at metrics against only categorical dimensions. Note that certain metrics that are cumulative may require time to be viewed. You will not be able to remove metric time in this case, and we will indicate when that is the case on the time menu. If you don't have metric time selected, you won't be able to change granularity or make trim selections.

The Time Granularity part of this menu allows you to control the aggregation of time with your metric. We support daily, monthly, weekly, quarterly, and yearly granularities.

The default granularity that's applied to the metric is dependent on the primary time dimension of the data source that the metric is built upon, and it can be set in metric metadata.

The granularity menu will hide options depending on the default granularity. For example, if the metric comes from a monthly data source, we won't allow you to select weekly or daily granularity. Similarly, we won't allow the user to select various presets that are less than the metric's default granularity.

Trim and Untrim

Granularity by default trims incomplete time periods so there are no false trends displayed. As an example, if you select weekly granularity and the latest day of data occurs in the middle of the week, we will trim the output to return only the latest complete week. If you would prefer the whole window of data, there is a toggle in the Metric Time menu that allows you to choose.

Other Dimensions

You can select other categorical or non-primary time dimensions to group your data by using this menu.

If your dimension values have high cardinality and we are able to plot your selection, you can optionally change the number of groupings displayed in the chart using the Max Lines selection.

When grouping your metric by dimension values, the tooltip (which you can see by hovering over the chart pints) will display a total for each day at the top. The total represents the sum of all the groupings and will be present for metrics that are based on additive measures.

Filters

Date Range

You can filter the selected date range using the filter menu. The date range selection has presets as well as custom calendar selection.

Dimension Filters

To filter by dimensions, first, you'll select a dimension to filter on (e.g., country) and then we will present you with all the values you can select (e.g., Japan) to filter down by. The menu to select dimensions is searchable and is split up by an identifier of the data source it comes from.

Once you find the right identifier, you can see a list of dimensions associated with it, and once you select a dimension, you can see all the values associated with the dimension in the filter value menu, with checkboxes next to each. Select the dimensions that apply in your filtering, and you'll notice the graph view will change and prompt you to update results.

Time over Time Comparison

Time over Time comparison allows you to compare various time ranges and view a relative growth rate. We support Day over Day, Week over Week, Month Over Month, Quarter over Quarter, and Year over Year.

The time over time comparison functionality is a property that can be defined per metric. When querying two metrics, they must share the same Time over Time selection. We will also hide time comparison options that don't make sense for a given granularity, and vice versa. This means that the time comparison period must be greater than equal to the given granularity. For example, we won't show a week-over-week time comparison option if you've selected monthly granularity.

Other Details

Granularity Standards

  • For weekly granularity, we use the ISO-standard default of Monday.
  • For quarterly granularity, we will use the calendar quarter (ex: Q1 is January 1st to March 31st)
  • For monthly, granularity, we use the calendar month (ex: January 1st to January 31st)
  • For yearly granularity, we use the calendar year (ex: January 1, 2020, to December 31, 2020)

Filling Zeros

If there are any time periods without data when granularity is applied, we fill in a value of 0 for metrics that are based on measures that aggregate with a count or sum. For all other metric types we show a null value.

Calculating Time Over Time

We use the pandas pct_change method to calculate time comparison, and then multiply the results by 100. For most values, the pct_change method will calculate via this formula:

(Values in Period 2 - Values in Period 1) / Values in Period 1

But if there is no data in Period 1, pct_change will resolve to infinity. In this case, we will show an infinity symbol on the chart. For more information, check out the pandas docs for the pct_change method.