Cohorts and segments

A segment is a population: a set of key-dimension values picked out by filters, by measures, or both. The planner materializes it as a CTE and joins it into the query on the key. Applied to the whole query it behaves like a cohort filter. Applied to one measure through apply_to it lets a cohort’s number sit beside the unconstrained baseline in the same row. An expanding segment goes the other way and carries a dimension of the other members sharing the key into the query, which is how basket pairs are built. Reference: Segments.

The shorthand has no segment syntax; these requests are JSON only. The statements on this page were planned with a system-admin context that bypasses the branch’s policies; the SQL is the same on a branch without policies.

Query-level include

Web revenue per product, restricted to products that have at least one row 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 segment is its own CTE. seg4a94… selects the key (i_product_name) from the tables needed to evaluate the segment’s filters, grouped by the key so each member appears once.
  • The query is INNER JOINed to it on the key. apply_to is empty, so the join sits in the main query and every projected measure is constrained.
  • The segment’s filter does not leak into the query’s WHERE. The query asks nothing about category; membership is decided inside the CTE.

Measure-level, inside a blend

Same projections plus the store measure, and the segment now applies to ws_net_paid only. The row shows book-product web revenue beside all-product store revenue.

{"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 join moved inside one aggregation CTE. Only ag4e17… (web) joins seg4a94…. The store CTE ag6610… aggregates every product.
  • The blend is unchanged. The two aggregation CTEs are stitched with the same FULL OUTER JOIN and COALESCE as a plain cross-fact blend. Products without book rows still appear with Web Net Paid as NULL.
  • apply_to matches by alias first, then by measure uid. "apply_to": ["Web Net Paid"] would do the same. If two projections share a measure, give one an alias and name the alias.

Expanding segment: basket pairs

For each product, the other products the same customers bought in store, with web revenue of the first product. The key is customer_id; the segment carries product_name as a new column.

{"spec": {
  "projections": [{"field": "product_name", "alias": "Product Name"}, {"field": "ws_net_paid", "alias": "Web 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 holds (key, carried dimension) pairs. seg87ae… groups store_sales by customer and product.
  • The join matches on the key and excludes self-pairs. pair_dedupe defaults to neq, which is the <> in the join condition. lt keeps one canonical ordering of each pair; none keeps self-pairs.
  • The carried column is projected and grouped. Q2.dimd3303bd AS "Product Name (same Customer ID)" comes from the segment, not from the model, and joins the GROUP BY. Omit as and the column is named Product Name (same Customer Id) by default.
  • join picks inner or left. "join": "left" keeps products whose customers bought nothing else. mode and apply_to are not allowed on an expanding segment.

Variations

  • Exclude mode. "mode": "exclude" on the first request turns the population into an anti-join: the segment CTE is unchanged and the query keeps products that are not in it. No captured statement to quote here; the CTE body is identical to the include case.

  • Membership defined by a measure. Products that sold more than 1000 in store, applied to web revenue:

    "segments": [{"name": "Big in Store", "keys": ["product_name"], "measures": ["net_paid"],
              "filters": [{"field": "net_paid", "predicate": "greater_than", "value": "1000"}],
              "apply_to": ["ws_net_paid"]}]

    Listing net_paid under measures routes the segment through store_sales; the measure filter becomes a HAVING sum(ss_net_paid) > 1000 on the segment CTE, which groups by the key. Several measures from several facts (bought in store OR catalog) go in the same measures list; that is the supported way to say “any of these facts”, since or groups are not allowed inside a flat filter list.

  • Rank the population. A top_n filter inside a segment ranks the members: {"field": "product_name", "predicate": "top_n", "value": "20", "top_n_measure": "net_paid"} with "measures": ["net_paid"] keeps the twenty best-selling store products as the cohort.

Next steps