재구매, 연속 구매

재구매(매월 연속 구매)

- lag함수 , lead함수를 사용해서 발생 간격을 계산

1. 임시 데이타

CREATE TEMP TABLE transaction (buyer_id int, tstamp timestamp);
INSERT INTO transaction VALUES 
 (1,'2012-01-03 20:00')
,(1,'2012-01-05 20:00')
,(1,'2012-01-07 20:00')  -- multiple transactions this month
,(1,'2012-02-03 20:00')  -- next month
,(1,'2012-03-05 20:00')  -- next month
,(2,'2012-01-07 20:00')
,(2,'2012-03-07 20:00')  -- not next month
,(3,'2012-01-07 20:00')  -- just once
,(4,'2012-02-07 20:00'); -- just once

2. 쿼리(월별, 연속구매여부)

WITH t AS (

   SELECT 
         buyer_id                                  -- 구매 유저

         ,date_trunc('month', tstamp) AS month     -- 구매 발생 월

         ,count(*) AS item_transactions                  -- 구매 회수 

         ,lag(date_trunc('month', tstamp)) OVER (PARTITION BY  buyer_id
                                           ORDER BY date_trunc('month', tstamp)) 
          = date_trunc('month', tstamp) - interval '1 month'
            OR NULL AS repeat_transaction    -- 이전 구매 발생이 1달전인가?

   FROM   
        transaction
   WHERE  
        tstamp >= '2012-01-01'::date
   AND    
        tstamp <  '2012-05-01'::date -- time range of interest.
   GROUP  
        BY 1, 2

   )
SELECT month
      ,sum(item_transactions) AS num_trans            -- 거래수

      ,count(*) AS num_buyers                                 -- 구매 유저수

      ,count(repeat_transaction) AS repeat_buyers   -- 재구매 유저수

      ,round(
          CASE WHEN sum(item_transactions) > 0
             THEN count(repeat_transaction) / sum(item_transactions) * 100
             ELSE 0
          END, 2) AS buyer_retention     -- 재구매 비율

FROM   t
GROUP  BY 1
ORDER  BY 1;

결과

  month  | num_trans | num_buyers | repeat_buyers | buyer_retention_pct
---------+-----------+------------+---------------+--------------------
 2012-01 |         5 |          3 |             0 |               0.00
 2012-02 |         2 |          2 |             1 |              50.00
 2012-03 |         2 |          2 |             1 |              50.00

http://stackoverflow.com/questions/16492842/how-to-calculate-retention-month-over-month-using-sql

https://www.postgresql.org/docs/9.6/static/functions-window.html

http://stackoverflow.com/questions/17301816/lag-function-and-group-by