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

Drill Basics

First of all, you must enable Drill in the Countly Dashboard by going to Management > Plugins in the top-right corner. You will then found Drill in the Explore section of your Dashboard.

Now,  let's understand how Drill work by going through its screen:

1. Drill Type: Here you select the data type you are going to segment. You can choose between session, event, views, crashes, or feedback types. Your selection of different data types will affect the overview graph below.

2. Graph view: You can define time granularity by month, week, day, or hour, and select a specific time frame. You can also select the data values shown, such as users, times or times/users.

3. Query Builder: Here’s the part where you can select a filter 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). Depending on the selected data type, different filter categories are listed as well: event properties, crash segments, view segments, feedback segments, etc.

4. Data Table view: Shows the query results in a table format (which corresponds to the graph in the Graph view) and allows you to download the data.

5. Bookmarks tab: You can reach your previously bookmarked queries (see Bookmarks).

6. Actions: You can view available actions applicable to the query being built (see Other actions).

7. Execute query: You can execute a query once you finished building it.

Now, depending on the Drill Type you select (Sessions, Events, Views, Crashes, or Feedback), the available filters in the Query Builder will change. All the options possible are:

Creating a Query

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 the configurations). 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>).

Drill Use Case

Before we start getting more into how to drill down data, we need to make sure you understand Countly Events since Countly Drill is all about them.

With Events, Countly lets you keep track of your own metrics in a pretty simple way by sending event data like this:

{
	"key": "Ride",
	"count": 1,
	"sum": 15.99,
        "duration": 500,
	"segmentation": {
		"Car Type": "Luxury",
		"Time of Day": "15:00",
		"Coupon Used": "CLYROCKS"
	}
}

Here, we are tracking the Ride event for our hypothetical app that has the segmentation properties called Car Type, Time of Day, and Coupon Used, all of which let us keep track various occurrences of the same event. We use the sum property to store the amount paid for the ride.

We normally get a report that looks like the one below for our Ride event. This is a great way to see a high-level overview of how things are going and performing single-level segmentations on our data.

Using Countly Drill we will be able to:

• Perform advanced segmentations on our data by applying AND, OR, and BY 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
• View data on a line, pie, or bar chart, whichever makes more sense for the current data.
• Change the time buckets displayed on the chart and table to hourly, daily, weekly, or monthly.
• View how many times the event occurred as well as how many users performed this event and an average (labeled as times/users)

Running a Simple Query

Let's go ahead and see some of these highlights using real reports for our custom event Ride.

To view Ride events with "Car Type": "Luxury", we simply build the query below and click Execute query. Once the execution is done, the graph and data table will be updated accordingly. Please note that changing time periods also triggers the execution of a query.

Segmenting by a Field

Segmenting by Multiple Fields

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

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

Let's say we want to know the number of event 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, the Drill dashboard allows you to select up to 3 fields to be used with BY 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-BY (Platform): Android, iOS, Mac, Windows
• 2-BY (Platform, App Version): (Android | 2.3), (Android | 2.0), (iOS | 2.3), (iOS | 2.0), etc.
• 3-BY (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 Ride events segmented by "Platform" and "App Version". 

Each bar represents a pair like (2.0 | iOS), (2.8 | Android), etc.

For 2-way segmented queries like the example query above, you can also use a punchcard custom visualization method:

In this chart type, circle sizes are arranged proportionally to other items in the table. That is, circle sizes represent the relative difference between pairs.

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 Data Table view could be a faster way of seeing all data:

Segmenting by Country

Country segmentation is handled slightly differently from other types. Since country is a special field, segmented values are displayed on a world map. For multiple segmented queries, however, visualization options fall back to the standard options covered above and country names are shown just like any other field. 

Continuing with our use case example, we query Ride events with "Car Type": "Luxury" or "Car Type": "Basic" that have a "Coupon Used": "CLYROCKS", and select the report to be segmented by Country:

Bookmarks

Other Actions

When creating a query, Drill offers you a set of useful actions in addition to bookmarks.

Save to Report Manager: You can create drill reports to view a particular drill query later. These reports can be accessed from the Utilities > Report Manager. You can also attach your reports as widgets to your custom dashboards.  

Send a Message: You can send push notifications to users matching your query.

View User Profiles: You can view matching users in User Profiles.

Session Drill

Session Drill is an extension to Countly Drill which provides the same drilling functionalities, but applied to your session data. This feature becomes available automatically upon enabling the Drill plugin.

Every view under the Analytics section of the Countly Dashboard menu now has a Drill button as shown below which takes you to the Drill view and automatically applies the related filter for you.

For this particular case when you click the Drill button under Devices view, you will automatically be navigated to Drill with a BY filter on Device. You can then continue to drill down this data by applying more filters.

Currently, you can apply any combination of AND, OR, or BY filters to the following user properties:

• App Version
• Carrier
• Country
• City
• First Session
• Last Payment (available only if Revenue Analytics is active)
• Last Session
• Platform
• Platform Version
• Resolution
• Session Count
• Total Session Duration

We could have added thousand more items to this list but we believe you are ready to see it for yourself at this point. Session Drill is not just a powerful stand-alone tool, but it binds many other powerful analytics tools into one, and you only need to try it out one time to discover its truly unlimited potential. Sign up for a free Countly Enterprise Edition account and start your discovery, today!