1. Intro
This codelab will teach you how to use logistic regression to understand the degree to which features such as gender, age group, impression time, and browser type correlate to a user's likelihood to click an ad.
Prerequisites
To complete this codelab, you'll need enough high quality campaign data to create a model.
2. Pick a campaign
Begin by selecting an old campaign containing a large quantity of high quality data. If you don't know which campaign is likely to have the best data, run the following query on the oldest full month of data that you have access to:
SELECT
campaign_id,
COUNT(DISTINCT user_id) AS user_count,
COUNT(*) AS impression_count
FROM adh.google_ads_impressions
ORDER BY user_count DESC;
Selecting older data lets you train and test your model on data that will soon be removed from Ads Data Hub. If you encounter model-training limits on this data, those limits will end when the data is deleted.
If your campaign is particularly active, a week of data may be enough. Lastly, the number of distinct users should be 100,000 or more, especially if you're training using many features.
3. Create a temporary table
Once you've identified the campaign you'll use to train your model, run the query below.
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 and train a model
It's a best practice to separate your table creation steps from your model creation steps.
Run the following query on the temporary table you created in the previous step. Don't worry about providing start and end dates, as these will be inferred based on data in the temporary table.
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. Interpret results
When the query finishes running, you'll get a table that resembling the one below. Results from your campaign will differ.
| Row |
precision |
recall |
accuracy |
f1_score |
log_loss |
roc_auc |
|---|---|---|---|---|---|---|
| 1 |
0.53083894341399718 |
0.28427804486705865 |
0.54530547622568992 |
0.370267971696336 |
0.68728232223722974 |
0.55236263736263735 |
Examine weights
Run the following query to look at the weights to see what features contribute to your model's likelihood to predict a click:
SELECT
*
FROM
ML
.
WEIGHTS
(
MODEL
`binary_logistic_example`
)
The query will produce results similar to those below. Note that BigQuery will sort the given labels and choose the "smallest" to be 0 and the largest to be 1. In this example, clicked is 0 and not_clicked is 1. Thus, interpret larger weights as an indication that the feature makes clicks less likely. Additionally, day 1 corresponds to Sunday.
| 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 |
