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

Kevin-source-art

已于 2025-03-01 22:45:43 修改

阅读量1k

点赞数 10

文章标签：数据分析数据挖掘 python excel

于 2025-03-01 19:20:17 首次发布

本文链接：https://blog.csdn.net/2201_75990437/article/details/145950359

版权

项目背景
基于电商业务场景，通过构建用户、商品、订单及订单明细四张核心数据表，结合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()