电商数据分析 7 大核心场景｜SQL 实战版（含表结构 + 汇报话术）

目录项目1：流量与GMV日报/周报/月报一、业务场景二、表结构三、实战SQL四、日报/周报/月报切换方法五、业务汇报话术项目2：活动效果分析——大促/日常活动一、业务场景二、表结构三、实战SQL1. 活动整体效果2. 活动 vs 日常 对比3. 新客 vs 老客贡献（判断活动拉新效果）四、业务汇报话术项目3：购物漏斗转化分析（流量流失定位）一、业务场景二、表结构三、实战SQL版本1：标准漏斗（统计各环节人数）版本2：进阶版（同一个用户完整路径）四、业务分析逻辑五、业务汇报话术项目4：用户RFM价值分层一、业务场景二、业务口径三、表结构四、实战SQL步骤1：计算每个用户的R、F、M值步骤2：计算R、F、M的中位数（分界值）步骤3：给用户打R/F/M高低分，完成8类分层四、工作应用五、业务汇报话术项目5：商品动销 爆款/滞销分析一、业务场景二、表结构三、实战SQL1. 每个商品销量、GMV、同类目排名2. 爆款/滞销款标记3. 类目结构健康度分析（头部商品贡献占比）四、业务汇报话术项目6：渠道质量 投放ROI分析一、业务场景二、表结构三、实战SQL1. 各渠道基础效果（引流、转化、GMV）2. 渠道ROI与获客成本四、业务判断标准五、业务汇报话术项目7：用户留存分析（次日/7日/30日留存）一、业务场景二、表结构三、实战SQL（电商留存口径）四、留存判断标准五、业务汇报话术项目1：流量与GMV日报/周报/月报一、业务场景运营/老板每日/每周/每月需要监控平台核心数据，了解业务整体表现，定位数据波动原因，如GMV突降、转化率下滑，支撑日常决策与业务调整。核心需求：监控GMV、访客、订单、客单价、转化率，按日/周/月、渠道、类目拆分，对比同比/环比。二、表结构-- 1. 用户访问表:流量数据，统计访客 CREATE TABLE user_visit ( id BIGINT, user_id BIGINT, visit_time DATETIME, channel VARCHAR(32) -- 引流渠道：抖音/天猫/京东/自然流量 ); -- 2. 用户信息表:关联用户渠道 CREATE TABLE user_info ( user_id BIGINT, register_time DATETIME, channel VARCHAR(32) ); -- 3. 订单表:核心交易数据 CREATE TABLE order_info ( order_id BIGINT, user_id BIGINT, sku_id BIGINT, pay_time DATETIME, pay_amount DECIMAL(12,2), order_status TINYINT -- 1=支付成功，0=退款/关闭 ); -- 4. 商品类目表:拆分类目维度 CREATE TABLE sku_info ( sku_id BIGINT, category1 VARCHAR(32), -- 一级类目：美妆/口腔护理 category2 VARCHAR(32) -- 二级类目：美妆-口红/口腔-牙膏 );三、实战SQLWITH -- 1. 每日UV:访客数，去重 daily_uv AS ( SELECT DATE(visit_time) AS dt, channel, COUNT(DISTINCT user_id) AS uv FROM user_visit GROUP BY dt, channel ), -- 2. 每日交易数据:GMV、订单、买家数 daily_trade AS ( SELECT DATE(o.pay_time) AS dt, u.channel, s.category1, s.category2, COUNT(DISTINCT o.order_id) AS order_cnt, -- 支付订单数 COUNT(DISTINCT o.user_id) AS buyer_cnt, -- 支付买家数 SUM(o.pay_amount) AS gmv -- GMV（支付成功金额） FROM order_info o LEFT JOIN user_info u ON o.user_id = u.user_id LEFT JOIN sku_info s ON o.sku_id = s.sku_id WHERE o.order_status = 1 -- 只统计支付成功订单 GROUP BY dt, u.channel, s.category1, s.category2 ), -- 3. 合并流量+交易数据 daily_all AS ( SELECT t.dt, t.channel, t.category1, t.category2, u.uv, t.order_cnt, t.buyer_cnt, t.gmv, ROUND(t.gmv / t.order_cnt, 2) AS per_order_price, -- 客单价 ROUND(t.buyer_cnt / u.uv, 4) AS cvr -- 转化率（支付买家/访客） FROM daily_trade t LEFT JOIN daily_uv u ON t.dt = u.dt AND t.channel = u.channel -- 按日期+渠道关联 ) -- 4. 最终报表+同比/环比 SELECT dt, channel, category1, category2, uv, order_cnt, buyer_cnt, gmv, per_order_price, cvr, -- 环比（与前一天对比） ROUND((gmv / LAG(gmv,1) OVER (PARTITION BY channel ORDER BY dt) - 1), 2) AS gmv_chain_ratio, -- 同比（与去年同一天对比） ROUND((gmv / LAG(gmv,365) OVER (PARTITION BY channel ORDER BY dt) - 1), 2) AS gmv_year_ratio FROM daily_all ORDER BY dt DESC, channel, gmv DESC;四、日报/周报/月报切换方法日报：保留DATE(visit_time) AS dt（按天分组）周报：改为DATE_FORMAT(visit_time, '%Y-%u') AS dt（按年-周分组）月报：改为DATE_FORMAT(visit_time, '%Y-%m') AS dt（按年-月分组）其他逻辑不变，自动生成对应周期报表。五、业务汇报话术今日/本周/本月 GMV XX 万，环比 +X%，同比 +X%，整体表现良好/需关注。核心渠道：XX 渠道贡献 X% GMV，转化率 XX，高于其他渠道，可维持投入。核心类目：XX 类目 GMV 占比最高，客单价 XX 元，类目健康度良好。异常点：转化率环比下滑 X%，需排查流量质量或活动承接问题。项目2：活动效果分析——大促/日常活动一、业务场景运营做完活动，如：618/会员日/新品首发/满减活动 后，需要评估活动效果：GMV是否达标、拉新效果如何、投入产出比是否合理，判断活动是否值得继续做，为后续活动优化提供依据。核心需求：对比活动期与日常数据，分析活动GMV、新老客贡献、渠道效果，评估活动价值。二、表结构-- 1. 用户表 CREATE TABLE user_info ( user_id BIGINT, register_time DATETIME, channel VARCHAR(32) -- 引流渠道 ); -- 2. 订单表 CREATE TABLE order_info ( order_id BIGINT, user_id BIGINT, pay_time DATETIME, pay_amount DECIMAL(12,2), order_status TINYINT, -- 1=支付成功 is_activity TINYINT -- 1=活动订单，0=日常订单 ); -- 3. 行为表 CREATE TABLE behavior ( user_id BIGINT, visit_time DATETIME, cart_time DATETIME, order_time DATETIME );三、实战SQL1. 活动整体效果SELECT DATE(pay_time) AS dt, COUNT(DISTINCT order_id) AS order_cnt, COUNT(DISTINCT user_id) AS user_cnt, SUM(pay_amount) AS gmv, ROUND(SUM(pay_amount)/COUNT(DISTINCT order_id),2) AS per_order_price FROM order_info WHERE order_status = 1 AND is_activity = 1 -- 只看活动订单 GROUP BY dt ORDER BY dt;2. 活动 vs 日常 对比SELECT '活动期' AS period, COUNT(DISTINCT user_id) AS user_cnt, SUM(pay_amount) AS gmv, ROUND(SUM(pay_amount)/COUNT(DISTINCT user_id),2) AS avg_user_gmv FROM order_info WHERE order_status=1 AND is_activity=1 UNION ALL SELECT '日常期' AS period, COUNT(DISTINCT user_id) AS user_cnt, SUM(pay_amount) AS gmv, ROUND(SUM(pay_amount)/COUNT(DISTINCT user_id),2) AS avg_user_gmv FROM order_info WHERE order_status=1 AND is_activity=0;3. 新客 vs 老客贡献（判断活动拉新效果）WITH first_buy AS ( -- 计算每个用户的首单时间 SELECT user_id, MIN(pay_time) first_time FROM order_info WHERE order_status=1 GROUP BY user_id ) SELECT -- 标记新客（活动期首单）、老客（活动期非首单） CASE WHEN o.pay_time = fb.first_time THEN '新客' ELSE '老客' END AS user_type, COUNT(DISTINCT o.user_id) AS user_cnt, SUM(o.pay_amount) AS gmv, ROUND(SUM(o.pay_amount)/COUNT(DISTINCT o.user_id),2) AS avg_user_gmv FROM order_info o JOIN first_buy fb ON o.user_id = fb.user_id WHERE o.order_status=1 AND o.is_activity=1 GROUP BY user_type;四、业务汇报话术本次活动累计GMV XX 万，比日常提升 XX%，活动效果显著/未达预期。活动期间新客占比 XX%，拉新效果良好，但老客贡献GMV占比更高（XX%），核心依赖老客复购。投入产出比XX，建议后续活动重点针对老客做复购激励，新客侧重低价引流，提升活动性价比。项目3：购物漏斗转化分析（流量流失定位）一、业务场景运营/产品经常困惑：“流量不少，但下单的人很少”，需要定位购物链路中流失最严重的环节，针对性优化页面、流程或运营策略，提升整体转化率。核心需求：梳理“浏览→加购→下单→支付”全链路，计算各环节转化率，定位核心流失环节，给出优化方向。