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 rangeCustom- 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 UnittoAll Timeor 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