sql能查到数据 dataset对象里面没有值_新零售数据分析报告

零售项目数据分析报告

一、项目背景

随着零售市场的竞争日趋激烈，在试图满足客户期望时，最优化服务业务流程的能力是最重要的。通道化和管理数据，以争取客户的利益以及创造利润为目标，对生存至关重要。
理想情况下，零售商的客户数据反映了公司在吸引和培养客户方面的成功。零售商使用转换率，平均订单价值，购买新近度和最近交易中花费的总金额等指标来构建总结客户行为的报告。这些度量提供了对客户行为倾向的总体了解。
本文以MySql（版本5.7）内的项目零售记录数为对象进行业务分析，将主要分析活跃顾客购物情况，商品销售情况等，形成以产品——用户为核心分析目标的零售数据报告。

1.数据来源与表结构

本文采用的数据集来自于和鲸社区（kesci），由sisis通过网络爬虫获取，数据链接为https://www.kesci.com/home/dataset/5e0f04a22823a10036b28228。

本数据总共包含三个表，即Customer顾客信息表，prod_cat_info 产品信息表， Transactions交易订单信息表，分别记录了顾客的相信信息，产品的详细分类以及交易订单的详情。三张表分别通过customer_id，prod_cat_code，cust_id进行关联

2.数据分析工具

MySql：主要用于查询、提取数据库内数据

Excel：用于数据可视化制作以及部分简单数据处理

平台：Navicat Premium 12，主要用作连接MySql

二、数据预处理

1.数据导入

创建数据库GOODS,将三个表的csv 文件分别导入

导入成功截图：

2.缺失值清洗

此环节的目的主要是针对表中数据缺失的部分进行处理，有两种方法，一种是针对缺失值填充数据（均值等可量化的值），一种是直接去除不需要的row。

缺失值查询：select * from table where column is null

经查询，三表均没有空值，即数据表完整，无缺失。

3.格式内容清洗

此部分主要针对各字段的格式进行统一，包括时间，价格等

经查，三表数据格式一致

4.逻辑错误清洗

此部分主要针对数据重复、矛盾值内容等进行清洗，筛掉重复的内容，同时，对矛盾值进行处理。

SQL语句：

查重语句：

SELECT

transaction_id,

count( * ) AS count

FROM

transactions

GROUP BY

transaction_id

HAVING

count > 1

查询截图：

通过查询发现，在订单表中，共有2057条重复订单号，由于其数据量较少，因此，直接采用excel去重。

同时发现，transaction表中，QTY和RATE字段出现了为负的情况，这在逻辑上是不合理的，因此，我们利用数据分析工具，去除前面的负号。

SQL语句：

update transactions set Qty = -Qty where Qty < 0

update transactions set Rate= - Rate where Rate < 0

update transactions set Total_amt= -Total_amt where Total_amt < 0

同时，由于原表Qty等的类型都为varchar，不适合数据计算，因此将其更换为int类型，这里不做赘述。

运行完成后数据：

5.非需求数据处理

分析transcations和product两表可知，其中字段prod_subcat_code和pro_sub_cat_code对此次分析意义不太，在备份后进行删除。

SQL语句：

Alter table transcations drop column pro_sub_cat_code

Alter table transcations drop prod_subcat_cod

经过数据清洗后，得到有效数据为：

Custemor表：5637条

Product表：23条

Transactions表：20878条

三、分析目标

1.用户画像分析

分析维度：用户

分析指标：用户id，消费金额，用户所在城市，年龄

拟定相关标准，通过查询各用户的性别，地域，年龄等，探究出高消费人群的画像，并对比男女消费情况，探求其中的差异。

2.商品热度分析

分析维度：商品

分析指标：产品id，产品销量，产品销售总额

通过对各商品的销售总额进行分析，得出热销商品和冷门商品的种类和销量，从而为零售备货提供一定指导。

3.销售平台分析

分析维度：各平台销售额

分析指标：商店种类，销售数量，销售额

通过对各销售平台（电子商店，旗舰店，远程商店）等平台的销售分析，获取目前大众流行的购物方式，从而为零售行业的发展提供借鉴意义。

四、数据分析与可视化

1.用户画像分析

1.1提取数据字段

用户id，来自表格customer，即customer_id，与订单表transactions的cust_id进行关联；

消费金额，来自transactions表中的total_amt字段。

用户所在城市，来自表格customer，即city_code字段。

用户性别，来自于表格customer，即Gender字段。

1.2 SQL代码

查出交易订单中消费金额最多的前500位顾客的id，出生日期，性别，城市:

SELECT

customer.customer_id,

customer.date,

customer.Gender,

customer.city_code,

sum( transactions.Total_amt )

FROM

customer

JOIN transactions ON customer.customer_id = transactions.cust_id

GROUP BY

customer_id

ORDER BY

sum( Total_amt ) DESC

LIMIT 500

运行结果：

查出消费金额前500位顾客的性别分布：

