Attribution support for GA4 - 2023.08.24
Overview
This release introduces new code that will allow you to continue to get first click, last ad click, last marketing click, and assisted attribution models even after the switchover to GA4.
It includes the following:
New transform code that will feed GA4 data into the Attribution 2.0 scripts that make it possible to get First Click, Last Ad Click, Last Marketing Click, and Assisted attribution even after the cutover to GA4. The data will feed into the
uts.multi_channel_funneltable, which blends Universal Analytics Multi-Channel Funnel (MCF) data and new Google Analytics 4 BigQuery MCF data.New transform code to update order channel dimensiones in
drp.order_channelto get first-click data from GA4. Without this transform code, you will only get first-click data from Universal Analytics.
Why are we releasing this?
Current code for attribution only includes Universal Analytics data. The updates enable you to get attribution data from GA4 after the UA sunset if you have the GA4 via BigQuery integration set up in Daasity.
How do you utilize this new feature?
To get the benefit of this release, you need to enable the Google Analytics 4 via BigQuery integration and leave the Multi-Channel Funnel report toggled "On". Your multi-channel funnel data will begin loading into your data warehouse, and the data will be transformed in the following daily workflow. You will need to load history if you want historical data included.
Code updates
Since this is a base product update, Daasity will be deploying transform code updates to enable this feature. If we are blocked from adding this code to your code repository, we will not push any code and will contact you.
Adding new scripts to daily_incremental.yml
In order to run the transformation scripts that enable this feature, we will be making the following changes to your daily_incremental.yml file (you will need access to the platform-sql-shared github repo to view these links):
Removing temporary UTS multi-channel funnel script
We will be removing the following script from any yml files: github://platform-sql-shared/scripts/datamart/13_channel_attribution/003_CHN_BAS_temp_uts_multi_channel_funnel.sql
We are removing this script because it conflicts with the new code. The new code creates a uts.multi_channel_funnel table that supports data from both Universal Analytics and GA4. The code removed was creating an outdated version that only supported Universal Analytics and conflicts with the new structure.
ga.base_multi_channel
There are 2 legacy tables that exposed UA multi-channel funnel data: ga.base_multi_channel_funnel and ga.base_multi_channel.
We do not have plans to make a UTS version of the base_multi_channel table. However, the ga.base_multi_channel simply consisted of first and last click data for each transaction, which you should be able to derive from the uts.multi_channel_funnel table.
For an example, you can examine script 3071, which is an updated version of script 3070. 3070 used ga.base_multi_channel to get first-click data, while 3071 uses uts.multi_channel_funnel to get the same data.
Transform code changes
| Type | File path | Description |
|---|---|---|
Added | New script that inserts data from the | |
Added | New script that inserts the Universal Analytics data from | |
Added | New script that inserts the GA4 data from | |
Added | New UTS-friendly version of script 3070. Creates & populates table | |
Added | New UTS-friendly version of script 3280. Modifies how data from | |
Changed | Added all of the new scripts to the daily_incremental script manifest file. | |
Changed | Added all of the new scripts that generate the new version of |
New table specifications
ga4_bq.base_multi_channel_funnel
This table is created & populated by platform-sql-shared/scripts/base/2600_google_analytics/2635_GA4_BAS_base_multi_channel_funnel.sql.
It preps raw data from ga4_bq_stg.base_multi_channel_funnel, giving it a similar structure to ga.base_multi_channel_funnel and making it ready for insertion into uts.multi_channel_funnel.
| Column | Description |
|---|---|
id | Unique identifier for this row |
ga4_property_id | The ID of the GA4 property associated with the integration |
stream_id | The stream ID associated with the pageview & transaction |
created_on | The UTC timestamp of when the transaction was tracked in GA4 |
transaction_id | The ID of the transaction, as tracked in GA4 |
sequence | The position in which this pageview falls relative to the first session or pageview tracked in the 30 days prior to the transaction |
touchpoint_timestamp | The UTC timestamp of the pageview |
basic_channel_path | The default channel grouping value for the pageview |
source_medium_path | Concatentation of |
medium_path | The traffic medium value associated with the pageview |
source_path | The traffic source value associated with the pageview |
campaign_path | The traffic campaign value associated with the pageview |
content_path | The traffic content value associated with the pageview |
keyword_path | The traffic keyword (utm_term) value associated with the pageview |
fbadid | The value of the |
gadid | The value of the |
padid | The value of the |
scadid | The value of the |
ttadid | The value of the |
page_location | Value of the page_location event parameter from the page_view hit This is the URL on which the page_view hit occurred. |
channel | The channel value, based upon the values in other columns and the rules set up in your Channel-Mapping BSD |
vendor | The vendor value, based upon the values in other columns and the rules set up in your Channel-Mapping BSD |
sub_channel | The sub_channel value, based upon the values in other columns and the rules set up in your Channel-Mapping BSD |
media_type | The media_type value, based upon the values in other columns and the rules set up in your Channel-Mapping BSD |
attribution_channel | The attribution_channel value, based upon the values in other columns and the rules set up in your Channel-Mapping BSD |
__sync_key | Unique identifier for this row |
_account_id | Your Daasity account ID |
__synced_at | Timestamp of when this row was last synced with the source |
__loaded_at | Timestamp of when this row was loaded from the source table into this table |
uts.multi_channel_funnel
This table is populated by the following scripts:
These scripts insert data from ga.base_multi_channel_funnel and ga4_bq.base_multi_channel_funnel.
Data from the two tables is blended based on the cutover date set in the GA4 integration. If there is no cutover date, they use 7/1/2023 as the cutover date.
| Column | Description |
|---|---|
id | Unique identifier for this row |
ga4_property_id | If data is from GA4, the GA4 property ID associated with the source data If data is from UA, this field is NULL |
ga4_stream_id | If data is from GA4, the stream ID associated with the source data If data is from UA, this field is NULL |
ua_view_id | If data is from UA, the view ID associated with the source data If data is from GA4, this field is NULL |
created_on | The timestamp of the transaction, according to Google Analytics This will be in UTC for GA4. For UA, it will be in whatever timezone is used in your Google Analytics property. |
transaction_id | The ID for the transaction as tracked in Google Analytics |
order_id | The UOS order ID for this transaction, if we are able to join the GA transaction ID to an order in your ecommerce platform
The source of this field is |
sequence | The position in which this session (for UA) or pageview (for GA4) falls relative to the first session or pageview tracked in the 30 days prior to the transaction |
touchpoint_timestamp | If data is from GA4, this will be the UTC timestamp of the pageview This will be NULL for UA, since it is not available through the API. |
basic_channel_path | The traffic default channel grouping value associated with the session (for UA) or the pageview (for GA4) |
source_medium_path | Concatenation of |
medium_path | The traffic medium value associated with the session (for UA) or the pageview (for GA4) |
source_path | The traffic source value associated with the session (for UA) or the pageview (for GA4) |
campaign_path | The traffic campaign value associated with the session (for UA) or the pageview (for GA4) |
content_path | The traffic content value associated with the session (for UA) or the pageview (for GA4) |
keyword_path | The traffic keyword (utm_term) value associated with the session (for UA) or the pageview (for GA4) |
fbadid | If data is from GA4, the value of the |
gadid | If data is from GA4, the value of the |
padid | If data is from GA4, the value of the |
scadid | If data is from GA4, the value of the |
ttadid | If data is from GA4, the value of the |
channel | The channel value, based upon the values in other columns and the rules set up in your Channel-Mapping BSD |
vendor | The vendor value, based upon the values in other columns and the rules set up in your Channel-Mapping BSD |
sub_channel | The sub_channel value, based upon the values in other columns and the rules set up in your Channel-Mapping BSD |
media_type | The media_type value, based upon the values in other columns and the rules set up in your Channel-Mapping BSD |
attribution_channel | The attribution_channel value, based upon the values in other columns and the rules set up in your Channel-Mapping BSD |
traffic_data_source | Will be |
traffic_data_source_detail | Indicates whether the source of the data is GA4 or Universal Analytics |
__sync_key | Unique identifier for this row |
_account_id | Your Daasity account ID |
__synced_at | Timestamp of when this row was last synced with the source |
__loaded_at | Timestamp of when this row was loaded from the source table into this table |
Last updated