Documentation > API

Understanding CleverTap Query Language

The CleverTap Query Language (CQL) can be used to analyze all your event data, identify groups of users, and also target them using our APIs. It can be used with:

  • The profile/event export APIs to obtain raw data
  • The counts API to obtain dashboard counts for events, profiles, properties and trends
  • The campaign creation API to target these users through push notifications (as part of the “where” clause)

Understanding query language

CQL is expressed as a JSON formatted payload in the API request body.

In general, the base query should have an event_name, and from & to dates formatted as integers in YYYYMMDD format.

Base query

{
    "event_name": "App Launched",
    "from": 20150810,
    "to": 20151025
}
copy Copied

Additional filters are possible on top of the base query, such as:

  • event property filters
  • session property filters
  • common profile property filters
  • advanced event filters (did-none, did-any, did-all…)

Event and session property based filters are applied directly to the base query.

When specifying common profile property or advanced event based filters, the base query can be omitted for target creation & people export APIs, and defaults to all events over the past 364 days.

Base event query is mandatory for the event export API in all cases.

Understanding query operators & values

CQL supports (key, operator, value) combinations to filter results.

  • All value fields must be primitives (integer, double, or string), or array(s) of primitives
  • Boolean fields must be passed as string, with value “True” or “False”.
  • When the value is a String, the operator can be either `equals`, `not_equals` or `contains`.
  • In case of numeric values, the operator can be one of `equals`, `greater_than`, `greater_than_equals`, `less_than` or `less_than_equals`
  • For lists, the operator is applied as an `OR` on all values.

Event property filters

Event property filters enable developers to query for User Profiles or Events based on custom event properties.

Example

{
    "event_name": "choseNewFavoriteFood",
    "event_properties": [
        {
            "name": "value",
            "operator": "contains",
            "value": "pizza"
        }
    ],
    "from": 20150810,
    "to": 20151025
}
copy Copied

Session property filters

Session property filters enable developers to query for User Profiles or User Events that exhibit certain kind of session information. The following session property filters are currently supported:

All the parameters mentioned below are optional. CQL supports querying on any combinations of these parameters

  • utm_source – The utm_source of the session. You are required the set the value to a known utm_source. Must be String
  • utm_medium – The utm_medium of the session. You are required to set the value to a known utm_medium. Must be String
  • utm_campaign – The utm_campaign of the session. You are required to set the value to a know utm_campaign. Must be String
  • session_referrer – The session_referrer of the session. You are required to set the value to a known session_referrer. Must be String
  • session_source – The session_source of the session. Possible values are Direct, Others, Search, Social, Email, UnAvailable, Batch. Must be String
  • first_time – filters whether the user performed the event for the first time. Accepted value is "True", or left blank. Must be String
  • time_of_day – Time of day of the event. Possible values must be an Array of Strings (size 2), containing the start and end time range in 24 hour “HH:MM” format
  • day_of_month – Day of month when the event was raised. Value field can be an integer between 1 and 31, or an array of possible integer values
  • day_of_week – Day of week when the event was raised. Value field can be a string denoting day of week (Sunday, Monday,… Saturday), or an array of possible string values

Session property filters are not supported with Notification Sent event.

Example

{
    "event_name": "choseNewFavoriteFood",
    "session_properties": [
        {
            "name": "first_time",
            "value": "True"
        },
        {
            "name": "time_of_day",
            "value": [
                "22:30",
                "22:35"
            ]
        }
    ],
    "from": 20150810,
    "to": 20151025
}
copy Copied

Common profile properties

Common property filters enable developers to query for people or events that map with certain profile specific information. The following common profile property filters are currently supported:

All the parameters mentioned below are optional. We support querying on any combinations of these parameters

app_fields

Supports filtering on OS Version, Version (refers to app version), SDK Version, Make and Model of device.

Example

{
    "common_profile_properties": {
        "app_fields": [
            {
                "name": "Make",
                "operator": "contains",
                "value": "Apple"
            }
        ]
    }
}
copy Copied

profile_fields

Supports querying custom profile fields.

Profile properties such as Name, Email, Identity, Phone etc are not supported. To download based on email/identity, refer here. To send push based on identity, refer here

Example

{
    "common_profile_properties": {
        "profile_fields": [
            {
                "name": "language",
                "operator": "equals",
                "value": "en"
            }
        ]
    }
}
copy Copied

demographics

Supports filtering on Gender, Age, Education, and Employed. Possible values are listed below

Gender – Possible values are Unknown, Male and Female
Age – Possible values are Unknown, Kid, Youth, Young_Adult, Adult and Senior where Kid refers to age < 18 years, Youth is 18-24, Young_Adult is 25-40, Adult is 41-60 and Senior is > 60 years
Education – Possible values are Unknown, School, College, and Graduate
Employed – Possible values are Unknown, Yes and No

Example

