淘寶用戶行為分析(Mysql)

數(shù)據(jù)背景

來源：阿里移動推薦算法
背景：2014年是阿里巴巴集團(tuán)移動電商業(yè)務(wù)快速發(fā)展的一年镜沽，例如2014雙11大促中移動端成交占比達(dá)到42.6%，超過240億元帝际。相比PC時代锈死，移動端網(wǎng)絡(luò)的訪問是隨時隨地的庆械，具有更豐富的場景數(shù)據(jù)，比如用戶的位置信息、用戶訪問的時間規(guī)律等碌廓。
這個比賽的目的是：要使用訓(xùn)練數(shù)據(jù)(2014年11月18日至2014年12月18日)建立推薦模型，并輸出用戶在接下來一天(2014年12月19日)對商品子集購買行為的預(yù)測結(jié)果背传。
而我的目標(biāo)則是從該數(shù)據(jù)進(jìn)行隨機(jī)抽樣呆瞻，并用mysql進(jìn)行分析，提高自己對電商指標(biāo)體系的認(rèn)識径玖。

數(shù)據(jù)詳情

tianchi_mobile_recommend_train_user命名為USER表：

user_id痴脾，用戶標(biāo)識，抽樣&字段脫敏
item_id梳星，商品標(biāo)識赞赖，字段脫敏
behavior_type顽爹，用戶對商品的行為類型界睁，包括瀏覽(1)、收藏(2)封孙、加購物車(3)韵吨、購買(4)
user_geohash, 用戶位置的空間標(biāo)識匿垄，可以為空, 由經(jīng)緯度通過保密的算法生成
item_category, 商品分類標(biāo)識, 字段脫敏
time, 行為時間, 精確到小時級別

提出問題

分析用戶使用APP過程中的常用電商指標(biāo)，了解運營現(xiàn)狀归粉，查看各個環(huán)節(jié)的流失率年堆，并找到需要改進(jìn)的環(huán)節(jié)。
研究用戶在不同維度下的行為規(guī)律盏浇，了解用戶行為特征变丧，優(yōu)化運營策略。
研究用戶的價值绢掰、針對不同價值的用戶進(jìn)行精細(xì)化運營
研究用戶生命周期痒蓬，針對不同周期的用戶采取不同的策略

電商指標(biāo)

我對電商指標(biāo)的理解，是基于六個維度的：

用戶
商品
商品類別
用戶行為
行為地址
時間

基于用戶和商品滴劲、商品類別可以分析用戶整體的購買偏好攻晒。
基于用戶和用戶行為可以分析PV、UV班挖、PV/UV鲁捏、跳失率、總訂單量萧芙、用戶行為之間的轉(zhuǎn)化率等给梅。
基于用戶和時間可以分析用戶購買的時間偏好。
基于商品類別和用戶行為可以分析不同商品類別的轉(zhuǎn)化率的差異双揪。
基于商品類別和時間可以分析不同商品類別的熱銷時間段动羽。
基于用戶行為和時間可以分析用戶的行為特征。
基于時間和別的字段渔期，結(jié)合RFM模型可以給用戶價值打標(biāo)簽运吓。
基于AARRR模型渴邦，可以分析用戶的生命周期，劃分不同用戶所處的周期階段拘哨。

數(shù)據(jù)的導(dǎo)入及清洗

將csv導(dǎo)入mysql的方法：

命令行：關(guān)于如何使用命令提示符將CSV文件導(dǎo)入MySQL
python：將大csv文件導(dǎo)入mysql數(shù)據(jù)庫
下面的實例用python抽取100000條數(shù)據(jù)谋梭，并導(dǎo)入mysql的數(shù)據(jù)庫：taobao。

from sqlalchemy import create_engine
import pandas as pd
import pymysql
import numpy as np
#讀取數(shù)據(jù)
data = pd.read_csv('user.csv',encoding = 'gbk')
data = data.sample(n=1000000)
#創(chuàng)建連接數(shù)據(jù)庫對象
engine = create_engine('mysql+pymysql://用戶名:密碼@localhost/數(shù)據(jù)庫名?charset=utf8')
#存入數(shù)據(jù)庫
data.to_sql('數(shù)據(jù)庫表名字',engine)

清除空值

