一、數(shù)據(jù)來(lái)源及說明

數(shù)據(jù)來(lái)源:
https://tianchi.aliyun.com/dataset/dataDetail?dataId=46&userId=1

本文從數(shù)據(jù)集中選取包含了2014年11月18日至2014年12月18日之間，9967名隨機(jī)用戶共1876222條行為數(shù)據(jù)，數(shù)據(jù)集的每一行表示一條用戶行為，共6列。

列字段包含以下：

• user_id：用戶身份
• item_id：商品ID
• behavior_type：用戶行為類型（包含點(diǎn)擊、收藏、加購(gòu)物車、購(gòu)買四種行為，分別用數(shù)字1、2、3、4表示）
• user_geohash：地理位置（有空值）
• item_category：品類ID（商品所屬的品類）
• time：用戶行為發(fā)生的時(shí)間

二、提出問題

1、整體用戶的購(gòu)物情況
pv（總訪問量）、日均訪問量、uv（用戶總數(shù)）、有購(gòu)買行為的用戶數(shù)量、用戶的購(gòu)物情況、復(fù)購(gòu)率分別是多少？

2、用戶行為轉(zhuǎn)化漏斗
點(diǎn)擊— 加購(gòu)物車— 收藏— 購(gòu)買各環(huán)節(jié)轉(zhuǎn)化率如何？購(gòu)物車遺棄率是多少，如何提高？

3、購(gòu)買率高和購(gòu)買率為 0 的人群有什么特征

4、基于時(shí)間維度了解用戶的行為習(xí)慣

5、基于RFM模型的用戶分析

三、數(shù)據(jù)清洗

1.導(dǎo)入數(shù)據(jù)

由于數(shù)據(jù)量有100多萬(wàn)，通過數(shù)據(jù)庫(kù)管理工具 workbench 將數(shù)據(jù)集導(dǎo)入 MySQL 數(shù)據(jù)庫(kù)會(huì)表較慢，我這里使用ETL工具kettle進(jìn)行導(dǎo)數(shù)，能夠提高導(dǎo)數(shù)效率，也方便后續(xù)實(shí)現(xiàn)報(bào)表自動(dòng)化處理，數(shù)據(jù)庫(kù)的表名為user。

2.缺失值處理

item_category 列表示地理位置信息，由于數(shù)據(jù)存在大量空值，且位置信息被加密處理，難以研究，因此后續(xù)不對(duì)item_category列進(jìn)行分析。

mysql> select * from user limit 10;
+-----------+-----------+---------------+--------------+---------------+---------------+
| user_id   | item_id   | behavior_type | user_geohash | item_category | time          |
+-----------+-----------+---------------+--------------+---------------+---------------+
|  98047837 | 232431562 |             1 |              |          4245 | 2014-12-06 02 |
|  97726136 | 383583590 |             1 |              |          5894 | 2014-12-09 20 |
|  98607707 |  64749712 |             1 |              |          2883 | 2014-12-18 11 |
|  98662432 | 320593836 |             1 | 96nn52n      |          6562 | 2014-12-06 10 |
|  98145908 | 290208520 |             1 |              |         13926 | 2014-12-16 21 |
|  93784494 | 337869048 |             1 |              |          3979 | 2014-12-03 20 |
|  94832743 | 105749725 |             1 |              |          9559 | 2014-12-13 20 |
|  95290487 |  76866650 |             1 |              |         10875 | 2014-11-27 16 |
|  96610296 | 161166643 |             1 |              |          3064 | 2014-12-11 23 |
| 100684618 |  21751142 |             3 |              |          2158 | 2014-12-05 23 |
+-----------+-----------+---------------+--------------+---------------+---------------+
10 rows in set (0.00 sec)

3.數(shù)據(jù)一致化處理

由于 time 字段的時(shí)間包含（年-月-日）和小時(shí)，為了方便分析，將該字段分成 2 個(gè)字段，一個(gè)日期列（date）和一個(gè)小時(shí)列（time）。

