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.
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.