---
title: CLR and RFM Data Table Glossary
slug: clr-and-rfm-data-table-glossary
docTags: 
createdAt: 2023-03-30T20:47:16.000Z
---



### 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.