Shopify customer RFM analysis

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

ColumnDescription
unique_idPK of the table, which is the combination of customer_id & source_relation & date_month.
customer_globalidUnique ID for customer across sources = the combination of customer_id & source_relation
date_monthThe report month
created_timestampThe date and time when the customer was created.
default_address_idThe default address for the customer.
emailThe unique email address of the customer. Attempting to assign the same email address to multiple customers returns an error.
full_nameThe customer's full name.
customer_idA unique identifier for the customer.
phoneThe unique phone number (E.164 format) for this customer. Attempting to assign the same phone number to multiple customers returns an error.
account_stateThe state of the customer's account with a shop.
first_order_timestampThe timestamp the customer completed their first order.
source_relationThe 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_tagsA string aggregated list of all tags associated with a customer.
lifetime_count_ordersThe number of orders associated with this customer.
lifetime_total_spentThe total amount of money in shop currency that the customer has spent on orders across their order history.
lifetime_total_refundedThe total amount of money that the customer has been refunded on orders across their order history.
lifetime_total_netThe total amount of money (minus refunds) that the customer has spent across their order history.
lifetime_total_shippingTotal shipping costs attributed to the customer.
lifetime_total_taxTotal amount of tax attributed to the customer.
lifetime_total_discountTotal discounts attributed to the customer.
most_recent_order_timestampThe timestamp the customer completed their most recent order up until the date_month specified.
rfm_frequency_scoreThe 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_scoreThe 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_scoreDays 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_scoreThe 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_segmentThe 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_ordersThe number of days from the lastest order date til current day (of specified timezone, or default UTC)

We're making analytics
easy for everyone.

Take control of your data like never before. Join the fight.

© 2023 Infinite Canvas Inc.
Twitter logo
LinkedIn logo
Spotify logo