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 ConceptsPinned 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_
or30_day_
use default windows of 7 days or 30 days relative topinned_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 |
| 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 |
| parameter | Start of the segment instance window. Filters which segment instances (runs/snapshots) to include when building the customer list. Use with |
Pinned Event Date |
| parameter | The anchor date used to compare before/after segment instance windows. |
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 |
| 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 |
Segment ID |
| parameter | The unique identifier for a segment. Example value: |
Dimensions
Dimensions are immutable properties that can be used for grouping data
Title | ID | Type | Description |
---|---|---|---|
Customer Identity |
| string | The identity of the customer in the segment (can be Example values: |
Customer Identity Type |
| string | The type of identity the segment uses for this customer. Example values: |
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: |
Segment Name |
| string | The human-readable name of the segment. Example values: |
Derived
Derived fields are metrics that are pre-calculated using multiple measures or advanced formulas
Title | ID | Type | Description |
---|---|---|---|
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 List Attrition | 30_days_list_attrition | formula | Number 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 Growth | 30_days_list_growth | 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 Recent Joiners | 30_days_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 . |
Updated about 20 hours ago