数据分析案例 自行车公司

---

前言

本文是对Adventure Works报告的一个总结，记录了整个项目需求分析与实现的过程。该项目主要任务是要对Adventure Works的总体销售数据进行可视化，涉及到使用Pyhton提取数据库中数据并进行加工处理，将处理后的数据放入数据库并连接到Tableau实现可视化。

分析成果的链接：销售报表

一、项目背景介绍

项目数据来源：数据来源于adventure Works Cycles公司的的样本数据库。

Adventure Works Cycles是一家大型跨国制造公司，基地在美国洛杉矶。该公司生产和销售自行车以及自行车相关物品，销售至全球各个地区。该公司2019年开始，对中国授权四个渠道代理商进行代理销售自家产品。该样本数据为2019年1月1日至今的销售相关数据，项目旨在帮助Adventure Works Cycles针对中国市场进行数据分析。

同时为了更好地理解该公司业务，以下附上一些简单介绍。

1. 代理商介绍
   这家公司在中国有四个代理商，Adventure Works Cycles对代理商的基本要求：
   (1). 公司规模不小于200人，并且在中国有10年以上分销代理经验；
   (2). 必须能涉足线上以及线下去进行销售。

2. 产品介绍
   这家公司主要有下面三个产品线：
   (1). Adventure Works Cycles生产的自行车，该产品线为公司主要产品，利润最大；
   (2). 自行车配件，如：轮胎、自行车架、自行车看台等；
   (3). 相关服饰，如运动衫、头盔、手套等。

二、需求分析与实现

项目目标：

通过现有数据，从渠道维度、产品维度、消费者维度分析重要指标，并获取最新的商品销售趋势，以及消费者画像，为公司的制造和销售提供指导性建议，以增加公司的收益。

项目任务：

1. 理解公司数据库，梳理个数据表中的关系和逻辑
2. 探索数据库并罗列分析指标
3. 汇总数据建立数据仓库，并且搭建可视化监控数据

1.理解公司提供的数据以及之间的关系和逻辑

数据：
拿到的是ODS（Operation Data Store）层里，ods表和fact事实表的数据，即操作性储存数据。把数据整理成如下数据字典：

用户表：

表名	表注释	字段	字段注释
ods_customer	每日新增用户表	customer_key	客户主键
ods_customer	每日新增用户表	agent_key	代理商编号
ods_customer	每日新增用户表	create_date	创建日期
ods_customer	每日新增用户表	birth_date	生日
ods_customer	每日新增用户表	marital_status	是否结婚（M：是，S：否）
ods_customer	每日新增用户表	yearly_income	年收入
ods_customer	每日新增用户表	chinese_territory	所在区域（华北、华东…）
ods_customer	每日新增用户表	chinese_province	所在省份
ods_customer	每日新增用户表	chinese_city	所在城市
ods_customer	每日新增用户表	chinese_longitude	经度
ods_customer	每日新增用户表	chinese_latitude	纬度

产品表：

表名	表注释	字段	字段注释
ods_product	产品表	product_key	产品主键
ods_product	产品表	cpzl_zw	产品类别
ods_product	产品表	cplb_zw	产品子类
ods_product	产品表	cp_name	产品名
ods_product	产品表	unit_cost	产品成本
ods_product	产品表	unit_price	产品单价

代理商表：

表名	表注释	字段	字段注释
ods_distributor	代理商表	dist_key	代理商主键
ods_distributor	代理商表	dist_name	代理商全称
ods_distributor	代理商表	dist_contact	代理商联系方式
ods_distributor	代理商表	dist_email	代理商邮件
ods_distributor	代理商表	dist_address	代理商公司地址
ods_distributor	代理商表	from_date	开始合作时间
ods_distributor	代理商表	to_date	结束合作时间
ods_distributor	代理商表	dist_website	代理商线上商场网页
ods_distributor	代理商表	dist_POS_no	代理商拥有门店数

订单表：

表名	表注释	字段	字段注释
fact_sales_orders	订单明细表	sales_order_key	订单主键
fact_sales_orders	订单明细表	create_date	订单日期
fact_sales_orders	订单明细表	customer_key	客户主键
fact_sales_orders	订单明细表	dist_key	代理商主键
fact_sales_orders	订单明细表	product_key	产品主键
fact_sales_orders	订单明细表	sales_channel	购买渠道(线上/线下)

关系：

1. 订单明细表的每条记录都有customer_key、dist_key、product_key去链接其他三个表。
2. 一个代理商可能有零或多个用户，但一个用户必须属于一个代理商。当一个用户从不同代理商购物时，他会被默认成为一个新的用户。

2.罗列重要指标，布局BI

由于该公司生产的主要产品是耐用品，更换频率不高，top10%的客户才购买两件或两件以上，于是对消费者数的关注度不大。

dw_customer_orders["customer_key"].value_counts().describe(percentiles=[0.8,0.85,0.9])
"""
结果：
count   289581.00000
mean         1.13746
std          0.40705
min          1.00000
50%          1.00000
80%          1.00000
85%          1.00000
90%          2.00000
max          8.00000
Name: customer_key, dtype: float64
"""

主要关注整体的销量和销售额，并且对这两个指标的整体趋势，以及需要关注消费者特征（地域分布、消费能力等）。

1. 销量=∑订单数
2. 销售额=∑单个产品金额*购买客户数

同时还有成本、利润，并且根据实际情况，公司会有给不同代理商不同渠道不同产品设定目标，我随机创建了目标数据，在作图的时候体现出目标与实际的差距。

1. 销量目标完成率=销量/目标销量
2. 销售额目标完成率=销售额/目标销售额

随机生成目标的代码：

sum_amount_goal_list = []
sum_order_goal_list = []
for i in range(dw_by_day.shape[0]):
    sum_amount_goal = round(random.uniform(0.85, 1.15),2)
    sum_order_goal = round(random.uniform(0.85, 1.1),2)