Updating downstream tables to use UTS data - 2023.05.22

Overview

This update changes our transform code so any downstream tables that use UA data will be updated to instead use UTS data, which blends UA & GA4 based on the cutover date. So if you have a GA4 integration with a cutover date in the past, downstream reporting like daily plan will now use GA4 numbers post-cutover-date.

Background

Blended data from UA, GA4, and Amazon Sales & traffic reports have been flowing into the uts.traffic, uts.product_page, uts.transactions, and uts.shopping_stage for a while. But that data wasn't being used for downstream tables.

That means that if you had UA and GA4 with a cutover date of 5/1, for example, the data in the UTS reports would show UA data through 4/30, and GA4 data for 5/1 and beyond.

But other places that use GA data — like Daily Plan numbers — would still show only UA data, because we had not updated those scripts. This update changes that.

Impacted data

Any reporting using Daily Plan numbers or Daily Company Metrics numbers will be impacted. They will now use session & conversion data from UTS instead of from the UA only.

It also affects the old versions of our marketing attribution explores -- Channel & Vendor Level Marketing Performance. After the cutover date, these scripts will be matching on UTS transactions (blended from UA & GA4), rather than UA only.

If you have only UA — or if you have GA4 but no cutover date set — nothing will change. It will only impact you if you:

A. Have GA4 with a cutover date that has already passed, and/or

B. Have Amazon Sales & Traffic reports set up

Example

The following merchant has a UA -> GA4 cutover date of 5/1.

You can see that the last day where the UTS report uses UA data is 4/30, and after that it's all GA4 data. Here's the data from the Traffic explore:

And in the Daily Plan to Actual explore, the numbers are the same. They use UA data through 4/30, and after that it's all GA4:

Code Changes

Pull Requests

• platform-sql-shared

• platform-sql-v2

Added

• scripts/datamart/13_channel_attribution/0051_CHN_BAS_stg_shopify_orders_update_list.sql

Changed

• scripts/base/3000_channel/3010_CHN_BAS_order_channel_source.sql

  • Updated to use uts.transactions instead of ga.base_transactions .

  • Removes a lot of the complicated join logic from the script, as this logic is now done in the script that creates uts.transactions

• scripts/base/3000_channel/3015_CHN_BAS_enquire_channel_source.sql

  • Updated to use uts.transactions instead of ga.base_transactions .

  • Removes a lot of the complicated join logic from the script, as this logic is now done in the script that creates uts.transactions

• scripts/base/3000_channel/3050_CHN_BAS_order_channel_influencer_utm.sql

  • Updated to use uts.transactions instead of ga.base_transactions .

  • Removes a lot of the complicated join logic from the script, as this logic is now done in the script that creates uts.transactions

• scripts/base/4400_company_metrics/4400_PFT_BAS_daily_company_metrics.sql

  • Updated to use uts.traffic instead of ga.base_traffic

  • Modified to join with shopify or big commerce data — rather than only shopify data

• scripts/base/4400_company_metrics/4401_PFT_BAS_daily_plan_to_actual.sql

  • Updated to use uts.traffic instead of ga.base_traffic

  • Removes join condition that uses the view_id since there is no view_id for GA4 data

• scripts/base/4400_company_metrics_vofd/4400_PFT_BAS_daily_company_metrics.sql

  • Updated to use uts.traffic instead of ga.base_traffic

• scripts/base/4400_company_metrics_vofd/4401_PFT_BAS_daily_plan_to_actual.sql

  • Updated to use uts.traffic instead of ga.base_traffic

  • Removes join condition that uses the view_id since there is no view_id for GA4 data

• scripts/datamart/13_channel_attribution/005_CHN_BAS_stg_orders_update_list.sql