SELECT Gender,count(*) from (

SELECT

customer.customer_id,

customer.date,

customer.Gender,

customer.city_code,

sum( transactions.Total_amt )

FROM

customer

JOIN transactions ON customer.customer_id = transactions.cust_id

GROUP BY

customer_id

ORDER BY

sum( Total_amt ) DESC

LIMIT 500

) as test

GROUP BY Gender

运行结果：

可视化展示：

结合数据和可视化得知，在高消费人群中，男女占比分别为 49.4%和50.6%，几乎没有差别。

查出消费金额前500位顾客的地域分布：

SELECT city_code,count(*) from (

SELECT

customer.customer_id,

customer.date,

customer.Gender,

customer.city_code,

sum( transactions.Total_amt )

FROM

customer

JOIN transactions ON customer.customer_id = transactions.cust_id

GROUP BY

customer_id

ORDER BY

sum( Total_amt ) DESC

LIMIT 500

) as test

GROUP BY city_code运行结果：

可视化展示：

从查询结果和可视化可以看到，在10个城市中，高消费人群分布最多的是8号城市，而分布最少的是6号城市，其他城市相对来说较为平均。

男女平均消费金额对比：

SQL语句：

SELECT

Gender,

avg( transactions.Total_amt )

FROM

customer

JOIN transactions ON customer.customer_id = transactions.cust_id

GROUP BY

customer.Gender

查询结果：

可视化处理：

可以看出，在所有的交易数据中，男女平均消费金额分别为2621.2537，261。8833，总体看来，人群消费能力大概一致。

不同城市零售消费金额对比：

SQL语句：

SELECT

city_code,

avg( transactions.Total_amt )

FROM

customer

JOIN transactions ON customer.customer_id = transactions.cust_id

GROUP BY

customer.city_code

运行结果：

可视化处理：

可以看出，各城市的平均消费金额差异比较明显，人均消费能力最强的为10号城市，为2673.7元，而人均消费能力较弱的是6号城市，人均消费金额为2546，2元，这也上文中，消费前500人群城市分布契合。

综合来看，消费前500的人群中，男女分布较为均衡，同时，在所有订单中，男女平均消费也比较接近，因此可以认定，高消费人群受性别的影响较小；而从数据来看，人均消费金额和消费前500人群城市分布最多的都是10号城市，而最少的都是6号城市，因此可以粗略认为，10号城市的经济水平高于6号城市，高消费人群也分布于此，其他城市相对来说差别较小。

2.商品热度分析

2.1提取数据字段

产品id，来自表product，字段名为prod_cat_id，transaction表中的prod_cat_id相关联。

产品销售数量，来自表transaction，字段名为Qty

产品单价，来自表transaction，字段名为Rate

产品销售额，来自表transaction，字段名为Total_mat

其中，product.Prod_cat_id和transaction.prod_cat_id相关联。

2.2 SQL代码

查出各商品销售数量和销售额

SELECT

prod_cat_code,

sum(Qty),

sum(Total_amt)

FROM

transactions

GROUP BY

prod_cat_code

运行结果：

可视化处理：

从中可以看出，6种商品种，销量最好的是5号商品，为16297，而销量最少的是4号商品，仅为5392.而从销售额来看，5号商品也是明显高于其他商品。

3.销售平台分析

3.1提取字段

销售渠道，来自transaction表，字段名为Store_type.

销售总额，来自transaction表，字段名为Total_mat.

3.2 SQL语句

查询每种销售渠道的销售总额并排序：

SELECT

Store_type,

sum( Total_amt )

FROM

transactions

GROUP BY

Store_type

ORDER BY

sum( Total_amt )

运行结果：

可视化处理：

从中可以看出，e—shop的销售占比达到了41%，而MBR的销售仅为20%，可见，电子购物已经代替了部分线下购物需求。

五、项目结论

1.用户分析

经过多方面分析，发现人群消费能力与性别的关系较小，高消费人群的画像为女/10号城市，因此，可以在10城市开设一些相对品牌化的零售店，满足高消费人群其他方面的品质化要求，以高质量、高服务来促进城市零售业的发展与转型。

2.商品分析。

在6种商品种，销量最好的为5号商品，而销量最差的是4号商品，因此，在零售店的进货结构中，可以通过对商品销量的进一步预测，结合商品销售数据，合理完善商品存货结构，从而减小库存浪费和积压货物的风险。

3.平台渠道

从数据中看出，e—shop，即电子商店在所有的销售数量中占了多数，而其他线下的平台相对来说占比较小，可以看出的是，随着电子商务的发展，越来越多的人为了减轻购物压力，会选择网络购物，各零售企业要抓住需求，规划合理的销售渠道。同时可以看出的是，线下实体店的总占比还是要超过电子商店的，大胆猜测是由于部分用户为了追求更好的购物体验感，因此，实际上，实体店并不会消亡，各商家可选择结合市场趋势，充分融合线上线下，打通线上线下壁垒，积极开启新零售之路！