手把手教你用 SQL 实现电商产品用户分析(ORACLE)

--1.创建用户行为表
CREATE TABLE EVENTS
(
  DATES    DATE,  
  USER_ID  VARCHAR2(50),
  ITEM_ID  VARCHAR2(50),
  BEHAVIOR VARCHAR2(50)
);
COMMENT ON TABLE EVENTS
  IS '用户行为表';
COMMENT ON COLUMN EVENTS.DATES
  IS '日期';
COMMENT ON COLUMN EVENTS.USER_ID
  IS '用户ID';
COMMENT ON COLUMN EVENTS.ITEM_ID
  IS '产品ID';
COMMENT ON COLUMN EVENTS.BEHAVIOR
  IS 'pv-浏览/点击、fav-收藏、cart-加入购物车、buy-下单支付';  
  
--2.插入测试数据
TRUNCATE TABLE EVENTS;
INSERT INTO EVENTS(DATES,USER_ID,ITEM_ID,BEHAVIOR)
SELECT TO_DATE('20201125', 'YYYYMMDD') + ABS(MOD(DBMS_RANDOM.RANDOM, 30)),
       '0000' || ABS(MOD(DBMS_RANDOM.RANDOM, 10)),
       'ISSUE_0'||ABS(MOD(DBMS_RANDOM.RANDOM, 10)),
       DECODE(ABS(MOD(DBMS_RANDOM.RANDOM, 6)),
              0,
              'PV',
              1,
              'PV',
              2,
              'PV',
              3,
              'BUY',
              4,
              'FAV',
              'CART')
  FROM dual CONNECT BY ROWNUM <= 100;
COMMIT;

--3.统计PV、UV、以及pv/uv
--pv page view数
SELECT COUNT(*) AS PV,
       COUNT(DISTINCT T.USER_ID) AS UV,
       COUNT(*) / COUNT(DISTINCT T.USER_ID) PVDIVUV
  FROM EVENTS T
 WHERE T.BEHAVIOR = 'PV';
--4.购买用户数
SELECT COUNT(DISTINCT T.USER_ID) FROM EVENTS T
 WHERE T.BEHAVIOR = 'BUY';
--5.平均日浏览量
SELECT AVG(PV)
  FROM (SELECT T.DATES, COUNT(*) PV
          FROM EVENTS T
         WHERE T.BEHAVIOR = 'PV'
         GROUP BY T.DATES) D;
--6.平均日用户量
SELECT AVG(UV)
  FROM (SELECT T.DATES, COUNT(DISTINCT USER_ID) UV
          FROM EVENTS T
         WHERE T.BEHAVIOR = 'PV'
         GROUP BY T.DATES) D;
--7.Bounce rate 跳出率(只有一次点击行为的用户/总用户数)
--假设只有一个页面可以浏览，用户点进页面后要么收藏加购付款，要么跳出。
SELECT SUM(BOUNCE_USER) BOUNCE_USERS,
       SUM(TOTAL_USER) TOTAL_USERS,
       SUM(BOUNCE_USER) / SUM(TOTAL_USER) BOUNCE_RATE
  FROM (SELECT COUNT(T1.USER_ID) BOUNCE_USER, 0 AS TOTAL_USER
          FROM EVENTS T1
         WHERE T1.BEHAVIOR = 'PV'
           AND NOT EXISTS (SELECT 1
                  FROM EVENTS T2
                 WHERE T2.BEHAVIOR IN ('FAV', 'CART', 'BUY')
                   AND T1.USER_ID = T2.USER_ID)
        UNION ALL
        SELECT 0 AS BOUNCE_USER, COUNT(DISTINCT USER_ID) AS TOTAL_USER
          FROM EVENTS) T;
--8.漏斗分析
--转化率是以页面访问(PV) -> 加入购物车(CART)/收藏(FAV) -> 购买(BUY)路径为基准进行的计算，
--并且将收藏和加入购物车的行为进行了合并(考虑到这两个阶段不分先后顺序，而且都是确定购买意向的行为)
SELECT 
	TOTAL_CLICKED_USER,   --总访问用户数
	PV_TO_CART_FAV_USERS, --加入购物车/收藏用户数
	PV_TO_BUY_USERS,      --购买用户数
	PV_TO_CART_FAV_USERS/TOTAL_CLICKED_USER as pv_to_fav_cart_ratio, --访问->购物车/收藏转化率
	PV_TO_BUY_USERS/TOTAL_CLICKED_USER as pv_to_buy_ratio            --访问->购物车/收藏->购买转化率
