Skip to main content
All CollectionsBuilding ReportsMost Popular Reports
Query
Query

A deep dive article on the Query chart type

Updated over 3 months ago

Query is a very flexible chart that can help you answer questions related to "How Many" users are performing specific events within your product.

Query Use Cases

Our Query chart can be very useful in the following Use Cases, but certainly not an exhaustive list!

  • How many users performed an event on my product over the last 7 days?

  • What percentage of users who are paying performed our critical event last month?

  • What is our monthly active user counts for the past 6 months?

  • How many users do we have on average over the past 7 days performing any event?

Building Your Measures

A measure is comprised of 3 items, the function you want to count/compute, the event you want to analyze and any filters you'd like to apply. You can learn more about Measures in this article about components of a Chart.

Functions

  • Count Events: This function will count the total number of times the event you've selected has been performed. If a user does an event multiple times, each occurrence of that event will be counted.

  • Uniques: This function will count the unique identifiers of whichever you've selected who performed the event you select.

    • These can be customized to your data model, if you'd like to configure additional functions please reach out to us!

    • Examples include User ID, Anonymous Visitor ID, Session ID, Account ID

  • Math Functions: This includes Sum, Avg, Min, Max functions that can be applied to numeric event fields.

    • These are only available for field values that are numeric (i.e. 1,2,3)

Event

Select the event(s) you want to analyze inside of your chart by selecting them through the dropdown.

  • You can select multiple events to perform an OR logic, which means all members of the measure had to perform Event 1 or Event 2.

  • You can also select any Virtual Events you may have created. You will see those available at the top of your Event dropdown list.

Add Additional Events and Measures

When building a Query multiple events within a single measure could be considered part of your metric. In Kubit you are able to achieve this in two ways:

  • Add additional events within the same Event module to perform an OR statement. All filters applied will be included for each event

  • Add additional events with the "+ Add Measure".

  • Adding additional measures without using the Formula will result in just the two measures shown on the report visualization.

Filter

Once you've selected your event(s) you can filter them depending on the specific use case or analysis you're building.

  • Build your own filter through the available operators and utilizing the AND/OR logic if needed.

  • If you don't see a field available in the dropdown but may see it on other events that's alright. It means the field you're looking for may not be available within that event.

  • You can also select any Saved Filters you may have created. You will see those available at the top of your field dropdown list.

Compound Measures

A Compound Measure is a type of Measure that involve arithmetic between two or more simple Measures, explained above. An example of a common compound measure is something like 'average events per user'. When you need to calculate as a numerator divided by a denominator, that will be a Compound Measure.

To save a Measure you've created follow the instructions here.

Kubit supports Compound Measures using the Formula bar below the Measure builder.

Formula Operators

Supported operators will be +, -, /, *

  • + addition

    • A + B

  • - subtraction

    • A - B

  • / division

    • A / B

  • * multiplication

    • A * B

In any mathematical expression, it is essential to understand the order of operations to get the desired result. In Kubit, there are 4 operators available. Whenever a division operator is present, Kubit will first evaluate the numerator followed by the denominator and then perform the division operation.

1st

2nd

3rd

4th

5th

6th

Parenthesis

Exponents

Multiplication

Division

Addition

Subtraction

Here are a few examples to illustrate how Kubit calculates a Compound Measure:

  • (4 x 6) ÷ (2) = 12

  • (4 + 6) ÷ (2 + 3) = 2

  • (5) ÷ (4 - 1) = 1.6

  • 5 + 1 + 2 = 8

Supported Functions

  • min(A, B) - returns the min value from both measures

  • max(A, B) - returns the max value from both measures

  • ifnull(A, <default>) - returns A if not null, otherwise the default value

  • sum(A, B, C) - shortcut for sum of multiple measures

  • ceil(A, <scale>) - returns values from A rounded to the nearest equal or larger integer, or to the nearest equal or larger value with the specified number of places after the decimal point

  • round(A, <scale>) - Returns rounded values for A

  • floor(A, <scale>) - returns values from A rounded to the nearest equal or smaller integer, or to the nearest equal or smaller value with the specified number of places after the decimal point

  • pow(A, x) - returns measure value raised to the specified power (x)

  • sqrt(A) - square root of A

  • log(<base>, A) - returns the logarithm of a number given a base

    • The base should be positive number and not equal to 1

    • To solve for an error related to this be sure to set a Measure Filter where the value must be > 0

