The AI.CAUSAL_EFFECT function

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Note: To request feedback or support for this feature, send an email to [e-mail].

This document describes the AI.CAUSAL_EFFECT function, which lets you quantify the impact of specific interventions on time series data. For example, if you launched a marketing campaign on a specific date, you can measure by how much your incremental sales increased relative to what would have happened without the campaign. The function compares actual post-intervention values against an estimate of the outcome without the action. AI.CAUSAL_EFFECT offers the following features:

• Time series segmentation: splits data based on a provided timestamp into pre-intervention and post-intervention periods to calculate absolute and relative effects and statistical significance.
• Parallel processing: supports analyzing multiple time series in parallel.
• Univariate time series modeling: uses ARIMA_PLUS forecasting to establish a counterfactual baseline without requiring control series or external covariates, preventing bias from experiment spillover effects.

Syntax

AI.CAUSAL_EFFECT(
  {TABLE TABLE | (query_statement)},
  data_col => DATA_COL,
  timestamp_col => TIMESTAMP_COL,
  intervention_timestamp => INTERVENTION_TIMESTAMP
  [, id_cols => ID_COLS]
  [, confidence_level => CONFIDENCE_LEVEL]
  [, output_time_series => OUTPUT_TIME_SERIES]
  [, num_post_intervention_points => NUM_POST_INTERVENTION_POINTS]
);

Arguments

• TABLE or query_statement: the table or GoogleSQL query that contains the time series data. This input must include data from before and after the intervention.

• DATA_COL: a STRING value that specifies the name of the column with the time series values to analyze. The data column must use one of the following data types:

  • INT64
  • NUMERIC
  • BIGNUMERIC
  • FLOAT64

• TIMESTAMP_COL: a STRING value that specifies the name of the column that contains the timestamps for the time series. The timestamp column must use one of the following data types:

  • TIMESTAMP
  • DATE
  • DATETIME

• INTERVENTION_TIMESTAMP: a TIMESTAMP value that indicates when the intervention occurred. This timestamp divides the data into the pre-intervention and post-intervention periods.

• ID_COLS: an ARRAY<STRING> value that contains the names of columns that identify individual time series. Use this to analyze multiple time series in a single call. Each unique combination of values in these columns defines a separate time series. Supported data types for these columns are STRING and INT64.

• CONFIDENCE_LEVEL: a FLOAT64 value in the range [0, 1) that specifies the percentage of future values expected to fall within the prediction interval. The default value is 0.95.

• OUTPUT_TIME_SERIES: an optional BOOL value that determines the level of detail in the output. If the value is FALSE (default), the function returns a summary view including only metadata and aggregated per-time series summary statistics. If the value is TRUE, the function returns the detailed time series view, including all pointwise data columns.

• NUM_POST_INTERVENTION_POINTS: an optional INT64 value that specifies the number of time series points after the INTERVENTION_TIMESTAMP to include in the causal effect analysis. The counterfactual forecast and all summary statistics are based on this value. If you don't specify a value, the analysis includes all data points from the INTERVENTION_TIMESTAMP to the end of the time series.

Output

By default, the function returns a summary table with one row per time series containing aggregated statistics.

If OUTPUT_TIME_SERIES is TRUE, it returns a flattened, granular table where each row represents a single timestamp, and aggregate values, like p_value, are repeated across every row to maintain a flat structure.

The output includes all of the columns that you specify in the ID_COLS argument in addition to the following columns:

Aggregated summary statistics

• p_value: a FLOAT64 value containing the two-tailed p-value for the null hypothesis for the entire post-intervention period. The p-value is calculated based on ARIMA_PLUS standard errors, leading to more conservative estimates of causal effect and fewer false positives.
• prob_causal_effect: a FLOAT64 value containing the probability of a causal effect, calculated as (1 - p_value).
• absolute_effect: a FLOAT64 value calculated as SUM(actual_value - expected_value) across the post-intervention period.
• relative_effect: a FLOAT64 value calculated as SUM(actual_value - expected_value)/SUM(expected_value) across the post-intervention period.
• status: a STRING value that contains the forecast status. This value is empty if the operation was successful. If the operation wasn't successful, the value is the error string. A common error is The time series data is too short. This error indicates that there wasn't enough historical data in the time series to generate a forecast. You need at least three data points.

Pointwise data

Pointwise data is included in the output only if the OUTPUT_TIME_SERIES argument is set to TRUE.

• <timestamp_col name>: a TIMESTAMP value that contains the timestamp of the data point from the TIMESTAMP_COL input. This column contains all timestamps (pre- and post-intervention) from the input.
• is_post_intervention: a BOOL value that's TRUE for timestamps greater than or equal to the INTERVENTION_TIMESTAMP, indicating that the data is from the post-intervention period. The value is FALSE for timestamps less than the INTERVENTION_TIMESTAMP, indicating that the timestamp is from the pre-intervention period.
• <data_col name>: a FLOAT64 value of the observed value from the data_col at the specified <timestamp_col name>.
• predicted_<data_col name>: a FLOAT64 value of the forecasted (counterfactual) value at the specified <timestamp_col name>. This value is NULL for all timestamps prior to the intervention timestamp.
• lower_bound: a FLOAT64 that contains the lower bound of the prediction result. This value is NULL for all timestamps prior to the intervention timestamp.
• upper_bound: a FLOAT64 that contains the upper bound of the prediction result. This value is NULL for all timestamps prior to the intervention timestamp.

Examples

The following examples demonstrate how to use the AI.CAUSAL_EFFECT function.

Impact of the 2023 Nobel Prize on Wikipedia page views

The following example shows you how to use the AI.CAUSAL_EFFECT function to analyze differences in Wikipedia page views related to the 2023 Nobel Prize winners:

