社交电商系统复盘：六步搭建用户增长与转化分析平台

一、复盘目标与数据基础搭建

复盘的核心是量化分析，你需要建立完整的数据采集体系。以下是必须采集的基础数据维度：

1.1 用户行为数据采集

在页面头部引入埋点SDK：

1.2 数据库表结构设计

创建MySQL数据表：

CREATE TABLE user_events (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id VARCHAR(50) NOT NULL,
event_type VARCHAR(50) NOT NULL COMMENT 'pageview, product_click, share, purchase',
event_data JSON NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_event_type (event_type),
INDEX idx_created_at (created_at)
);
CREATE TABLE user_relations (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
inviter_id VARCHAR(50) NOT NULL COMMENT '邀请人ID',
invitee_id VARCHAR(50) NOT NULL COMMENT '被邀请人ID',
level INT DEFAULT 1 COMMENT '关系层级',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY uk_invitee (invitee_id),
INDEX idx_inviter (inviter_id)
);

二、核心指标计算与监控

2.1 每日关键指标计算脚本

创建daily_metrics.py：

import pymysql
from datetime import datetime, timedelta
def calculate_daily_metrics():
conn = pymysql.connect(
host='localhost',
user='your_username',
password='your_password',
database='social_ecommerce',
charset='utf8mb4'
)
yesterday = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')
with conn.cursor() as cursor:
新用户数
cursor.execute("""
SELECT COUNT(DISTINCT user_id)
FROM user_events
WHERE DATE(created_at) = %s
AND event_type = 'register'
""", (yesterday,))
new_users = cursor.fetchone()[0]
分享转化率
cursor.execute("""
SELECT
COUNT(DISTINCT CASE WHEN event_type = 'share' THEN user_id END) as sharers,
COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) as buyers
FROM user_events
WHERE DATE(created_at) = %s
""", (yesterday,))
sharers, buyers = cursor.fetchone()
share_conversion = buyers / sharers if sharers > 0 else 0
插入计算结果
cursor.execute("""
INSERT INTO daily_metrics (date, new_users, share_conversion_rate)
VALUES (%s, %s, %s)
ON DUPLICATE KEY UPDATE
new_users = VALUES(new_users),
share_conversion_rate = VALUES(share_conversion_rate)
""", (yesterday, new_users, share_conversion))
conn.commit()
conn.close()
if __name__ == '__main__':
calculate_daily_metrics()

2.2 实时监控看板

创建dashboard.html实时展示数据：

今日新增用户
加载中...


分享转化率
加载中...

三、用户裂变路径分析

3.1 邀请关系链查询

创建invitation_analysis.sql分析用户裂变深度：

WITH RECURSIVE invitation_chain AS (
-- 基础查询：找到所有一级邀请
SELECT
inviter_id,
invitee_id,
1 as level,
inviter_id as root_inviter
FROM user_relations
WHERE inviter_id = '起始用户ID'
UNION ALL
-- 递归查询：逐级向下查找
SELECT
ur.inviter_id,
ur.invitee_id,
ic.level + 1,
ic.root_inviter
FROM user_relations ur
INNER JOIN invitation_chain ic ON ur.inviter_id = ic.invitee_id
)
SELECT
level,
COUNT(DISTINCT invitee_id) as invitee_count,
COUNT(DISTINCT CASE
WHEN EXISTS (
SELECT 1 FROM user_events ue
WHERE ue.user_id = ic.invitee_id
AND ue.event_type = 'purchase'
) THEN invitee_id
END) as purchasing_invitees
FROM invitation_chain ic
GROUP BY level
ORDER BY level;

3.2 裂变效果可视化

使用ECharts生成裂变关系图：

// 安装ECharts：npm install echarts
import  as echarts from 'echarts';
function renderInvitationChart(data) {
const chart = echarts.init(document.getElementById('invitation-chart'));
const option = {
tooltip: {},
series: [{
type: 'graph',
layout: 'force',
data: data.nodes,
links: data.links,
roam: true,
label: {
show: true,
position: 'right'
},
force: {
repulsion: 100,
edgeLength: 50
}
}]
};
chart.setOption(option);
}
// 获取数据并渲染
fetch('/api/invitation/network')
.then(response => response.json())
.then(renderInvitationChart);

四、商品传播热度分析

4.1 传播路径追踪

创建product_spread.sql分析商品传播路径：

-- 创建商品传播记录表
CREATE TABLE product_spread_paths (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
product_id VARCHAR(50) NOT NULL,
sharer_id VARCHAR(50) NOT NULL,
viewer_id VARCHAR(50) NOT NULL,
view_source VARCHAR(20) NOT NULL COMMENT 'direct, shared, recommended',
share_depth INT DEFAULT 0 COMMENT '分享层级',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_product (product_id),
INDEX idx_sharer (sharer_id),
INDEX idx_viewer (viewer_id)
);
-- 查询商品传播效果
SELECT
p.product_id,
p.product_name,
COUNT(DISTINCT ps.sharer_id) as total_sharers,
COUNT(DISTINCT ps.viewer_id) as total_viewers,
COUNT(DISTINCT CASE
WHEN ue.event_type = 'purchase' AND ue.event_data->>'$.product_id' = p.product_id
THEN ue.user_id
END) as purchases_from_spread,
ROUND(
COUNT(DISTINCT CASE
WHEN ue.event_type = 'purchase' AND ue.event_data->>'$.product_id' = p.product_id
THEN ue.user_id
END)  100.0 / COUNT(DISTINCT ps.viewer_id), 2
) as conversion_rate
FROM products p
LEFT JOIN product_spread_paths ps ON p.product_id = ps.product_id
LEFT JOIN user_events ue ON ps.viewer_id = ue.user_id
GROUP BY p.product_id, p.product_name
ORDER BY total_viewers DESC
LIMIT 10;

