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
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.
select segment_group(paying_users, non_paying_users, other), count (*) as users from cooladata where date_range(context) and filters(context) group by 1 order by 2
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”.
where|and NOT segment(<segmentName>)
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:
SELECT user_id FROM cooladata WHERE date_range(last 7 days) AND NOT segment(segment_new_users) AND segment(segment_news.customer_type) AND (event_name="Login" OR user_type="new")
- “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:
SELECT user_id FROM cooladata WHERE date_range(last 7 days) AND NOT segment(segment_new_users) AND (is_new=1 OR user_type="new")
- 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.
We’ll use a segment “new_users” we created to see the activity of these users over time.
SELECT date(event_time_ts) AS day, count(distinct user_id) AS users FROM cooladata WHERE date_range(context) AND slicers(context) AND segment(new_users) GROUP BY day ORDER BY day
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”.
where|and NOT segment(<tableName>[.<propertyName>])
We’ll use an emails list we built to easily create an email campaign (using Query API):
SELECT email FROM cooladata WHERE date_range(last 7 days) AND segment(churned_users.email) GROUP BY email