The Customer Segmentation Analytics table measures segment performance around a pinned date, including revenue, purchase frequency, and list dynamics. One row per customer per segment instance. Learn more about customer segmentation.

📋
Table Guide
What You Can Answer With This Table

How did a segment perform before vs after a specific date? — use pinned_event_date with revenue and AOV columns

What products and channels drive a segment's purchases? — use product/campaign/ad breakdown columns

How is segment membership changing? — use 30-day list dynamics (growth, attrition, persistence, loyalty)

Before You Query

Required parameters: pinned_event_date, instance_event_date_start, instance_event_date_end, secondary_event_date_start, secondary_event_date_end, segment_id

Pinned event date is the anchor for comparing "before" vs "after" performance.

Instance event dates filter which segment snapshots to include. Secondary event dates filter the joined activity (Orders, Pixel Orders).

Columns labeled 7_day_ or 30_day_ use default windows relative to pinned_event_date. To change the window, update the formula interval and rename the alias. Click a column ID in the SQL Builder to view default queries.

Query one derived metric per request. Combining many derived metrics in a single query can lead to slower performance.

Use smaller date ranges to avoid heavy queries.

Key Relationships
Table Join Key What the Join Enables
Customer Segmentation segment_id + instance_event_date Link analytics with the underlying segment membership snapshot
Customers customer_identity to customer_id or email_address Customer profile data (name, email, tags, consent)
Orders customer_identity to customer_id or email_address Transaction-level details not in pre-aggregated metrics (product IDs, order tags)
Pixel Orders customer_identity to customer_id or email_address Attribution-level detail (campaign IDs, channels)

When to Use a Different Table

Need the segment membership list (which customers, not performance) → Customer Segmentation table.

Need customer profiles without segment context → Customers table.

Need order-level detail not available in pre-aggregated metrics → join to Orders or Pixel Orders via customer_identity.

View the full Triple Whale Data Ontology →

Table	Join Key	What the Join Enables
Customer Segmentation	`segment_id` + `instance_event_date`	Link analytics with the underlying segment membership snapshot
Customers	`customer_identity` to `customer_id` or `email_address`	Customer profile data (name, email, tags, consent)
Orders	`customer_identity` to `customer_id` or `email_address`	Transaction-level details not in pre-aggregated metrics (product IDs, order tags)
Pixel Orders	`customer_identity` to `customer_id` or `email_address`	Attribution-level detail (campaign IDs, channels)

Parameters

Parameters can be used to customize and filter the data in the table, and are applied before aggregation

Title	ID	Type	Description
Instance Event Date End	`instance_event_date_end`	parameter	End of the segment instance window. Filters which segment instances (runs/snapshots) to include when building the customer list. Use with `instance_event_date_start`. Example value: `2025-08-31`
Instance Event Date Start	`instance_event_date_start`	parameter	Start of the segment instance window. Filters which segment instances (runs/snapshots) to include when building the customer list. Use with `instance_event_date_end`. Example value: `2025-08-01`
Pinned Event Date	`pinned_event_date`	parameter	The anchor date used to compare before/after segment instance windows. Example value: `2025-08-25`
Secondary Event Date End	`secondary_event_date_end`	parameter	End of the joined activity window. Filters joined activity pulled from tables other than the Customer Segmentation table (e.g. Orders, Pixel Orders). Use with `secondary_event_date_start`. Example value: `2025-09-01`
Secondary Event Date Start	`secondary_event_date_start`	parameter	Start of the joined activity window. Filters joined activity pulled from tables other than the Customer Segmentation table (e.g. Orders, Pixel Orders). Use with `secondary_event_date_end`. Example value: `2025-08-01`
Segment ID	`segment_id`	parameter	The unique identifier for a segment. Example value: `64b8f144256b30501b2ea1e0`

Dimensions

Dimensions are immutable properties that can be used for grouping data

Title	ID	Type	Description
Customer Identity	`customer_identity`	string	The identity of the customer in the segment (can be `email` or `customer_id`). Example values: `67b076c419252e53fbc0841g`, `[email protected]`
Customer Identity Type	`customer_identity_type`	string	The type of identity the segment uses for this customer. Example values: `email`, `customer_id`
Instance Event Date	`instance_event_date`	date	The event date used for data from tables other than the Customer Segmentation table (e.g., Orders table, Pixel Orders table). Used in membership/instance joins. Formatted according to the ISO 8601 international standard. Example value: `2025-01-19 22:21:04`
Segment Name	`segment_name`	string	The human-readable name of the segment. Example values: `Fourth of July Sale`, `At least 3 orders`, `Jan Non Recent Active`
Shop ID	`shop_id`	string	The unique ID of the shop (often corresponds to the shop domain). Can be used to group or filter data by shop in multi-store reports. Example values: `example-US.myshopify.com`, `example-EU.myshopify.com`
Shop Name	`shop_name`	string	The name of the shop. Can be used to group or filter data by shop in multi-store reports. Example values: `example-US`, `example-EU`

