Sobre este codelab
1. Introdução
Este codelab ensina a usar a regressão linear para criar um modelo que prevê o custo por clique.
Pré-requisitos
Para concluir este codelab, vai precisar de dados de campanhas de alta qualidade suficientes para criar um modelo.
2. Crie uma tabela temporária
Execute a seguinte consulta
CREATE TABLE
linear_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,
clk.advertiser_click_cost_usd AS label
FROM adh.google_ads_impressions imp
INNER 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
)
# Need just one user ID or regression won't work
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,
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
label IS NOT NULL
AND
gender_name IS NOT NULL
AND
age_group_name IS NOT NULL
);
3. Crie e prepare um modelo
É uma prática recomendada separar os passos de criação da tabela dos passos de criação do modelo.
Execute a seguinte consulta na tabela temporária que criou no passo anterior. Não se preocupe em fornecer datas de início e fim, uma vez que estas serão inferidas com base nos dados da tabela temporária.
CREATE OR REPLACE
MODEL `example_linear`
OPTIONS(
model_type = 'adh_linear_regression'
)
AS (
SELECT *
FROM
tmp.linear_regression_example_data
);
SELECT * FROM ML.EVALUATE(MODEL `example_linear`)
Linha | mean_absolute_error | mean_squared_error | mean_squared_log_error | median_absolute_error | r2_score | explained_variance |
1 | 0,11102380666874107 | 0,019938972461569476 | 0,019503393448234131 | 0,091792024503562136 | -9,8205955364568478 | -9,7975398794423025 |