數(shù)據(jù)介紹中說明只有user_geohash中存在空值(非空比例在35%左右)且經(jīng)過加密處理倦青，無法對該字段進(jìn)行分析章蚣，因此，直接刪除處理姨夹。

#查看user_geohash字段的非空比例
SELECT COUNT(u.`user_geohash`)/(SELECT COUNT(*) FROM USER) AS 'user_geohash字段的非空比例' 
FROM USER AS u
WHERE u.`user_geohash` IS NOT NULL;

#由于這列存在大量的空值且經(jīng)過加密處理，故刪除此列
ALTER TABLE USER DROP COLUMN user_geohash;

查找重復(fù)數(shù)據(jù)

這里的數(shù)據(jù)有重復(fù)值也是可以理解的矾策，因為記錄行為的最小粒度為小時磷账，同一用戶同一個行為在同一小時內(nèi)是可能存在多次的，因此這里不去重處理贾虽。

SELECT *, COUNT(*)
FROM USER AS u
GROUP BY u.`user_id`, u.`item_id`, u.`behavior_type`, u.`time`
HAVING COUNT(*) > 1;

查看數(shù)據(jù)是否存在異常

檢查數(shù)據(jù)的時間范圍和行為數(shù)據(jù)的類別是否為4個即可逃糟。

# 檢查時間是否異常
SELECT MIN(u.`time`) AS '時間起點', MAX(u.`time`) AS '時間終點'
FROM USER AS u;

# 檢查用戶行為類別是否異常
SELECT DISTINCT u.`behavior_type`
FROM USER AS u;

缺失值檢查

# 檢查缺失值
SELECT COUNT(u.`user_id`), 
       COUNT(u.`item_id`), 
       COUNT(u.`item_category`), 
       COUNT(u.`behavior_type`),
       COUNT(u.`time`)
FROM USER AS u

進(jìn)一步處理

將日期和時間進(jìn)行分離

#添加列：alter table 表名 add column 列名 varchar(30);
ALTER TABLE USER ADD COLUMN `date` VARCHAR(20) NOT NULL AFTER `time`;
#更新列：UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]
UPDATE USER
SET DATE = TIME;
#將date轉(zhuǎn)為年月日
UPDATE USER
SET DATE = DATE_FORMAT(DATE, '%Y-%m-%d');
#將time轉(zhuǎn)為小時
UPDATE USER
SET TIME = DATE_FORMAT(TIME, '%H');
#檢查一下轉(zhuǎn)化結(jié)果
SELECT *
FROM USER AS u

將用戶行為數(shù)據(jù)進(jìn)行替換： 1：pv 2：fav 3：cart 4：buy

UPDATE USER 
SET `behavior_type` = (CASE behavior_type
                 WHEN 1 THEN "pv"
                 WHEN 2 THEN "fav"
                 WHEN 3 THEN "cart"
                 WHEN 4 THEN "buy"
                 ELSE "other"
                 END);

感覺寫的沒問題但是一直報錯，查了一下stackoverflow發(fā)現(xiàn)原來py導(dǎo)入蓬豁，各字段的格式并不是我們想要的格式绰咽，需要進(jìn)行修改。注意：字段的類型一般不要修改地粪，一定要謹(jǐn)慎取募。

DESC USER;
#alter table 表名 modify column 字段名 類型;
ALTER TABLE USER MODIFY COLUMN behavior_type VARCHAR(20);

驗證結(jié)果

SELECT *
FROM USER AS u;

指標(biāo)的構(gòu)建

1. 總體運營指標(biāo)

① 流量指標(biāo)

計算頁面訪客數(shù)(pv)、獨立訪客數(shù)(uv)蟆技、人均點擊數(shù)(uv/pv)

# uv  pv pv\uv
SELECT COUNT(u.`user_id`) AS 'pv',
       COUNT(DISTINCT u.`user_id`) AS 'uv',
       COUNT(u.`user_id`)/COUNT(DISTINCT u.`user_id`) AS 'pv\uv',
       COUNT(u.`user_id`)/(COUNT(DISTINCT u.`user_id`)*30) AS '日人均點擊次數(shù)'
FROM USER AS u
WHERE u.`behavior_type` = 'pv';

頁面訪客：942253次玩敏、獨立訪客數(shù)：9922位、人均點擊次數(shù)95次质礼。
95/30≈3.2次旺聚，日人均點擊次數(shù)大概為3次/人/天

