数据分析实操篇:基于python+mysql+power bi的广告投放效果分析

# 导入所需库import pandas as pdimport numpy as npfrom sqlalchemy import create_engine, textimport pymysqlfrom datetime import datetime# MySQL数据库连接配置DB_CONFIG = {    'host': 'localhost',           # 数据库主机地址    'user': 'root',                # 数据库用户名    'password': '88888888',        # 数据库密码    'database': 'ecommerce_analysis',  # 数据库名称    'port': 3306                   # MySQL默认端口}# 创建数据库连接引擎def get_db_engine():    """创建MySQL数据库连接引擎"""    connection_string = f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}?charset=utf8mb4"    return create_engine(connection_string)

# 读取CSV文件df = pd.read_csv('../data/KAG_conversion_data.csv')# 查看数据基本信息print(f"数据形状: {df.shape}")print(f"\n数据字段: {list(df.columns)}")print(f"\n前5行数据预览:")df.head()

# 字段重命名为中文df.columns = [col.lower() for col in df.columns]df.rename(columns={    'ad_id': '广告ID',    'xyz_campaign_id': '广告系列ID',    'fb_campaign_id': '脸书广告系列ID',    'age': '年龄段',    'gender': '性别',    'interest': '兴趣代码',    'impressions': '曝光量',    'clicks': '点击量',    'spent': '花费_美元',    'total_conversion': '总转化数',    'approved_conversion': '批准转化数'}, inplace=True)print("重命名后的字段:")print(list(df.columns))

# 数值字段列表numeric_cols = ['曝光量', '点击量', '花费_美元', '总转化数', '批准转化数']# 转换为数值类型for col in numeric_cols:    df[col] = pd.to_numeric(df[col], errors='coerce')print("数据类型转换完成")df[numeric_cols].dtypes

# 检查缺失值print("缺失值统计:")print(df.isnull().sum())# 记录清洗前的数量before_drop = len(df)# 删除关键字段为空的行df = df.dropna(subset=['广告ID', '广告系列ID', '曝光量'])# 记录清洗后的数量after_drop = len(df)print(f"\n删除空值记录: {before_drop - after_drop} 条")print(f"清洗后记录数: {after_drop} 条")

# 点击率 CTR = 点击量 / 曝光量 × 100df['点击率_CTR_百分比'] = (df['点击量'] / df['曝光量'].replace(0, np.nan)) * 100df['点击率_CTR_百分比'] = df['点击率_CTR_百分比'].fillna(0).round(4)# 千次曝光成本 CPM = (花费 / 曝光量) × 1000df['千次曝光成本_CPM_美元'] = (df['花费_美元'] / df['曝光量'].replace(0, np.nan)) * 1000df['千次曝光成本_CPM_美元'] = df['千次曝光成本_CPM_美元'].fillna(0).round(2)# 单次点击成本 CPC = 花费 / 点击量df['单次点击成本_CPC_美元'] = df['花费_美元'] / df['点击量'].replace(0, np.nan)df['单次点击成本_CPC_美元'] = df['单次点击成本_CPC_美元'].fillna(0).round(4)# 转化率 = 总转化数 / 点击量 × 100df['转化率_百分比'] = (df['总转化数'] / df['点击量'].replace(0, np.nan)) * 100df['转化率_百分比'] = df['转化率_百分比'].fillna(0).round(4)# 广告支出回报率 ROAS = 批准转化数 / 花费df['广告支出回报率_ROAS'] = df['批准转化数'] / df['花费_美元'].replace(0, np.nan)df['广告支出回报率_ROAS'] = df['广告支出回报率_ROAS'].fillna(0).round(4)# 单次转化成本 CPA = 花费 / 批准转化数df['单次转化成本_CPA_美元'] = df['花费_美元'] / df['批准转化数'].replace(0, np.nan)df['单次转化成本_CPA_美元'] = df['单次转化成本_CPA_美元'].fillna(0).round(4)# 添加ETL时间戳df['ETL处理时间'] = datetime.now()

# 整体数据概览print("=" * 60)print("数据统计报告")print("=" * 60)print(f"\n📊 数据概览:")print(f"   - 总记录数: {len(df):,} 条")print(f"   - 广告系列数: {df['广告系列ID'].nunique()} 个")print(f"   - 广告系列列表: {sorted(df['广告系列ID'].unique())}")print(f"   - 年龄段: {df['年龄段'].unique().tolist()}")print(f"   - 性别: {df['性别'].unique().tolist()}")print(f"   - 兴趣代码数: {df['兴趣代码'].nunique()} 个")# 核心指标统计total_spend = df['花费_美元'].sum()total_impressions = df['曝光量'].sum()total_clicks = df['点击量'].sum()total_approved = df['批准转化数'].sum()avg_roas = df['广告支出回报率_ROAS'].mean()avg_ctr = df['点击率_CTR_百分比'].mean()print(f"\n💰 核心指标:")print(f"   - 总花费: ${total_spend:,.2f}")print(f"   - 总曝光量: {total_impressions:,}")print(f"   - 总点击量: {total_clicks:,}")print(f"   - 总批准转化数: {total_approved:,}")print(f"   - 平均ROAS: {avg_roas:.4f}")print(f"   - 平均CTR: {avg_ctr:.2f}%")

# 创建数据库连接engine = get_db_engine()# 表名（中文表名，便于BI工具识别）table_name = '广告投放效果数据'# 将数据写入MySQLdf.to_sql(table_name, engine, if_exists='replace', index=False, chunksize=1000)print(f"✅ 数据写入成功: {len(df)} 条记录 -> 表 `{table_name}`")# 验证写入结果with engine.connect() as conn:    result = conn.execute(text(f"SELECT COUNT(*) FROM `{table_name}`"))    count = result.fetchone()[0]    print(f"✅ 验证通过: `{table_name}` 表共有 {count} 条记录")

# 查看最终的表字段with engine.connect() as conn:    result = conn.execute(text(f"DESCRIBE `{table_name}`"))    print("\n📋 最终表结构:")    for row in result:        print(f"   - {row[0]}")

# 保存为CSV文件（便于备份或离线分析）df.to_csv('../output/广告投放数据_清洗后.csv', index=False, encoding='utf-8-sig')print("✅ 清洗后数据已保存: output/广告投放数据_清洗后.csv")

┌─────────────┐     ┌─────────────┐     ┌─────────────┐│   Extract   │ ──▶ │  Transform  │ ──▶ │    Load     ││   数据提取   │     │   数据转换   │     │   数据加载   │└─────────────┘     └─────────────┘     └─────────────┘      │                    │                    │      ▼                    ▼                    ▼   CSV文件           清洗+派生指标           MySQL数据库