mysql> alter table user add date varchar(20) not null after item_category;
mysql> update user set date = time;
mysql> update user set date = replace(date,date,substring_index(date,' ',1));
mysql> update user set time = replace(time,time,substring_index(time,' ',-1));
mysql> select * from user limit 5;
+----------+-----------+---------------+--------------+---------------+------------+------+
| user_id  | item_id   | behavior_type | user_geohash | item_category | date       | time |
+----------+-----------+---------------+--------------+---------------+------------+------+
| 98047837 | 232431562 |             1 |              |          4245 | 2014-12-06 | 02   |
| 97726136 | 383583590 |             1 |              |          5894 | 2014-12-09 | 20   |
| 98607707 |  64749712 |             1 |              |          2883 | 2014-12-18 | 11   |
| 98662432 | 320593836 |             1 | 96nn52n      |          6562 | 2014-12-06 | 10   |
| 98145908 | 290208520 |             1 |              |         13926 | 2014-12-16 | 21   |
+----------+-----------+---------------+--------------+---------------+------------+------+
5 rows in set (0.00 sec)

由于 behavior_type 列的四種行為類型分別用 1，2，3，4 表示點(diǎn)擊、收藏、加購(gòu)物車、購(gòu)買四種行為，為了方便查看數(shù)據(jù)，將1，2，3，4替換為 ‘pv'、’fav‘，’cart'，‘buy' 。

mysql> alter table user modify behavior_type varchar(20);
mysql> update user set behavior_type = replace(behavior_type,1,'pv');
mysql> update user set behavior_type = replace(behavior_type,2,'fav');
mysql> update user set behavior_type = replace(behavior_type,3,'cart');
mysql> update user set behavior_type = replace(behavior_type,4,'buy');
mysql> select * from user limit 5;
+----------+-----------+---------------+--------------+---------------+------------+------+
| user_id  | item_id   | behavior_type | user_geohash | item_category | date       | time |
+----------+-----------+---------------+--------------+---------------+------------+------+
| 98047837 | 232431562 | pv            |              |          4245 | 2014-12-06 | 02   |
| 97726136 | 383583590 | pv            |              |          5894 | 2014-12-09 | 20   |
| 98607707 |  64749712 | pv            |              |          2883 | 2014-12-18 | 11   |
| 98662432 | 320593836 | pv            | 96nn52n      |          6562 | 2014-12-06 | 10   |
| 98145908 | 290208520 | pv            |              |         13926 | 2014-12-16 | 21   |
+----------+-----------+---------------+--------------+---------------+------------+------+
5 rows in set (0.00 sec)

通過查詢表結(jié)構(gòu)，可以看到 date 列日期列不是日期類型:

mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| user_id       | int(11)     | YES  |     | NULL    |       |
| item_id       | int(11)     | YES  |     | NULL    |       |
| behavior_type | varchar(20) | YES  |     | NULL    |       |
| user_geohash  | text        | YES  |     | NULL    |       |
| item_category | int(11)     | YES  |     | NULL    |       |
| date          | varchar(20) | NO   |     | NULL    |       |
| time          | varchar(20) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

將date 列改成 date 類型：

mysql> alter table user modify date date;
mysql> desc user;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| user_id       | int(11)     | YES  |     | NULL    |       |
| item_id       | int(11)     | YES  |     | NULL    |       |
| behavior_type | varchar(20) | YES  |     | NULL    |       |
| user_geohash  | text        | YES  |     | NULL    |       |
| item_category | int(11)     | YES  |     | NULL    |       |
| date          | date        | YES  |     | NULL    |       |
| time          | varchar(20) | YES  |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

四、構(gòu)建模型和分析問題

1.總體用戶購(gòu)物情況

（1）pv（總訪問量）

mysql> select count(behavior_type) as 總訪問量
    -> from user
    -> where behavior_type = 'pv';
+--------------+
| 總訪問量     |
+--------------+
|      1768720 |
+--------------+
1 row in set (0.61 sec)

（2）日均訪問量

mysql> select date, count(behavior_type) as 日均訪問量 from user where behavior_type = 'pv' group by date order by date limit 10;
+------------+-----------------+
| date       | 日均訪問量      |
+------------+-----------------+
| 2014-11-18 |           52940 |
| 2014-11-19 |           52021 |
| 2014-11-20 |           50978 |
| 2014-11-21 |           47847 |
| 2014-11-22 |           52362 |
| 2014-11-23 |           55367 |
| 2014-11-24 |           54978 |
| 2014-11-25 |           53898 |
| 2014-11-26 |           52194 |
| 2014-11-27 |           53284 |
+------------+-----------------+
10 rows in set (1.23 sec)

