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 10 is best and 1 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