Countly Drill is a powerful, advanced segmentation feature that allows you to deep dive into your incoming raw data, create queries, and filter it by user properties.

Feature Dependencies

As Drill pulls behavioral and performance data sent from your application based on other Countly features, you must first have the following enabled and set up:

• Events
• Views
• Feedback
• Crashes

Benefits of Drill

Drill must be at the core of any data-powered strategy, as it lets you build codeless queries to delve into all your data in a way that only granular access can. This way, you can answer any questions that you may have about how, when, and by who your applications get used, if your product or service is working and delivering as you had expected, and track what works and what doesn't.

When using Drill, you will be able to:

• Perform advanced segmentations on our data by applying AND and OR filters to our segmentation properties as well as user properties such as Device, App Version, Platform, Platform Version, Session Count, Session Duration, First Session, Last Session, Country, City, and Carrier, among others.
• View data on a line, pie, or bar chart, whichever makes more sense for the chosen query.
• Choose the time buckets for the query results displayed to hourly, daily, weekly, or monthly.

• Quickly set up queries that you can save and reproduce.

• Combine the results of those queries with other Countly features, like Push Notifications and User Profiles, and act upon data in no time.

Getting Started

First of all, make sure that Drill is enabled. To do so, in the Sidebar, go to Management > Feature Management, and enable the Drill toggle.

Drill offers granular access to data sent from your applications by the SDK. In Drill, you can build queries based on different data sets that are displayed in Countly with features that require individual enabling (see the Dependencies section above)

• Sessions: enabled by default.
• Events: requires enabling of the Events feature.
• View: requires enabling of the Views feature
• Feedback: requires enabling of at least one Feedback feature, i.e., either Surveys, Ratings, or NPS®.
• Crash: requires enabling of the Crashes/Errors feature.

Drill Overview

Query Types

When building queries, you can choose one of the following data types:  

Input Types

In Drill, when you are building a query and click on a drop-down menu as you segment your data, there are different input types based on what kind of data has been collected by the specified property (i.e., Event Properties, User Properties, Custom Properties, Campaign Properties):

• When the incoming data is a number, your input can be any arbitrary number.
• When the incoming data is a date, your input can be a time period in a calendar.
• When the incoming data is a string, your input can be selected from a dropdown with all the possible values provided up to a limit (as defined in Settings). By default, the limit is 100 entries. If this limit is reached, then depending on configuration, it either switches to plain arbitrary input string or to a searchable list. Note that if you only have a dropdown with selectable values (i.e. without any option to insert arbitrary values), it means that this property has not yet reached the limit of unique values.

Operators

The list of available operators gets updated based on the selected data type. Here is a complete list of offered operators:

• is, is not: Basic comparison operators for strings, numbers, and dates.
• greater than, at least, less than, at most: Operators that can be used with numerical values and dates.
• contains, doesn't contain: These operators are used to check whether a field contains a determined string value (e.g., <Name contains John>).
• is set: In some cases, a field might not exist at all for a portion of event records. This operator gives you a better level of control over those fields (e.g., Events where browser version is not provided: <Browser version is set False>).

Metric Breakdowns

The query type will affect how you can visualize query results. You can select between the following metrics according to each type insofar there is data available:

Using Drill

Upon accessing Drill, you will be greeted by this View.

In it, you will find:

1. Query Name: Set by default to the time in which this new query is being done. It can be edited when you save it (9).
2. Data type selector: Choose the data type that will be used for the query. You can choose between Sessions (set by default), Events, View, Feedback, and Crash.
3. Segmentation builder: Select a filter that will segment your data based on User Properties (e.g., First Seen, City, Device, Platform, or Name), Custom Properties (e.g., Facebook Login, Twitter name, Has Apple Watch OS, etc.), or Campaign Properties (e.g., Campaign Name), as well as any other custom properties you might have set. Segmentation is selected by default, but you may use the toggle button to disable it. 
4. Breakdown selector: Depending on the selected data type, different filter categories are listed as well: event properties, crash segments, view segments, feedback segments, etc. Breakdown by is selected by default, but you may use the toggle button to disable it. 
5. Date range selector
6. User Profiles shortcut: Redirects you to the User Profiles feature, automatically listing the users segmented as a result of the query. When used prior to running the query, the User Profiles list will display the entire, unsegmented user list (i.e., you will see all your users). When used after the query is run, the User Profiles list will display only the user profiles that match the query.
7. Run query button: Executes the query built. Once a query is run, every time a change in the query builder - e.g., editing segmentation filters, changing the date range, etc. - you will get an alert message noting that the query needs to be re-run.
8. Query results: Displays the query built upon being executed. You can select the metric by which the results get based on. You can also define the time granularity by time buckets and select how data gets visualized in the graph. In addition, you get a results table that lets you navigate or download tabular data.
9. Save query: When working on a new query, it allows you to save it by adding a name and selecting its visibility for other users in your organization. When working on a query that was previously saved, it allows you to edit it by saving the changes, or to use the changes to create a new query.
10. Action buttons:
    • Last queries: Opens the report manager to see queries that take too long (more than 30 seconds by default but it can be edited in Management). The button includes a counter so you can easily see if there are any queued queries without opening the report.
    • New Query: Cleans the query builder so you can start a new one.
    • Open Query: Browse, open, or delete any saved queries.
