3010_CHN_BAS_order_channel_source Transformation

Target Database Table

drp.order_channel_source.

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_staging uos_orders_to_update database Primary source
uos orders database Lookup
drp integration_mapping database Ensure unique records
platform account_linked_integrations database Ensure unique records
ga base_transcaction database Lookup
drp order_channel_source database Ensure staging matches base table

SQL Flow

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

3010_CHN_BAS_order_channel_source Transformation

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.