This article will help you learn about the fields available in the Customer RFM & First/Second/Last Order 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.customer_profiles] table which is generated as part of the Daasity transformation process and has the following fields available/visible for use.
| Field Name | Field Type | Description | Source / Calculation |
| First Order Date | Dimension Group | Date of the First Valid Order | first_order_date |
| Last Order Date | Dimension Group | Date of the Last Valid Order | last_order_date |
| Second Order Date | Dimension Group | Date of the Second Valid Order | second_order_date |
| Customer Segment Last Week | Dimension | The Customer Segment value for this customer last week (7 Days Ago) | customer_segment_lw |
| Customer Segment Last Year | Dimension | The Customer Segment value for this customer one year ago | customer_segment_ly |
| Customer Segment This Month | Dimension | The Customer Segment value for this customer as of the 1st of this Month | customer_segment_tm |
| Customer Segment Today | Dimension | The Customer Segment value for this customer as of today | customer_segment_today |
| 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 |
| Customer with Valid Order | Dimension | indicating if the customer has made at least one Valid purchase | CASE WHEN num_orders >= 1 THEN TRUE ELSE FALSE END |
| First Order: Daasity Attribution Vendor | Dimension | The vendor of the first order based on BSD Channel Mapping and Daasity Attribution | first_order_attribution_vendor |
| First Order: Daasity Channel | Dimension | The channel of the first order based on Daasity Attribution | first_order_attribution_channel |
| First Order: First-Click Channel | Dimension | The channel of the first order based on Multi-Channel Funnel data | first_order_fc_channel |
| First Order: First-Click Vendor | Dimension | The vendor of the first order based on BSD Channel Mapping and Multi-Channel Funnel data | first_order_fc_vendor |
| First Order: Last-Click Channel | Dimension | The channel of the first order based on GA | first_order_lc_channel |
| First Order: Last-Click Vendor | Dimension | The vendor of the first order based on BSD Channel Mapping and GA data | first_order_lc_vendor |
| First Order: Number of Items | Dimension | The number of items purchased in the first order | first_order_items |
| First Order: Number of Non-Free Items | Dimension | The number of non-free items purchased in the first order (will not include samples with a $0 value) | first_order_non_free_items |
| First Order: Number of Products | Dimension | The number of products (SKUs) purchased in the first order | first_order_products |
| First Order: Payment Gateway | Dimension | The payment gateway used of the first order's largest amount if multiple payment gateway | first_order_payment_gateway |
| First Order: Store Type | Dimension | Store Type of the first purchase (Retail, Ecommerce, Marketplace) | first_order_store_type |
| Customer Ever Repurchased Flag | Dimension | Yes if the customer has ever made a 2nd purchase | customer_repurchased_flag |
| Ecommerce Buyer Flag | Dimension | Indicates if the Customer ever made a purchase in the Online Store (Ecommerce) | ecommerce_buyer |
| Ever Subscriber Flag | Dimension | Yes if customer has ever had a valid subscription | ever_subscriber_flag |
| Marketplace Buyer Flag | Dimension | Indicates if the Customer ever made a purchase via an Online Marketplace (Amazon, EBay, etc.) | marketplace_buyer |
| Omni-channel Buyer Flag | Dimension | Indicates if the Customer ever made a purchase in multiple channels (Retail, ECommerce, Marketplace) | omnichannel_buyer |
| Retail Buyer Flag | Dimension | Indicates if the Customer ever made a purchase via Retail | retail_buyer |
| Last Order: Daasity Attribution Vendor | Dimension | The vendor of the last order based on BSD Channel Mapping and Daasity Attribution | last_order_attribution_vendor |
| Last Order: Daasity Channel | Dimension | The channel of the last order based on Daasity Attribution | last_order_attribution_channel |
| Last Order: First-Click Channel | Dimension | The channel of the last order based on Multi-Channel Funnel data | last_order_fc_channel |
| Last Order: First-Click Vendor | Dimension | vendor of the last order based on BSD Channel Mapping and Multi-Channel Funnel data | last_order_fc_vendor |
| Last Order: Last-Click Channel | Dimension | The channel of the last order based on GA | last_order_lc_channel |
| Last Order: Last-Click Vendor | Dimension | The vendor of the last order based on BSD Channel Mapping and GA data | last_order_lc_vendor |
| Last Order: Number of Items | Dimension | The number of items purchased in the last order | last_order_items |
| Last Order: Number of Non-Free Items | Dimension | The number of non-free items purchased in the last order (will not include samples with a $0 value) | last_order_non_free_items |
| Last Order: Number of Products | Dimension | The number of products (SKUs) purchased in the last order | last_order_products |
| Last Order: Store Type | Dimension | The Store Type of the last purchase (Retail, Ecommerce, Marketplace) | last_order_store_type |
| Most Frequent Payment Gateway | Dimension | The most frequent used payment gateway of the customer | most_frequent_payment_gateway |
| Second Order: Daasity Attribution Vendor | Dimension | The vendor of the second order based on BSD Channel Mapping and Daasity Attribution | second_order_attribution_vendor |
| Second Order: Daasity Channel | Dimension | The channel of the second order based on Daasity Attribution | second_order_attribution_channel |
| Second Order: First-Click Channel | Dimension | The channel of the second order based on Multi-Channel Funnel data | second_order_fc_channel |
| Second Order: First-Click Vendor | Dimension | The vendor of the second order based on BSD Channel Mapping and GA data | second_order_fc_vendor |
| Second Order: Last-Click Channel | Dimension | The channel of the second order based on GA | second_order_lc_channel |
| Second Order: Last-Click Vendor | Dimension | The vendor of the second order based on BSD Channel Mapping and GA data | second_order_lc_vendor |
| Second Order: Number of Items | Dimension | The number of items purchased in the second order | second_order_items |
| Second Order: Number of Non-Free Items | Dimension | The number of non-free items purchased in the second order (will not include samples with a $0 value) | second_order_non_free_items |
| Second Order: Number of Products | Dimension | The number of products (SKUs) purchased in the last order | second_order_products |
| Second Order: Store Type | Dimension | The Store Type of the second purchase (Retail, Ecommerce, Marketplace) | second_order_store_type |
| Daasity Attribution Acquisition Cost | Measure | The cost to acquire this customer based on the Daasity Attribution Channel | AVERAGE(first_order_attribution_cpa) |
| First Click Acquisition Cost | Measure | The cost to acquire this customer based on the First Click Channel | AVERAGE(first_order_first_click_cpa) |
| Last Click Acquisition Cost | Measure | The cost to acquire this customer based on the Last Click Channel | AVERAGE(first_order_last_click_cpa) |
| Count - Orders | Measure | Count of total orders | SUM(num_orders) |
| Count - SKUs | Measure | Number of unique SKUs the customer has purchased | SUM(num_skus) |
| Count - Units | Measure | Count of total units ordered | SUM(num_units) |
| Count - Active Customers | Measure | Count of customers that have purchased in the last 12 months | COUNT(DISTINCT(CASE WHEN last_order_date > DATEADD(days, -365, CURRENT_TIMESTAMP()) THEN unique_customer_id ELSE NULL END)) |
| Count - Customers | Measure | Count of customers regardless of if they have placed or not placed an order | COUNT(DISTINCT(unique_customer_id)) |
| Total - Daasity LTV/CAC Ratio | Measure | Total Gross Margin / Daasity Acquisition Cost - the return on what is cost to acquire the customer | CASE WHEN SUM(first_order_attribution_cpa) = 0 THEN NULL ELSE SUM(gross_margin) / SUM(first_order_attribution_cpa) END |
| Total - Discount Amount | Measure | Total discount amount | SUM(discount_amount) |
| Total - Gift Card Sales Amount | Measure | Sum of amount of Gift Card Dollars($) sold | SUM(gift_card_sales_amount) |
| Total - Gross Margin | Measure | Total Gross margin from customers: Net Sales less Product Cost | SUM(gross_margin) |
| Total - Gross Sales | Measure | Total Gross sales from customers: Amount Charged - Taxes | SUM(gross_sales) |
| Total - Net Contribution | Measure | Total Net contribution from customers: Gross Margin - Last Click Marketing Cost | SUM(lc_net_contribution) |
| Total - Net Sales | Measure | Total Net sales from customers: Amount Charged - Taxes - Refunds | SUM(net_sales) |
| Total - Product Sales Amount | Measure | Total product sales amount" | SUM(product_sales_amount) |
Explores
This view is part of the following explores:
- Customer Details
- Lifetime Value
- Product Affinity
- Lifetime Value First Order SKU
- Email Campaign Performance Details
- Order & Order Line Revenue
- Transactional Sales Report
Transformation Code
This view is a Daasity derived table generated by the following code:
- [4620_CUS_BAS_customer_profiles.sql]