This article will help you learn about the fields available in the Customer Retention Performance MTD view, the field types, descriptions and how the fields are calculated
Key Topics
Click on the links below to take you to the section where you can learn more about this View and where it is used and how it is created
View Definition
This view is linked to the [drp.retention_performance] table which generated as part of the Daasity transformation process and has the following fields available/visible for use.
| Field Name | Field Type | Description | Source / Calculation |
| Customer Segment | Dimension | The Customer Segment value for this customer at the start of the month | customer_segment |
| Frequency Group | Dimension | The group (segment) for the frequency based on the groups in the Daasity UI | customer_frequency_group |
| Frequency Pentile | Dimension | The pentile (segment from 1 to 5) for when a customer last purchased (1 is most recent) | customer_frequency_pentile |
| Monetary Group | Dimension | The group (segment) for the monetary based on the groups in the Daasity UI | customer_monetary_group |
| Monetary Pentile | Dimension | The pentile (segment from 1 to 5) for a customers AOV (1 is the highest AOV) | customer_monetary_pentile |
| Recency Group | Dimension | The group (segment) for the recency based on the groups in the Daasity UI | customer_recency_group |
| Recency Pentile | Dimension | The pentile (segment from 1 to 5) for when a customer last purchased (1 is the recent) | customer_recency_pentile |
| RFM Group | Dimension | The group for RFM based on Pentiles: Recency (1-5) / Frequency (1-5) / Monetary (1-5) | rfm_group |
| RFM Score | Dimension | The raw score for the RFM = F * SQRT(M) / R | rfm_score |
| RFM Score Decile | Dimension | The group (segment from 1 to 10) of the RFM score (1 is the best) | rfm_score_decile |
| Performance Period | Dimension | Use to show performance for either This Month or Last Month | performance_period |
| Unique Customer ID | Dimension | ||
| Average Order Value (AOV) | Measure | Average Order Value = Total Sales/Number of Orders | COALESCE(SUM(total_net_sales), 0) / NULLIF(COALESCE(SUM(num_orders), 0), 0) |
| Average Unit Revenue (AUR) | Measure | Average Unit Revenue = Total Sales/Number of Units | COALESCE(SUM(total_net_sales), 0) / NULLIF(COALESCE(SUM(num_units), 0), 0) |
| Revenue per Customer | Measure | Average Revenue per Customer that ordered this month | CASE WHEN COALESCE(SUM(num_ordering_customers ), 0) = 0 THEN 0.00 ELSE COALESCE(SUM(gross_sales), 0) / NULLIF(COALESCE(SUM(num_ordering_customers ), 0), 0) END |
| Units per Transaction (UPT) | Measure | Units per Transaction = Total Units/Number of Orders | COALESCE(SUM(num_units), 0) * 1.0 / NULLIF(COALESCE(SUM(num_orders), 0), 0) |
| Count Customers Ordering | Measure | The total number of customers that have placed an order this month | COALESCE(SUM(num_ordering_customers ), 0) |
| Count Customers | Measure | Count of Customers | COUNT(DISTINCT(unique_customer_id)) |
| Count Orders | Measure | Total number of orders | COALESCE(SUM(num_orders), 0) |
| Count Units | Measure | Total number of units | COALESCE(SUM(num_units), 0) |
| Percent of Segment Ordering (%) | Measure | Percent of Customer from Segment placing an order this month | COALESCE(SUM(num_ordering_customers ), 0) / NULLIF(COUNT(DISTINCT(unique_customer_id)), 0) |
| Total Gross Margin | Measure | Total Gross Margin of orders: Net Sales less SKU cost | COALESCE(SUM(gross_margin), 0) |
| Total Gross Sales | Measure | Total Gross sales of orders: Amount Charged - Tax Amount | COALESCE(SUM(gross_sales), 0) |
| Total Net Sales | Measure | Total Net sales of orders: Amount Charged - Tax - Refund | COALESCE(SUM(net_sales), 0) |
Explores
This view is part of the following explores:
- Customer Retention Performance MTD
Transformation Code
This view is a Daasity derived table generated by the following code:
- [4640_CUS_BAS_retentoon_performance.sql]