②每日流量指標(biāo)變化趨勢

# 計算每天的uv  pv pv\uv
SELECT u.date AS '日期',
       COUNT(u.`user_id`) AS 'pv',
       COUNT(DISTINCT u.`user_id`) AS 'uv',
       COUNT(u.`user_id`)/COUNT(DISTINCT u.`user_id`) AS '人均頁面訪問數(shù)'
FROM USER AS u
WHERE u.`behavior_type` = 'pv'
GROUP BY u.date
ORDER BY u.date ASC;

導(dǎo)出數(shù)據(jù)

# 后邊加
INTO OUTFILE '/daily_uvpv.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

需要更改配置什么的，一般情況下是不會存在導(dǎo)出數(shù)據(jù)的眶蕉，因此砰粹，直接用sqluog的導(dǎo)出功能了。

可視化分析趨勢

pv造挽、uv指標(biāo)呈正相關(guān)性碱璃；
三個指標(biāo)在大部分時間走勢是穩(wěn)定的，從2014-12-11開始上升饭入，到2014-12-12達(dá)到峰值厘贼，2014-12-13結(jié)束回到正常水平。
雙十二活動的影響無疑是明顯的圣拄。

AARRR漏斗轉(zhuǎn)化率

①計算每個用戶行為的pv

# 用戶行為的pv
SELECT u.`behavior_type`, COUNT(u.`behavior_type`) AS 'behavior_count'
FROM USER AS u
GROUP BY u.`behavior_type`
ORDER BY behavior_count DESC;

由于在購物環(huán)節(jié)中嘴秸，收藏和加入購物車是沒有先后之分的，這兩個環(huán)節(jié)可以放在一起，因此轉(zhuǎn)化率只有瀏覽-加入購物車/收藏岳掐、收藏-購買凭疮。

從轉(zhuǎn)化率來看：瀏覽-收藏/加入購物車的轉(zhuǎn)化率僅為5%、收藏\加入購物車-購買的轉(zhuǎn)化率也只有20%串述，也不是太高执解，說明有非常多的用戶在收藏和加入購物車之后并沒有真正的購買。
從占比來看：總體收藏的占比為2.11%纲酗，總體加入購物車的占比為2.98%衰腌，總體購買的占比僅有1%，說明有非常多的用戶在瀏覽后沒有進(jìn)行下一步操作觅赊，有非常多的無效點擊右蕊；

② 按照獨立訪客計算漏斗轉(zhuǎn)化率

# 用戶行為的uv
SELECT u.`behavior_type`, COUNT(DISTINCT u.`user_id`) AS 'behavior_count'
FROM USER AS u
GROUP BY u.`behavior_type`
ORDER BY behavior_count DESC;

用戶從瀏覽到收藏/加入購物車的轉(zhuǎn)化率很高，為94.15%吮螺，說明用戶有瀏覽后收藏/加入購物車的習(xí)慣饶囚。但是從收藏/加入購物車到購買的轉(zhuǎn)化率僅為38.45%，是用戶流失發(fā)生的主要環(huán)節(jié)鸠补。

訂單指標(biāo)

① 成交量

總體成交量與人均購買次數(shù)

# 總體成交量與人均購買次數(shù)
SELECT COUNT(u.`behavior_type`) AS 成交總量
FROM USER AS u
WHERE u.`behavior_type` = 'buy';

每日成交量與人均購買次數(shù)走勢

# 每日成交量與人均購買次數(shù)走勢
SELECT u.`date`, 
       COUNT(u.`user_id`) AS '日成交量',
       COUNT(DISTINCT u.`user_id`) AS '日用戶數(shù)',
       COUNT(u.`behavior_type`)/COUNT(DISTINCT u.`user_id`) AS '人均日購買次數(shù)'
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`date`
ORDER BY u.`date` ASC;

訂單在大部分時間是保持平穩(wěn)的萝风，而在2014-12-12這天有爆發(fā)的增長，造成這一現(xiàn)象的原因是雙十二電商大促活動紫岩，與前面的流量走勢相結(jié)合進(jìn)行分析规惰，可以得到互相印證。
而且這一個月內(nèi)泉蝌，平均一個用戶購買了2.1次卿拴，每天的訂單量和流量指標(biāo)是一致的。

② 復(fù)購率