SELECT * FROM AI.CAUSAL_EFFECT(
  (
    SELECT
      DATE(datehour) AS view_date,
      title,
      SUM(views) AS daily_views
    FROM `bigquery-public-data.wikipedia.pageviews_2023`
    WHERE
      datehour BETWEEN '2023-07-01' AND '2023-10-31'
      AND wiki = 'en'
      AND title IN (
        'Nobel_Prize',
        'Nobel_Prize_in_Physics',
        'Nobel_Prize_in_Chemistry',
        'Nobel_Prize_in_Physiology_or_Medicine',
        'Quantum_dot',
        'Attosecond',
        'Physics',
        'Chemistry',
        'Quantum_mechanics'
      )
    GROUP BY view_date, title
  ),
  data_col => 'daily_views',
  timestamp_col => 'view_date',
  intervention_timestamp => '2023-10-02', -- Start of 2023 Nobel Prize announcement week
  id_cols => ['title']
);

This query uses the Wikipedia page title as the id_col to organize the default summary view:

+--------------------------+-------------------+--------------------+--------------------+--------------------+--------+
| title                    | p_value           | prob_causal_effect | absolute_effect    | relative_effect    | status |
+--------------------------+-------------------+--------------------+--------------------+--------------------+--------+
| Chemistry                | 0.912565563365... | 0.087434436634...  | -361.1770287181... | -0.017380008274... |        |
| Attosecond               | 0.0               | 1.0                | 25601.92393847...  | 21.97446568855...  |        |
| Nobel_Prize_in_Physics   | 0.034821045775... | 0.965178954224...  | 13342.83392650...  | 1.014111538302...  |        |
| Quantum_mechanics        | 0.557980122639... | 0.442019877360...  | -5896.606165928... | -0.111127547618... |        |
| Physics                  | 0.671949069437... | 0.328050930562...  | -2149.471016462... | -0.063529026814... |        |
| Nobel_Prize_in_Chemistry | 3.204658760580... | 0.999999999967...  | 10490.52230016...  | 1.712045773819...  |        |
| Nobel_Prize              | 2.051915413137... | 0.999999794808...  | 41474.77221542...  | 1.210510083471...  |        |
| ...                      | ...               | ...                | ...                | ...                | ...    |
+--------------------------+-------------------+--------------------+--------------------+--------------------+--------+

The default summary view highlights a statistically significant impact on the award category pages and the specific scientific breakthrough. The Attosecond page experienced a 2,197.45% relative increase over its predicted baseline, with a p-value of 0.0 and a 100% probability of a causal effect, confirming the 2023 Physics prize announcement as a significant driver of increased traffic. Similarly, the main Nobel Prize, Nobel Prize in Chemistry, and Nobel Prize in Physics pages saw statistically significant relative increases of 121.05%, 171.20%, and 101.41% respectively, with all p-values less than 0.05.

In contrast, broad scientific disciplines and adjacent concepts showed no statistically significant causal effect. The Chemistry page saw a slight 1.74% decrease compared to predictions with a p-value of 0.913 (causal probability of 8.74%), Physics saw a 6.35% decrease with a p-value of 0.672 (causal probability of 32.81%), and Quantum mechanics saw an 11.11% decrease with a p-value of 0.558 (causal probability of 44.20%).

Impact of the COVID-19 pandemic on New York City taxi trips

The following example shows you how to use the AI.CAUSAL_EFFECT function to quantify the impact of the COVID-19 pandemic on taxi trips in New York City:

SELECT pickup_date, trip_count, predicted_trip_count, lower_bound, upper_bound
FROM
  AI.CAUSAL_EFFECT(
    (
      SELECT
        DATE(pickup_datetime) AS pickup_date,
        COUNT(*) AS trip_count
      FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2020`
      WHERE EXTRACT(YEAR FROM pickup_datetime) = 2020
      GROUP BY pickup_date
    ),
    data_col => 'trip_count',
    timestamp_col => 'pickup_date',
    intervention_timestamp => '2020-03-11',  -- WHO declares COVID-19 a pandemic
    num_post_intervention_points => 120,
    output_time_series => TRUE);

This query sets OUTPUT_TIME_SERIES to TRUE, so the output includes pointwise values.

+-------------+------------+----------------------+--------------------+---------------------+
| pickup_date | trip_count | predicted_trip_count | lower_bound        | upper_bound         |
+-------------+------------+----------------------+--------------------+---------------------+
| 2020-01-01  | 169437.0   | null                 | null               | null                |
| 2020-01-02  | 162141.0   | null                 | null               | null                |
| 2020-01-03  | 183477.0   | null                 | null               | null                |
| 2020-01-04  | 182752.0   | null                 | null               | null                |
| 2020-01-05  | 164399.0   | null                 | null               | null                |
| ...         | ...        | ...                  | ...                | ...                 |
| 2020-07-04  | 14476.0    | 191116.09175879564…  | 19877.20579618783… | 362354.9777214034…  |
| 2020-07-05  | 14450.0    | 126387.79611099855…  | -45584.014231973…  | 298359.606453970…   |
| 2020-07-06  | 24504.0    | 146169.325786536…    | -26532.2985241909… | 318870.950097263…   |
| 2020-07-07  | 26050.0    | 174436.93300780…     | 1008.56587460252…  | 347865.30014100095… |
| 2020-07-08  | 27038.0    | 208480.5766585973…   | 34328.4994002080…  | 382632.65391698654… |
+-------------+------------+----------------------+--------------------+---------------------+

To plot this data in BigQuery, use the Visualize tab.

What's next

• Learn about forecasting.
• Learn about anomaly detection.