（3）uv（用戶總數(shù)）

mysql> select count(distinct user_id)  as 用戶總數(shù) from user;
+--------------+
| 用戶總數(shù)     |
+--------------+
|         9967 |
+--------------+

（4）有購(gòu)買行為的用戶數(shù)量

mysql> select count(distinct user_id) as 購(gòu)買用戶數(shù) from user where behavior_type = 'buy';
+-----------------+
| 購(gòu)買用戶數(shù)      |
+-----------------+
|            5878 |
+-----------------+

（5）用戶的購(gòu)物情況

mysql> create view user_behavior as
    -> select user_id, count(behavior_type),
    -> sum(case when behavior_type='pv' then 1 else 0 end) as 點(diǎn)擊次數(shù),
    -> sum(case when behavior_type='fav' then 1 else 0 end) as 收藏次數(shù),
    -> sum(case when behavior_type='cart' then 1 else 0 end) as 加購(gòu)物車次數(shù),
    -> sum(case when behavior_type='buy' then 1 else 0 end) as 購(gòu)買次數(shù)
    -> from user
    -> group by user_id
    -> order by count(behavior_type) desc;
mysql> select * from user_behavior limit 5;
+-----------+----------------------+--------------+--------------+--------------------+--------------+
| user_id   | count(behavior_type) | 點(diǎn)擊次數(shù)     | 收藏次數(shù)     | 加購(gòu)物車次數(shù)       | 購(gòu)買次數(shù)     |
+-----------+----------------------+--------------+--------------+--------------------+--------------+
|  65645933 |                 4161 |         3661 |          487 |                 10 |            3 |
|  73196588 |                 4003 |         4003 |            0 |                  0 |            0 |
| 130270245 |                 3968 |         3776 |          151 |                 40 |            1 |
|  83813302 |                 3493 |         3416 |           57 |                 16 |            4 |
|  36233277 |                 3117 |         2790 |          290 |                 30 |            7 |
+-----------+----------------------+--------------+--------------+--------------------+--------------+

（6）復(fù)購(gòu)率：產(chǎn)生兩次或兩次以上購(gòu)買的用戶占購(gòu)買用戶的比例

mysql> select
    -> sum(case when 購(gòu)買次數(shù)>1 then 1 else 0 end) as 購(gòu)買數(shù)大于1次,
    -> sum(case when 購(gòu)買次數(shù)>0 then 1 else 0 end) as 總購(gòu)買數(shù),
    -> concat(round(sum(case when 購(gòu)買次數(shù)>1 then 1 else 0 end)/sum(case when 購(gòu)買次數(shù)>0 then 1 else 0 end)*100,2),'%') as 復(fù)購(gòu)率
    -> from user_behavior;
+---------------------+--------------+-----------+
| 購(gòu)買數(shù)大于1次       | 總購(gòu)買數(shù)     | 復(fù)購(gòu)率    |
+---------------------+--------------+-----------+
|                3649 |         5878 | 62.08%    |
+---------------------+--------------+-----------+

2.用戶行為轉(zhuǎn)化漏斗

在購(gòu)物環(huán)節(jié)中收藏和加入購(gòu)物車兩個(gè)環(huán)節(jié)沒有先后之分，所以將這兩個(gè)環(huán)節(jié)可以放在一起作為購(gòu)物環(huán)節(jié)的一步。最終得到用戶購(gòu)物行為各環(huán)節(jié)轉(zhuǎn)化率，如下:

mysql> select sum(點(diǎn)擊次數(shù)) as 點(diǎn)擊總數(shù), sum(收藏次數(shù)) as 收藏總數(shù), sum(加購(gòu)物車次數(shù)) as 加購(gòu)物車總數(shù), sum(購(gòu)買次數(shù)) as 購(gòu)買總數(shù)
    -> from user_behavior;
