About this codelab
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 |