一、分析思路

二、明确问题

1、整体认识：描述统计分析

对业务有个整体的认识，选择非重复用户数、商品数和品类

SELECT
    COUNT( DISTINCT user_id ) AS 会员数,
    COUNT( DISTINCT item_id ) AS 商品数,
    COUNT( DISTINCT category_id ) AS 商品类目数 
FROM
    userbehavior;

2、AIPL漏斗分析

对会员的行为进行观察，因为分析的是淘宝行为，在电商领域比较常用的方法是阿里提出的AIPL营销模型：

• A - Awareness 认知：浏览
• I - Interested 兴趣：收藏、加购
• P - Purchase 购买：购买
• L - Loyalty 忠诚：复购

为了方便分析数据，对现有数据表进行行列互换，增加浏览、收藏、加购、购买、日期、星期、小时的数据列，并创建视图。便于我们根据不同的流转情况，对运营现状有整体的把握。

CREATE VIEW behavior AS 
SELECT
    *,
    DATE_FORMAT( datetime, '%Y-%m-%d' ) AS `dates` 
FROM
    (
    SELECT
        user_id,
        datetime,
        MAX( CASE behavior_type WHEN 'pv' THEN 1 ELSE 0 END ) AS `view`,
        max( CASE behavior_type WHEN 'fav' THEN 1 ELSE 0 END ) AS favor,
        max( CASE behavior_type WHEN 'buy' THEN 1 ELSE 0 END ) AS buy,
        max( CASE behavior_type WHEN 'cart' THEN 1 ELSE 0 END ) AS cart 
    FROM
        userbehavior 
    GROUP BY
        user_id,
      datetime 
    ) AS a;

3、计算出A、I、P的值

浏览数、收藏数、购买数可直接对view、favor+cart、buy进行求和计算

SELECT 
    SUM(`VIEW`) as A,
    SUM(favor)+SUM(cart) as I,
    SUM(buy) as P
FROM behavior;

4、计算L的值

对于会员复购行为，计算思路是：

1）通过【窗口函数】统计会员的每一次消费动作，即记录第n次消费

SELECT
    user_id,
    datetime,
    dense_rank() over ( PARTITION BY user_id ORDER BY datetime ) AS 'n_consume' 
FROM
    behavior 
WHERE
    buy = 1 
ORDER BY
    user_id,
    datetime;

2）有了每个会员的第n次消费记录字段后，即可将第一次消费 列为首次消费，而第二次开始的皆为【复购消费】

3）最后统计【复购消费】行为，即为“L”即可

SELECT SUM(buy) as L FROM(
SELECT user_id,datetime,buy,ROW_NUMBER() over (PARTITION by user_id ORDER BY datetime) as 消费次数
FROM behavior
WHERE buy=1 ORDER BY user_id,datetime) as b
WHERE 消费次数>1;

5、sql分析结果

6、总结

由漏斗图可以得到如下初步结论：

A→I：这部分是拉新环节，可见由浏览到收藏加购的转化率极低，根据象限思维，确定该问题的优先级为：重要且紧急P0

P→L：这部分是用户复购环节，做的非常好，可以总结业务经验和复购原因，进一步推广复用。确定该事项优先级为：重要不紧急P1

I→P：这部分是用户破冰环节，由收藏加购到下单的转化率还可以，但是还有提高空间，可以进一步挖掘用户购买转化路径，确定该事项优先级为：不重要不紧急P2