文章目錄

一、分析目标
二、資料說明
三、實作步驟
- 1.建立使用者名額體系
- 2.建立分階段使用者标簽
- - 2.1 按使用者行為統計每日的不同階段使用者規模
  - 2.2 按周為機關分析最近兩周的不同階段使用者的變化
  - 2.3 對比分析新使用者群體與激活使用者群體的人群畫像、行為資料特點
- 3、使用者複購分析
- - 3.1 計算2月1日至4月30日使用者的複購情況
  - 3.2 對比複購使用者和非複購使用者的使用者畫像、行為特點
  - 3.3 結合最後一周的分階段使用者标簽，分析近期複購使用者的使用者激活、使用者流失的情況
- 4、報告

一、分析目标

1.搭建日常營運名額體系；

2.分析現有使用者構成，統計每日使用者構成情況；

3.分析使用者複購情況，為後續複購營運做指導。

二、資料說明

User_info 使用者資訊表：

Hadoop環境中使用Hive工具實作電商網站使用者營運分析一、分析目标二、資料說明三、實作步驟

user_action 使用者行為表：

Hadoop環境中使用Hive工具實作電商網站使用者營運分析一、分析目标二、資料說明三、實作步驟

三、實作步驟

首先需要搭建營運名額體系，讓營運人員能夠看到使用者規模以及規模的變動狀态；然後我們需要按照使用者階段（新使用者、激活使用者、睡眠使用者、流失使用者）将使用者區分出來，統計每天不同階段的使用者數量，讓營運能夠了解不同階段使用者規模，設計更加貼切的使用者營運方案。

建表：

create table user_info
(user_id string,
age_between string,
sex int,
user_level int,
reg_time date
) 
row format delimited fields terminated by '\t'
tblproperties("skip.header.line.count"="1"); 

load data local inpath '/home/hadoop/datas/HomeWork/user_info_sample.txt' into table
user_info;

ALTER TABLE user_info SET SERDEPROPERTIES ('serialization.encoding'='GBK');

create table User_action
(user_id string,
sku_id string,
action_time Timestamp,
model_id int,
type int,
cate int,
brand int
) 
row format delimited fields terminated by '\t'
tblproperties("skip.header.line.count"="1"); --轉載時跳過第一行
--裝載資料
load data local inpath '/home/hadoop/datas/HomeWork/user_action_sample.txt' into
table user_action;

1.建立使用者名額體系

我們需要根據使用者活躍名額來逐漸建立使用者名額體系：

每日活躍使用者數：DAU，即每日活躍使用者的排重統計量；

select
    date(action_time) action_time,
    count(distinct user_id) DAU
from
    user_action
group by
    date(action_time);

每日新注冊使用者數：DNU，即每日新注冊使用者的排重統計量；

select
    reg_time,
    count(distinct user_id)
from user_info
group by reg_time;

每日消費轉化率：即每日所有使用者的有下單行為的使用者數與每日活躍使用者數的百分比；

select
    date(action_time) action_time,
    concat(round(count(distinct if(type=4,user_id,Null))/count(distinct user_id)*100,2),"%") as per_con_rate
from user_action
group by date(action_time)
order by date(action_time);

每日新使用者消費轉化率：即每日新注冊使用者中，有下單行為的使用者數與每日新注冊使用者數的百分比；

select
    reg_time,
    concat(round(count(distinct if(type=4,user_info.user_id,Null))/count(distinct user_info.user_id)*100,2),"%") as new_per_con_rate
from user_info
left join user_action on user_info.reg_time=date(user_action.action_time)
and user_info.user_id=user_action.user_id
group by reg_time
order by reg_time;

分析近期各個名額的變動特點：

select action_time,
  concat(round((DAU - DAU_1)/DAU_1 *100,2),"%") as DAU_DOD,
  concat(round((DNU - DNU_1)/DNU_1 *100,2),"%") as DNU_DOD,
  concat(round((per_con_rate - per_con_rate_1) / per_con_rate_1 *100,2),"%") as per_con_rate_DOD,
  concat(round((new_per_con_rate - new_per_con_rate_1) / new_per_con_rate_1 *100,2),"%") as new_per_con_rate_DOD,
  concat(round((DAU - DAU_7)/DAU_7 *100,2),"%") as DAU_WOW,
  concat(round((DNU - DNU_7)/DNU_7 *100,2),"%") as DNU_WOW,
  concat(round((per_con_rate - per_con_rate_7) / per_con_rate_7 *100,2),"%") as per_con_rate_WOW,
  concat(round((new_per_con_rate - new_per_con_rate_7) / new_per_con_rate_7 *100,2),"%") as new_per_con_rate_WOW
