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.

select segment_group(<segment_names>,[other])

Example:

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

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.

where|and segment(<segmentName>)

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")

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:

  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.

Example:

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

where|and segment(<tableName>[.<propertyName>])

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

where|and NOT segment(<tableName>[.<propertyName>])

Example:

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