website logo
Navigate through spaces
⌘K
OVERVIEW
ORDER MANAGEMENT (OMS)
SHIPPING - SETUP
Shipping Methods
Shipping Zones
Shipping Method Setup Examples
TAXES - Setup
Tax Automation with Stripe Tax
Tax Automation with Avalara
CONTENT MANAGEMENT (CMS)
DATA
CUSTOMER LIFETIME REVENUE
Customer Lifetime Revenue Explanation
Customer Lifetime Revenue and Purchase Likelihood
Recency Frequency and Monetary
CLR and RFM Data Table Glossary
RFM and CLR in the Hub
LOOKER
Modifying a Dashboard
Timestamp differences between Looker, OMS and Shopify
How to create custom calculations in Looker
Looker and Shopify data models
Custom fields
How to 'Save and Schedule' reports
Creating Custom Reports or Look
Looks vs Dashboards
Login Looker FAQ
Looker Glossary
SEGMENT EVENT TRACKING
What are event tracking
Adding Tracking
What is the Tracking Plan?
The Chord Tracking Plan
The Shopify Tracking Plan
Event Tracking FAQ
EVENT UPDATES PAGE
Email Subscription Payment Event Update
Product Feed Setup
Getting Segment to Production
Consent Management
Installing in Next.js
Installing in Gatsby
Integrations
👩‍💻Developer Tools
Using Chord with Shopify
Docs powered by archbee 

CLR and RFM Data Table Glossary

1min



Introduction

The following are the columns in the table chord_ds.current_batch_output.clr_ests_current, the feeder table for RFM, and CLR Looker explores. 

An observation in this table is at the chord_tenant_id, user_id (customer_static_id for Shopify) level, so it represents a summary view of a tenant’s customer. 

Statistically modeled features are prepended with predicted_, and the other fields are directly modeled in SQL. Data for a row is based on batch_max_date, the maximum date that data is read for the batch run.

chord_tenant_id: chord tenant id. user_id: customer_static_id from shopify, user_id from chordoms. email: last email address for user_id. company: string that I use for telling chord_tenant_ids apart. cohort: first day of first month of user_id’s first purchase. zip: user_id’s last shipping zip code. state: user_id’s last shipping state. country: user_id’s last shipping country. max_dt: max date of user’s last purchase. min_dt: min date of user’s first purchase. cust_age: months since first purchase. trans_cnt: total count of transactions. sum_net_revenue: sum of net revenue. avg_ticket: user’s avg net revenue purchase. first_purchase_amt: total sum revenue of first purchase. last_purchase_amt: total sum revenue of last purchase. retail_net_rev: total user net revenue that is from shopify or legacy order_channel. subscription_net_rev: total user net revenue that is from order_channel ‘subscriptions’. other_net_rev: total user net revenue that is from not retail or subscription. retail_trans_cnt: count user transactions that is from shopify or legacy order_channel. subscription_trans_cnt: count user transactions that is from order_channel ‘subscriptions’. other_trans_cnt: count user transactions that are from not retail or subscription. pct_net_revenue_is_promo: user’s sum of total_promo / sum of net revenue. sum_promos: user’s sum of total_promo. pct_trans_with_promo: percent of user’s transactions that had total_promo>$0. avg_promo: user’s average total_promo per transaction. first_purchase_has_promo: true if first purchase had total_promo>$0, false otherwise. First_name: user’s last listed first name. Predicted_gender: simple first name match on known gender probabilities. batch_max_date: target date for the batch run, also max date of transactions in the batch run. t: time between max batch date and user’s first transaction. recency: time between max batch date and user’s last transaction. frequency: user’s total count of transactions. frequency_bucket: user’s 1-5 bucket score, where 1 is best, 5 is worst, where {5 if 1 trans, 4 if 2, 3 if 3, 2 if 4, 1 if 5 or more transactions} monetary: sum of user’s net revenue. predicted_probability_alive: model probability of purchasing from tenant again. predicted_clr: models customer lifetime revenue, equals (prob alive) * (future purchases) * (avg net revenue) recency_bucket: user’s 1-5 bucket score, where 1 is best, 5 is worst, based on quantiles or recency over the past 370 days. monetary_bucket: user’s 1-5 bucket score, where 1 is best, 5 is worst, based on quantiles or recency over the past 370 days. rfm: string that concatenates recency, frequency, and monetary buckets. For example a ‘1_1_1’ is a one for recency, frequency, and monetary, and 3_1_2 is a 3 for recency, 1 for frequency, and 2 for monetary. rfm_score: simple rfm score (1/recency) * frequency * sqrt(monetary) is_current: true if row is from the current batch run, false if from previous batch. rfm_score_bucket: user’s decile (1-10) score, where 1 is best and 10 is worst, based on rfm score. monetary_bucket_min: monetary bucket string for looker graph legends. monetary_bucket_max: monetary bucket string for looker graph legends. monetary_bucket_ranges: monetary bucket string for looker graph legends. recency_bucket_min: recency bucket string for looker graph legends. recency_bucket_max: recency bucket string for looker graph legends. recency_bucket_ranges: recency bucket string for looker graph legends. frequency_bucket_min: frequency bucket string for looker graph legends. frequency_bucket_max: frequency bucket string for looker graph legends. frequency_bucket_ranges: frequency bucket string for looker graph legends.

Updated 14 Apr 2023
Did this page help you?
Yes
No
PREVIOUS
Recency Frequency and Monetary
NEXT
RFM and CLR in the Hub
Docs powered by archbee 
TABLE OF CONTENTS
Introduction