跳到主要内容

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

一、题目背景​

直通车是淘宝/天猫商家最核心的付费推广工具——商家为关键词出价（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 | 25         | 7500.00          |
| 连衣裙   | 2023-03-02| 2.50 | 12000      | 380   | 1050 | 20         | 5800.00          |
| 连衣裙   | 2023-03-03| 2.80 | 18000      | 520   | 1600 | 30         | 9000.00          |
| 运动鞋   | 2023-03-01| 3.00 | 8000       | 240   | 780  | 8          | 2400.00          |
| 运动鞋   | 2023-03-02| 3.00 | 9500       | 280   | 900  | 12         | 3600.00          |
| 运动鞋   | 2023-03-03| 3.20 | 10000      | 300   | 1050 | 10         | 2800.00          |
| 蓝牙耳机 | 2023-03-01| 4.00 | 5000       | 150   | 650  | 5          | 1500.00          |
| 蓝牙耳机 | 2023-03-02| 4.00 | 4500       | 120   | 520  | 3          | 900.00           |
| 蓝牙耳机 | 2023-03-03| 3.80 | 6000       | 180   | 750  | 6          | 1800.00          |
| 充电宝   | 2023-03-01| 1.50 | 20000      | 600   | 950  | 45         | 4500.00          |
| 充电宝   | 2023-03-02| 1.50 | 18000      | 550   | 880  | 40         | 3800.00          |
| 充电宝   | 2023-03-03| 1.60 | 22000      | 680   | 1150 | 50         | 5200.00          |
+----------+----------+-------+------------+-------+------+------------+------------------+

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

三、思路分析​

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

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

四、逐步推导​

步骤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         |
+----------+-----------+-------------+------------+------------+------------------+

步骤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) >= 3 then '优质-建议加预算'
           when total_conv_value / nullif(total_cost, 0) >= 1 then '一般-维持观察'
           else '亏损-建议降价或暂停'
       end as 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
    group by keyword
) t
order by 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 | 一般-维持观察      |
+----------+-----------+-------------+------------+------------+------------------+--------+------+------+-------+-------------------+

连衣裙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。实际中需要关联点击表和订单表，通过归因逻辑匹配。

六、举一反三​

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

七、知识点总结​

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

八、建表语句和数据插入​

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

INSERT INTO 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);
📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

微信公众号二维码
💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题

交流微信二维码

你可能还想看

相关题目
13. 购物车分析01. 用户行为轨迹02. 双11 GMV