3050_CHN_BAS_order_channel_influencer_utm Transformation

Source Database Table

drp.order_channel_influencer_utm, drp_staging.order_channel_source.

Script Description and Logic

Pulls order-related fields and BSD tracked UTMs for all orders with influencer derived UTMs. All UTMs and influencers in the primary source table, bsd.influencers, are included and reported by unique order id and store combination. From influencers, a lookup is performed to pull a list of influencer UTMs and stored as influencer_utm. Next orders is joined to influencer_utm to generate a list of orders with UTMs derived from social media influencers tracked in the BSD and then sequenced in preparation for removal of duplicates. The resulting table is stored as rows_deduped. rows_deduped is then inserted into drp_staging.order_channel_influencer_utm. All records are removed from the final table, drp.order_channel_influencer_utm, and updated by way of insertion from the query (upsert). An additional insert into drp_staging.order_channel_source is then performed for use in later processes.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
uos orders database Primary
ga base_transactions database Lookup
bsd influencers database Lookup
drp_staging order_channel_influencer_utm database Pre-insert
drp integration_mapping database Ensure unique records
platform account_linked_integrations database Ensure unique records
N/A influencer_utm derived Pulls a list of UTMs and social media influencers from BSD
N/A rows_deduped derived Generates a list of orders that have UTMs derived from social media influencers, removes duplicate records

SQL Flow

To View Larger Image, Right Click and Choose Open Image In New Tab.

3050_CHN_BAS_order_channel_influencer_utm Transformation

Calculated and Derived Fields

 N/A.


All content © Daasity 2021. Do not copy, share or distribute.