+--------------+--------------+--------------------+--------------+
| 點(diǎn)擊總數(shù)     | 收藏總數(shù)     | 加購(gòu)物車總數(shù)       | 購(gòu)買總數(shù)     |
+--------------+--------------+--------------------+--------------+
|      1768720 |        37000 |              52180 |        18322 |
+--------------+--------------+--------------------+--------------+

mysql> select
    -> concat(round(sum(點(diǎn)擊次數(shù))/sum(點(diǎn)擊次數(shù))*100,2),'%') as pv,
    -> concat(round((sum(收藏次數(shù))+sum(加購(gòu)物車次數(shù)))/sum(點(diǎn)擊次數(shù))*100,2),'%') as pv_to_favCart,
    -> concat(round(sum(購(gòu)買次數(shù))/sum(點(diǎn)擊次數(shù))*100,2),'%') as pv_to_buy
    -> from user_behavior;
+---------+---------------+-----------+
| pv      | pv_to_favCart | pv_to_buy |
+---------+---------------+-----------+
| 100.00% | 5.04%         | 1.04%     |
+---------+---------------+-----------+
1 row in set (2.18 sec)

不同的行業(yè)轉(zhuǎn)化率會(huì)有差異，據(jù)2012年的一項(xiàng)研究表明，在整個(gè)互聯(lián)網(wǎng)范圍內(nèi)，平均轉(zhuǎn)化率為2.13%（數(shù)據(jù)來(lái)源于《精益數(shù)據(jù)分析》），圖中所示購(gòu)買行為的轉(zhuǎn)化率為1.04%，與行業(yè)平均值存在較大差異，淘寶移動(dòng)端用戶行為的轉(zhuǎn)化率還有很大的增長(zhǎng)空間。

3.購(gòu)買率高和購(gòu)買率為低的人群有什么特征

購(gòu)買率高用戶特征:

#按購(gòu)買率從高到低排序
mysql> select user_id, 點(diǎn)擊次數(shù), 加購(gòu)物車次數(shù), 購(gòu)買次數(shù), round(購(gòu)買次數(shù)/點(diǎn)擊次數(shù)*100,2) as 購(gòu)買率 from user_behavior group by user_id order by 購(gòu)買率 desc limit 5;
+----------+--------------+--------------------+--------------+-----------+
| user_id  | 點(diǎn)擊次數(shù)     | 加購(gòu)物車次數(shù)       | 購(gòu)買次數(shù)     | 購(gòu)買率    |
+----------+--------------+--------------------+--------------+-----------+
| 56970308 |            4 |                  0 |            5 |    125.00 |
| 39912392 |            1 |                  0 |            1 |    100.00 |
| 84281661 |            1 |                  0 |            1 |    100.00 |
| 39095072 |            2 |                  0 |            2 |    100.00 |
| 47763414 |            2 |                  0 |            1 |     50.00 |
+----------+--------------+--------------------+--------------+-----------+
5 rows in set, 4 warnings (2.24 sec)
#按購(gòu)買率從低到高排序
mysql> select user_id, 點(diǎn)擊次數(shù), 加購(gòu)物車次數(shù), 購(gòu)買次數(shù), round(購(gòu)買次數(shù)/點(diǎn)擊次數(shù)*100,2) as 購(gòu)買率 from user_behavior group by user_id order by 購(gòu)買率  limit 5;
+-----------+--------------+--------------------+--------------+-----------+
| user_id   | 點(diǎn)擊次數(shù)     | 加購(gòu)物車次數(shù)       | 購(gòu)買次數(shù)     | 購(gòu)買率    |
+-----------+--------------+--------------------+--------------+-----------+
|  69033110 |            0 |                  1 |            0 |      NULL |
|  45881494 |            0 |                  0 |            1 |      NULL |
|  24869620 |            0 |                  0 |            2 |      NULL |
| 117489231 |            0 |                  0 |            1 |      NULL |
|  12222620 |          140 |                  1 |            0 |      0.00 |
+-----------+--------------+--------------------+--------------+-----------+

由以上結(jié)果可以看出，購(gòu)買率高的用戶點(diǎn)擊率反而不是最多的，這些用戶收藏?cái)?shù)和加購(gòu)物車的次數(shù)也很少，一般不點(diǎn)擊超過5次就直接購(gòu)買，由此可以推斷出這些用戶為理智型消費(fèi)者，有明確的購(gòu)物目標(biāo)，屬于缺啥買啥型，很少會(huì)被店家廣告或促銷吸引。
購(gòu)買率為低用戶特征：