from(
  select *,
    lag(DAU,1) over(order by action_time) as DAU_1,
    lag(DNU,1) over(order by action_time) as DNU_1,
    lag(per_con_rate,1) over(order by action_time) as per_con_rate_1,
    lag(new_per_con_rate,1) over(order by action_time) as new_per_con_rate_1,
    lag(DAU,7) over(order by action_time) as DAU_7,
    lag(DNU,7) over(order by action_time) as DNU_7,
    lag(per_con_rate,7) over(order by action_time) as per_con_rate_7,
    lag(new_per_con_rate,7) over(order by action_time) as new_per_con_rate_7
  from(
        select date(action_time) action_time,
             count(distinct user_id) DAU,
             concat(round(count(distinct if(type=4,user_id,Null))/count(distinct user_id)*100,2),"%") as per_con_rate
        from user_action
        group by date(action_time) ) as table_a
   left join(
        select reg_time,
              count(distinct user_info.user_id) as DNU,
              concat(round(count(distinct if(type=4,user_info.user_id,Null))/count(distinct user_info.user_id)*100,2),"%") as new_per_con_rate
        from  user_info
        left join user_action 
        on user_info.user_id=user_action.user_id
        and reg_time=date(action_time)
        group by reg_time) as table_b on table_a.action_time=table_b.reg_time) as table_c
order by action_time;

2.建立分階段使用者标簽

将注冊當天的使用者作為新使用者

riqi=reg_time

将注冊未滿一周的使用者作為未激活使用者（為了避免新使用者成為跳出使用者）

(max_time-reg_time<7 or max_time is Null) and riqi-reg_time<14

注冊後的第8天，使用者進入激活期，激活期為注冊後的第8到第14天，在激活期回訪的使用者，則成為激活使用者。（為了讓未激活使用者成為激活使用者）

max_time-reg_time>7 and riqi-max_time<7

若使用者成為激活使用者後，有一周沒有活躍；或從注冊後在激活期（第8到第14天）未激活且第14天之後也未活躍的使用者作為睡眠使用者。（主要做喚醒）

(max_time-reg_time>7 and riqi-max-time>7 and riqi-max_time<=21)
or(riqi-reg_time>14 and riqi-reg_time<=28 and (max_time-reg_time<7 or max_time is Null))

将成為睡眠使用者後，有兩周沒有活躍的使用者作為流失使用者。（雖然同為喚醒範圍，但是流失使用者的喚醒難度要大于睡眠使用者，在預算較少的喚醒活動中不予考慮。）

(max_time-reg_time>7 and riqi-max_time>21) or
(riqi-reg_time>28 and (max_time-reg_time<7 or max_time is Null))

之是以我們需要統計不同階段的使用者，是因為我們僅從DAU、DNU或MAU資料都很難去觀察我們使用者池的使用者構成如何，使用者是沉澱下來了逐漸變多？還是在逐漸減少？這些是需要我們通過将不同階段使用者細拆之後才能統計出來的。

2.1 按使用者行為統計每日的不同階段使用者規模

當新增使用者、未激活使用者、激活使用者增加，且睡眠使用者、流失使用者減少，那我們的使用者規模就在呈上升趨勢；反之，則呈下降趨勢。分析近期我們使用者規模的變動趨勢，判斷我們的使用者池的健康程度。

-- 準備統計日期資料（每日都要統計，故将使用者行為表中的時間單獨拉出作為統計時間）
create table riqi as
select date(action_time) riqi from user_action group by date(action_time);

-- 準備最後一次使用者行為時間（由于統計日統計的是上一次的行為時間，故限制統計日期上一次行為時間作為最後一次行為時間。riqi表是單獨拉出的連續時間，并非使用者最後一次行為時間，riqi表時間與使用者行為時間無關） 
create table base_info as
select 
    riqi,
    user_id,
    reg_time,
    (select max(action_time) max_time from user_action where date(action_time)<riqi.riqi and user_id=user_info.user_id) as max_time
from riqi,user_info;

select riqi,
       user_type,
       count(*)
from(
    select *,
          case when riqi=reg_time then "new_user"
          when ( day(max_time-reg_time)<7 or max_time is Null) and day(riqi-reg_time)<14 then "inaction_user"
          when day(max_time-reg_time)>7 and day(riqi-max_time)<7 then "action_user"
          when (day(max_time-reg_time)>7 and day(riqi-max_time)>7 and day(riqi-max_time)<=21) or (day(riqi-reg_time)>14 and day(riqi-reg_time)<=28 and (day(max_time-reg_time)<7 or max_time is Null)) then "sleep_user"
          when (day(max_time-reg_time)>7 and day(riqi-max_time)>21) or (day(riqi-reg_time)>28 and (day(max_time-reg_time)<7 or max_time is Null)) then "lose_user"
          end as user_type
     from base_info
) as a
group by riqi,user_type;

