Update stg_ga4__events.sql to Include fbclid Detection

[ageofneil]

The current logic in stg_ga4__events.sql detects Google click IDs (gclid) but does not account for Facebook click IDs (fbclid). To improve attribution tracking and ensure accurate categorization of traffic sources, we should update the logic to also detect fbclid in the page_location field.

Proposed Solution
Modify the CTE detect_gclid in the stg_ga4__events.sql file to check for both gclid and fbclid values. Rename the CTE to detect_click_ids to reflect the broader scope.

Updated Code Example:

detect_click_ids as (
    select
        * except (event_source, event_medium, event_campaign),
        case
            when (page_location like '%gclid%' and event_source is null) then "google"
            when (page_location like '%fbclid%' and event_source is null) then "facebook"
            else event_source
        end as event_source,
        case
            when (page_location like '%gclid%' and event_medium is null) then "cpc"
            when (page_location like '%fbclid%' and event_medium is null) then "paid_social"
            else event_medium
        end as event_medium,
        case
            when (page_location like '%gclid%' and event_campaign is null) then "(cpc)"
            when (page_location like '%fbclid%' and event_campaign is null) then "(paid_social)"
            else event_campaign
        end as event_campaign
    from include_event_key
)

[dgitis]

Are there any issues with Facebook Ads tagging?

The issue with Google Ads is that it relies on a backend integration that sends bad attribution data to the BQ integration. FB doesn't have this problem and ad managers are used to tagging their FB ads properly as this is how it is tracked.

Unless there's a consistent problem then I'm inclined to leave things as they are and recommend that individuals having problems, like people who don't know to tag their FB ads properly, override the default stg_ga4__events model and write their own with their own custom logic.

That, by the way, is quite simple to do. Set the package-level model to enabled: false. This is what that would look like in the dbt_project.yml file.

models:
  ga4:
    staging:
      stg_ga4__events:
        +enabled: false

Then you would copy the model to your project with the same name and make the changes at project level.

Bug please, if there is an actual, consistent problem with FB ads, then please do share that here.

Otherwise, fix it in your project and close this issue.