drive-snowflake

GA4 & web analytics

GA4 is the largest single source in this project — its staging footprint alone outnumbers every ad platform combined — feeding the web_analytics domain, the biggest domain at both the intermediate and marts layers. This page covers the breadth of what GA4 actually tracks, then the one join that matters most: attributing web traffic to a specific piece of stock.

What GA4 actually carries

Standard GA4 metrics (pageviews, activeUsers, engagedSessions, engagementRate, sessions, userEngagementDuration) and dimensions (date, pagePath, country), plus a custom event dimension specific to this site: customEvent:stock_external_identifier.

The staging models fall into distinct categories, not one generic "traffic" table:

  • Attribution, two different models — ga4__audience_channel_first_click_monthly_stg and ga4__audience_channel_each_click_monthly_stg track first-click and each-click channel attribution separately, not just one attribution methodology.
  • Forecasting inputs — ga4__audienceforecast_channel_metrics_stg, ga4__audienceforecast_channelsession_metrics_stg, ga4__audienceforecast_sitesection_metrics_stg feed forward-looking reporting models (see Reporting layer for business__daily_decay_rpt, which does real decay-curve forecasting downstream of this data).
  • Country/hourly analysis — ga4__audienceanalysis_country_stg and _country_hourly_stg give both daily and hourly granularity for country-level traffic, distinct from the site-section and channel breakdowns.
  • Stock/CFS attribution — ga4__audience_pagepath_cfs_daily_stg, ga4__audience_stock_external_identifier_cfs_daily_stg, and returning-visitor equivalents of each. This is the category the stock join below builds on.
  • External partner integrations — ga4__strattonfinance_event_metrics_stg (Stratton Finance, a novated-lease/finance partner) and ga4__unbounce_audiencemetrics_channel_stg (Unbounce landing pages) — GA4 here tracks third-party-hosted funnels, not just Drive's own site.
  • Content performance — ga4__article_daily_stg for editorial article traffic.

This breadth is why web_analytics dwarfs every other domain at the intermediate and marts layers — GA4 alone covers more distinct reporting angles than the ad platforms combined.

The page_path → stock join lives in this dbt project, not just on the ingestion side

web_analytics__audience_pagepath_cfs_daily_int.sql is where GA4 traffic gets attributed to a specific stock listing:

snippet.sqlsql
extracted as (
    select *, regexp_substr(page_path, '\\d+') as manifold_stock_id
    from base
    where regexp_substr(page_path, '\\d+') >= 962045199
),
mapping as (
    select manifold_stock_id, stock_external_identifier
    from {{ ref('manifold__stock_identifier_xref_stg') }}
    where manifold_stock_id is not null and stock_external_identifier is not null
),
joined as (
    select e.event_date, e.page_path, m.stock_external_identifier, e.views, ...
    from extracted e
    left join mapping m on e.manifold_stock_id = m.manifold_stock_id
    where m.stock_external_identifier is not null
)

This extracts a numeric stock ID directly out of the URL path (regexp_substr(page_path, '\\d+')), then joins it against manifold__stock_identifier_xref_stg — a plain pass-through staging model reading source('manifold', 'stock_identifier_xref'). That source resolves cross-database to VEHICLE_STOCK_DB.VEHICLE_STOCK_SCHEMA.STOCK_IDENTIFIER_XREF, a table populated on the ingestion side rather than by this dbt project — this model only ever reads it.

A near-identical join appears again in web_analytics__audience_returning_users_pagepath_cfs_daily_int.sql, for returning-visitor traffic specifically.

The >= 962045199 lower bound on the extracted numeric ID isn't explained in a comment — it's presumably a floor below which no real stock ID exists, filtering out coincidental numeric matches in unrelated URL paths, but that's inferred from the value's role in the query, not stated anywhere in the code.

Some intermediate models exist entirely by filtering a generic staging model

Not every site-section reporting angle has its own dedicated GA4 staging model. web_analytics__showroom_daily_int and web_analytics__sell_my_car_daily_int — two specific site sections — both derive from the same generic ga4__audiencemetricsdata_sectionchannel_stg staging model, presumably by filtering to their own section value, rather than each having a ga4__showroom_stg/ga4__sellmycar_stg of their own. If you're looking for a specific site section's GA4 data and there's no staging model with that name, check whether it's actually a filtered slice of one of the generic section/channel staging models instead.

Where this feeds

web_analytics intermediate and mart models built from this join feed into the stock and inventory reporting domains — stock__ga4_dealermarketplace_rpt.sql, for instance, combines monthly GA4 engagement with dealer enrichment and vehicle price bucketing at a grain of one row per month × page path × stock_external_identifier, explicitly excluding hidden stock.

See also

Esc
Esc Close