mysql> select user_id, 點(diǎn)擊次數(shù), 加購(gòu)物車次數(shù), 購(gòu)買次數(shù), round(購(gòu)買次數(shù)/點(diǎn)擊次數(shù)*100,2) as 購(gòu)買率 from user_behavior group by user_id order by 購(gòu)買次數(shù) limit 10;
+-----------+--------------+--------------------+--------------+-----------+
| user_id   | 點(diǎn)擊次數(shù)     | 加購(gòu)物車次數(shù)       | 購(gòu)買次數(shù)     | 購(gòu)買率    |
+-----------+--------------+--------------------+--------------+-----------+
|  12222620 |          140 |                  1 |            0 |      0.00 |
|  26438512 |          193 |                 14 |            0 |      0.00 |
| 136496700 |           48 |                  2 |            0 |      0.00 |
|  84614339 |            7 |                  0 |            0 |      0.00 |
| 138162465 |           83 |                  0 |            0 |      0.00 |
|   1041761 |          287 |                  0 |            0 |      0.00 |
|  21818576 |          140 |                  1 |            0 |      0.00 |
|  33077425 |          301 |                  2 |            0 |      0.00 |
|  21984163 |           30 |                  0 |            0 |      0.00 |
|  39883816 |            6 |                  1 |            0 |      0.00 |
+-----------+--------------+--------------------+--------------+-----------+
10 rows in set, 4 warnings (2.23 sec)

由以上結(jié)果可以看出，購(gòu)買率為低用戶分為兩類，一類是點(diǎn)擊次數(shù)少的，一方面的原因是這類用戶可能是不太會(huì)購(gòu)物或者不喜歡上網(wǎng)的用戶，可以加以引導(dǎo)，另一方面是從商品的角度考慮，是否商品定價(jià)過高或設(shè)計(jì)不合理；第二類用戶是點(diǎn)擊率高、收藏或加購(gòu)物車也多的用戶，此類用戶可能正為商家的促銷活動(dòng)做準(zhǔn)備，下單欲望較少且自制力較強(qiáng)，思慮多或者不會(huì)支付，購(gòu)物難度較大。

4.基于時(shí)間維度了解用戶的行為習(xí)慣

（1）一天中用戶活躍時(shí)段分布

mysql> select time, count(behavior_type) as 用戶行為總量,
    -> sum(case when behavior_type='pv' then 1 else 0 end) as 點(diǎn)擊次數(shù),
    -> sum(case when behavior_type='fav' then 1 else 0 end) as 收藏次數(shù),
    -> sum(case when behavior_type='cart' then 1 else 0 end) as 加購(gòu)物車次數(shù),
    -> sum(case when behavior_type='buy' then 1 else 0 end) as 購(gòu)買次數(shù)
    -> from user
    -> group by time
    -> order by time;
+------+--------------------+--------------+--------------+--------------------+--------------+
| time | 用戶行為總量       | 點(diǎn)擊次數(shù)     | 收藏次數(shù)     | 加購(gòu)物車次數(shù)       | 購(gòu)買次數(shù)     |
+------+--------------------+--------------+--------------+--------------------+--------------+
| 00   |              79057 |        74498 |         1648 |               2138 |          773 |
| 01   |              40866 |        38657 |          927 |               1019 |          263 |
| 02   |              22163 |        20974 |          492 |                571 |          126 |
| 03   |              14828 |        14072 |          313 |                379 |           64 |
| 04   |              11989 |        11338 |          295 |                302 |           54 |
| 05   |              13470 |        12789 |          306 |                326 |           49 |
| 06   |              24138 |        22828 |          544 |                615 |          151 |
| 07   |              44268 |        41991 |          900 |               1081 |          296 |
| 08   |              60635 |        57323 |         1211 |               1527 |          574 |
| 09   |              74415 |        69935 |         1625 |               1955 |          900 |
| 10   |              84487 |        79142 |         1753 |               2511 |         1081 |
| 11   |              80614 |        75554 |         1662 |               2302 |         1096 |
| 12   |              81581 |        76733 |         1512 |               2242 |         1094 |
| 13   |              91904 |        86323 |         1801 |               2615 |         1165 |
| 14   |              90378 |        84904 |         1790 |               2592 |         1092 |
| 15   |              91234 |        85684 |         1843 |               2632 |         1075 |
| 16   |              87846 |        82640 |         1721 |               2411 |         1074 |
| 17   |              77514 |        73061 |         1495 |               2178 |          780 |
| 18   |              83854 |        79208 |         1649 |               2256 |          741 |
| 19   |             112641 |       106714 |         2175 |               2852 |          900 |
| 20   |             143282 |       135775 |         2533 |               3784 |         1190 |
| 21   |             167176 |       158072 |         3119 |               4635 |         1350 |
| 22   |             167580 |       158172 |         3011 |               4970 |         1427 |
| 23   |             130302 |       122333 |         2675 |               4287 |         1007 |
+------+--------------------+--------------+--------------+--------------------+--------------+
24 rows in set (2.46 sec)

