Segments

A segment is a population: the set of values of one or more key dimensions whose members satisfy some filters, found through some measures’ fact tables. The planner builds it as a CTE grouped on the keys and joins it into the query. Use segments for “customers who bought books”, “products sold in store”, “the top 50 stores by revenue”, and for putting a cohort beside the baseline in one result.

SegmentSpec

"segments": [{
  "name": "Book Products",
  "mode": "include",
  "keys": ["product_name"],
  "measures": [],
  "filters": [{"field": "category", "predicate": "equals", "value": "books"}],
  "apply_to": ["ws_net_paid"]
}]

Each key and measure is a field reference (uid, name, synonym, @d/@m). An ambiguous key is retried as a dimension and an ambiguous measure as a measure. Unknown keys inside a segment are rejected as malformed spec: ....

Rules and messages

The first message explains the design: there are no stored segments. A spec carries the full definition every time.

Query-level segments

With apply_to empty the segment constrains every row of the query. Web revenue by product, for products in the books category:

{"spec": {
  "name": "Product Sales",
  "projections": [{"field": "product_name", "alias": "Product Name"}, {"field": "ws_net_paid", "alias": "Web Net Paid"}],
  "segments": [{"name": "Book Products", "mode": "include", "keys": ["product_name"],
                "filters": [{"field": "category", "predicate": "equals", "value": "books"}]}]
}}

WITH seg4a9429dd0d8da260126c0bdb19cc5c6a AS (
SELECT
	T0."i_product_name" AS "dimbe52306"
FROM
	item T0
WHERE
	LOWER(T0."i_category") = 'books'
GROUP BY
	T0."i_product_name"
)
SELECT
	T1."i_product_name" AS "Product Name",
	sum(T0."ws_net_paid") AS "Web Net Paid"
FROM
	web_sales T0
	JOIN item T1
		ON T0.ws_item_sk = T1.i_item_sk
	INNER JOIN seg4a9429dd0d8da260126c0bdb19cc5c6a AS Q2
		ON T1."i_product_name" = Q2.dimbe52306
GROUP BY
	T1."i_product_name"

The seg… CTE is the population: one row per key value that passes the filter. The query INNER JOINs it on the key. Here the segment only needs item, because the filter and the key both live there; a segment whose filter needs a fact table (or that lists measures) is built from that fact.

Measure-level segments

apply_to names the measure projections the segment constrains, by display alias first, then by measure uid or name. Other measures stay unconstrained, which puts the cohort beside the baseline in one result. Same query plus store revenue, with the books segment applied only to the web measure:

{"spec": {
  "name": "Product Sales",
  "projections": [
    {"field": "product_name", "alias": "Product Name"},
    {"field": "ws_net_paid", "alias": "Web Net Paid"},
    {"field": "net_paid", "alias": "Net Paid"}
  ],
  "segments": [{"name": "Book Products", "mode": "include", "keys": ["product_name"],
                "filters": [{"field": "category", "predicate": "equals", "value": "books"}],
                "apply_to": ["ws_net_paid"]}]
}}

WITH ag6610e03993b3da57101097f16f2df44d AS (
SELECT
	T1."i_product_name" AS "dimbe52306",
	sum(T0."ss_net_paid") AS "msr621f67c"
FROM
	store_sales T0
	JOIN item T1
		ON T0.ss_item_sk = T1.i_item_sk
GROUP BY
	T1."i_product_name"
), seg4a9429dd0d8da260126c0bdb19cc5c6a AS (
SELECT
	T0."i_product_name" AS "dimbe52306"
FROM
	item T0
WHERE
	LOWER(T0."i_category") = 'books'
GROUP BY
	T0."i_product_name"
), ag4e173d8139dc2fe7bf4cf4d6aced1d58 AS (
SELECT
	T1."i_product_name" AS "dimbe52306",
	sum(T0."ws_net_paid") AS "msr0c0d023"
FROM
	web_sales T0
	JOIN item T1
		ON T0.ws_item_sk = T1.i_item_sk
	INNER JOIN seg4a9429dd0d8da260126c0bdb19cc5c6a AS Q2
		ON T1."i_product_name" = Q2.dimbe52306
GROUP BY
	T1."i_product_name"
)
SELECT
	COALESCE(A1.dimbe52306, A0.dimbe52306) AS "Product Name",
	A0.msr0c0d023 AS "Web Net Paid",
	A1.msr621f67c AS "Net Paid"