2.2 按周為機關分析最近兩周的不同階段使用者的變化

使用者量變化、人均浏覽數變化、人均點選數變化、人均加購物車數變化、人均下單數變化、購買轉化率變化。

select if(date(action_time)>"2016-04-08","week_1","week_2") as week,
       count(distinct user_id) DAU_week,
       round(count(if(type=1,user_id,Null))/count(distinct user_id),2) avg_user1,
       round(count(if(type=6,user_id,Null))/count(distinct user_id),2) avg_user6,
       round(count(if(type=2,user_id,Null))/count(distinct user_id),2) avg_user2,
       round(count(if(type=4,user_id,Null))/count(distinct user_id),2) avg_user4,
       concat(round(count(distinct if(type=4,user_id,Null))/count(distinct user_id)*100,2),"%") buy_rate
from(
select * from user_action where date(action_time)>"2016-04-01"
) as a
group by date(action_time)>"2016-04-08";

2.3 對比分析新使用者群體與激活使用者群體的人群畫像、行為資料特點

簡述近期産品引入的新使用者群體是否健康。

-- 新使用者群體
select age_between,sex,
  count(distinct a.user_id) count_user,
  round(count(if(type=1,a.user_id,Null))/count(distinct a.user_id),2) avg_user1,
  round(count(if(type=6,a.user_id,Null))/count(distinct a.user_id),2) avg_user6,
  round(count(if(type=2,a.user_id,Null))/count(distinct a.user_id),2) avg_user2,
  round(count(if(type=4,a.user_id,Null))/count(distinct a.user_id),2) avg_user4,
  concat(round(count(distinct if(type=4,a.user_id,Null))/count(distinct a.user_id)*100,2),"%") buy_rate 
from
(select * from user_info where reg_time>"2016-04-08" and reg_time<="2016-04-15") as a
left join 
(select * from user_action where date(action_time)>"2016-04-08") as b
on a.user_id=b.user_id
group by age_between,sex
grouping sets(age_between,sex);

-- 激活使用者群體
select age_between,sex,
  count(distinct a.user_id) count_user,
  round(count(if(type=1,a.user_id,Null))/count(distinct a.user_id),2) avg_user1,
  round(count(if(type=6,a.user_id,Null))/count(distinct a.user_id),2) avg_user6,
  round(count(if(type=2,a.user_id,Null))/count(distinct a.user_id),2) avg_user2,
  round(count(if(type=4,a.user_id,Null))/count(distinct a.user_id),2) avg_user4,
  concat(round(count(distinct if(type=4,a.user_id,Null))/count(distinct a.user_id)*100,2),"%") buy_rate 
from
(select * from user_info where reg_time<="2016-04-08") as a
inner join 
(select * from user_action where date(action_time)>"2016-04-08") as b
on a.user_id=b.user_id
group by age_between,sex
grouping sets(age_between,sex);

3、使用者複購分析

對于使用者營運來說，由于獲客成本居高不下，提升使用者複購是将使用者價值最大化的關鍵。

那到底什麼時候該關注複購，凱文·希爾斯特羅姆在《精益資料分析》一書中給了參考：

90天内重複購買率達到1%~15%；說明你處于使用者擷取模式；把更多的精力和資源投入到新使用者擷取和轉化；
90天内重複購買率達到15~30%；說明你處于混合模式；平衡用在新使用者轉化和老使用者留存、複購上的精力和資源；
90天内重複購買率達到30%以上；說明你處于忠誠度模式；把更多的精力和資源投入到使用者複購上；

3.1 計算2月1日至4月30日使用者的複購情況

使用者複購率 = 複購使用者數 / 活躍使用者數；
複購使用者數：一段時間内購買次數達兩次及以上的使用者數

經過統計，使用者的複購率為8.87%，說明産品處于使用者擷取模式。

select 
count(if(buy_count>=2,user_id,Null)) re_buy,
count(distinct user_id) AU,
concat(round(count(if(buy_count>=2,user_id,Null))/count(distinct user_id)*100,2),"%") as re_buy_rate
from 
(select user_id,
     count(if(type=4,user_id,Null)) buy_count
from user_action
where date(action_time)>="2016-02-01"
group by user_id) as a;

3.2 對比複購使用者和非複購使用者的使用者畫像、行為特點

結合使用者資訊表和使用者購買行為表，可以看出複購使用者的特點：複購使用者26-35歲的男性居多。

