简介本资源为电子商务沙盘运营与推广课程中“数据魔方——商品及关键词数据分析”章节的配套课件面向电商专业学生、沙盘竞赛选手及需要掌握数据化选品与推广的运营初学者帮助读者理解如何借助数据魔方完成市场需求、市场供给与关键词三层数据分析。压缩包内仅1个pptx文件约2.53MB以幻灯片形式呈现完整教学脉络涵盖数据魔方的产品定位与淘词功能、展现量、点击量、点击率、转化量、转化率、点击花费、平均点击单价、搜索相关性等关键词指标解读以及第一轮第一期、第二期的市场需求数据、市场供给数据与经营分析实例。读者可据此掌握依据需求与价格变化判断商品生命周期、确定采购数量与价格、优化商品关键词与首页详情页展示、制订营销与采购计划的方法并借助城市与人群维度的数据还原市场场景。目前已有69人学习适合课堂学习与沙盘实战对照查阅。1. 商品与关键词数据分析瓶颈往往在最后一公里做电商业务数据分析的人多半碰到过这种场面SQL 写了三十条Python 里跑出七八张图最后要把商品及关键词数据分析做成一份给运营、采购、商品负责人看的 pptx光贴图、调字号、对齐图例就耗掉一下午。下周数据一更新整套动作再来一遍。一份 89 页的报告之所以值得单独拆开讲是因为它代表了一类很常见的交付形态页数多、结构固定、每页基本是「一个商品结论 一张图 一段关键词说明」。用 Excel 数据分析的手工方式做边际成本几乎不下降一旦拆成指标口径、取数链路、分析模型、渲染管道四层它就能收敛成一条每周自动跑的命令。下面按这条链路把每层的做法、参数和坑讲清楚。2. 商品及关键词数据分析的指标口径与取数链路指标口径不统一后面所有图表都是在放大误差。同一个「支付转化率」运营按访客数算商品同学按商品详情页 PV 算两边差出三倍会上吵到散会也吵不出结论。所以第一个动作不是写代码而是把核心指标定义锁死成一张可查阅的口径表并且让口径表本身也进版本管理。2.1 商品维度指标先统一口径再谈建模商品侧的分析指标体系落到日报层面其实就十几个字段但每个字段都要写清楚分子分母、时间粒度和快照规则。指标计算口径建议粒度常见坑GMV下单金额含未支付商品 × 日与成交额混用报表翻倍支付转化率支付买家数 / 商品访客数商品 × 日分母用 PV 会系统性偏低动销率有支付商品数 / 在架商品数类目 × 周在架数要取当日快照不能用当前值件单价支付金额 / 支付件数商品 × 日未剔退款大促期虚高复购率周期内支付≥2 次买家 / 总买家类目 × 月周期定义漂移环比不可比库存周转天数平均库存 / 日均出库商品 × 月平均库存取期初期末会被大促拉偏这张表建议直接落成一张dim_metric_def维表字段包括metric_code、metric_name、formula、grain、owner。报表里的每个数字都带上metric_code出问题时能一层层回溯到公式而不是靠聊天记录对账。提示动销率这类依赖「在架商品数」的指标务必用数仓里按天快照的 SKU 状态表直接查商品主表拿到的永远是「现在还在架上」的那批历史周报会越跑越好看。2.2 关键词底表搜索词、曝光、点击、成交的四表结构关键词分析的最小可用数据集是四张表搜索词表、曝光表、点击表、成交归因表。前两张决定「有没有需求」后两张决定「需求有没有被接住」。表名关键字段说明dwd_search_kwkw、user_id、dt、scene用户实际输入的搜索词注意大小写与空格归一dwd_kw_expokw、item_id、dt、expo_cnt关键词下商品曝光一关键词可对多商品dwd_kw_clickkw、item_id、dt、click_cnt点击行为用于算点击率dwd_kw_orderkw、item_id、dt、pay_amt、pay_cnt归因到关键词的成交归因窗口要写死归一化是这一步最容易被忽略的细节「蓝牙耳机」和「蓝牙 耳机」、全角半角、繁简混用如果不在 ETL 阶段统一同一个词会被拆成四五个长尾词效矩阵直接失真。常见做法是建一张dim_kw_alias同义词表用规则 人工审核的方式维护而不是指望分词模型能自动合并。2.3 用 SQL 把商品宽表和关键词宽表关联合成分析表下面这段是把商品日粒度指标与关键词指标合成一张分析宽表的骨架跑在离线数仓里天级调度。-- 商品关键词分析宽表天级全量重算按 dt 分区 WITH item_base AS ( SELECT dt, item_id, cate_id, SUM(pay_amt) AS pay_amt, -- 商品成交额 COUNT(DISTINCT buyer_id) AS buyer_cnt, -- 支付买家数 COUNT(DISTINCT visitor_id) AS uv, -- 商品访客数 SUM(pay_cnt) AS pay_cnt -- 支付件数 FROM dwd_item_trade_di WHERE dt ${bizdate} GROUP BY dt, item_id, cate_id ), kw_base AS ( SELECT e.dt, e.item_id, COUNT(DISTINCT e.kw) AS kw_cnt, -- 该商品被多少关键词曝光 SUM(e.expo_cnt) AS expo_cnt, SUM(COALESCE(c.click_cnt, 0)) AS click_cnt, SUM(COALESCE(o.pay_amt, 0)) AS kw_pay_amt FROM dwd_kw_expo e LEFT JOIN dwd_kw_click c ON e.dt c.dt AND e.kw c.kw AND e.item_id c.item_id LEFT JOIN dwd_kw_order o ON e.dt o.dt AND e.kw o.kw AND e.item_id o.item_id WHERE e.dt ${bizdate} GROUP BY e.dt, e.item_id ) INSERT OVERWRITE TABLE ads_item_kw_analysis_di PARTITION (dt ${bizdate}) SELECT i.dt, i.item_id, i.cate_id, i.pay_amt, i.buyer_cnt, i.uv, i.buyer_cnt / NULLIF(i.uv, 0) AS pay_cvr, -- 支付转化率 i.pay_amt / NULLIF(i.pay_cnt, 0) AS unit_price, k.kw_cnt, k.expo_cnt, k.click_cnt / NULLIF(k.expo_cnt, 0) AS kw_ctr, -- 关键词点击率 k.kw_pay_amt / NULLIF(k.click_cnt, 0) AS kw_cvr_amt -- 点击到成交金额效率 FROM item_base i LEFT JOIN kw_base k ON i.dt k.dt AND i.item_id k.item_id;这段 SQL 有三个点值得说清。其一NULLIF包裹分母是必须的零曝光商品在新品期非常常见不加会直接报除零错误加了则得到 NULL后续在 Python 里用fillna(0)处理更可控。其二LEFT JOIN方向是「商品保留全部、关键词可以缺失」因为报告里 89 页中有相当一部分是纯商品页关键词只是解释变量。其三${bizdate}这种调度参数写成占位符便于回刷历史分区——回刷时只改日期不碰逻辑。跑完之后建议先做一次分布体检pay_cvr是否落在 0~1 之间、kw_ctr是否有大于 1 的异常值。点击率大于 1 基本可以确定是曝光表去重没做干净或者点击表混进了非曝光位的数据这类问题在建模阶段之前必须清掉。3. Python 商品分层与关键词效率矩阵宽表出来以后才有资格谈模型。数据分析里的模型不是越复杂越好能用一个分箱讲清楚的不要上聚类。这一层的目标是把 89 页报告需要的「结论」提前算好让渲染层只负责画不做判断。3.1 商品 RFM 分层分位阈值与分箱RFM 在商品维度上的映射需要改写R 是最近一次支付距今天数F 是支付频次M 是支付金额。零售和电商的切分点不同用固定阈值容易在小类目上全部落进同一档用分位数更稳。import pandas as pd import numpy as np df pd.read_parquet(ads_item_kw_analysis_di/dt2024-06-01) # 1) 构造 RFM 三列示例中 R 由宽表计算字段带入 rfm df[[item_id, cate_id, recency_days, buyer_cnt, pay_amt]].copy() rfm.columns [item_id, cate_id, R, F, M] # 2) 分位打分R 越小越好所以用 ascendingTrue 后取反 rfm[R_s] pd.qcut(rfm[R], q4, labels[4, 3, 2, 1]).astype(int) rfm[F_s] pd.qcut(rfm[F].rank(methodfirst), q4, labels[1, 2, 3, 4]).astype(int) rfm[M_s] pd.qcut(rfm[M].rank(methodfirst), q4, labels[1, 2, 3, 4]).astype(int) rfm[rfm_score] rfm[R_s] * 100 rfm[F_s] * 10 rfm[M_s] # 3) 落成可读分层用于报告中的商品分组页 def seg(row): if row[R_s] 3 and row[M_s] 3: return 高价值在售 if row[R_s] 2 and row[M_s] 3: return 高价值流失预警 if row[R_s] 3 and row[M_s] 2: return 高频低客单 return 长尾待清理 rfm[segment] rfm.apply(seg, axis1) print(rfm[segment].value_counts())qcut用分位数而不是等差阈值是为了让每一档都有足够样本labels的方向必须对着业务含义调这里 F 和 M 越大越好所以正向映射R 越小越好所以反向。rank(methodfirst)是绕开重复值导致qcut边界报错的常规手段商品成交额出现大量相同值比如都是 99 元时特别容易踩。分层结果直接决定后面 pptx 里商品分组的页数和顺序所以这一步的输出要落盘成item_segment.parquet别只留在内存里。3.2 关键词效率矩阵曝光、点击率、转化率的四象限关键词的结论通常用四象限表达横轴点击率纵轴点击到成交的转化效率气泡大小是曝光量。象限线用类目中位数而不是平均值避免被少数大词拉偏。象限判定条件业务动作明星词CTR ≥ 中位数 且 转化 ≥ 中位数加大投放、保排名引流词CTR ≥ 中位数 且 转化 中位数优化详情页与价格带潜力词CTR 中位数 且 转化 ≥ 中位数优化主图标题提升曝光低效词两者均低于中位数降权或从标题中移除kw df.groupby([kw], as_indexFalse).agg( expo(expo_cnt, sum), click(click_cnt, sum), amt(kw_pay_amt, sum), ) kw[ctr] kw[click] / kw[expo].replace(0, np.nan) kw[cvr_amt] kw[amt] / kw[click].replace(0, np.nan) # 曝光过小的词统计噪声大先过滤再加象限标签 kw kw[kw[expo] 200].dropna(subset[ctr, cvr_amt]) ctr_mid, cvr_mid kw[ctr].median(), kw[cvr_amt].median() kw[quadrant] np.select( [(kw[ctr] ctr_mid) (kw[cvr_amt] cvr_mid), (kw[ctr] ctr_mid) (kw[cvr_amt] cvr_mid), (kw[ctr] ctr_mid) (kw[cvr_amt] cvr_mid)], [明星词, 引流词, 潜力词], default低效词, )expo 200这条阈值是经验值类目流量差异大时要按 p10 分位动态调固定 200 在小类目上会把词全过滤掉报告里会出现空页。np.select的写法比链式apply快一个量级几万关键词量级下差距明显。3.3 关键词与品类的关联度卡方检验快速筛报告里常有一页叫「各品类的高频关键词」如果只按词频排序头部基本被「包邮」「正品」这类通用词占满。用卡方检验衡量关键词与品类的关联强度能自动把通用词压下去。from scipy.stats import chi2_contingency rows [] for kw_name, g in df.groupby(kw): tbl pd.crosstab(g[cate_id], g[item_id].notna()) # 品类 × 是否出现该词 if tbl.shape[0] 2 or tbl.shape[1] 2: continue chi2, p, _, _ chi2_contingency(tbl) rows.append({kw: kw_name, chi2: chi2, p: p, n: len(g)}) assoc pd.DataFrame(rows).query(p 0.05).sort_values(chi2, ascendingFalse)chi2_contingency要求每个单元格期望频数不低于 5小样本品类会触发警告这时要么合并品类要么改用 Fisher 精确检验。输出按chi2降序取每品类 Top10正好对应报告里每个品类一页的结构。4. 用 python-pptx 生成 89 页商品关键词报告前面算出来的 DataFrame本质上是「每页该说什么」的清单。渲染层要做的只有三件事按清单决定页数和顺序、把图和数据塞进版式、保证页码目录自洽。4.1 先定母版骨架再写一行渲染代码不要用默认 blank 版式直接堆控件89 页规模下改一次标题字号就是 89 次改动。正确做法是在母版里预置四种版式封面页、章节页、图表页左图右文、表格页。占位符命名建议用title、subtitle、pic_area、text_area代码里按名字取而不是按索引。from pptx import Presentation from pptx.util import Inches, Pt prs Presentation(template.pptx) # 母版里已定义好 4 种版式 LAYOUT {s.name: s for s in prs.slide_layouts} def add_slide(kind: str): 按版式名新建一页并返回占位符字典 slide prs.slides.add_slide(LAYOUT[kind]) ph {p.placeholder_format.idx: p for p in slide.placeholders} return slide, ph slide, ph add_slide(chart_page) ph[title].text 男装类目高价值在售商品分布 ph[title].text_frame.paragraphs[0].runs[0].font.size Pt(24)按版式名取而不是按数字索引是因为模板被别人调整过一次索引就全错位了按名字取至少能抛出 KeyError 让你立刻发现。字号在这里显式覆盖一次是为了防止运营在母版里把正文改成 28 号导致图被挤出去。4.2 图表页matplotlib 出图与 add_picture 落位参数图表不要用 pptx 原生图表对象理由很实际原生图表的样式调整代码量是 matplotlib 的三倍而且中文字体容易变成方框。先用 matplotlib 按 16:9 尺寸导出 PNG再用add_picture精确落位。import matplotlib matplotlib.use(Agg) import matplotlib.pyplot as plt plt.rcParams[font.sans-serif] [Noto Sans CJK SC] # 中文字体缺了会出方框 plt.rcParams[axes.unicode_minus] False def render_bar(df, path, title): fig, ax plt.subplots(figsize(6.4, 3.6), dpi200) # 对应 6.4×3.6 英寸 ax.barh(df[item_id].astype(str), df[pay_amt], color#3b6ea5) ax.set_title(title, fontsize11) ax.tick_params(labelsize8) fig.tight_layout() fig.savefig(path, transparentFalse) plt.close(fig) # 落位单位是 Inches这里的数值要和母版占位符位置对齐 slide.shapes.add_picture(png_path, leftInches(0.5), topInches(1.4), widthInches(6.4), heightInches(3.6))figsize与Inches保持一致是关键否则图片会被 pptx 拉伸200 dpi 的清晰度也会被浪费。只给width不给height会保持纵横比但容易被母版占位符高度截断所以两个都给、并且按 16:9 算好比例更稳。plt.close(fig)必须写89 次渲染不释放 figure内存会一路涨到进程被杀。4.3 表格页与结论页的自动填充表格数据超过 8 行就必须分页一页塞 30 行没人看。下面这个函数把 DataFrame 切片成多页每页复用同一版式。def fill_table_page(df, page_title, rows_per_page8): for i in range(0, len(df), rows_per_page): chunk df.iloc[i:i rows_per_page] slide, ph add_slide(table_page) ph[title].text f{page_title}{i // rows_per_page 1} shape slide.shapes.add_table( len(chunk) 1, len(chunk.columns), Inches(0.5), Inches(1.4), Inches(9), Inches(0.4 * (len(chunk) 1)) ) tbl shape.table for c, col in enumerate(chunk.columns): tbl.cell(0, c).text str(col) for r, (_, row) in enumerate(chunk.iterrows(), start1): for c, col in enumerate(chunk.columns): tbl.cell(r, c).text f{row[col]:,.2f} if isinstance(row[col], float) else str(row[col])rows_per_page8是按母版行高反推的行高 0.4 英寸、页面可用高度约 5 英寸超过就会溢出到页面外。数字统一走,.2f格式化避免 0.30000000000000004 这种浮点尾巴直接出现在客户面前。4.4 目录页与页码让 89 页自己数得清楚页数一多目录页码必须自动生成手填一定会错。做法是先把所有页面内容排成一个list[dict]的任务队列遍历渲染时记录每页的标题与序号最后再回头插入目录页。plan build_page_plan(seg_df, kw_df, assoc_df) # 返回 [{kind:..., title:...}, ...] toc [] for idx, page in enumerate(plan, start1): slide, ph add_slide(page[kind]) ph[title].text page[title] toc.append((page[section], page[title], idx)) render_body(slide, page) # 分发到图表页/表格页渲染函数 # 目录页用占位符插入到第 2 页位置 toc_slide, toc_ph add_slide(toc_page) toc_ph[text_area].text_frame.text \n.join( f{sec} {t} · P{n} for sec, t, n in toc )build_page_plan建议按「章节 → 品类 → 商品分层」三级展开这样页数与业务维度天然对齐89 页这个量级通常对应 6 个章节、每章 12~16 页。目录页插在渲染之后页码才准如果先建目录页再渲染页码会整体偏移一页。5. 报告可追溯与增量渲染的收尾技巧交付物一旦进入周更节奏重点就从「做出来」转到「改得动」。89 页的报告改一个数字重跑一遍是几分钟但如果每次都要全量重算很快会变成没人敢碰的脚本。5.1 把口径和数据指纹写进备注页每页的 pptx notes 里写入三行信息所用指标的metric_code、数据分区日期、生成脚本的 git commit 短 hash。这不需要额外系统pptx 自带的备注页就够。notes slide.notes_slide.notes_text_frame notes.text fmetric{,.join(page[metrics])}\ndt{bizdate}\ncommit{get_git_sha()}出问题时任何一个人打开备注页就知道这页数字从哪来不用翻聊天记录找人问。get_git_sha()用subprocess.check_output([git, rev-parse, --short, HEAD])取即可注意捕获异常脚本在非 git 环境里跑要能降级成空字符串。5.2 只重渲染变化的页增量渲染的判断依据是页面内容的哈希把每页的参数序列化成 JSON 取 sha1和上一版记录比对变了才重新出图。import hashlib, json def page_hash(page: dict) - str: payload json.dumps(page, sort_keysTrue, defaultstr, ensure_asciiFalse) return hashlib.sha1(payload.encode(utf-8)).hexdigest()配合一份render_manifest.json记录每页哈希与产物路径重跑时跳过未变化的页。大促期间商品池一天一变命中率可能只有三成日常平销期命中率通常能到七成以上渲染时间按比例下降。5.3 交付前的三处自检自检项检查方式不通过的典型原因页数与目录一致比对len(prs.slides)与 plan 长度表格分页函数多切了一页无空图页遍历 shapes 检查 picture 数量某品类过滤后无数据数字量级合理抽取 10 页人工核对单位混用元 / 万元第三项最容易被跳过也最容易出事。单位在模板里是「万元」脚本里按「元」取数页面不会报错只会安静地多出四个零。稳妥做法是在渲染函数里对pay_amt做一次显式单位换算并把单位字符串拼进标题让标题自己声明口径而不是靠读者去猜图例。本文还有配套的精品资源点击获取