{"id":5976,"date":"2016-11-11T15:29:07","date_gmt":"2016-11-11T20:29:07","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=5976"},"modified":"2016-11-11T15:29:07","modified_gmt":"2016-11-11T20:29:07","slug":"recency-frequency-monetary-rfm-customer-valuation-in-sql","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2016\/11\/11\/recency-frequency-monetary-rfm-customer-valuation-in-sql\/","title":{"rendered":"Recency Frequency Monetary (RFM) customer valuation in SQL"},"content":{"rendered":"<p>RFM is certainly not perfect.  We identified a company purchasing our product at huge discounts (sales, etc.).  As such, they show up as a good customer, but only from the standpoint of the top line.<\/p>\n<p>The key is to check gross margin.  This will show those who are the most profitable, and don&#8217;t simply purchase everything when it is heavily discounted.  It&#8217;s good to know each type of customer, but the most profitable ones should be part of the analysis.  This is not normally the case when using RFM.<\/p>\n<pre>\r\nhive> select member_key, rfm_recency, rfm_frequency, rfm_monetary,\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rfm_recency*100 + rfm_frequency*10 + rfm_monetary as rfm_combined\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0 from (select member_key,\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ntile (5) over (order by last_purchase_date) as rfm_recency,\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ntile (5) over (order by count_purchases) as rfm_frequency,\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ntile (5) over (order by total_amount) as rfm_monetary\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from (select member_key,\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 max(trxn_date) as last_purchase_date,\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 count(*) as count_purchases,\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 sum(ttl_amount_after_discount) as total_amount\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from fact_transaction_summary\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 group by member_key) a\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) b\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0 order by rfm_combined desc\r\n\u00a0\u00a0\u00a0 >\u00a0\u00a0 limit 10;\r\nQuery ID = hive_20161110154920_4402f3b1-ee9d-4922-ae87-50a85e7c2c48\r\nTotal jobs = 5\r\nLaunching Job 1 out of 5\r\nNumber of reduce tasks not specified. Estimated from input data size: 160\r\nOK\r\n1198618327\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 555\r\n1205639576\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 555\r\n1169604726\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 555\r\n1135392826\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 555\r\n1139089861\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 555\r\n1111592362\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 555\r\n1199168550\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 555\r\n1188063454\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 555\r\n1222207202\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 555\r\n1197784897\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 555\r\nTime taken: 2637.964 seconds, Fetched: 10 row(s)\r\nhive>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>RFM is certainly not perfect. We identified a company purchasing our product at huge discounts (sales, etc.). As such, they show up as a good customer, but only from the standpoint of the top line. The key is to check&hellip;<\/p>\n<p class=\"more-link-p\"><a class=\"more-link\" href=\"http:\/\/appcrawler.com\/wordpress\/2016\/11\/11\/recency-frequency-monetary-rfm-customer-valuation-in-sql\/\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[53,59,54],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/5976"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=5976"}],"version-history":[{"count":5,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/5976\/revisions"}],"predecessor-version":[{"id":5981,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/5976\/revisions\/5981"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=5976"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=5976"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=5976"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}