image.png

可以看出，每日0點(diǎn)到5點(diǎn)用戶活躍度快速降低，降到一天中的活躍量最低值，6點(diǎn)到10點(diǎn)用戶活躍度快速上升，10點(diǎn)到18點(diǎn)用戶活躍度較平穩(wěn)，17點(diǎn)到23點(diǎn)用戶活躍度快速上升，達(dá)到一天中的最高值。

（2）一周中用戶活躍時(shí)段分布
由于第一周和第五周的數(shù)據(jù)不全，因此這兩周的數(shù)據(jù)不考慮到此次數(shù)據(jù)分析中。

mysql> select date_format(date,'%w') as weeks, count(behavior_type) as 用戶行為總量,
    -> sum(case when behavior_type='pv' then 1 else 0 end) as 點(diǎn)擊次數(shù),
    -> sum(case when behavior_type='fav' then 1 else 0 end) as 收藏次數(shù),
    -> sum(case when behavior_type='cart' then 1 else 0 end) as 加購(gòu)物車次數(shù),
    -> sum(case when behavior_type='buy' then 1 else 0 end) as 購(gòu)買次數(shù)
    -> from user
    -> group by weeks
    -> order by weeks;
+-------+--------------------+--------------+--------------+--------------------+--------------+
| weeks | 用戶行為總量       | 點(diǎn)擊次數(shù)     | 收藏次數(shù)     | 加購(gòu)物車次數(shù)       | 購(gòu)買次數(shù)     |
+-------+--------------------+--------------+--------------+--------------------+--------------+
| 0     |             244035 |       230288 |         5028 |               6589 |         2130 |
| 1     |             238739 |       225341 |         4633 |               6524 |         2241 |
| 2     |             296893 |       280178 |         5846 |               8155 |         2714 |
| 3     |             296527 |       279804 |         5952 |               8091 |         2680 |
| 4     |             302815 |       285496 |         6063 |               8602 |         2654 |
| 5     |             264299 |       247736 |         4795 |               7978 |         3790 |
| 6     |             232914 |       219877 |         4683 |               6241 |         2113 |
+-------+--------------------+--------------+--------------+--------------------+--------------+
7 rows in set (2.43 sec)

image.png

由以上結(jié)果可以看出，每周用戶活躍度較穩(wěn)定，每周四活躍度會(huì)有小幅降低，但是周末會(huì)慢慢回升。

5.基于 RFM 模型找出有價(jià)值的用戶

RFM模型是衡量客戶價(jià)值和客戶創(chuàng)利能力的重要工具和手段，其中由3個(gè)要素構(gòu)成了數(shù)據(jù)分析最好的指標(biāo)，分別是：

• R-Recency（最近一次購(gòu)買時(shí)間）
• F-Frequency（消費(fèi)頻率）
• M-Money（消費(fèi)金額）
  由于數(shù)據(jù)源沒有相關(guān)的金額數(shù)據(jù)，暫且通過 R 和 F 的數(shù)據(jù)對(duì)客戶價(jià)值進(jìn)行打分。
  （1）計(jì)算R-Recency
  由于數(shù)據(jù)集包含的時(shí)間是從2014年11月18日至2014年12月18日，這里選取2014年12月19日作為計(jì)算日期，統(tǒng)計(jì)客戶最近發(fā)生購(gòu)買行為的日期距離2014年12月19日間隔幾天，再對(duì)間隔時(shí)間進(jìn)行排名，間隔天數(shù)越少，客戶價(jià)值越大，排名越靠前。