11. Send push notification: Opens a drawer to send a One-time Push Notification to all users segmented by the query. The button is only available in applications for mobile devices - i.e., if your application is not mobile, the button will not be clickable.

Creating a Query

When you create a query, the possible segmentations for the incoming data will depend on the properties being sent along with the event or session. Depending on the breakdown applied, you will also have different graphic outputs, such as time series, pie charts, bar charts, or stacked bar charts.

As you construct a query by adding more properties using the corresponding operators and filtering them with AND or OR filters, you can drill down into your data deeper. Sometimes, simple queries are enough to answer particular product questions. But if you need to add more layers of complexity or get a clear understanding of how your application gets used under particular scenarios or identify highly-segmented user groups, you can keep adding properties.

A Simple Query

This is a great way to see a high-level overview of how things are going and performing single-level segmentations on our data. 

Say that in your gaming app you would like to identify users that are still using older versions of your application.

Basing your query on Sessions, simply set the segmentation to filter out your latest version.

The resulting output will include the basic metrics for the total occurrences, graphed in a time series, as well as providing the tabular results, as shown below.

Now, if you want to break down these total numbers by a particular property, such as the country where these older versions of your app are used, just add the Breakdown by Country and re-execute the query, as shown below.

Note that, due to the multiplicity of occurrences in these chosen segmentations, there are different visualization options.

Adding Multiple Segmentations

Now, let's go a bit deeper by extending our query and adding conditions to understand more about these users accessing the older versions.

For instance, if you want to determine how valuable these users are, you can factor into the query if the users are highly experienced in the game or if retained within the last 6 months, and that they have had at least 3 sessions in your chosen time range.

And, then select to visualize the results as a stacked bar chart.

Adding Multiple Breakdowns

Segmenting by multiple properties? Is it even possible? The answer is a big yes!

Using multiple breakdowns for segmenting allows you to generate a Cartesian projection for the values of particularly defined properties. This is typically helpful when you need to analyze the correlation between the values of certain properties. For instance, you can easily detect frequently co-occurring values (e.g., event count, user count) of two properties (e.g., Device, App version).

Let's say we want to know the number of session occurrences grouped by Device and App Version pairs:

According to the table, our event has occurred significantly more for (Device A, Version 3.2) pair. We can thus infer that there is something special about the pair, which is possibly an important insight for our app. 

When you run multiple segmentations, Drill allows you to select up to 3 properties to be used as breakdowns for your queries. The visualization of this multiple-segmented data is not much different from individual segmentation as you can still use 3 basic chart types (line, pie, and bar chart) to graph the query results. There is only a nuance for segment names: 

• 1 Breakdown (Platform): Android, iOS, Mac, Windows
• 2 Breakdowns (Platform, App Version): (Android | 2.3), (Android | 2.0), (iOS | 2.3), (iOS | 2.0), etc.
• 3 Breakdowns (Platform, App Version, Platform Version): (Android | 2.3 | 1.0), (Android | 2.0 | 1.0), (Android | 2.3 | 2.0), (Android | 2.0 | 2.0), etc.

So, let's see this in action. Show Sessions which App Version is not the latest, broken down by App Version and Platform.

In the results, each bar represents a pair like (2.5 | Windows), (1.4 | Windows), etc.

It is important to note that the number of segment pairs (or triples) can be large, especially when there are many different values for each field. In those cases, the results table could be a faster way of seeing all data.