文章目录
- 1. 项目准备
- 1.1 需求分析
- 1.2 数据结构
- 1.2.1 订单表
- 1.2.2 访客表
- 1.2.3 商品类目表
- 1.2.4 退款表
- 1.2.5 物流表
- 1.2.6 用户表
- 1.3 准备数据文件
- 1.3.1 上传数据文件到虚拟机
- 1.3.2 在HDFS上创建数据目录
- 1.3.3 上传数据文件到HDFS目录
- 1.4 创建数据库
- 1.5 创建数据表
- 1.5.1 创建订单表
- 1.5.2 创建访客表
- 1.5.3 创建商品类目表
- 1.5.4 创建退款表
- 1.5.5 创建物流表
- 1.5.6 创建用户表
- 2. 项目实现
- 2.1 总体运营指标
- 2.1.1 独立访客数(UV)
- 2.1.2 页面访问数(PV)
- 2.1.3 人均页面访问数(PPV)
- 2.1.4 总订单数量
- 2.1.5 成交金额
- 2.1.6 客单价=销售额/购买人数
- 2.1.7 销售毛利（毛利最高的时间商品）
- 2.1.8 毛利率
- 2.2 流量类指标
- 2.3 网站销售类指标
- 2.4 商品类指标
- 2.5 营销类指标
- 2.6 物流类指标
- 3. 数据展示
- 3.1 可视化软件简介
- 3.2 可视化软件安装
- 3.2.1 下载Tableau可视化软件
- 3.2.2 安装Tableau可视化软件
- 3.3 数据展示
- 3.3.1 导入数据
- 3.3.2 独立访客数
- 3.3.3 页面访问数
- 3.3.4 人均页面访问数
- 3.3.5 总订单数量
- 3.3.6 跳出率
- 3.3.7 页面访问时长
- 3.3.8 下单—支付金额转化率
- 3.3.9 下单—支付买家数转化率
- 3.3.10 退款金额
- 3.3.11 出版社图书前10名
- 3.3.12 出版社销量排行
- 3.3.13 平均发货时间
1. 项目准备
1.1 需求分析
- 数据采集和整合：收集电商平台的交易日志，将其存储到合适的数据库或数据仓库中，并保证数据的准确性、完整性和实时性。
- 数据清洗和预处理：对采集到的数据进行清洗、转换和预处理，以确保数据的质量。
- 用户行为分析：通过分析交易日志中的用户行为数据，如点击、浏览、购买等，识别用户偏好、购买习惯和潜在的流失因素等。
- 销售趋势分析：通过分析销售数据，如销售额、销售渠道等，发现热门商品及其销售趋势，优化库存管理和采购计划。
- 交易异常监测：通过分析日志数据，检测异常订单、欺诈行为和支付问题等，提高系统的安全性。
- 数据可视化和报告：将分析结果以直观的图表、可视化仪表板和报告的形式展示，帮助决策者更好地理解数据，并做出相应的业务决策。
1.2 数据结构
1.2.1 订单表
| 字段解释 | 字段名 | 数据类型 |
|---|---|---|
| 订单 ID | order_id | string |
| 订单时间 | order_time | bigint |
| 买家 ID | user_id | bigint |
| 商品 ID | goods_id | bigint |
| 买家 IP | user_ip | string |
| 收货地址 | ship_add (shipping address) | string |
| 手机号 | phone_no | string |
| 点击来源 | click_source | string |
| 单点费用 | click_cost | float |
| 订单完成时间 | pay_ct (payment completion time) | bigint |
| 订单状态 | pay_status (payment status) | string |
1.2.2 访客表
| 字段解释 | 字段名 | 数据类型 |
|---|---|---|
| 访问 IP | access_ip | string |
| 访问时间 | access_time | bigint |
| 网页跳转时间 | jump_time | bigint |
| 用户 ID | user_id | bigint |
| 手机号 | phone_no | string |
| 商品 ID | goods_id | bigint |
| 访问链接 | access_con (access connection) | string |
| 访问事件 | access_event | string |
| 点击来源 | click_source | string |
| 单点费用 | click_cost | float |
1.2.3 商品类目表
| 字段解释 | 字段名 | 数据类型 |
|---|---|---|
| 商品 ID | goods_id | bigint |
| 商品名称 | goods_name | string |
| 作者 | author | string |
| 出版社 | press | string |
| 出版时间 | pub_time (published time) | bigint |
| ISBN 编号 | ISBN | float |
| 定价 | pricing | float |
| 商品售价 | goods_price | bigint |
| 月销量 | mon_sales (month sales) | string |
| 发货地址 | del_add (delivery address) | string |
1.2.4 退款表
| 字段解释 | 字段名 | 数据类型 |
|---|---|---|
| 订单 ID | order_id | bigint |
| 买家 ID | user_id | bigint |
| 图书 ID | goods_id | bigint |
| 买家 IP | user_ip | string |
| 订单时间 | order_time | bigint |
| 退款时间 | refund_time | bigint |
| 收货地址 | ship_add (shipping address) | string |
| 手机号 | phone_no | string |
| 运费险 | fre_ins (freight insurance) | string |
| 退款原因 | refund_reason | string |
1.2.5 物流表
| 字段解释 | 字段名 | 数据类型 |
|---|---|---|
| 订单 ID | order_id | bigint |
| 物流单号 | log_num (logistics single number) | bigint |
| 发货时间 | del_time (the delivery time) | bigint |
| 收货时间 | rec_time (receiving time) | bigint |
1.2.6 用户表
| 字段解释 | 字段名 | 数据类型 |
|---|---|---|
| 用户 ID | user_id | bigint |
| 性别 | gender | string |
| 年龄 | age | bigint |
| 访问时间 | access_time | bigint |
| 最后一次登录时间 | last_login (last login time) | bigint |
| 点击来源 | click_source | string |
1.3 准备数据文件
1.3.1 上传数据文件到虚拟机
- 在slave2节点上执行命令：
mkdir /ecdata
- 执行命令：
cd /ecdata
- 将6个数据文件上传到
/ecdata目录
- 执行命令：
ll
1.3.2 在HDFS上创建数据目录
- 执行命令：
hdfs dfs -mkdir /ecdata
1.3.3 上传数据文件到HDFS目录
执行命令：
hdfs dfs -put item.txt /ecdata
执行命令：
hdfs dfs -put logistics.txt /ecdata
执行命令：
hdfs dfs -put orders.txt /ecdata
执行命令：
hdfs dfs -put refund.txt /ecdata
执行命令：
hdfs dfs -put users.txt /ecdata
执行命令：
hdfs dfs -put visitors.txt /ecdata
1.4 创建数据库
- 执行命令：
create database ecdata;
- 执行命令：
use ecdata;，切换到ecdata数据库
1.5 创建数据表
1.5.1 创建订单表
- 创建订单表
- 执行语句
create table orders ( order_id string, order_time bigint, user_id bigint, goods_id bigint, user_ip string, ship_add string, phone_no string, click_source string, click_cost float, pay_ct bigint, pay_status string ) row format delimited fields terminated by '\t';
- 执行语句
- 向订单表加载数据
- 执行语句：
load data inpath '/ecdata/orders.txt' into table orders;
- 执行语句：
- 查看订单表记录
- 执行语句：
select * from orders limit 20;
- 执行语句：
1.5.2 创建访客表
创建访客表
- 执行语句
create table visitors ( access_ip string, access_time bigint, jump_time bigint, user_id bigint, phone_no string, goods_id bigint, access_con string, access_event string, click_source string, click_cost float ) row format delimited fields terminated by '\t';
- 执行语句
向访客表加载数据
- 执行语句：
load data inpath '/ecdata/visitors.txt' into table visitors;
- 执行语句：
查看访客表记录
- 执行语句：
select * from visitors limit 20;
- 执行语句：
1.5.3 创建商品类目表
创建商品类目表
- 执行语句
create table item ( goods_id bigint, goods_name string, author string, press string, pub_time bigint, isbn float, pricing float, goods_price bigint, mon_sales string, del_add string ) row format delimited fields terminated by '\t';
- 执行语句
向商品类目表加载数据
- 执行语句：
load data inpath '/ecdata/item.txt' into table item;
- 执行语句：
查看商品类目表记录
- 执行语句：
select * from item limit 20;
- 执行语句：
1.5.4 创建退款表
创建退款表
- 执行语句
create table refund ( order_id bigint, user_id bigint, goods_id bigint, user_ip string, order_time bigint, refund_time bigint, ship_add string, phone_no string, fre_ins string, refund_reason string ) row format delimited fields terminated by '\t';
- 执行语句
向退款表加载数据
- 执行语句：
load data inpath '/ecdata/refund.txt' into table refund;
- 执行语句：
查看退款表记录
- 执行语句：
select * from refund limit 20;
- 执行语句：
1.5.5 创建物流表
创建物流表
- 执行语句
create table logistics ( order_id bigint, log_num bigint, del_time bigint, rec_time bigint ) row format delimited fields terminated by '\t';
- 执行语句
向物流表加载数据
- 执行语句：
load data inpath '/ecdata/logistics.txt' into table logistics;
- 执行语句：
查看物流表记录
- 执行语句：
select * from logistics limit 20;
- 执行语句：
1.5.6 创建用户表
创建用户表
- 执行语句
create table users ( user_id bigint, gender string, age bigint, access_time bigint, last_login bigint, click_source string ) row format delimited fields terminated by '\t';
- 执行语句
向用户表加载数据
- 执行语句：
load data inpath '/ecdata/users.txt' into table users;
- 执行语句：
查看用户表记录
- 执行语句：
select * from users limit 20;
- 执行语句：
2. 项目实现
2.1 总体运营指标
2.1.1 独立访客数(UV)
时间维度
执行语句
select from_unixtime(access_time, 'HH') as hour, count(distinct user_id) as uv from visitors where access_time is not null group by from_unixtime(access_time, 'HH') order by hour;
结果说明：该查询按小时统计了独立访客数（UV），结果显示全天各时段均有用户访问，整体分布较均匀。高峰出现在15点（483人）和19点（484人），低谷在20点（382人），表明用户活跃时段集中在下午至晚间，符合典型电商使用习惯。
小时 独立访客数 00 481 01 487 02 453 03 435 04 438 05 454 06 477 07 436 08 470 09 456 10 443 11 431 12 438 13 438 14 439 15 483 16 434 17 453 18 471 19 484 20 382 21 428 22 461 23 422
地域维度
执行语句
select split(o.ship_add, ' ')[0] as province, count(distinct o.user_id) as visitor_count from orders o inner join visitors v on o.user_id = v.user_id where o.ship_add is not null and o.ship_add != '' and split(o.ship_add, ' ')[0] is not null group by split(o.ship_add, ' ')[0] order by visitor_count desc;
结果说明：该查询统计了既访问网站又下单的用户按省份分布的独立访客数，覆盖全国31个省级行政区。用户主要集中在云南（58人）、河北（56人）和黑龙江、浙江等地，直辖市及西部地区用户相对较少，反映活跃用户多分布于人口大省或电商渗透率较高区域。
省份 下单独立访客数 云南省 58 河北省 56 黑龙江省 54 浙江省 53 广东省 52 辽宁省 52 四川省 50 山西省 49 内蒙古自治区 49 河南省 49 吉林省 48 江苏省 48 湖南省 48 新疆维吾尔自治区 46 广西壮族自治区 46 甘肃省 44 福建省 44 安徽省 43 陕西省 43 山东省 42 西藏自治区 41 江西省 40 贵州省 38 青海省 35 湖北省 34 重庆市 31 海南省 27 宁夏回族自治区 25 天津市 16 北京市 15 上海市 14
渠道维度
执行语句
select click_source as channel, count(distinct user_id) as uv from visitors where click_source is not null and click_source != '' group by click_source order by uv desc;
结果说明：该查询按渠道统计了独立访客数（UV），结果显示“聚划算”引流效果最强（1702人），其次是“淘宝直播”（1397人）和“直通车”（1199人），而“搜索”渠道仅1人。表明促销活动与直播内容是吸引用户访问的主要来源，传统自然搜索占比较低。
渠道 独立访客数 聚划算 1702 淘宝直播 1397 直通车 1199 淘宝橱窗 924 阿里博客 905 淘宝社区 886 淘宝搜索 541 搜索 1
2.1.2 页面访问数(PV)
时间维度
执行语句
select from_unixtime(access_time, 'HH') as hour, count(*) as pv from visitors where access_time is not null group by from_unixtime(access_time, 'HH') order by hour;
结果说明：该查询按小时统计了页面访问量（PV），结果显示全天各时段均有稳定访问，高峰出现在15点（551次）和19点（538次），低谷在20点（435次）和04点（477次）。整体分布较均衡，无极端波动，表明用户活跃时段覆盖全天，符合典型电商平台的访问规律。
小时 页面访问数 00 531 01 546 02 502 03 480 04 477 05 497 06 518 07 483 08 524 09 523 10 489 11 486 12 491 13 493 14 488 15 551 16 484 17 509 18 528 19 538 20 435 21 479 22 511 23 467
地域维度
执行语句
select split(o.ship_add, ' ')[0] as province, count(*) as pv from visitors v join orders o on v.user_id = o.user_id where o.ship_add is not null and o.ship_add != '' and split(o.ship_add, ' ')[0] is not null group by split(o.ship_add, ' ')[0] order by pv desc;
结果说明：该查询统计了有下单记录用户的页面访问量（PV）按省份分布情况。结果显示云南省（369）、河北省（357）和浙江省（334）访问量最高，直辖市如上海、北京、天津相对较低，表明活跃用户主要集中在人口大省或电商渗透率较高的地区，整体与用户基数和消费活跃度正相关。
省份 页面访问量 云南省 369 河北省 357 浙江省 334 黑龙江省 333 辽宁省 328 广东省 322 四川省 313 内蒙古自治区 310 河南省 310 山西省 304 江苏省 302 湖南省 300 吉林省 295 新疆维吾尔自治区 287 广西壮族自治区 285 甘肃省 277 福建省 277 陕西省 272 安徽省 268 山东省 260 江西省 249 西藏自治区 249 贵州省 238 青海省 224 湖北省 217 重庆市 191 海南省 170 宁夏回族自治区 155 天津市 100 北京市 96 上海市 90
渠道维度
执行语句
select click_source as channel, count(*) as pv from visitors where click_source is not null and click_source != '' group by click_source order by pv desc;
结果说明：该查询按渠道统计页面访问量（PV），结果显示“聚划算”以3652次访问居首，其次是“淘宝直播”（2374）和“直通车”（1752），传统搜索类渠道如“淘宝搜索”仅642次，“搜索”渠道最低（2次）。说明营销活动和内容直播是当前主要流量来源，用户更倾向于通过促销和互动场景进入平台。
渠道 页面访问数 聚划算 3652 淘宝直播 2374 直通车 1752 淘宝橱窗 1222 阿里博客 1218 淘宝社区 1168 淘宝搜索 642 搜索 2
2.1.3 人均页面访问数(PPV)
时间维度
执行语句
select from_unixtime(access_time, 'HH') as hour, count(*) as pv, count(distinct user_id) as uv, round(count(*) * 1.0 / count(distinct user_id), 2) as ppv from visitors where access_time is not null group by from_unixtime(access_time, 'HH') order by hour;
结果说明：该查询按小时计算了人均页面访问数（PPV = PV/UV），结果显示全天 PPV 稳定在 1.09–1.15 之间，用户浏览深度较为均衡。高峰时段如 09 点（1.15）和 15 点（1.14）略高，表明用户在活跃时段停留更久或浏览更多页面，整体用户体验和内容吸引力良好。
小时 页面访问数 独立访客数 人均页面访问数 00 531 481 1.10 01 546 487 1.12 02 502 453 1.11 03 480 435 1.10 04 477 438 1.09 05 497 454 1.09 06 518 477 1.09 07 483 436 1.11 08 524 470 1.11 09 523 456 1.15 10 489 443 1.10 11 486 431 1.13 12 491 438 1.12 13 493 438 1.13 14 488 439 1.11 15 551 483 1.14 16 484 434 1.12 17 509 453 1.12 18 528 471 1.12 19 538 484 1.11 20 435 382 1.14 21 479 428 1.12 22 511 461 1.11 23 467 422 1.11
地域维度
执行语句
select t.province, sum(t.pv) as total_pv, count(distinct t.user_id) as uv, round(sum(t.pv) * 1.0 / count(distinct t.user_id), 2) as ppv from ( select v.user_id, count(*) as pv, split(o.ship_add, ' ')[0] as province from visitors v join orders o on v.user_id = o.user_id where o.ship_add is not null and o.ship_add != '' and split(o.ship_add, ' ')[0] is not null group by v.user_id, split(o.ship_add, ' ')[0] ) t group by t.province order by ppv desc;
结果说明：该查询按省份统计了人均页面访问数（PPV），结果显示各省份 PPV 集中在 6.07–6.63 之间，整体用户浏览深度较高。河南省以 6.63 居首，西藏自治区最低（6.07），但差异不大，表明各地用户活跃度和内容吸引力较为均衡。
省份 页面访问数 独立访客数 人均页面访问数 云南省 369 58 6.36 河北省 357 56 6.38 浙江省 334 53 6.30 黑龙江省 333 54 6.17 辽宁省 328 52 6.31 广东省 322 52 6.19 四川省 313 50 6.26 内蒙古自治区 310 49 6.33 河南省 310 49 6.33 山西省 304 49 6.20 江苏省 302 48 6.29 湖南省 300 48 6.25 吉林省 295 48 6.15 新疆维吾尔自治区 287 46 6.24 广西壮族自治区 285 46 6.20 甘肃省 277 44 6.29 福建省 277 44 6.29 陕西省 272 43 6.33 安徽省 268 43 6.23 山东省 260 42 6.19 西藏自治区 249 41 6.07 江西省 249 40 6.23 贵州省 238 38 6.26 青海省 224 35 6.40 湖北省 217 34 6.38 重庆市 191 31 6.16 海南省 170 27 6.30 宁夏回族自治区 155 25 6.20 天津市 100 16 6.25 北京市 96 15 6.40 上海市 90 14 6.43
渠道维度
执行语句
select click_source as channel, count(*) as pv, count(distinct user_id) as uv, round(count(*) * 1.0 / count(distinct user_id), 2) as ppv from visitors where click_source is not null and click_source != '' group by click_source order by pv desc;
结果说明：该查询按渠道统计了PV、UV及人均页面访问数（PPV）。结果显示，“聚划算”引流最强（PV 3652，UV 1702），且PPV达2.15，用户浏览深度最高；“淘宝直播”和“直通车”次之。自然“搜索”渠道虽PPV为2.00，但样本极小（仅1人），参考价值有限，整体体现促销与内容渠道更有效激发用户活跃。
渠道 页面访问数 独立访客数 人均页面访问数 聚划算 3652 1702 2.15 淘宝直播 2374 1397 1.70 直通车 1752 1199 1.46 淘宝橱窗 1222 924 1.32 阿里博客 1218 905 1.35 淘宝社区 1168 886 1.32 淘宝搜索 642 541 1.19 搜索 2 1 2.00
2.1.4 总订单数量
2.1.5 成交金额
2.1.6 客单价=销售额/购买人数
2.1.7 销售毛利（毛利最高的时间商品）
2.1.8 毛利率
2.2 流量类指标
2.3 网站销售类指标
2.4 商品类指标
2.5 营销类指标
2.6 物流类指标
3. 数据展示
3.1 可视化软件简介
- Tableau 是一款领先的可视化数据分析工具，能够快速连接多种数据源（如 Excel、SQL 数据库、云平台等），通过拖拽式操作实现高效的数据探索与交互式仪表板构建。其核心优势在于强大的可视化能力、实时数据更新和用户友好的界面，无需编程即可生成图表、地图、趋势分析等，广泛应用于商业智能、运营监控与决策支持。Tableau 支持多端共享与协作，助力个人和企业将复杂数据转化为直观洞察，提升数据驱动决策的效率与准确性。