电商数据分析项目全流程实战：从数据建模到可视化洞察

电商数据分析项目全流程实战：从数据建模到可视化洞察

引用

CSDN

1.

https://blog.csdn.net/2201_75990437/article/details/145950359

本文将介绍一个完整的电商数据分析项目，从数据建模到可视化洞察，涵盖用户画像、交易行为和商品运营等多个维度的分析。通过这个项目，你将学习到如何使用SQL、Excel和Python等工具进行数据处理和分析，为电商运营决策提供数据支持。

项目背景

基于电商业务场景，通过构建用户、商品、订单及订单明细四张核心数据表，结合SQL高级查询、Excel Power Query和Python可视化技术，实现了多维度的业务分析，最终输出用户画像、销售趋势、品类偏好等关键指标，为运营决策提供数据支持。

项目结构

  
E-commerce-Analysis/
├── Excel_Analysis/                          # Excel分析文件
│   ├── 不同省份用户的消费情况（数据透视表）.xlsx
│   ├── 不同年龄段用户的订单数量与消费金额（数据透视表）.xlsx
│   ├── RFM分析.xlsx
│   ├── 不同支付方式的订单数量占比.png
│   ├── 每月订单金额的趋势变化.png
│   └── 不同省份订单金额汇总.png
├── Python_Files/                            # Python文件
│   ├── data_generator.ipynb
│   ├── data_import.ipynb
│   └── data_mapping.ipynb
├── SQL_Advanced_Query_Results/              # SQL高级查询分析结果
│   ├── 不同年龄段用户购买的热门商品品类.csv
│   ├── 不同省份用户的消费总额分布.csv
│   ├── 不同支付方式下的平均订单金额和订单数量.csv
│   ├── 每个品类商品的销售数量和销售金额.csv
│   ├── 每月订单数量和平均订单金额的变化趋势.csv
│   └── 月度销售趋势分析.csv
├── SQL_Scripts/                             # SQL脚本文件
│   ├──查询分析/                          
│   │   ├── 不同年龄段用户购买的热门商品品类.sql
│   │   ├── 不同省份用户的消费总额分布.sql
│   │   ├── 不同支付方式下的平均订单金额和订单数量.sql
│   │   ├── 每个品类商品的销售数量和销售金额.sql
│   │   ├── 每月订单数量和平均订单金额的变化趋势.sql
│   │   └── 月度销售趋势分析.sql
│   ├──创建表/
│   │   ├── 建立用户表.sql
│   │   ├── 建立商品表.sql
│   │   ├── 建立订单表.sql
│   │   └── 建立订单详细表.sql
├── SQL_Data_Tables/                         # SQL数据表
│   ├── users.dbf
│   ├── products.dbf
│   ├── orders.dbf
│   └── order_details.dbf
├── Original_Data/                           # 原始数据-Python生成
│   ├── users.csv
│   ├── products.csv
│   ├── orders.csv
│   └── order_details.csv
└── E-R_Diagram.png                          # 数据表ER图
  

技术栈与工具

• 数据库建模：Navicat Premium 17
• 数据分析：SQL、Excel Power Query
• 可视化：Python (Matplotlib/Seaborn)、Excel 数据透视图
• 数据生成：Python Faker 库

核心维度分析

1.用户画像分析

• 不同年龄段用户购买的热门商品品类
• 各省份用户的消费总额分布

2.交易行为分析

• 支付方式对应的订单金额与数量
• 月度订单量及平均金额趋势

3.商品运营分析

• 各品类商品的销量与销售额排名

关键实现步骤

1.数据建模（ER图）

通过外键关联实现用户-订单-商品的多层级关系。

2.Python生成数据示例

生成用户数据

  
# 生成用户数据
generated_usernames = set()  # 用于跟踪已生成的用户名的集合
 