總體的復(fù)購率

# 復(fù)購人數(shù)
SELECT COUNT(t.cnt) AS '復(fù)購人數(shù)'
FROM (SELECT u.`user_id`, COUNT(u.`user_id`) AS cnt
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`user_id`
HAVING cnt > 1) AS t;

#復(fù)購率
SELECT SUM(IF(t.cnt = 1, 0, 1))/SUM(IF(t.cnt IS NULL, 0, 1)) AS '復(fù)購率'
FROM (SELECT u.`user_id`, COUNT(u.`user_id`) AS cnt
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`user_id`) AS t;

復(fù)購人數(shù)為2268人梨与，復(fù)購率為49.51%堕花。

商品品類復(fù)購排行榜

SELECT u.`item_category`, COUNT(*) AS '購買次數(shù)'
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`item_category`
ORDER BY 購買次數(shù) DESC
LIMIT 10;

可惜這里的商品類別是經(jīng)過脫敏的，否則可以進(jìn)行研究和下鉆粥鞋，優(yōu)化商品結(jié)構(gòu)缘挽。

用戶復(fù)購排行

SELECT u.`user_id`, COUNT(*) AS '購買次數(shù)'
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`user_id`
ORDER BY 購買次數(shù) DESC
LIMIT 10;

這些用戶對于平臺的忠誠度是比較高的，對于平臺的價值也是比較高的呻粹，可以開發(fā)用戶信息庫壕曼、收集詳實的用戶資料，追蹤記錄顧客的交易情況等浊，收集用戶畫像腮郊，或者線上組織客戶VIP微信群，微信通知系統(tǒng)等筹燕，針對這些用戶的購買偏好進(jìn)行更精準(zhǔn)的運營轧飞。

2. 用戶行為特征分析

用戶行為時間的特征

① 按日期粒度：

SELECT u.`date`,
       COUNT(*) AS '行為總數(shù)',
       SUM(IF(u.`behavior_type` = 'pv', 1, 0)) AS '點擊次數(shù)',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '收藏次數(shù)',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '加入購物車次數(shù)',
       SUM(IF(u.`behavior_type` = 'buy', 1, 0)) AS '購買次數(shù)',
       COUNT(DISTINCT u.`user_id`) AS '用戶總數(shù)',
       COUNT(*)/COUNT(DISTINCT u.`user_id`) AS '人均行為次數(shù)',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'pv', 1, 0))/COUNT(*)*100, 2), '%')  AS '點擊數(shù)占比',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'buy', 1, 0))/COUNT(*)*100, 2), '%')  AS '成交數(shù)占比'
FROM USER AS u
GROUP BY u.`date`
ORDER BY u.`date` ASC;

按如期粒度來看衅鹿，用戶或活躍度與總體行為數(shù)是正相關(guān)的，走勢平穩(wěn)过咬，雙十二電商大促這天各項指標(biāo)暴增大渤，且當(dāng)天點擊數(shù)占比有所下降(用戶的點擊更有針對性), 成交數(shù)占比大幅上升。

② 按周的粒度
查詢數(shù)據(jù)發(fā)現(xiàn)2014-11-18到2014-11-23,2014-12-15到2014-12-18均不滿一周掸绞，因此泵三，只取完整的三周進(jìn)行分析：

SELECT DATE_FORMAT(u.`date`, '%W') AS '星期',
       COUNT(*) AS '行為總數(shù)',
       SUM(IF(u.`behavior_type` = 'pv', 1, 0)) AS '點擊次數(shù)',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '收藏次數(shù)',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '加入購物車次數(shù)',
       SUM(IF(u.`behavior_type` = 'buy', 1, 0)) AS '購買次數(shù)',
       COUNT(DISTINCT u.`user_id`) AS '用戶總數(shù)',
       COUNT(*)/COUNT(DISTINCT u.`user_id`) AS '人均行為次數(shù)',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'pv', 1, 0))/COUNT(*)*100, 2), '%')  AS '點擊數(shù)占比',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'buy', 1, 0))/COUNT(*)*100, 2), '%')  AS '成交數(shù)占比'
FROM USER AS u
WHERE u.date BETWEEN '2014-11-24' AND '2014-12-14'
GROUP BY DATE_FORMAT(u.`date`, '%W')
ORDER BY DATE_FORMAT(u.`date`, '%W') ASC;

