Acerca de este codelab
1. Introducción
Con este codelab, aprenderás a usar la regresión lineal para crear modelos que predigan el coste por clic.
Requisitos previos
Para completar este codelab, necesitarás lo siguiente:
Para completar este codelab, necesitarás suficientes datos de campañas de alta calidad para crear un modelo.
2. Crear una tabla temporal
Ejecuta esta 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. Crear y entrenar un modelo
Te recomendamos que separes los pasos de creación de la tabla y del modelo.
Ejecuta la siguiente consulta en la tabla temporal que has creado en el paso anterior. No te preocupes por proporcionar las fechas de inicio y finalización, ya que se deducirán a partir de los datos de la tabla temporal.
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`)
Fila | 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 |