bigquery_forecasting_load

-- Coding is For Losers is Awesome: https://github.com/coding-is-for-losers/ga4-bigquery-starter

-- Build ARIMA model
CREATE OR REPLACE MODEL `forecasting.forecast_client_name`
  OPTIONS(
    MODEL_TYPE='ARIMA_PLUS',
    TIME_SERIES_TIMESTAMP_COL='date',
    TIME_SERIES_DATA_COL='sessions',
    HOLIDAY_REGION='US'
  ) AS
  SELECT
    date,
    sessions,

  FROM
    (
        WITH raw_ga_4 AS (

            SELECT
            * except(row)
            FROM (
            SELECT
                -- extracts date from source table
                parse_date('%Y%m%d',regexp_extract(_table_suffix,'[0-9]+')) as table_date,
                -- flag to indicate if source table is `events_intraday_`
                case when _table_suffix like '%intraday%' then true else false end as is_intraday,
                *,
                row_number() over (partition by user_pseudo_id, event_name, event_timestamp order by event_timestamp) as row
            FROM
                `adapt-ml.analytics_XXXXXXXXXXXXXXX.events_*`

                )
            WHERE
            row = 1

            ),

        pageviews AS (
            SELECT
                parse_date("%Y%m%d", event_date) event_date,
                event_timestamp,
                user_pseudo_id,
                user_first_touch_timestamp,
                device.category as device_category,
                device.language as device_language,
                device.web_info.browser as device_browser,
                geo.continent as geo_continent,
                geo.country as geo_country,
                max(if(params.key = 'ga_session_id', params.value.int_value, null)) ga_session_id,
                max(if(params.key = 'ga_session_number', params.value.int_value, null)) ga_session_number,
                cast(max(if(params.key = 'session_engaged', params.value.string_value, null)) as int64) session_engaged,
                max(if(params.key = 'page_title', params.value.string_value, null)) page_title,
                max(if(params.key = 'page_location', params.value.string_value, null)) page_location,
                max(if(params.key = 'source', params.value.string_value, null)) utm_source,
                max(if(params.key = 'medium', params.value.string_value, null)) utm_medium,
                max(if(params.key = 'campaign', params.value.string_value, null)) utm_campaign,
                max(if(params.key = 'page_referrer', params.value.string_value, null)) utm_referrer
                FROM raw_ga_4,
                UNNEST(event_params) AS params
                WHERE event_name = 'page_view'

                GROUP BY event_date, event_timestamp, user_pseudo_id, user_first_touch_timestamp, device_category, device_language, device_browser, geo_continent, geo_country
                )


        SELECT
        DISTINCT(event_date) as date,
        COUNT(DISTINCT(ga_session_id)) AS sessions,
        COUNT(DISTINCT(user_pseudo_id)) AS users
        FROM pageviews
        WHERE utm_medium = "organic"
        GROUP BY event_date
    )