users = []
for user_id in range(1, 1001):  # 生成1000个用户
    while True:  # 循环直到生成一个不重复的用户名
        username = fake.user_name().replace(' ', '_').lower()  # 生成用户名，并处理为统一格式（例如，移除空格，转换为小写）
        if username not in generated_usernames:
            generated_usernames.add(username)  # 将用户名添加到集合中
            break  # 跳出循环
 
    user = {
        "user_id": user_id,
        "username": username,
        "registration_date": fake.date_between('-2y').strftime('%Y-%m-%d'),  # 日期位于当前日期之前两年(-2y)到当前日期之间的某个节点
        "province": fake.province(),
        "age": random.randint(18, 62),  # 年龄为18到62岁
        "gender": random.choice(['M', 'F', 'Unknown'])
    }
    users.append(user)  

3.Python连接SQL导入数据示例

  
# 创建数据库连接
engine = create_engine('mysql+pymysql://root:123456@localhost:3306/ecommerce_analysis')
# 读取 CSV 文件
users = pd.read_csv(r'D:\Money\自研项目\Ecommerce-Analysis\原始数据\users.csv')
products = pd.read_csv(r'D:\Money\自研项目\Ecommerce-Analysis\原始数据\products.csv')
# 定义数据类型映射，确保与数据库表结构匹配
user_types = {
    'user_id': Integer(),
    'username': String(50),
    'registration_date': Date(),
    'province': String(20),
    'age': Integer(),
    'gender': Enum('M', 'F', 'Unknown')
}
product_types = {
    'product_id': Integer(),
    'product_name': String(100),
    'category': String(30),
    'price': DECIMAL(10, 2),
    'stock': Integer()
}
# 将数据插入到 SQL 表中，同时指定数据类型
users.to_sql('users', con=engine, if_exists='append', index=False, dtype=user_types)
products.to_sql('products', con=engine, if_exists='append', index=False, dtype=product_types)  

4.SQL高级查询示例

不同年龄段热门品类分析

  
-- 不同年龄段用户购买的热门商品品类
SELECT 
    CASE 
        WHEN u.age BETWEEN 18 AND 25 THEN '18 - 25'
        WHEN u.age BETWEEN 26 AND 35 THEN '26 - 35'
        WHEN u.age BETWEEN 36 AND 45 THEN '36 - 45'
        WHEN u.age BETWEEN 46 AND 55 THEN '46 - 55'
        ELSE '56+'
    END AS 年龄分组,
    p.category 商品品类,
    COUNT(*) AS 购买数量
FROM 
    users u
JOIN 
    orders o ON u.user_id = o.user_id
JOIN 
    order_details od ON o.order_id = od.order_id
JOIN 
    products p ON od.product_id = p.product_id
GROUP BY 
    年龄分组, 商品品类
ORDER BY 
    年龄分组, 购买数量 DESC;  

5. Excel Power Query 分析

RFM模型构建

R（最近购买时间）、F（购买频率）、M（消费金额） 通过数据透视表动态计算，划分用户价值等级。

部分示例

6. Python可视化

产品类别与销售数量的散点图矩阵（示例代码）

  
# 合并 order_details 和 products 表
merged_df = pd.merge(order_details, products, left_on='product_id', right_on='product_id')
# 选择需要的列
scatter_data = merged_df[['category', 'quantity']]
# 绘制散点图矩阵
g = sns.pairplot(scatter_data, hue='category')
plt.title('产品类别与销售数量的散点图矩阵')
plt.show()  

散点图矩阵

项目亮点

1. 全流程覆盖：从数据生成、ETL、分析到可视化，完整闭环。
2. 高效查询优化：通过索引和子查询提升SQL性能（如订单表按月份分区）。

3. Excel 透视分析：Excel多维度数据透视表。

总结

本项目通过整合SQL、Excel和Python技术，构建了一套标准化的电商数据分析流程，涵盖从数据建模到业务洞察的全链路方法。未来可扩展实时数据接入，进一步提升分析深度。