一周中的大部分時間用戶活躍度都比較平穩(wěn)，周五比較特殊衔掸，出現(xiàn)了增長烫幕。查看數(shù)據(jù)發(fā)現(xiàn)雙十二正好是周五，因此是可以理解的敞映。

③ 小時粒度

SELECT u.`time`,
       COUNT(*) AS '行為總數(shù)',
       SUM(IF(u.`behavior_type` = 'pv', 1, 0)) AS '點擊次數(shù)',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '收藏次數(shù)',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '加入購物車次數(shù)',
       SUM(IF(u.`behavior_type` = 'buy', 1, 0)) AS '購買次數(shù)',
       COUNT(DISTINCT u.`user_id`) AS '用戶總數(shù)',
       COUNT(*)/COUNT(DISTINCT u.`user_id`) AS '人均行為次數(shù)',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'pv', 1, 0))/COUNT(*)*100, 2), '%')  AS '點擊數(shù)占比',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'buy', 1, 0))/COUNT(*)*100, 2), '%')  AS '成交數(shù)占比'
FROM USER AS u
GROUP BY u.`time`
ORDER BY u.`time` ASC;

各項指標(biāo)呈正相關(guān)關(guān)系较曼，每天0-5點用戶的活躍率快速降低，講到一天活躍量的最低值驱显，6-10點用戶活躍度快速上升，10-18點用戶活躍度較為平穩(wěn)瞳抓，18-23點用戶活躍度快速上升埃疫，達(dá)到一天的峰值。

結(jié)論：晚間用戶最為活躍孩哑，但用戶行為傾向于瀏覽栓霜；白天時段，用戶的購買比率為一天內(nèi)最高的横蜒，此時購買的目的性最強(qiáng)胳蛮。

用戶商品偏好特征

SELECT u.`item_category`,
       SUM(IF(u.`behavior_type` = 'pv', 1, 0)) AS '點擊次數(shù)',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '收藏次數(shù)',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '加入購物車次數(shù)',
       SUM(IF(u.`behavior_type` = 'buy', 1, 0)) AS '購買次數(shù)',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'pv', 1, 0))/COUNT(u.`behavior_type`)*100, 2), '%')  AS '點擊數(shù)占比',
       CONCAT(ROUND(SUM(IF(u.`behavior_type` = 'buy', 1, 0))/COUNT(u.`behavior_type`)*100, 2), '%')  AS '成交數(shù)占比'
FROM USER AS u
GROUP BY u.`item_category`
ORDER BY 點擊次數(shù) DESC;

可以建立點擊數(shù)-購買數(shù)的二維圖像選擇合適的中心點，將產(chǎn)品分為四個類別：

點擊數(shù)高丛晌，購買數(shù)高仅炊。說明此類產(chǎn)品剛需比較強(qiáng)，品牌多且種類豐富澎蛛，用戶在較高的需求下有很多的選擇抚垄；
點擊數(shù)低購買數(shù)高。用戶的購買決策十分果斷谋逻，且對于該類產(chǎn)品的需求量也是很大的呆馁，說明該類產(chǎn)品選擇性比較小，可能形成幾個品牌壟斷的情況毁兆，或者產(chǎn)品的差異性較小浙滤，用戶不愿花費過多的精力去挑選。
點擊數(shù)低購買數(shù)低气堕，絕大多數(shù)產(chǎn)品都集中在這個象限纺腊，這種產(chǎn)品存在很多的替代品畔咧，用戶很難集中在某個子類進(jìn)行大量購買，而是跳躍式選購摹菠。
點擊數(shù)高購買數(shù)低盒卸，這類產(chǎn)品的需求彈性較大，用戶購買存在隨機(jī)性次氨。

用戶行為路徑上的特征
用戶購買商品分為以下幾類過程：

瀏覽后購買
瀏覽后加入購物車購買
瀏覽后收藏購買
瀏覽后收藏并加入購物車購買

CREATE VIEW 用戶行為 AS
SELECT u.`user_id`,
       SUM(IF(u.`behavior_type` = 'pv', 1, 0)) AS '點擊次數(shù)',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '收藏次數(shù)',
       SUM(IF(u.`behavior_type` = 'fav', 1, 0)) AS '加入購物車次數(shù)',
       SUM(IF(u.`behavior_type` = 'buy', 1, 0)) AS '購買次數(shù)'