FROM
(
	SELECT SUM(CASE WHEN T.PV_FLAG = 1 THEN 1 ELSE 0 END) TOTAL_CLICKED_USER,
	SUM(CASE WHEN T.PV_FLAG = 1 AND (T.FAV_FLAG = 1 OR T.CART_FLAG = 1) THEN 1 ELSE 0 END) PV_TO_CART_FAV_USERS,
	SUM(CASE WHEN T.PV_FLAG = 1 AND (T.FAV_FLAG = 1 OR T.CART_FLAG = 1) AND T.BUY_FLAG = 1 THEN 1 ELSE 0 END) PV_TO_BUY_USERS
	FROM 
	(
	SELECT USER_ID,MAX(CASE WHEN E.BEHAVIOR = 'PV' THEN 1 ELSE 0 END) PV_FLAG,
	MAX(CASE WHEN E.BEHAVIOR = 'FAV' THEN 1 ELSE 0 END) FAV_FLAG,
	MAX(CASE WHEN E.BEHAVIOR = 'CART' THEN 1 ELSE 0 END) CART_FLAG,
	MAX(CASE WHEN E.BEHAVIOR = 'BUY' THEN 1 ELSE 0 END) BUY_FLAG
	 FROM EVENTS E GROUP BY USER_ID
	)T
) TAR;


--9.每日新增购买/访问用户数
SELECT FRIST_DATES, COUNT(*) USERS
  FROM (SELECT E.USER_ID, MIN(DATES) FRIST_DATES
          FROM EVENTS E
         WHERE E.BEHAVIOR = 'PV'
         GROUP BY E.USER_ID) T
 GROUP BY FRIST_DATES;

SELECT FRIST_DATES, COUNT(*) USERS
  FROM (SELECT E.USER_ID, MIN(DATES) FRIST_DATES
          FROM EVENTS E
         WHERE E.BEHAVIOR = 'BUY'
         GROUP BY E.USER_ID) T
 GROUP BY FRIST_DATES;
--10.留存分析，同期群分析(同期群（cohort）是一组在特定时间做同样事的人)
SELECT *
  FROM (SELECT MIN_WEEK, COUNT(*) TOTAL_USERS
          FROM (SELECT USER_ID, TO_CHAR(MIN(DATES), 'ww') MIN_WEEK
                  FROM EVENTS T
                 WHERE T.BEHAVIOR = 'BUY'
                 GROUP BY USER_ID)
         GROUP BY MIN_WEEK) WEEKLY_USER
  LEFT JOIN (SELECT MIN_WEEK,
                    TO_CHAR(DATA_BUY.DATES, 'WW') - FIRWK_USER.MIN_WEEK WEEK_GAP,
                    COUNT(DISTINCT FIRWK_USER.USER_ID) REBUY_USERS
               FROM (SELECT USER_ID, TO_CHAR(MIN(DATES), 'WW') MIN_WEEK
                       FROM EVENTS T
                      WHERE T.BEHAVIOR = 'BUY'
                      GROUP BY USER_ID) FIRWK_USER
              INNER JOIN (SELECT * FROM EVENTS T WHERE T.BEHAVIOR = 'BUY') DATA_BUY
                 ON FIRWK_USER.USER_ID = DATA_BUY.USER_ID
              GROUP BY MIN_WEEK,
                       TO_CHAR(DATA_BUY.DATES, 'WW') - FIRWK_USER.MIN_WEEK) USERS_PER_WEEK
    ON WEEKLY_USER.MIN_WEEK = USERS_PER_WEEK.MIN_WEEK;
--11.复购分析
SELECT COUNT(USER_ID) DIS_BUYUSER,
       COUNT(CASE
               WHEN CNT > 1 THEN
                USER_ID
               ELSE
                NULL
             END) REBUY_USER,
       COUNT(CASE
               WHEN CNT > 1 THEN
                USER_ID
               ELSE
                NULL
             END) / COUNT(USER_ID) REBUY_USER_RATIO

  FROM (SELECT USER_ID, COUNT(*) CNT
          FROM EVENTS T
         WHERE T.BEHAVIOR = 'BUY'
         GROUP BY USER_ID) S;
--12.用户复购次数分布
SELECT BUY_REQ,
       USERS,
       SUM(USERS) OVER(ORDER BY BUY_REQ) CUM_USERS,
       SUM(USERS) OVER(ORDER BY BUY_REQ) / SUM(USERS) OVER() CUM_PCT_RATIO
  FROM (SELECT BUY_REQ, COUNT(DISTINCT USER_ID) USERS
          FROM (SELECT USER_ID, COUNT(*) BUY_REQ
                  FROM EVENTS T
                 WHERE T.BEHAVIOR = 'BUY'
                 GROUP BY USER_ID) T
         GROUP BY BUY_REQ);