3280_CHN_BAS_order_channel Transformation

Target Database Table

drp.order_channel.

Script Description and Logic

Pulls order ID as well as marketing channel information associated with that order to assign marketing channels to an order based on a variety of attribution models. Multiple attribution models are applied to each order to allow customers to compare these different models side-by-side.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
drp integration_mapping database Primary
drp order_channel_source database Primary
drp_staging uos_orders_to_update database Pulls uos orders to update
ga base_multi_channel database Pulls multi channel for first click attribution
N/A ocs_attribution derived Pulls order channel information for the order's minimum channel rank
N/A ocs_channel_rank derived Pulls the minimum channel rank per order
uos orders database Primary

SQL Flow

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

 3280_CHN_BAS_order_channel Transformation

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
fc_order_date TIMESTAMP ga.base_multi_channel created_on DATEADD(day,CAST(-bmc.time_lag_in_days_histogram AS INTEGER),bmc.created_on)
ga_default_lc_channel VARCHAR drp.integration_mapping type CASE
WHEN integration_mappings.type = 'ECOMMERCE' THEN NVL(ocs_gdlc.channel,'Missing From GA')
ELSE INITCAP(integration_mappings.type)
END
ga_fc_channel VARCHAR drp.integration_mapping type CASE
WHEN integration_mappings.type = 'ECOMMERCE' THEN NVL(bmc.channel_first_click,'Missing From GA')
ELSE INITCAP(integration_mappings.type)
END
ga_lc_channel VARCHAR drp.integration_mapping type CASE
WHEN integration_mappings.type = 'ECOMMERCE' THEN NVL(ocs_glc.channel,'Missing From GA')
ELSE INITCAP(integration_mappings.type)
END
attribution_channel VARCHAR drp.integration_mapping type CASE
WHEN integration_mappings.type = 'ECOMMERCE' THEN COALESCE(ocs_a.channel, ocs_glc.channel, ocs_gdlc.channel, 'Missing From GA')
ELSE INITCAP(integration_mappings.type)
END
ga_fc_vendor VARCHAR drp.integration_mapping type CASE
WHEN integration_mappings.type = 'ECOMMERCE' THEN NVL(bmc.vendor_first_click,'Missing From GA')
ELSE INITCAP(integration_mappings.type)
END
ga_lc_vendor VARCHAR drp.integration_mapping type CASE
WHEN integration_mappings.type = 'ECOMMERCE' THEN NVL(ocs_glc.vendor,'Missing From GA')
ELSE INITCAP(integration_mappings.type)
END
channel_source VARCHAR drp.order_channel_source channel_source COALESCE(ocs_a.channel_source, ocs_glc.channel_source, ocs_gdlc.channel_source)
utm_source VARCHAR drp.order_channel_source source COALESCE(ocs_glc.source, ocs_gdlc.source)
utm_medium VARCHAR drp.order_channel_source medium COALESCE(ocs_glc.medium, ocs_gdlc.medium)
utm_campaign VARCHAR drp.order_channel_source campaign COALESCE(ocs_glc.campaign, ocs_gdlc.campaign)
device_category VARCHAR drp.order_channel_source device_category COALESCE(ocs_glc.device_category, ocs_gdlc.device_category)
user_type VARCHAR drp.order_channel_source user_type COALESCE(ocs_glc.user_type, ocs_gdlc.user_type)
channel_rank VARCHAR drp.order_channel_source channel_rank COALESCE(ocs_a.channel_rank, ocs_glc.channel_rank, ocs_gdlc.channel_rank)


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