Shopify customer RFM analysis
An RFM analysis of each customers.
Use case
Operations
Apps
Shopify
Created by
Canvas
Each record represents a customer in Shopify.
Model columns
| Column | Description |
|---|---|
| unique_id | PK of the table, which is the combination of customer_id & source_relation & date_month. |
| customer_globalid | Unique ID for customer across sources = the combination of customer_id & source_relation |
| date_month | The report month |
| created_timestamp | The date and time when the customer was created. |
| default_address_id | The default address for the customer. |
| The unique email address of the customer. Attempting to assign the same email address to multiple customers returns an error. | |
| full_name | The customer's full name. |
| customer_id | A unique identifier for the customer. |
| phone | The unique phone number (E.164 format) for this customer. Attempting to assign the same phone number to multiple customers returns an error. |
| account_state | The state of the customer's account with a shop. |
| first_order_timestamp | The timestamp the customer completed their first order. |
| source_relation | The schema or database this record came from if you are making use of the shopify_union_schemas or shopify_union_databases variables, respectively. Empty string if you are not using either of these variables to union together multiple Shopify connectors. |
| customer_tags | A string aggregated list of all tags associated with a customer. |
| lifetime_count_orders | The number of orders associated with this customer. |
| lifetime_total_spent | The total amount of money in shop currency that the customer has spent on orders across their order history. |
| lifetime_total_refunded | The total amount of money that the customer has been refunded on orders across their order history. |
| lifetime_total_net | The total amount of money (minus refunds) that the customer has spent across their order history. |
| lifetime_total_shipping | Total shipping costs attributed to the customer. |
| lifetime_total_tax | Total amount of tax attributed to the customer. |
| lifetime_total_discount | Total discounts attributed to the customer. |
| most_recent_order_timestamp | The timestamp the customer completed their most recent order up until the date_month specified. |
| rfm_frequency_score | The lifetime number of orders divided into 5 buckets in increasing order of amount, with 5 corresponding to the largest orders count and 1 corresponding to the smallest |
| rfm_monetary_score | The lifetime net amount (minus refunds) divided into 5 buckets in increasing order of amount, with 5 corresponding to the largest amount of money and 1 corresponding to the smallest |
| rfm_recency_score | Days since last order, divided into 5 buckets in increasing order of freshness, with 5 corresponding to the most recent and 1 corresponding to the least recent |
| rfm_monetary_frequency_score | The average of rfm_frequency_score (with specified 'm_weight' weight) and rfm_monetary_score (with specified 'f_weight' weight). Note default values of m_weight and f_weight are 1 |
| rfm_segment | The customer segment based on rfm scores, including 'Cant lose them', 'Hibernating', 'Lost', 'Need attention', 'About to sleep', 'Loyal customers', 'New customers', 'Promising', 'Champions' |
| days_since_last_orders | The number of days from the lastest order date til current day (of specified timezone, or default UTC) |