Derived

Derived fields are metrics that are pre-calculated using multiple measures or advanced formulas

Title	ID	Type	Description
30-Day List Attrition	`30_day_list_attrition`	formula	Number of customers who were in the segment 30 days before the `pinned_event_date`, and left the segment on the `pinned_event_date`.
30-Day List Growth	`30_day_list_growth`	formula	Number of customers who were not in the segment 30 days before the `pinned_event_date`, and joined the segment on the `pinned_event_date`.
30-Day List Persistence	`30_day_list_persistence`	formula	Count of customers who remained in the segment for at least 30 days (were active both 30 days before the `pinned_event_date` and on the `pinned_event_date`).
30-Day Loyal Customers	`30_day_loyal_customers`	formula	Number of highly engaged customers who were active on both the start and end dates of a 31-day period and maintained 95% activity frequency (active on at least 29 out of 31 days)
30-Day Recent Joiners	`30_day_recent_joiners`	formula	Number of customers who were not in the segment 30 days before the `pinned_event_date` but joined the segment within the last 29 days leading up to the `pinned_event_date`.
Ad IDs (Last 7 Days)	`ad_ids_last_7_days`	formula	Ad IDs for customers from the selected segment instances in the 7 days prior to the `pinned_event_date`.
Ad IDs (Next 7 Days)	`ad_ids_next_7_days`	formula	Ad IDs for customers from the selected segment instances in the 7 days after the `pinned_event_date`.
AOV (Last 7 Days)	`aov_last_7_days`	formula	Average order value for customers from the selected segment instances in the 7 days prior to the `pinned_event_date`.
AOV (Next 7 Days)	`aov_next_7_days`	formula	Average order value for customers from the selected segment instances in the 7 days after the `pinned_event_date`.
Campaign IDs (Last 7 Days)	`campaign_ids_last_7_days`	formula	Campaign IDs for customers from the selected segment instances in the 7 days prior to the `pinned_event_date`.
Campaign IDs (Next 7 Days)	`campaign_ids_next_7_days`	formula	Campaign IDs for customers from the selected segment instances in the 7 days after the `pinned_event_date`.
Channels (Last 7 Days)	`channels_last_7_days`	formula	Pixel-attributed channels for customers from the selected segment instances in the 7 days prior to the `pinned_event_date`.
Channels (Next 7 Days)	`channels_next_7_days`	formula	Pixel-attributed channels for customers from the selected segment instances in the 7 days after the `pinned_event_date`.
Cities (Last 7 Days)	`cities_last_7_days`	formula	Shipping cities for customers from the selected segment instances in the 7 days prior to the `pinned_event_date`.
Cities (Next 7 Days)	`cities_next_7_days`	formula	Shipping cities for customers from the selected segment instances in the 7 days after the `pinned_event_date`.
Countries (Last 7 Days)	`countries_last_7_days`	formula	Shipping countries for customers from the selected segment instances in the 7 days prior to the `pinned_event_date`.
Countries (Next 7 Days)	`countries_next_7_days`	formula	Shipping countries for customers from the selected segment instances in the 7 days after the `pinned_event_date`.
Order Tags (Last 7 Days)	`order_tags_last_7_days`	formula	Order tags for customers from the selected segment instances in the 7 days prior to the `pinned_event_date`.
Order Tags (Next 7 Days)	`order_tags_next_7_days`	formula	Order tags for customers from the selected segment instances in the 7 days after the `pinned_event_date`.
Product IDs (Last 7 Days)	`product_ids_last_7_days`	formula	Product IDs purchased by customers from the selected segment instances in the 7 days prior to the `pinned_event_date`.
Product IDs (Next 7 Days)	`product_ids_next_7_days`	formula	Product IDs purchased by customers from the selected segment instances in the 7 days after the `pinned_event_date`.
Purchase Frequency (Last 7 Days)	`purchase_frequency_last_7_days`	formula	Average number of orders per customer from the selected segment instances in the 7 days prior to the `pinned_event_date`.
Purchase Frequency (Next 7 Days)	`purchase_frequency_next_7_days`	formula	Average number of orders per customer from the selected segment instances in the 7 days after the `pinned_event_date`.
Revenue (Last 7 Days)	`revenue_last_7_days`	formula	Total order revenue generated by customers from the selected segment instances in the 7 days prior to the `pinned_event_date`.
Revenue (Next 7 Days)	`revenue_next_7_days`	formula	Total order revenue generated by customers from the selected segment instances in the 7 days after the `pinned_event_date`.