关于此 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 |