Logistic regression 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.

CREATETABLE
binary_logistic_regression_example_data
AS(
WITHall_dataAS(
SELECT
imp.user_idasuser_id,
ROW_NUMBER()OVER(PARTITIONBYimp.user_id)ASrowIdx,
imp.browserasbrowser_name,
gender_nameasgender_name,
age_group_nameasage_group_name,
DATETIME(TIMESTAMP_MICROS(
imp.query_id.time_usec),"America/Los_Angeles")asimpression_time,
CASE# Binary classification of clicks simplifies model weight interpretation
WHENclk.click_id.time_usecISNULLTHEN0
ELSE1
ENDASlabel
FROMadh.google_ads_impressionsimp
LEFTJOINadh.google_ads_clicksclkUSING(impression_id)
LEFTJOINadh.genderONdemographics.gender=gender_id
LEFTJOINadh.age_groupONdemographics.age_group=age_group_id
WHERE
campaign_idIN(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(DAYOFWEEKFROMimpression_time)ASSTRING)ASday_of_week,
# Comment out the previous line if training on a single week of data
CAST(EXTRACT(HOURFROMimpression_time)ASSTRING)AShour,
FROM
all_data
WHERE
rowIdx=1# This ensures that there's only 1 row per user.
AND
gender_nameISNOTNULL
AND
age_group_nameISNOTNULL
);

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.

CREATEORREPLACE
MODEL`binary_logistic_example`
OPTIONS(
model_type='adh_logistic_regression'
)
AS(
SELECT*
FROM
tmp.binary_logistic_regression_example_data
);
SELECT*FROMML.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.

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*FROMML.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.