mysql> select a.*, (@rank := @rank+1) as recent_rank
    -> from (
    -> select user_id, datediff('2014-12-19', max(date)) as recent
    -> from user
    -> where behavior_type = 'buy'
    -> group by user_id
    -> order by recent) as a,
    -> (select @rank := 0) as b
    -> limit 5;
+-----------+--------+-------------+
| user_id   | recent | recent_rank |
+-----------+--------+-------------+
|  35205411 |      1 |           1 |
|   4361577 |      1 |           2 |
| 119191477 |      1 |           3 |
|  28467700 |      1 |           4 |
| 103439105 |      1 |           5 |
+-----------+--------+-------------+
5 rows in set, 2 warnings (0.66 sec)

（2）計(jì)算F-Frequency
先統(tǒng)計(jì)每位用戶的購(gòu)買頻率，再對(duì)購(gòu)買頻率進(jìn)行排名，頻率越大，客戶價(jià)值越大，排名越靠前。

mysql> select a.*, (@rank := @rank+1) as freq_rank
    -> from (
    -> select user_id, count(behavior_type) as frequency
    -> from user
    -> where behavior_type = 'buy'
    -> group by user_id
    -> order by frequency desc) as a,
    -> (select @rank := 0) as b
    -> limit 5;
+-----------+-----------+-----------+
| user_id   | frequency | freq_rank |
+-----------+-----------+-----------+
| 122338823 |       161 |         1 |
|  51492142 |        87 |         2 |
|  56560718 |        52 |         3 |
| 123842164 |        49 |         4 |
|  35306096 |        46 |         5 |
+-----------+-----------+-----------+
5 rows in set, 2 warnings (0.63 sec)

（3）對(duì)用戶進(jìn)行評(píng)分
對(duì)5878名有購(gòu)買行為的用戶按照排名進(jìn)行分組，共劃分為四組，對(duì)排在前四分之一的用戶打4分，排在前四分之一到四分之二（即二分之一）的用戶打3分，排在前四分之二到前四分之三的用戶打2分，剩余的用戶打1分，按照這個(gè)規(guī)則分別對(duì)用戶時(shí)間間隔排名打分和購(gòu)買頻率排名打分，最后把兩個(gè)分?jǐn)?shù)合并在一起作為該名用戶的最終評(píng)分。計(jì)算腳本如下：

mysql> SELECT r.user_id,r.recent,r.recent_rank,f.frequency,f.freq_rank,                                     
    -> CONCAT(   --  對(duì)客戶購(gòu)買行為的日期排名和頻率排名進(jìn)行打分
    -> CASE WHEN r.recent_rank <= (5878/4) THEN '4'
    -> WHEN r.recent_rank > (5878/4) AND r.recent_rank <= (5878/2) THEN '3'
    -> WHEN f.freq_rank > (5878*2/4) AND f.freq_rank <= (5878*3/4) THEN '2' ELSE '1' END,
    -> CASE WHEN f.freq_rank <= (5758/4) THEN '4'
    -> WHEN f.freq_rank > (5758/4) AND f.freq_rank <= (5758/2) THEN '3'
    -> WHEN f.freq_rank > (5758/2) AND f.freq_rank <= (5758*3/4) THEN '2' ELSE '1' END) AS user_value
    -> from
--  對(duì)每位用戶最近發(fā)生購(gòu)買行為的間隔時(shí)間進(jìn)行排名（間隔天數(shù)越少，客戶價(jià)值越大）
    -> (SELECT a.*,(@rank := @rank + 1) AS recent_rank
    -> FROM  --  統(tǒng)計(jì)客戶最近發(fā)生購(gòu)買行為的日期距離'2014-12-19'間隔幾天
    -> (SELECT user_id,DATEDIFF('2014-12-19',MAX(date)) AS recent
    -> FROM user
    -> WHERE behavior_type = 'buy'
    -> GROUP BY user_id
    -> ORDER BY recent) AS a,
    -> (SELECT @rank := 0) AS b) AS r,
