逻辑回归 Codelab

1. 简介

在本 Codelab 中，您将学习如何使用逻辑回归了解性别、年龄段、展示时间和浏览器类型等特征与用户点击广告的可能性之间的关联程度。

前提条件

要完成本 Codelab，您需要足够的高质量广告系列数据来建立模型。

2. 选择广告系列

首先，选择一个包含大量优质数据的旧广告系列。如果您不知道哪个广告系列可能包含最优质的数据，不妨针对您有权访问的时间最早的整月数据运行以下查询：

SELECT
  campaign_id,
  COUNT(DISTINCT user_id) AS user_count,
  COUNT(*) AS impression_count
FROM adh.google_ads_impressions

ORDER BY user_count DESC;

选择 12 到 13 个月的数据，针对即将从广告数据中心移除的数据训练模型并对其进行测试。如果这些数据遇到模型训练限制，这些限制将在数据删除后结束。

如果您的广告系列特别活跃，一周的数据可能就已足够。最后，去重用户数应不少于 10 万，尤其是使用多项特征进行训练时。

3. 创建临时表

确定要用于训练模型的广告系列后，运行以下查询。

CREATE TABLE
 binary_logistic_regression_example_data
AS(
 WITH all_data AS (
   SELECT
     imp.user_id as user_id,
     ROW_NUMBER() OVER(PARTITION BY imp.user_id) AS rowIdx,
     imp.browser as browser_name,
     gender_name as gender_name,
     age_group_name as age_group_name,
     DATETIME(TIMESTAMP_MICROS(
       imp.query_id.time_usec), "America/Los_Angeles") as impression_time,
     CASE # Binary classification of clicks simplifies model weight interpretation
        WHEN clk.click_id.time_usec IS NULL THEN 0
        ELSE 1
     END AS label
   FROM adh.google_ads_impressions imp
     LEFT JOIN adh.google_ads_clicks clk USING (impression_id)
     LEFT JOIN adh.gender ON demographics.gender = gender_id
     LEFT JOIN adh.age_group ON demographics.age_group = age_group_id
   WHERE
     campaign_id IN (YOUR_CID_HERE)
 )
 SELECT
   label,
   browser_name,
   gender_name,
   age_group_name,
   # Although BQML could divide impression_time into several useful variables on
   # its own, it may attempt to divide it into too many features. As a best
   # practice extract the variables that you think will be most helpful.
   # The output of impression_time is a number, but we care about it as a
   # category, so we cast it to a string.
   CAST(EXTRACT(DAYOFWEEK FROM impression_time) AS STRING) AS day_of_week,
   # Comment out the previous line if training on a single week of data
   CAST(EXTRACT(HOUR FROM impression_time) AS STRING) AS hour,
 FROM
   all_data
 WHERE
   rowIdx = 1 # This ensures that there's only 1 row per user.
   AND
   gender_name IS NOT NULL
   AND
   age_group_name IS NOT NULL
);

4. 创建和训练模型

最佳实践是将表创建步骤与模型创建步骤分开。

对您在上一步中创建的临时表运行以下查询。您无需提供开始日期和结束日期，系统会根据临时表中的数据推断出这两个日期。

CREATE OR REPLACE
MODEL `binary_logistic_example`
OPTIONS(
   model_type = 'adh_logistic_regression'
)
AS (
   SELECT *
   FROM
       tmp.binary_logistic_regression_example_data
);

SELECT * FROM ML.EVALUATE(MODEL `binary_logistic_example`)

5. 解读结果

查询运行完毕后，您将得到如下表格。您广告系列的结果可能会有所不同。

行

精确度

回想度

准确性

f1_score

log_loss

roc_auc

1

0.53083894341399718

0.28427804486705865

0.54530547622568992

0.370267971696336

0.68728232223722974

0.55236263736263735

查看权重

运行以下查询以查看权重，了解哪些特征会影响模型预测点击的可能性：

SELECT * FROM ML.WEIGHTS(MODEL `binary_logistic_example`)

查询会生成类似于以下内容的结果。请注意，BigQuery 会对给定标签进行排序，并将“最小”设为 0，将“最大”设为 1。在本例中，clicked 为 0，not_clicked 为 1。因此，可以将较大的权重解读为相应特征带来点击的可能性较低。此外，第 1 天对应星期日。

processed_input

weight

category_weights.category

category_weights.weight

1

INTERCEPT

-0.0067900886484743364

2

browser_name

null

unknown 0.78205563068099249

Opera 0.097073700069504443

Dalvik -0.75233190448454246

Edge 0.026672464688442348

Silk -0.72539916969348706

Other -0.10317444840919325

Samsung Browser 0.49861066525009368

Yandex 1.3322608977581121

IE -0.44170947381475295

Firefox -0.10372609461557714

Chrome 0.069115931084794066

Safari 0.10931362123676475

3

day_of_week

null

7 0.051780350639992277

6 -0.098905011477176716

4 -0.092395178188358462

5 -0.010693625983554155

3 -0.047629987110766638

1 -0.0067030673140933122

2 0.061739400111810727

4

hour

null

15 -0.12081420778273

16 -0.14670467657779182

1 0.036118460001355934

10 -0.022111985303061014

3 0.10146297241339688

8 0.00032334907570882464

12 -0.092819888101463813

19 -0.12158349523248162

2 0.27252001951689164

4 0.1389215333278028

18 -0.13202189122418825

5 0.030387010564142392

22 0.0085803647602565782

13 -0.070696534712732753

14 -0.0912853928925844

9 -0.017888651719350213

23 0.10216569641652029

11 -0.053494611827240059

20 -0.10800180853273429

21 -0.070702105471528345

0 0.011735200996326559

6 0.016581239381563598

17 -0.15602138949559918

7 0.024077394387953525

5

age_group_name

null

45-54 -0.013192901125032637

65+ 0.035681341407469279

25-34 -0.044038102549733116

18-24 -0.041488170110836373

unknown 0.025466344709472313

35-44 0.01582412778809188

55-64 -0.004832373590628946

6

gender_name

null

male 0.061475274448403977

unknown 0.46660611583398443

female -0.13635601771194916