4.2 热门商品识别

创建hot_products.py识别传播热度高的商品：

def identify_hot_products(days=7):
"""识别最近N天传播热度最高的商品"""
query = """
SELECT
product_id,
COUNT(DISTINCT sharer_id) as share_count,
COUNT(DISTINCT viewer_id) as view_count,
COUNT(DISTINCT CASE
WHEN EXISTS (
SELECT 1 FROM user_events ue
WHERE ue.user_id = psp.viewer_id
AND ue.event_type = 'purchase'
AND JSON_EXTRACT(ue.event_data, '$.product_id') = psp.product_id
) THEN viewer_id
END) as purchase_count
FROM product_spread_paths psp
WHERE created_at >= DATE_SUB(NOW(), INTERVAL %s DAY)
GROUP BY product_id
HAVING share_count >= 5  -- 至少有5个不同分享者
ORDER BY view_count DESC
LIMIT 20
"""
执行查询并返回结果
... 数据库查询代码
return hot_products

五、转化漏斗分析

5.1 完整转化漏斗SQL

创建conversion_funnel.sql：

SELECT
DATE(created_at) as date,
COUNT(DISTINCT CASE WHEN event_type = 'pageview' THEN user_id END) as visitors,
COUNT(DISTINCT CASE WHEN event_type = 'product_view' THEN user_id END) as product_viewers,
COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN user_id END) as cart_adders,
COUNT(DISTINCT CASE WHEN event_type = 'checkout_start' THEN user_id END) as checkout_starters,
COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) as purchasers,
ROUND(
COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END)  100.0 /
COUNT(DISTINCT CASE WHEN event_type = 'pageview' THEN user_id END), 2
) as overall_conversion_rate
FROM user_events
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY date DESC;

5.2 漏斗阶段流失分析

创建funnel_analysis.py分析各阶段流失原因：

def analyze_funnel_dropoff():
"""分析漏斗各阶段流失情况"""
analysis_query = """
-- 查看从加购到放弃的用户行为
SELECT
ue.user_id,
ue.event_type,
ue.event_data,
TIMESTAMPDIFF(MINUTE,
MAX(CASE WHEN event_type = 'add_to_cart' THEN created_at END),
NOW()
) as minutes_since_cart
FROM user_events ue
WHERE ue.user_id IN (
SELECT DISTINCT user_id
FROM user_events
WHERE event_type = 'add_to_cart'
AND created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
)
AND ue.created_at >= DATE_SUB(NOW(), INTERVAL 2 HOUR)
GROUP BY ue.user_id, ue.event_type, ue.event_data
HAVING MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) = 0
ORDER BY minutes_since_cart DESC;
"""
执行查询并分析结果
... 数据库查询和分析代码
return dropoff_analysis

六、复盘报告自动生成

6.1 报告数据聚合

创建report_generator.py自动生成复盘报告：

def generate_weekly_report(start_date, end_date):
"""生成周度复盘报告"""
report_data = {
'period': f'{start_date} 至 {end_date}',
'metrics': {},
'top_performers': {},
'issues': []
}
获取核心指标
metrics_query = """
SELECT
'new_users' as metric,
COUNT(DISTINCT user_id) as value
FROM user_events
WHERE event_type = 'register'
AND created_at BETWEEN %s AND %s
UNION ALL
SELECT
'total_orders' as metric,
COUNT(DISTINCT user_id) as value
FROM user_events
WHERE event_type = 'purchase'
AND created_at BETWEEN %s AND %s
UNION ALL
SELECT
'avg_order_value' as metric,
AVG(CAST(JSON_EXTRACT(event_data, '$.amount') AS DECIMAL(10,2))) as value
FROM user_events
WHERE event_type = 'purchase'
AND created_at BETWEEN %s AND %s
"""
执行查询并填充report_data
... 数据库查询代码
生成报告文件
report_content = f"""
社交电商复盘报告
报告周期：{report_data['period']}
核心指标：
- 新增用户：{report_data['metrics'].get('new_users', 0)}
- 总订单数：{report_data['metrics'].get('total_orders', 0)}
- 客单价：{report_data['metrics'].get('avg_order_value', 0):.2f}
表现最佳商品：
{chr(10).join([f"- {item['name']}: {item['sales']}单" for item in report_data['top_performers'].get('products', [])])}
待优化问题：
{chr(10).join([f"- {issue}" for issue in report_data['issues']])}
"""
保存报告
with open(f'report_{start_date}_{end_date}.txt', 'w', encoding='utf-8') as f:
f.write(report_content)
return report_content

6.2 自动化部署脚本

创建deploy.sh一键部署分析系统：

!/bin/bash
1. 创建数据库
echo "创建数据库..."
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS social_ecommerce;"
2. 导入表结构
echo "导入表结构..."
mysql -u root -p social_ecommerce < schema.sql
3. 安装Python依赖
echo "安装Python依赖..."
pip install -r requirements.txt
4. 设置定时任务
echo "设置定时任务..."
(crontab -l 2>/dev/null; echo "0 2    /usr/bin/python3 /path/to/daily_metrics.py") | crontab -
(crontab -l 2>/dev/null; echo "0 3   1 /usr/bin/python3 /path/to/generate_weekly_report.py") | crontab -
5. 启动监控服务
echo "启动监控服务..."
nohup python3 monitor_service.py > monitor.log 2>&1 &
echo "部署完成！"

按照以上六个步骤，你可以搭建完整的社交电商复盘系统。每天运行一次数据计算脚本，每周生成复盘报告，实时监控关键指标。所有代码均可直接复制使用，只需修改数据库连接信息即可部署。