-- 對(duì)每位用戶的購(gòu)買頻率進(jìn)行排名（頻率越大，客戶價(jià)值越大）
    -> (SELECT a.*,(@rank2 := @rank2 + 1) AS freq_rank
    -> FROM   --  統(tǒng)計(jì)每位用戶的購(gòu)買頻率
    -> (SELECT user_id,COUNT(behavior_type) AS frequency
    -> FROM user
    -> WHERE behavior_type = 'buy'
    -> GROUP BY user_id
    -> ORDER BY frequency DESC) AS a,
    -> (SELECT @rank2 := 0) AS b) AS f
    -> WHERE r.user_id = f.user_id
    -> limit 10;
+-----------+--------+-------------+-----------+-----------+------------+
| user_id   | recent | recent_rank | frequency | freq_rank | user_value |
+-----------+--------+-------------+-----------+-----------+------------+
|  35205411 |      1 |           1 |         3 |      1648 | 43         |
|   4361577 |      1 |           2 |        24 |        17 | 44         |
| 119191477 |      1 |           3 |         2 |      3202 | 42         |
|  28467700 |      1 |           4 |        14 |        69 | 44         |
| 103439105 |      1 |           5 |        11 |       138 | 44         |
|  95161544 |      1 |           6 |         6 |       593 | 44         |
|  63929694 |      1 |           7 |         6 |       742 | 44         |
| 104683710 |      1 |           8 |         7 |       536 | 44         |
| 126024699 |      1 |           9 |        12 |       115 | 44         |
|  39367110 |      1 |          10 |         2 |      3263 | 42         |
+-----------+--------+-------------+-----------+-----------+------------+
10 rows in set, 4 warnings (1.30 sec)

通過打分可以了解每位顧客的特性，從而實(shí)現(xiàn)差異化營(yíng)銷。比如對(duì)于 user_value = 44 的用戶，為重點(diǎn)用戶需要關(guān)注；對(duì)于user_value = 41 這類忠誠(chéng)度高而購(gòu)買能力不足的，可以可以適當(dāng)給點(diǎn)折扣或捆綁銷售來(lái)增加用戶的購(gòu)買頻率。對(duì)于 user_value = 14 這類忠誠(chéng)度不高而購(gòu)買能力強(qiáng)的，需要關(guān)注他們的購(gòu)物習(xí)性做精準(zhǔn)化營(yíng)銷。還可以通過每個(gè)月對(duì)用戶的評(píng)分變化，推測(cè)客戶消費(fèi)的異動(dòng)狀況，對(duì)于即將流失的客戶，通過電話問候、贈(zèng)送禮品、加大折扣力度等有效的方式挽回客戶。

五、結(jié)論

1、總體轉(zhuǎn)化率只有 1%，用戶點(diǎn)擊后收藏和加購(gòu)物車的轉(zhuǎn)化率在 5% ，需要提高用戶的購(gòu)買意愿，可通過活動(dòng)促銷、精準(zhǔn)營(yíng)銷等方式。

2、購(gòu)買率高且點(diǎn)擊量少的用戶屬于理智型購(gòu)物者，有明確購(gòu)物目標(biāo)，受促銷和廣告影響少；而購(gòu)買率低的用戶可以認(rèn)為是等待型或克制型用戶群體，下單欲望較少且自制力較強(qiáng)，購(gòu)物難度較大。

3、大部分用戶的主要活躍時(shí)間在10點(diǎn)到23點(diǎn)，在19點(diǎn)到23點(diǎn)達(dá)到一天的頂峰。每周五的活躍度有所下降，但周末開始回升。可以根據(jù)用戶的活躍時(shí)間段精準(zhǔn)推送商家的折扣優(yōu)惠或促銷活動(dòng)，提高購(gòu)買率。

4、通過 R 和 F 的數(shù)據(jù)對(duì)用戶行為進(jìn)行打分，對(duì)每位用戶進(jìn)行精準(zhǔn)化營(yíng)銷，還可以通過對(duì)R 和 F 的數(shù)據(jù)監(jiān)測(cè)，推測(cè)客戶消費(fèi)的異動(dòng)狀況，挽回流失客戶。