BigQuery DNN回归模型适配不同国家数据升降趋势的优化方案问询

Great question—let’s break down actionable adjustments to your BigQuery DNN model so it can adapt to both upward and downward trends across different countries, building on the solid baseline performance you’ve already seen.

1. Add Country-Time Interaction Features #

Right now, your model sees country_code and time-related features (year, weekday) as separate signals. It can’t easily learn how time affects each country uniquely. By creating interaction features, you explicitly tell the model to focus on country-specific time patterns.

Update your training query to generate these features:

CREATE OR REPLACE MODEL `External_Sources.DNN_REGRESSOR` 
OPTIONS(
  MODEL_TYPE='DNN_REGRESSOR', 
  ACTIVATION_FN = 'RELU', 
  BATCH_SIZE = 200, 
  HIDDEN_UNITS = [200,200,200,200,200,200,200,200,200,200], 
  INPUT_LABEL_COLS = ['questions_cnt_users'], 
  LEARN_RATE=0.001
)AS 
SELECT 
  *,
  -- Combine country with time granularities
  CONCAT(country_code, '_', CAST(year AS STRING)) AS country_year,
  CONCAT(country_code, '_', CAST(weekday AS STRING)) AS country_weekday,
  -- Create a continuous time index (e.g., year * 52 + weekday for week-based progression)
  (year * 52 + weekday) AS time_index
FROM `data`

These features give the model the context to learn that "US in 2024" has a different trend than "JP in 2024".

2. Optimize Embedding Layers for Categorical Features #

country_code is a high-cardinality categorical variable, and BigQuery’s default embedding dimensions might be too small to capture meaningful differences between countries. Explicitly set embedding dimensions to give the model more capacity to encode country-specific traits.

Adjust your model options to include EMBEDDING_DIMENSIONS:

CREATE OR REPLACE MODEL `External_Sources.DNN_REGRESSOR` 
OPTIONS(
  MODEL_TYPE='DNN_REGRESSOR', 
  ACTIVATION_FN = 'RELU', 
  BATCH_SIZE = 200, 
  HIDDEN_UNITS = [200,200,200,200,200,200,200,200,200,200], 
  INPUT_LABEL_COLS = ['questions_cnt_users'], 
  LEARN_RATE=0.001,
  -- Tune dimensions based on category count: larger for more countries
  EMBEDDING_DIMENSIONS = {'country_code': 16, 'type': 8}
)AS 
SELECT 
  *,
  (year * 52 + weekday) AS time_index
FROM `data`

A larger embedding dimension for country_code lets the model represent subtle differences between countries’ trends more effectively.

3. Inject Explicit Trend Slope Features #

For each country, precompute the historical trend direction (upward/downward slope) and feed this as a feature to the model. This gives the model a clear signal about each country’s baseline trend.

First, create a temp table with country-specific slopes:

CREATE TEMP TABLE country_trend_slopes AS
SELECT
  country_code,
  -- Calculate linear regression slope for each country's time series
  CORR(questions_cnt_users, time_index) * STDDEV(questions_cnt_users) / STDDEV(time_index) AS trend_slope
FROM (
  SELECT 
    country_code,
    questions_cnt_users,
    (year * 52 + weekday) AS time_index
  FROM `data`
)
GROUP BY country_code

Then join this with your training data:

CREATE OR REPLACE MODEL `External_Sources.DNN_REGRESSOR` 
OPTIONS(
  MODEL_TYPE='DNN_REGRESSOR', 
  ACTIVATION_FN = 'RELU', 
  BATCH_SIZE = 200, 
  HIDDEN_UNITS = [200,200,200,200,200,200,200,200,200,200], 
  INPUT_LABEL_COLS = ['questions_cnt_users'], 
  LEARN_RATE=0.001,
  EMBEDDING_DIMENSIONS = {'country_code': 16, 'type': 8}
)AS 
SELECT 
  d.*,
  c.trend_slope
FROM `data` d
LEFT JOIN country_trend_slopes c
ON d.country_code = c.country_code

The model can now use this slope to weight its predictions toward the observed trend for each country.

4. Add Lag Features for Time Series Context #

Time series data often has autocorrelation—past values predict future values. Add lag features (past questions_cnt_users values for each country) to let the model learn each country’s unique sequential patterns.

Update your training query with lag features:

CREATE OR REPLACE MODEL `External_Sources.DNN_REGRESSOR` 
OPTIONS(
  MODEL_TYPE='DNN_REGRESSOR', 
  ACTIVATION_FN = 'RELU', 
  BATCH_SIZE = 200, 
  HIDDEN_UNITS = [200,200,200,200,200,200,200,200,200,200], 
  INPUT_LABEL_COLS = ['questions_cnt_users'], 
  LEARN_RATE=0.001,
  EMBEDDING_DIMENSIONS = {'country_code': 16, 'type': 8}
)AS 
SELECT 
  *,
  -- Get previous 1 and 2 period values for each country
  LAG(questions_cnt_users, 1) OVER (PARTITION BY country_code ORDER BY year, weekday) AS lag_1,
  LAG(questions_cnt_users, 2) OVER (PARTITION BY country_code ORDER BY year, weekday) AS lag_2
FROM `data`
ORDER BY country_code, year, weekday

These lag features help the model adapt to whether a country’s values are consistently rising, falling, or fluctuating.

内容的提问来源于stack exchange，提问作者idan