Customer Segmentation Analytics Table (BETA)

customer_segmentation_analytics_tvf

The Customer Segmentation Analytics table provides performance and behavior metrics for customers within selected segment instances around a chosen pinned event date. It includes time-bounded revenue and AOV, purchase frequency, product/campaign/ad breakdowns, geographies, and 30-day list dynamics (growth, attrition, persistence, and loyalty). Query this table to measure how a segment performs before and after a snapshot, and to understand what products and channels drive that performance. Learn more about customer retention and customer segmentation.

🚧

Key Concepts

Pinned Event Date

  • The anchor date used to compare “before” vs. “after” performance for segment instances.

Instance Event Dates

  • instance_event_date_start / instance_event_date_end → Filters the segment instances (runs/snapshots) used to build the customer list.

Secondary Event Dates

  • secondary_event_date_start / secondary_event_date_end → Filters joined activity pulled from other tables (currently Orders & Pixel Orders).

Window Lengths

  • Columns labeled 7_day_ or 30_day_ use default windows of 7 days or 30 days relative to pinned_event_date. To view default queries, click the column ID in the left panel of the SQL Builder.
  • To change the window size (e.g., 3 days, 14 days, 60 days), update the formula interval and rename the alias for clarity.

Performance Tips

  • Query one derived metric per request. Combining many derived metrics in a single query can lead to slower performance and heavier resource usage.
  • Use smaller date ranges to avoid heavy queries.

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.

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.

Pinned Event Date

pinned_event_date

parameter

The anchor date used to compare before/after segment instance windows.

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.

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.

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

Derived

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

TitleIDTypeDescription
30-Day List Persistence30_day_list_persistenceformulaCount 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 Customers30_day_loyal_customersformulaNumber 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 List Attrition30_days_list_attritionformulaNumber of customers who were not in the segment 30 days before the pinned_event_date, and joined the segment at the pinned_event_date.
30-Day List Growth30_days_list_growthformulaNumber 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 Recent Joiners30_days_recent_joinersformulaNumber 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_daysformulaAd 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_daysformulaAd IDs for customers from the selected segment instances in the 7 days after the pinned_event_date.
AOV (Last 7 Days)aov_last_7_daysformulaAverage 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_daysformulaAverage 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_daysformulaCampaign 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_daysformulaCampaign IDs for customers from the selected segment instances in the 7 days after the pinned_event_date.
Channels (Last 7 Days)channels_last_7_daysformulaPixel-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_daysformulaPixel-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_daysformulaShipping 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_daysformulaShipping cities for customers from the selected segment instances in the 7 days after the pinned_event_date.
Countries (Last 7 Days)countries_last_7_daysformulaShipping 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_daysformulaShipping 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_daysformulaOrder 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_daysformulaOrder 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_daysformulaProduct 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_daysformulaProduct 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_daysformulaAverage 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_daysformulaAverage 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_daysformulaTotal 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_daysformulaTotal order revenue generated by customers from the selected segment instances in the 7 days after the pinned_event_date.