View: Customer Segments - Historical

This article will help you learn about the fields available in the Customer Segments - Historical 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.historical_customer_segmentation] 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 Score Date Dimension Group Month for which the Customer was scored month
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 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_decile
Unique Customer ID Dimension Unique identifier for the Customer (often the email address) unique_customer_id
Average Order Value Measure Average Order Value (AOV) of customers scored in that month CASE WHEN SUM(num_orders) = 0 THEN 0 ELSE SUM(gross_sales) / SUM(num_orders)
Count Customers Measure Count of customers that were scored in that month COUNT(unique_customer_id)
Total Gross Margin Measure Total Gross Margin of customers scored in that month SUM(gross_margin)
Total Gross Sales Measure Total Gross Sales of customers scored in that month SUM(gross_sales)
Total Net Sales Measure Total Net Sales of customers scored in that month SUM(net_sales)
Total Orders Measure Total Orders of customers scored in that month SUM(num_orders)

Explores

This view is part of the following explores:

  • Customer Segments - Historical

Transformation Code

This view is a Daasity derived table generated by the following code:

  • [4615_CUS_BAS_segment_history.sql]

Related Resources