View: Customer Retention Performance MTD

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]

Related Resources