阿里巴巴大数据面试SQL-直通车关键词ROI

数据仓库晨曦

发布于 2026-07-14 19:59:33

400

一、题目背景

直通车是淘宝/天猫商家最核心的付费推广工具——商家为关键词出价（bid），用户搜索该词时广告展现在搜索结果中，按点击扣费（CPC）。数据分析师需要帮商家计算每个关键词的投入产出比（ROI），找出"高花费低转化"的烧钱词和"低花费高转化"的潜力词，优化投放策略。

业务场景：一个天猫店铺每天的直通车预算5000元，投了50个关键词。运营需要每周出"关键词ROI报表"——哪些词ROI>3值得加预算，哪些词ROI<1需要降价或暂停。这道SQL就是该报表的核心查询。

二、题目

现有一张直通车广告投放表 t15_ad_log，记录了每个关键词每日的投放数据。请计算各关键词的ROI（投入产出比）和CPA（单次转化成本），并找出ROI >= 3的优质关键词和ROI < 1的亏损关键词。

ROI = 成交金额(conversion_value) / 广告花费(cost) CPA = 广告花费(cost) / 转化次数(conversion) CTR = 点击次数(click) / 曝光次数(impression)

直通车广告投放表 t15_ad_log：

+----------+-------------+-------+-------------+--------+----------+-------------+-------------------+
| keyword  |    date     |  bid  | impression  | click  |   cost   | conversion  | conversion_value  |
+----------+-------------+-------+-------------+--------+----------+-------------+-------------------+
| 连衣裙      | 2023-03-01  | 2.50  | 15000       | 450    | 1200.00  | 25          | 7500.00           |
| 连衣裙      | 2023-03-02  | 2.50  | 12000       | 380    | 1050.00  | 20          | 5800.00           |
| 连衣裙      | 2023-03-03  | 2.80  | 18000       | 520    | 1600.00  | 30          | 9000.00           |
| 运动鞋      | 2023-03-01  | 3.00  | 8000        | 240    | 780.00   | 8           | 2400.00           |
| 运动鞋      | 2023-03-02  | 3.00  | 9500        | 280    | 900.00   | 12          | 3600.00           |
| 运动鞋      | 2023-03-03  | 3.20  | 10000       | 300    | 1050.00  | 10          | 2800.00           |
| 蓝牙耳机     | 2023-03-01  | 4.00  | 5000        | 150    | 650.00   | 5           | 1500.00           |
| 蓝牙耳机     | 2023-03-02  | 4.00  | 4500        | 120    | 520.00   | 3           | 900.00            |
| 蓝牙耳机     | 2023-03-03  | 3.80  | 6000        | 180    | 750.00   | 6           | 1800.00           |
| 充电宝      | 2023-03-01  | 1.50  | 20000       | 600    | 950.00   | 45          | 4500.00           |
| 充电宝      | 2023-03-02  | 1.50  | 18000       | 550    | 880.00   | 40          | 3800.00           |
| 充电宝      | 2023-03-03  | 1.60  | 22000       | 680    | 1150.00  | 50          | 5200.00           |
+----------+-------------+-------+-------------+--------+----------+-------------+-------------------+

bid: 出价(元/次), impression: 曝光量, click: 点击量, cost: 花费(元), conversion: 转化次数, conversion_value: 成交金额(元)

三、思路分析

核心是按关键词聚合 + 核心指标计算 + ROI分层：

按关键词聚合：SUM 汇总 impression/click/cost/conversion/conversion_value
计算核心指标：CTR = click/impression, CPC = cost/click, ROI = conversion_value/cost, CPA = cost/conversion
ROI分层：CASE WHEN 分为 "ROI≥3优质"、"ROI 1-3一般"、"ROI<1亏损"
建议输出：为每个关键词生成投放建议（加预算/维持/降价/暂停）

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

步骤1：按关键词汇总广告数据

select keyword,
       sum(impression) as total_imp,
       sum(click) as total_click,
       sum(cost) as total_cost,
       sum(conversion) as total_conv,
       sum(conversion_value) as total_conv_value
from t15_ad_log
group by keyword

执行结果

