BigQuery AI.Key_Drivers

AI.KEY_DRIVERS automatically identifies the key dimensional segments most responsible for driving changes in a specified metric between a defined interest group and a reference group.

Syntax Reference

SELECT
  *
FROM
  AI.KEY_DRIVERS(
    { TABLE TABLE | (QUERY_STATEMENT) },
    metric_col => 'METRIC_COL',
    dimension_cols => DIMENSION_COLS,
    interest_label_col => 'INTEREST_LABEL_COL'
    [, min_apriori_support => MIN_APRIORI_SUPPORT]
    [, top_k => TOP_K]
    [, enable_pruning => ENABLE_PRUNING]
  )

Input Arguments

Output Schema

Returns a STRUCT with the following fields:

Examples

Identifying Key Drivers in 2024 H2 Liquor Sales

WITH InputData AS (
  SELECT
    sale_dollars,
    city,
    category_name,
    vendor_name,
    (date > '2024-07-01') AS IS_H2
  FROM `bigquery-public-data.iowa_liquor_sales.sales`
  WHERE EXTRACT(YEAR FROM DATE) = 2024
)
SELECT *
FROM AI.KEY_DRIVERS(
  TABLE InputData,
  metric_col => 'sale_dollars',
  dimension_cols => ['city', 'vendor_name', 'category_name'],
  interest_label_col => 'IS_H2',
  min_apriori_support => 0
);

Finding Top 5 Key Drivers in Bottle Costs

SELECT *
FROM AI.KEY_DRIVERS(
  (
    SELECT
      state_bottle_cost,
      city,
      category_name,
      (EXTRACT(MONTH FROM date) >= 7) AS is_h2
    FROM `bigquery-public-data.iowa_liquor_sales.sales`
    WHERE EXTRACT(YEAR FROM date) = 2024
  ),
  metric_col => 'state_bottle_cost',
  dimension_cols => ['city', 'category_name'],
  interest_label_col => 'is_h2',
  top_k => 5
);