CLR and RFM Data Table Glossary
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.