View: Customer RFM & First/Second/Last Order

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 - 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]

Related Resources