FROM USER AS u
GROUP BY u.`user_id`,
     u.`item_id`,
     u.`date`;

CREATE VIEW 標(biāo)準(zhǔn)化指標(biāo)表 AS
SELECT uh.`user_id`,
       CONCAT(IF(uh.`點擊次數(shù)`>=1,1,0),
       IF(uh.`收藏次數(shù)`>=1,1,0), 
       IF(uh.`加入購物車次數(shù)`>=1,1,0),
       IF(uh.`購買次數(shù)`>=1,1,0)) AS '行為路徑'
FROM 用戶行為 AS uh
WHERE uh.`購買次數(shù)` >= 1 AND uh.`點擊次數(shù)` <> 0;

SELECT a.`行為路徑`,
       COUNT(DISTINCT a.`user_id`) AS '用戶數(shù)'
FROM 標(biāo)準(zhǔn)化指標(biāo)表 AS a
GROUP BY a.`行為路徑`

由于數(shù)據(jù)是抽取的蔽介，這個所得的結(jié)果誤差較大。但這個結(jié)果顯示煮寡，直接夠買的用戶遠(yuǎn)遠(yuǎn)多于瀏覽后加購或者收藏再購買的用戶虹蓄。說明，大部分購買者都是喜歡直接購買商品的幸撕，而非仔細(xì)挑選商品薇组。

用戶復(fù)購率特征

#先計算每個用戶的購買次數(shù)
#然后對購買次數(shù)進(jìn)行統(tǒng)計
SELECT tmp.購買次數(shù), COUNT(tmp.購買次數(shù)) AS '用戶數(shù)'
FROM (SELECT u.`user_id`, COUNT(u.`user_id`) AS '購買次數(shù)'
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`user_id`
ORDER BY 購買次數(shù) DESC) AS tmp
GROUP BY tmp.購買次數(shù)
ORDER BY tmp.購買次數(shù) ASC

這一個月內(nèi)，用戶的購買次數(shù)大部分集中在5次以內(nèi)坐儿，開發(fā)空間較大律胀。

3.基于RFM模型分析用戶的價值

根據(jù)數(shù)據(jù)的情況，這里只能計算R和F
R(Recently): 最近一天的購買時間差(以2014-12-18為基準(zhǔn))
F(Frequency): 近期的購買頻率

DROP VIEW IF EXISTS RF的統(tǒng)計視圖;
# 創(chuàng)建RF的統(tǒng)計視圖
CREATE VIEW RF的統(tǒng)計視圖 AS 
SELECT u.`user_id`,
       DATEDIFF('2014-12-18', MAX(u.`date`)) AS R,
       COUNT(u.`user_id`) AS F
FROM USER AS u
WHERE u.`behavior_type` = 'buy'
GROUP BY u.`user_id`;

SELECT *
FROM RF的統(tǒng)計視圖

根據(jù)直方圖的情況：
將R/F分為四組：

0-5貌矿、5-12炭菌、12-23,、23-30分別對應(yīng)4逛漫、3黑低、2、1分
F值1-3酌毡、3-5克握、5-7、7以上分別對應(yīng)1枷踏、2菩暗、3、4分

DROP VIEW IF EXISTS 用戶價值打分;
CREATE VIEW 用戶價值打分 AS 
SELECT rf.`user_id`,
       (CASE 
       WHEN rf.R BETWEEN 0 AND 5 THEN 4
       WHEN rf.R BETWEEN 5 AND 12 THEN 3
       WHEN rf.R BETWEEN 12 AND 23 THEN 2
       WHEN rf.R BETWEEN 23 AND 30 THEN 1
       END) AS R_score,
       (CASE 
       WHEN rf.F BETWEEN 1 AND 3 THEN 1
       WHEN rf.F BETWEEN 3 AND 5 THEN 2
       WHEN rf.F BETWEEN 5 AND 7 THEN 3
       WHEN rf.F BETWEEN 7 AND 81 THEN 4
       END) AS F_score
FROM RF的統(tǒng)計視圖 AS rf;

SELECT *
FROM 用戶價值打分;

計算R旭蠕、F的平均值勋眯，確定評分標(biāo)準(zhǔn)

