Target Database Table
Script Description and Logic
Pulls order id, order-related fields, default GA last click and the BSD corrected last click at the order level. Orders from all integrations are included in the primary source table, uos.customers, and reported by unique order id ,last click and store combination.
From the uos_orders_to_update table, a join is made to uos.orders to lookup order information, a join to account_linked_integrations is made to track orders from stores in integrations and a join to ga base_transactions is made to pull in channel sources per order.
The joined tables are then inserted into drp_staging.order_channel_source. An additional join to integration_mapping is then made to the joined tables and then inserted into drp_staging.order_channel_source.
All records are removed from the final table,order_channel_source and updated by way of insertion from the query (upsert).
Source Tables Used In Script
|Schema||Table (or Derived Table) Name||Table Type||Purpose|
|drp||integration_mapping||database||Ensure unique records|
|platform||account_linked_integrations||database||Ensure unique records|
|drp||order_channel_source||database||Ensure staging matches base table|
To View Larger Image, Right Click and Choose Open Image In New Tab.
Calculated and Derived Fields
|Target Column||Target Column Data Type||Source schema.table||Source Column||Transformation/Logic|
|order_channel_id||CHAR||drp_staging.uos_orders_to_update, uos.orders||__shop_id, order_id||Combination of __shop_id, order_id and a text label of GA or DW last click to identify records|
All content © Daasity 2021. Do not copy, share or distribute.