Example Formulas

  • min(A,B)/C - is the minimum value between measure A and B, divided by measure C

  • pow(A, 2) - A raised to the power of 2

  • ifnull(A, 1.5) - if A is NULL, then return 1.5

  • ceil, round, floor are utilities for formatting the result measure; round(A, 3) - round A rounds A to a 3rd number of decimal places

  • sum(A,B,C) is a shortcut for A+B+C

  • ceil(min(A,ifnull(B,3.1)),5) - functions can be nested; round the result to 5th number of decimal places, where the result is the min value between A and B (where if B is NULL, then it will be 3.1)

Once you've written your formula and click out of the formula bar we will check if the syntax is correct. If it's invalid you will see an error message helping you fix the formula.

Cohort Measure Filter

Often it's important to filter an event within a compound measure by members in a Cohort instead of just an event. To add a Cohort Filter to a Measure you will click the "Measure Options" dropdown and select "Add Cohort Filter".

Create a New One: You are able to build the Cohort within the Query builder by clicking "Create Custom"

Use an Existing Cohort: If you've already created a Cohort that you want to use you'll simply select it from the list of available Cohorts.

Compare Mode in Query

Want to compare how a metric has changed over time? You can leverage the Compare feature in Query to achieve that!

There are multiple options you can leverage from the Compare dropdown:

  • Previous Week/Month/Quarter/Year - shortcuts to select a previous period based on the current report's date range

  • Custom - which allows you to manipulate the end date for the previous period and the start date is adjusted based on the length of the current report's date range

When using Compare any metrics for the previous period will be displayed as a dotted line on the chart, while the metrics for the current period will be displayed as a regular line, as shown here:

But even better, when you switch the Query result from Line Chart to Numeric you will also see the % change and that will make your Dashboards even more awesome:

Breakdown

When you're analyzing data it's important to be able to slice it by various attributes of the User or Event. Breakdowns allow you to group the results by these fields and even Bin them into more meaningful groups in Query.

  • Breakdown by a Field: Select the field you want to breakdown by to see results grouped based on that fields values. These can be things like "User Type" or "Subscription Mode".

Additional Bin Options

  • Breakdown with Binning

    • When you'd prefer to group values inside of the breakdown into groups or "bins" we've made that available in the Breakdown feature.

    • Select the field you want to breakdown by and click the context menu to expose "Add Binning"

      • You can bin items based on their values, Value Binning, or ranges of integers, Range Binning. Based on the data type you've selected you may see only one or both.

    • Once you've created your bin you can save it for Reuse.

Overall in Query

Whenever a Breakdown is specified in Query you can also use to Overall toggle to display the cumulative count for all breakdown groups as a separate group.

When you turn on the Overall you also get access to additional chart types:

  • Stacked Column

  • Percentage Area

  • Pie Chart

To access the Pie Chart you need to:

  • either set Time Unit to All Time

  • or switch to Stacked Column or Percentage Area chart and right-click on a single day, then Pie Chart

Compare Multiple Measures on a Chart

You can compare up to 6 measures in a chart by de-selecting the Compound Measure and selecting the desired Measures via the colored legend `Measure Selectors` below the chart.

Rename/Format Measures on Chart

Once you've constructed your chart the naming in the Measures legend should be easy to understand. To rename them select the context menu and perform a number of formatting actions.

You're able to:

  • Rename the Measure

  • Format to a %, #, or $

  • Convert to a different metric if the value displayed isn't ideal

    • An example could be you've got a Measure with a Sum of Seconds Watched and you want to convert it to Hours

  • Create a Cohort of all users within the Measure result

➡️ Next Article: Learn About Funnels

Related Articles
Funnel
Advanced Query Features
Did this answer your question?