給用戶貼上價值標(biāo)簽
重要價值客戶：R、F得分都高
重要保持客戶：R得分比較高下梢，F(xiàn)得分比較低
重要發(fā)展客戶：R得分比較低客蹋，F(xiàn)得分比較高
一般價值客戶：R、F得分都比較低

DROP VIEW IF EXISTS 用戶標(biāo)簽表;
CREATE VIEW 用戶標(biāo)簽表 AS 
SELECT s.`user_id`,
       (CASE 
       WHEN s.R_score >= 2.78 AND s.F_score >= 1.21 THEN '重要價值客戶'
       WHEN s.R_score >= 2.78 AND s.F_score <= 1.21 THEN '重要保持客戶'
       WHEN s.R_score <= 2.78 AND s.F_score >= 1.21 THEN '重要發(fā)展客戶'
       WHEN s.R_score <= 2.78 AND s.F_score <= 1.21 THEN '一般價值客戶'
       END) AS 客戶類型
FROM 用戶價值打分 AS s;

SELECT *
FROM 用戶標(biāo)簽表;

SELECT l.`客戶類型`, COUNT(l.`客戶類型`) AS 'cnt'
FROM 用戶標(biāo)簽表 AS l
GROUP BY l.`客戶類型`;

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末孽江，一起剝皮案震驚了整個濱河市讶坯，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌岗屏，老刑警劉巖辆琅，帶你破解...
沈念sama閱讀 206,126評論 6贊 481
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件漱办，死亡現(xiàn)場離奇詭異，居然都是意外死亡婉烟，警方通過查閱死者的電腦和手機(jī)娩井，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 88,254評論 2贊 382
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來似袁，“玉大人洞辣，你說我怎么就攤上這事￡夹疲” “怎么了扬霜？”我有些...
開封第一講書人閱讀 152,445評論 0贊 341
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長而涉。經(jīng)常有香客問我著瓶，道長，這世上最難降的妖魔是什么啼县？我笑而不...
開封第一講書人閱讀 55,185評論 1贊 278
?港島之戀（遺憾婚禮）
正文為了忘掉前任材原，我火速辦了婚禮，結(jié)果婚禮上季眷，老公的妹妹穿的比我還像新娘余蟹。我一直安慰自己，他們只是感情好瘟裸，可當(dāng)我...
茶點故事閱讀 64,178評論 5贊 371
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布客叉。她就那樣靜靜地躺著诵竭，像睡著了一般话告。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上卵慰，一...
開封第一講書人閱讀 48,970評論 1贊 284
城市分裂傳說
那天沙郭，我揣著相機(jī)與錄音，去河邊找鬼裳朋。笑死病线，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的鲤嫡。我是一名探鬼主播送挑，決...
沈念sama閱讀 38,276評論 3贊 399
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼暖眼！你這毒婦竟也來了惕耕？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 36,927評論 0贊 259
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤诫肠，失蹤者是張志新（化名）和其女友劉穎司澎，沒想到半個月后欺缘，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 43,400評論 1贊 300
?護(hù)林員之死
正文獨居荒郊野嶺守林人離奇死亡挤安，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 35,883評論 2贊 323
?白月光啟示錄
正文我和宋清朗相戀三年谚殊，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片蛤铜。...
茶點故事閱讀 37,997評論 1贊 333
活死人
序言：一個原本活蹦亂跳的男人離奇死亡嫩絮，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出昂羡，到底是詐尸還是另有隱情絮记，我是刑警寧澤，帶...
沈念sama閱讀 33,646評論 4贊 322
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布虐先，位于F島的核電站怨愤，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏蛹批。R本人自食惡果不足惜撰洗，卻給世界環(huán)境...
茶點故事閱讀 39,213評論 3贊 307
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望腐芍。院中可真熱鬧差导，春花似錦、人聲如沸猪勇。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,204評論 0贊 19
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽泣刹。三九已至助析，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間椅您，已是汗流浹背外冀。一陣腳步聲響...
開封第一講書人閱讀 31,423評論 1贊 260
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機(jī)就差點兒被人妖公主榨干…… 1. 我叫王不留掀泳，地道東北人雪隧。一個月前我還...
沈念sama閱讀 45,423評論 2贊 352
代替公主和親
正文我出身青樓，卻偏偏與公主長得像员舵，于是被迫代替她去往敵國和親脑沿。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點故事閱讀 42,722評論 2贊 345