-- 複購使用者
select age_between,sex,
count(if(buy_count>=2,a.user_id,Null)) re_buy,
count(distinct a.user_id) AU,
concat(round(count(if(buy_count>=2,a.user_id,Null))/count(distinct a.user_id)*100,2),"%") as re_buy_rate
from 
(select user_id,count(if(type=4,user_id,Null)) buy_count from user_action
group by user_id) as a
left join
(select user_id,age_between,sex from user_info) as b
on a.user_id=b.user_id
group by age_between,sex
grouping sets(age_between,sex);

-- 非複購使用者
select age_between,sex,
count(if(buy_count<2,a.user_id,Null)) not_re_buy,
count(distinct a.user_id) AU,
concat(round(count(if(buy_count<2,a.user_id,Null))/count(distinct a.user_id)*100,2),"%") as not_re_buy_rate
from 
(select user_id,count(if(type=4,user_id,Null)) buy_count from user_action
group by user_id) as a
left join
(select user_id,age_between,sex from user_info) as b
on a.user_id=b.user_id
group by age_between,sex
grouping sets(age_between,sex);

3.3 結合最後一周的分階段使用者标簽，分析近期複購使用者的使用者激活、使用者流失的情況

select user_type,count(distinct user_id) user_num
from(
select distinct a.user_id as user_id,
          case when reg_time="2016-04-15" then "new_user"
          when (day(max_time-reg_time)<=7 or max_time is Null)and reg_time>"2016-04-01" then "inaction_user"
          when day(max_time-reg_time)>7 and max_time>"2016-04-08" then "action_user"
          when (day(max_time-reg_time)>7 and max_time<"2016-04-08" and max_time>="2016-03-25") or (reg_time<"2016-04-01" and reg_time>="2016-03-18" and (day(max_time-reg_time)<7 or max_time is Null)) then "sleep_user"
          when (day(max_time-reg_time)>7 and max_time<"2016-03-25") or (reg_time<"2016-03-18" and (day(max_time-reg_time)<7 or max_time is Null)) then "lose_user"
          end as user_type
from
(select a1.user_id user_id,a1.action_time action_time,max(a1.action_time)over(partition by a1.user_id) max_time
from(
    select user_id,date(action_time) action_time,count(user_id)over(partition by user_id) buy_count
    from user_action where type=4) as a1 
    where buy_count>=2)as a
left join
(select user_id,reg_time from user_info) as b
on a.user_id=b.user_id
) as c
group by user_type;

4、報告

https://gitee.com/Ariel999/data-analysis-case/blob/master/Hive%E5%AD%A6%E4%B9%A0/%E7%94%A8%E6%88%B7%E8%BF%90%E8%90%A5%E5%89%8D%E6%9C%9F%E5%88%86%E6%9E%90%E6%8A%A5%E5%91%8A-%E5%BC%A0%E8%8D%A3%E8%8D%A3.pptx

Hadoop環境中使用Hive工具實作電商網站使用者營運分析一、分析目标二、資料說明三、實作步驟

文章目錄

一、分析目标

二、資料說明

三、實作步驟

1.建立使用者名額體系

2.建立分階段使用者标簽

2.1 按使用者行為統計每日的不同階段使用者規模

2.2 按周為機關分析最近兩周的不同階段使用者的變化

2.3 對比分析新使用者群體與激活使用者群體的人群畫像、行為資料特點

3、使用者複購分析

3.1 計算2月1日至4月30日使用者的複購情況

3.2 對比複購使用者和非複購使用者的使用者畫像、行為特點

3.3 結合最後一周的分階段使用者标簽，分析近期複購使用者的使用者激活、使用者流失的情況

4、報告

繼續閱讀

【分類算法】什麼是分類算法定義分類與聚類分類過程方法

申請評分模型拒絕推斷（RI）方法申請評分模型拒絕推斷（RI）方法

Sql優化一：sql語句優化

Nacos 2.0 更新前後性能對比壓測

hadoop 用MR實作join操作

Centos7 下 Hadoop 2.6.4 分布式叢集環境搭建摘要叢集準備安裝JDK 安裝 Hadoop 2.6.4 部署 slaver1-slaver4 啟動 hadoop 叢集成功了

尚矽谷—韓順平—圖解 Java設計模式（結構型）（55～）

Storm編譯打包過程中遇到的一些問題及解決方法

MapReduce的幾個企業級經典面試案例MapReduce的幾個企業級經典面試案例

9.spark Core 進階2--Cashe

淺談企業活動中進行資料分析的重要性

ubuntu14.04下安裝hbse1.0.1.1

User Defined Hadoop DataType

Ambari介紹和架構原理

NOSQL安全攻擊

win10本地scala和spark安裝安裝scala安裝spark