Segmentation can be used in CQL for breakdown or filtering. Filtering supports using segments (to filter by users), or any other table in your project (Aggregation Tables, Integration tables, etc.).
Read more: Creating and managing segments, Using segments in reports and dashboards

 

Breakdown by Segments

To display users from specific segments: add the following expression to the select clause. In the brackets, include the names of all the segments you want to include in the query. Add the term “other” to also include users not included in these segments. The query output will include the segment name as a data column.

Example:

 

Filter by Segments

To filter any CQL query by users in a segment: add the following expression to the where clause. Use the segment name as saved.

To filter a query by data NOT in the specific segment: precede the segment statement by “NOT”.

Filter by multiple segments: it is possible to slice a query using multiple segments connected by an “AND” logical operator. “NOT segment” can also be used, and can be combined with regular segment statements. Only results that belong to the intersection of all the segments will be shown.
For example, the following would be a valid query:

Notes:

  • “OR” cannot be used on segments: To use an “OR” condition in conjunction with segments (on condition other than the segment statement), enclose it in brackets, with “And” operator. For example:
  • Cohort and Funnel can only be segmented by “user_id”: it is only possible to segment Cohort and Funnel queries by the “user_id” property (no need to declare it specifically).
  • Cannot segment by property “event_name”: the property “event_name” cannot be used when filtering by segment.

Example:

We’ll use a segment “new_users” we created to see the activity of these users over time.

 

Filter by table

Filter by segment can also be used with external tables (other than segments).
To filter using a table: specify the table name instead of the segment name, and append “.<propery_name>” to specify the segmentation property  (default is “user_id” and doesn’t have to be stated).

To filter a query by data NOT in the specific table: precede the segment statement by “NOT”.

Example:

We’ll use an emails list we built to easily create an email campaign (using Query API):

Print Friendly, PDF & Email