Transforms
      Back to home
      1. Help Center
      2. Technical Documentation
      3. Transforms

      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.