{
    "common_profile_properties": {
        "demographics": [
            {
                "name": "Education",
                "operator": "equals",
                "value": [
                    "College",
                    "Graduate"
                ]
            },
            {
                "name": "Gender",
                "value": "Female"
            }
        ]
    }
}
copy Copied

technographics

Supports filtering on Platform, Platform Type, Device, OS and Browser. Possible values are listed below

Platform – Possible values are WebApp, AndroidApp, iOSApp and WindowsApp
Platform Type – Possible values are WebApp, MobileApp
Device – Possible values are Desktop, Mobile, Tablet and TV
OS – Possible values are Others, Android, iOS, Windows, Mac, BlackBerry and Linux
Browser – Possible values are Others, Firefox, Chrome, Seamonkey, Safari, Opera, IE and UCBrowser

Example

{
    "common_profile_properties": {
        "technographics": [
            {
                "name": "Device",
                "operator": "equals",
                "value": [
                    "Mobile",
                    "Tablet"
                ]
            },
            {
                "name": "Platform",
                "value": "AndroidApp"
            }
        ]
    }
}
copy Copied

reachability

Support filtering on the following:
has_token (whether the user has a device token)
has_email (whether the user has an email address associated)
MSG-push (False indicates that the user has unsubscribed from push notifications)
MSG-email (False indicates that the user has unsubscribed from email notifications)

Example

{
    "common_profile_properties": {
        "reachability": [
            {
                "name": "has_token",
                "value": "True"
            }
        ]
    }
}
copy Copied

geo_fields

Supports filtering on geo location info. Must be formatted as one or more Array(s) of Strings, each of size 3, in [Country, Region, City] format

Example

{
    "common_profile_properties": {
        "geo_fields": [
            [
                "India",
                "Maharashtra",
                "Mumbai"
            ],
            [
                "United States",
                "New York",
                "New York City"
            ]
        ]
    }
}
copy Copied

geo_radius

Supports filtering on geo radius. Must be formatted as one or more Array(s) of numbers (Int/Double), each of size 3, in [Latitude, Longitude, Radius] format

Radius is expressed in kilometers

Example

{
    "common_profile_properties": {
        "geo_radius": [
            [
                35,
                42.5,
                5.2
            ],
            [
                77.6,
                76.2,
                11
            ]
        ]
    }
}
copy Copied

Advanced queries

Query with did none block

To segment users who performed NONE of the specified action(s) within the given range.

{
    "advanced_query": {
        "did_none": [
            {
                "event_name": "Charged",
                "from": 20151010,
                "to": 20151011,
                "event_properties": [
                    {
                        "name": "Amount",
                        "operator": "equals",
                        "value": 100
                    }
                ]
            },
            {
                "event_name": "App Launched",
                "from": 20151011,
                "to": 20151012
            }
        ]
    }
}
copy Copied

Query with did all block

To segment users who performed ALL of the specified action(s) within the given range.

{
    "advanced_query": {
        "did_all": [
            {
                "event_name": "Charged",
                "from": 20151010,
                "to": 20151011,
                "event_properties": [
                    {
                        "name": "Feedback",
                        "operator": "contains",
                        "value": "excellent"
                    }
                ]
            },
            {
                "event_name": "App Launched",
                "from": 20151011,
                "to": 20151012,
                "operator": "equals",
                "value": 10
            }
        ]
    }
}
copy Copied

Note: The operator and value are optional within each internal event block. Default values for the same are “greater_than_equals” and 1 respectively.

Query with did any block

To segment users who performed ANY of the specified action(s) within the given range.

{
    "advanced_query": {
        "did_any": {
            "any_events": [
                {
                    "event_name": "Charged",
                    "from": 20151010,
                    "to": 20151011,
                    "event_properties": [
                        {
                            "name": "Amount",
                            "operator": "equals",
                            "value": 100
                        }
                    ]
                },
                {
                    "event_name": "App Launched",
                    "from": 20151011,
                    "to": 20151012
                }
            ],
            "operator": "equals",
            "value": 10
        }
    }
}
copy Copied

Note: The operator and value are for the entire “any_events” block, and are resolved for the entire combination of events present within. They are optional, with default values being “greater_than_equals” and 1 respectively.

Sample queries

Special events

Notification Sent

Notification Sent is a special system event that can be used to obtain information about users receiving notifications through CleverTap. It supports filtering by the event properties Campaign ID (integer ID of the campaign shown on the dashboard) and Campaign Type.

Campaign Type can have the following possible values (passed as string or array of string) – "Mobile Push - Android", "Mobile Push - iOS", "Mobile Push - Windows", "Email", "Facebook Audiences", "SMS", "Web Push - Chrome".

Notification Sent event is available for filtering on the user profile download API and the push campaign API (as part of the where clause).

Filtering by session properties is not supported for the Notification Sent event as it is a system event.