Sometimes you may want to calculate decile from recency in MySQL based on. E.g, to rank customers based on recency of purchases or orders instead of based on total sales.
Calculate Decile from Recency
This helps create special offers for people who have recently bought something on your website. There are no functions for it yet. Here’s a ready made query to do it.
E.g, You have a table orders which contains all product orders for each user. You want to calculate decile from recency of orders.
order +-----------+------------+------------------+ | user_id | product | purchase_date | +-----------+------------+------------------+ | 1 | Soap | 2013-11-20 | | 4 | Perfume | 2013-07-02 | | 1 | Noodles | 2013-10-20 | | 4 | Soap | 2013-11-11 | | 1 | Glue | 2013-09-12 | | 3 | Deo | 2013-10-20 | +-----------+------------+------------------+
deciles +-----------+----------------+---------+---------------+ | user_id | latest | rank | decile | +-----------+----------------+---------+---------------+ | 1 | 2013-11-20 | 1 | 100 | | 4 | 2013-11-11 | 2 | 66.67 | | 3 | 2013-10-20 | 3 | 33.33 | +-----------+----------------+---------+---------------+
Here’s a query you can use to calculate decile from recency in MySQL. Just replace the columns – user_id, purchase_date and table – order. It gets the latest purchase date each user. Then it ranks them on latest over date of purchase. Finally, it calculates decile using rank.
select user_id,latest,rank,round(10*(cnt-rank+1)/cnt,0) as decile from (SELECT user_id,latest,@curRank := @curRank + 1 AS rank FROM (select user_id,max(purchase_date) as latest from `order` group by user_id) p, (SELECT @curRank := 0) r ORDER BY latest desc ) as dt,(select count(distinct user_id) as cnt from `order`) as ct
If you already have latest purchase date for each user in the table and want to directly use the table to calculate decile from recency of purchase, here’s a query
select user_id,purchase_date,rank,round(10*(cnt-rank+1)/cnt,0) as decile from (SELECT user_id,purchase_date,@curRank := @curRank + 1 AS rank FROM `order` p, (SELECT @curRank := 0) r ORDER BY purchase_date desc ) as dt,(select count(distinct user_id) as cnt from `order`) as ct
order +-----------+----------------+---------+---------------+ | user_id | purchase_date| rank | decile | +-----------+----------------+---------+---------------+ | 1 | 2013-11-20 | 1 | 10 | | 4 | 2013-11-11 | 2 | 7 | | 3 | 2013-10-20 | 3 | 3 | +-----------+----------------+---------+---------------+
deciles +-----------+----------------+---------+---------------+ | user_id | purchase_date| rank | decile | +-----------+----------------+---------+---------------+ | 1 | 2013-11-20 | 1 | 10 | | 4 | 2013-11-11 | 2 | 7 | | 3 | 2013-10-20 | 3 | 3 | +-----------+----------------+---------+---------------+
