View: Intermediary DRP Table(s): drp.order_channel_source

Source Database Table

drp.order_channel_source

Script that Populates Database Table

3010_CHN_BAS_order_channel_source.sql

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

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