+----------+------------+--------------+-------------+-------------+-------------------+
| keyword  | total_imp  | total_click  | total_cost  | total_conv  | total_conv_value  |
+----------+------------+--------------+-------------+-------------+-------------------+
| 连衣裙      | 45000      | 1350         | 3850.00     | 75          | 22300.00          |
| 运动鞋      | 27500      | 820          | 2730.00     | 30          | 8800.00           |
| 蓝牙耳机     | 15500      | 450          | 1920.00     | 14          | 4200.00           |
| 充电宝      | 60000      | 1830         | 2980.00     | 135         | 13500.00          |
+----------+------------+--------------+-------------+-------------+-------------------+
4 rows selected (0.501 seconds)(https://www.dwsql.com)

步骤2：计算CTR/CPC/ROI/CPA并分层

执行SQL

select keyword,
       total_imp, total_click, total_cost, total_conv, total_conv_value,
       round(total_click * 1.0 / nullif(total_imp, 0), 4) as ctr,
       round(total_cost / nullif(total_click, 0), 2) as cpc,
       round(total_conv_value / nullif(total_cost, 0), 2) as roi,
       round(total_cost / nullif(total_conv, 0), 2) as cpa,
       case
           when total_conv_value / nullif(total_cost, 0) >= 3then'优质-建议加预算'
           when total_conv_value / nullif(total_cost, 0) >= 1then'一般-维持观察'
           else'亏损-建议降价或暂停'
       endas suggestion
from (
    select keyword,
           sum(impression) as total_imp,
           sum(click) as total_click,
           sum(cost) as total_cost,
           sum(conversion) as total_conv,
           sum(conversion_value) as total_conv_value
    from t15_ad_log
    groupby keyword
) t
orderby roi desc

执行结果

+----------+------------+--------------+-------------+-------------+-------------------+---------+-------+-------+---------+-------------+
| keyword  | total_imp  | total_click  | total_cost  | total_conv  | total_conv_value  |   ctr   |  cpc  |  roi  |   cpa   | suggestion  |
+----------+------------+--------------+-------------+-------------+-------------------+---------+-------+-------+---------+-------------+
| 连衣裙      | 45000      | 1350         | 3850.00     | 75          | 22300.00          | 0.0300  | 2.85  | 5.79  | 51.33   | 优质-建议加预算    |
| 充电宝      | 60000      | 1830         | 2980.00     | 135         | 13500.00          | 0.0305  | 1.63  | 4.53  | 22.07   | 优质-建议加预算    |
| 运动鞋      | 27500      | 820          | 2730.00     | 30          | 8800.00           | 0.0298  | 3.33  | 3.22  | 91.00   | 优质-建议加预算    |
| 蓝牙耳机     | 15500      | 450          | 1920.00     | 14          | 4200.00           | 0.0290  | 4.27  | 2.19  | 137.14  | 一般-维持观察     |
+----------+------------+--------------+-------------+-------------+-------------------+---------+-------+-------+---------+-------------+
4 rows selected (0.616 seconds)(https://www.dwsql.com)

连衣裙ROI=5.79最高，投产比接近6倍——每花1元广告费带来5.79元成交，强烈建议追加预算。蓝牙耳机ROI=2.19略高于盈亏线，但CPA高达137元（获取一个转化要花137元广告费），需要优化落地页或降低出价。

五、常见坑点

坑1：混淆ROI和ROAS

ROI = (收入-成本) / 成本，是利润率视角。本题计算的是ROAS（Return on Ad Spend）= 成交额 / 广告花费。在面试中如果直接说"ROI=5.79"，面试官可能追问"你算的ROI是毛利ROI还是GMV ROI？"。明确口径：本题是GMV/花费。

坑2：忽略NULLIF导致除零报错

click/conversion/cost 都可能为0（关键词没有任何点击或转化），不加 NULLIF 会报除零错误。但 NULLIF 返回 NULL 后，ROI/CPA 也是 NULL——不投放的关键词不会有指标，需要在分析前过滤。

坑3：转化归因窗口

直通车转化通常有15天归因窗口——用户点击广告后15天内下单都算该广告的转化。如果只统计当天点击+当天转化的组合，会严重低估ROI。实际中需要关联点击表和订单表，通过归因逻辑匹配。

六、举一反三

关键词分时ROI：GROUP BY keyword + hour，分析不同时段的ROI差异——某些词在晚上8-10点ROI高、在凌晨ROI低，根据时段调价（分时折扣）
搜索词 vs 投放词差异：关联搜索词报告（用户实际搜索了什么词触发了广告），发现"连衣裙"广告被"裙子"、"长裙"等搜索词触发，扩展否定词或精准匹配
质量分分析：CTR低但出价高的词，往往质量分低、实际CPC高。CTR < 2% 的关键词需要优化创意（主图、标题）
预算分配优化：当总预算5000元时，按ROI从高到低排序，逐一分配预算直到预算耗尽——这是最优预算分配的贪心算法

七、知识点总结

考点	说明
GROUP BY 关键词聚合	多天数据汇总到关键词粒度
复合指标计算	CTR=click/imp, ROI=value/cost, CPA=cost/conv
NULLIF 防除零	分母为0时返回NULL而非报错
CASE WHEN ROI分层	≥3优质 / 1-3一般 / <1亏损
业务建议输出	SQL不止算数字，还要给出可执行的投放建议

八、建表语句和数据插入

CREATE TABLE t15_ad_log (
    keyword          stringCOMMENT'关键词',
    date             stringCOMMENT'日期',
    bid              decimal(10,2) COMMENT'出价(元/次)',
    impression       intCOMMENT'曝光量',
    click            intCOMMENT'点击量',
    cost             decimal(10,2) COMMENT'广告花费(元)',
    conversion       intCOMMENT'转化次数',
    conversion_value decimal(10,2) COMMENT'成交金额(元)'
) COMMENT'直通车广告投放表';

INSERTINTO t15_ad_log VALUES
('连衣裙',   '2023-03-01', 2.50, 15000, 450, 1200.00, 25, 7500.00),
('连衣裙',   '2023-03-02', 2.50, 12000, 380, 1050.00, 20, 5800.00),
('连衣裙',   '2023-03-03', 2.80, 18000, 520, 1600.00, 30, 9000.00),
('运动鞋',   '2023-03-01', 3.00, 8000,  240, 780.00,  8,  2400.00),
('运动鞋',   '2023-03-02', 3.00, 9500,  280, 900.00,  12, 3600.00),
('运动鞋',   '2023-03-03', 3.20, 10000, 300, 1050.00, 10, 2800.00),
('蓝牙耳机', '2023-03-01', 4.00, 5000,  150, 650.00,  5,  1500.00),
('蓝牙耳机', '2023-03-02', 4.00, 4500,  120, 520.00,  3,  900.00),
('蓝牙耳机', '2023-03-03', 3.80, 6000,  180, 750.00,  6,  1800.00),
('充电宝',   '2023-03-01', 1.50, 20000, 600, 950.00,  45, 4500.00),
('充电宝',   '2023-03-02', 1.50, 18000, 550, 880.00,  40, 3800.00),
('充电宝',   '2023-03-03', 1.60, 22000, 680, 1150.00, 50, 5200.00);

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-07-11，如有侵权请联系 cloudcommunity@tencent.com 删除

sql