Bigquery AI ML

ML.CORRELATION(
  { TABLE TABLE_NAME | (QUERY_STATEMENT) },
  target_col => TARGET_COL,
  target_correlation_cols => TARGET_CORRELATION_COLS
  [, dimension_cols => DIMENSION_COLS ]
  [, method => METHOD ]
)

CREATE OR REPLACE TABLE my_dataset.marketing_sample AS (
  -- New York data
  SELECT 'USA' AS country, 'New York' AS city, 'Electronics' AS product_category, 100 AS ad_spend, 150 AS budget, 1000 AS revenue UNION ALL
  SELECT 'USA', 'New York', 'Electronics', 150, 200, 1500 UNION ALL
  SELECT 'USA', 'New York', 'Apparel',     200, 250, 2000 UNION ALL

  -- Seattle data
  SELECT 'USA', 'Seattle',  'Apparel',     200, 250, 2000 UNION ALL
  SELECT 'USA', 'Seattle',  'Apparel',     300, 350, 3000 UNION ALL

  -- London data (Genuine NULL country)
  SELECT NULL,  'London',   'Electronics', 100, 120, 500  UNION ALL
  SELECT NULL,  'London',   'Electronics', 200, 220, 900  UNION ALL

  -- Missing city data (Genuine NULL city)
  SELECT NULL,  NULL,       'Apparel',     200, 200, 1000 UNION ALL
  SELECT NULL,  NULL,       'Apparel',     250, 250, 1200
);


/*---------+----------+------------------+----------+---------+---------+
 | country | city     | product_category | ad_spend | budget  | revenue |
 +---------+----------+------------------+----------+---------+---------+
 | USA     | New York | Electronics      | 100      | 150     | 1000    |
 | USA     | New York | Electronics      | 150      | 200     | 1500    |
 | USA     | New York | Apparel          | 200      | 250     | 2000    |
 | USA     | Seattle  | Apparel          | 200      | 250     | 2000    |
 | USA     | Seattle  | Apparel          | 300      | 350     | 3000    |
 | null    | London   | Electronics      | 100      | 120     | 500     |
 | null    | London   | Electronics      | 200      | 220     | 900     |
 | null    | null     | Apparel          | 200      | 200     | 1000    |
 | null    | null     | Apparel          | 250      | 250     | 1200    |
 +---------+----------+------------------+----------+---------+---------*/

SELECT
  country,
  segment,
  correlation,
  segment_size
FROM ML.CORRELATION(
  TABLE my_dataset.marketing_sample,
  target_col => 'revenue',
  target_correlation_cols => 'ad_spend',
  dimension_cols => ['country']
);

/*---------+---------------------------------------------------------------+-------------+--------------+
 | country | segment                                                       | correlation | segment_size |
 +---------+---------------------------------------------------------------+-------------+--------------+
 | 'USA'   | [{dimension_col: 'country', dimension_value: 'USA'}] | 1.0         | 2            |
 | NULL    | [{dimension_col: 'country', dimension_value: null}]  | 1.0         | 2            |
 | NULL    | []                                                            | 0.688       | 4            |
 +---------+---------------------------------------------------------------+-------------+--------------*/

SELECT *
FROM
ML.CORRELATION(
 (SELECT * FROM my_dataset.marketing_sample WHERE country = 'USA'),
 target_col => 'revenue',
 target_correlation_cols => ['ad_spend', 'budget'],
 dimension_cols => ['city', 'product_category']
);

SELECT
  -- Create a clean label for reporting
  CASE
    -- If 'city' is NULL and not in the segment array, it's a total
    WHEN city IS NULL AND NOT EXISTS(SELECT 1 FROM UNNEST(segment) s WHERE s.dimension_col = 'city')
      THEN 'ALL CITIES (Global)'
    -- If 'city' is NULL and in the segment array, it's missing data
    WHEN city IS NULL
      THEN 'UNKNOWN CITY'
    ELSE city
  END AS city_label,
  correlation,
  segment_size
FROM ML.CORRELATION(
  TABLE my_dataset.marketing_sample,
  target_col => 'revenue',
  target_correlation_cols => 'ad_spend',
  dimension_cols => ['city']
);