FROM
	ag4e173d8139dc2fe7bf4cf4d6aced1d58 A0
	FULL OUTER JOIN ag6610e03993b3da57101097f16f2df44d A1
		ON A1.dimbe52306 = A0.dimbe52306

The segment join sits only inside the web sales aggregation CTE. The store sales CTE has no segment, so Net Paid covers every product, and the FULL OUTER JOIN keeps products that appear on either side. One request gives the cohort’s measure and the baseline measure side by side per product. To compare the same measure in and out of the cohort, project it twice with different aliases and point apply_to at one of them; apply_to matches the alias verbatim.

include and exclude

mode: "exclude" keeps the rows whose key is not in the population (an anti-join). Products never sold in the books category:

{"name": "Not Books", "mode": "exclude", "keys": ["product_name"],
 "filters": [{"field": "category", "predicate": "equals", "value": "books"}]}

Measures in a segment

Listing a measure under measures routes membership through that measure’s fact table, and a filter on a measure becomes a HAVING on the segment CTE. Products with more than 1000 in store revenue:

{"name": "Store Sellers", "keys": ["product_name"], "measures": ["net_paid"],
 "filters": [{"field": "net_paid", "predicate": "greater_than", "value": "1000"}]}

The CTE groups store_sales by product and applies HAVING sum(ss_net_paid) > 1000; the query then inner-joins the surviving products. A top_n filter inside a segment ranks the population the same way: the top 50 products by store revenue as a segment, then any measure over them.

This is also how to express membership through any of several facts. A flat filter list cannot say “bought in store or on the web” (the error for an or group inside a flat list says as much); list both measures instead:

{"name": "Any Channel", "keys": ["customer_id"], "measures": ["net_paid", "ws_net_paid"]}

Expanding segments

An expanding segment carries a dimension of the other members that share the key into the query, which turns a fact into a pair matrix: products bought by the same customer, items in the same order. expanding lists the carried dimensions; as (wire name) is the output column, defaulting to "<Field> (same <Key name>)".

{"spec": {
  "projections": [{"field": "product_name"}, {"field": "ws_net_paid"}],
  "segments": [{"name": "Customer Products", "keys": ["customer_id"],
                "expanding": [{"field": "product_name", "as": "Product Name (same Customer ID)"}]}]
}}

WITH seg87ae4658940c714d42c8e2714d387839 AS (
SELECT
	T1."c_customer_id" AS "dimf5dcb79",
	T2."i_product_name" AS "dimd3303bd"
FROM
	store_sales T0
	JOIN customer T1
		ON T0.ss_customer_sk = T1.c_customer_sk
	JOIN item T2
		ON T0.ss_item_sk = T2.i_item_sk
GROUP BY
	T1."c_customer_id",
	T2."i_product_name"
)
SELECT
	T1."i_product_name" AS "Product Name",
	sum(T0."ws_net_paid") AS "Web Net Paid",
	Q2.dimd3303bd AS "Product Name (same Customer ID)"
FROM
	web_sales T0
	JOIN item T1
		ON T0.ws_item_sk = T1.i_item_sk
	INNER JOIN customer T3
		ON T0.ws_bill_customer_sk = T3.c_customer_sk
	INNER JOIN seg87ae4658940c714d42c8e2714d387839 AS Q2
		ON T3."c_customer_id" = Q2.dimf5dcb79 AND
		T1."i_product_name" <> Q2.dimd3303bd
GROUP BY
	T1."i_product_name",
	Q2.dimd3303bd

The CTE is every (customer, product) pair. The query joins it on the customer and keeps the companion product as a column, so each row is “web revenue of product A, for customers who also bought product B”. The <> is the default pair_dedupe: "neq", which drops self-pairs.

mode and apply_to cannot be combined with expanding.

Next steps

• Filters for the predicates a segment filter can use
• Security context: policies apply inside segment CTEs too
• Example requests