基于阿里云天池的飞猪平台用户行为分析——MySQL

前言

本文是基于阿里云天池的飞猪平台用户行为分析，使用MySQL和Excel做数据分析，对输出结果使用Excel和PowerBI进行数据可视化。

一、数据分析步骤

明确问题、理解数据、数据清洗、数据分析、数据可视化

二、明确问题

1、数据来源

阿里云天池数据集（User Behavior Data from Fliggy Trip Platform for Recommendation）：https://tianchi.aliyun.com/dataset/113649

2、分析目标

从用户的行为数据中挖掘当前用户的行为特征，通过分析这些特征能够发现平台当前所面临的一些问题并分析原因，从而能出台一些针对性的解决措施，扩大用户的使用转化率。

3、建立指标体系

运用多维度拆解分析方法，分为三个维度（用户、商品、行为）

（1）用户维度

用户画像：年龄、性别、职业、区域分布

留存分析（用户粘性）：次日、三日、七日、十五日、三十日

用户价值分析（RFM）

（2）商品维度

商品画像：种类、地域、标签分布

热门商品分析：点击、收藏、加购、购买前十

（3）行为维度

运用AAARR模型分析

aquisition用户获取：访客量（UV）

activation用户激活：浏览量（PV）、成交量

retention用户留存：见用户维度留存分析

revenue用户收益：复购率、跳失率

refer用户推荐：各环节转化率（漏斗分析）

三、理解数据

1、数据描述

文件名	介绍	包含字段
user_item_behavior_history.csv	所有用户行为数据	用户ID，产品ID，行为类型，时间戳
user_profile.csv	所有用户基本属性画像	用户ID，年龄，性别，职业，常住城市，人群标签
item_profile.csv	产品所有基本属性	产品ID，产品类别ID，产品城市，产品标签

2、数据展示

表1：User_profile.csv

每一行代表一个用户，共500万余条数据

A字段：用户ID B字段:年龄 C字段：性别（1-未知，2-男性，3-女性） D字段：职业 E字段：常住城市 F字段：人群标签

表2：item_profile.csv

每一行代表一个商品，共27万余条数据

A：产品ID B：产品类别ID C：产品城市 D：产品标签

表3：user_item_behavior_history.csv

每一行代表一个用户数据行为，共2亿余条数据

A：用户ID B：产品ID C：行为类型（clk, fav, cart, pay） D：时间戳

四、数据清洗

1、备份表

-- 备份表
CREATE TABLE userbehavior_beifen(
User_ID VARCHAR(255),
product_ID VARCHAR(255),
behavior_type VARCHAR(255),
timestamp VARCHAR(255)
);
-- 插入数据
INSERT into userbehavior_beifen SELECT * FROM user_item_behavior_history;

2、处理重复值

-- 处理重复值
ALTER TABLE userbehavior_beifen
 ADD id int PRIMARY KEY auto_increment;
DELETE FROM userbehavior_beifen
WHERE id in (
SELECT id FROM(
SELECT *,
ROW_NUMBER() over(partition by User_ID,product_ID,behavior_type,`timestamp`) '重复值'
FROM userbehavior_beifen) temp
WHERE 重复值>1);

3、处理空值

-- 处理空值（数据比较干净，没有空值）
DELETE FROM userbehavior_beifen 
WHERE User_ID is null or product_ID is null or behavior_type is null
or `timestamp` is null;