Channel Attribution Data Mart Code

This page provides information on the transform code block that is needed to create Channel Attribution Data Mart and what should be added to a Script Manifest file in order to execute the code

Workflows

Upstream Data Mart Dependencies

This data mart and code block is dependent on the following data marts being updated in order to update with new data:

• UOS

• UTS

The proper sequence of either a Workflow or position in a Script Manifest file to populate this Data Mart is highlighted in the Transform Code Sequence - Data Mart page

Script Manifest File (YML)

Transformation Code Requirements

The following code should be added in the Script Manifest file

  order_channel_stg:
    integrations:
      - google_analytics
    scripts:
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/000_CHN_BAS_reset_order_attribution.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/001_CHN_BAS_table_creation.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/003_CHN_BAS_temp_uts_multi_channel_funnel.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/005_CHN_BAS_stg_orders_update_list.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/110_CHN_BAS_stg_ga_last_click_raw.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/120_CHN_BAS_stg_ga_last_click.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/130_CHN_BAS_stg_first_click.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/140_CHN_BAS_stg_first_ad_click.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/150_CHN_BAS_stg_last_ad_click.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/160_CHN_BAS_stg_first_mktg_click.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/170_CHN_BAS_stg_last_mktg_click.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/180_CHN_BAS_stg_discount_code_channel.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/190_CHN_BAS_stg_order_attribution_detail.sql"

  order_attribution_enquire:
    integrations:
      - enquire_labs
    scripts:
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/200_CHN_BAS_stg_self_reported_channel.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/201_CHN_BAS_stg_order_attribution_detail_insert_enquire.sql"

  order_channel_attribution:
    integrations:
      - google_analytics
    scripts:
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/210_CHN_BAS_stg_merchant_attribution.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/220_CHN_BAS_stg_order_attribution_detail_insert_ma.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/230_CHN_BAS_order_attribution_detail.sql"
      - "github://platform-sql-shared/scripts/datamart/13_channel_attribution/240_CHN_BAS_order_attribution.sql"

Order Channel

Implement all of the Staging and Attribution blocks to create the following tables:

• [dm_chn.dim_order_attribution_detail]

• [dm_chn.dim_order_attribution]

Add the Enquire block if you have the Fairing extractor installed and want to incorproate survey based data into your attribution

Order Channel Staging

Enable this code if you need to reset the order attribution:

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/000_CHN_BAS_reset_order_attribution.sql

Create the final Order Channel tables

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/001_CHN_BAS_table_creation.sql

Determine the orders and channel for each of the attribution methods:

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/003_CHN_BAS_temp_uts_multi_channel_funnel.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/005_CHN_BAS_stg_orders_update_list.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/110_CHN_BAS_stg_ga_last_click_raw.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/120_CHN_BAS_stg_ga_last_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/130_CHN_BAS_stg_first_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/140_CHN_BAS_stg_first_ad_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/150_CHN_BAS_stg_last_ad_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/160_CHN_BAS_stg_first_mktg_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/170_CHN_BAS_stg_last_mktg_click.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/180_CHN_BAS_stg_discount_code_channel.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/190_CHN_BAS_stg_order_attribution_detail.sql

Fairing Order Attribution

Add self-reported (survey) attribution if Fairing is enabled as an extractor. Use the block that matches the merchant's Fairing integration version.

Fairing V2 (current):

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/200_CHN_BAS_stg_fairing_v2_channel.sql

Fairing V1 / Enquire Labs (legacy):

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/200_CHN_BAS_stg_self_reported_channel.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/201_CHN_BAS_stg_order_attribution_detail_insert_enquire.sql

Note: New merchants connecting Fairing should use the V2 integration (fairing_v2). The legacy enquire_labs block is documented here only for existing merchants who connected before Fairing V2 was available.

Order Attribution

Build the final attribution tables

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/210_CHN_BAS_stg_merchant_attribution.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/220_CHN_BAS_stg_order_attribution_detail_insert_ma.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/230_CHN_BAS_order_attribution_detail.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/datamart/13_channel_attribution/240_CHN_BAS_order_attribution.sql