Order and Order Line Revenue

This page provides detailed instructions on the Order and Order Line Revenue Code transform code block and what should be added to the Script Manifest file in order to execute the code

Script Manifest File (YML)

Upstream Transformation Dependencies

This code block is dependent on the following blocks being implemented:

• Initialization

• UOS

• Google Analytics

• Order Status

• Order Channel and Marketing

Transformation Code Requirements

The following code should be added to the main workflow in the appropriate position, after Daily Plan Code and before Daily Plan to Actuals per the Transform Code Sequence - DRP page

  #--------------------------------------------------------------------------------------------------------------------
  # SECTION: 4000_COSTS and 4300_ORDER_LINE_REVENUE
  # DEPENDENCIES:
  #   - Section: 0500_UOS
  #   - Section: 2600_GA
  #   - Section: 2700_ORDER_STATUS
  #   - Section: 3000_CHANNEL
  # REQUIRED: No
  # PURPOSE: This code the DRP tables for Order Line Revenue and the staging/interim tables that are needed to create
  #  the final table and should be enabled once the upstream blocks are enabled
  #--------------------------------------------------------------------------------------------------------------------
  order_line_revenue:
    scripts:
      - "github://platform-sql-shared/scripts/base/4000_costs/4109_CST_BAS_order_channel_no_ga.sql"
      - "github://platform-sql-shared/scripts/base/4000_costs/4110_CST_BAS_daily_first_click_cost_per_order.sql"
      - "github://platform-sql-shared/scripts/base/4000_costs/4111_CST_BAS_daily_last_click_cost_per_order.sql"
      - "github://platform-sql-shared/scripts/base/4000_costs/4112_CST_BAS_daily_attribution_cost_per_order.sql"
      - "github://platform-sql-shared/scripts/base/4000_costs/4120_CST_BAS_order_marketing_cost.sql"
      - "github://platform-sql-shared/scripts/base/4000_costs/4140_CST_BAS_fulfillment_cost.sql"
      - "github://platform-sql-shared/scripts/base/4000_costs/4150_CST_BAS_order_item_fulfillment_cost.sql"
      - "github://platform-sql-shared/scripts/base/4300_order_line_revenue/4310_ORL_BAS_order_line_revenue.sql"
      - "github://platform-sql-shared/scripts/base/4300_order_line_revenue/4315_ORL_BAS_update_amount_order_status.sql"
      - "github://platform-sql-shared/scripts/base/4300_order_line_revenue/4320_ORL_BAS_product_affinity.sql"
      - "github://platform-sql-shared/scripts/pro/4400_marketing_performance/4410_MKP_BAS_marketing_performance.sql"

Order Line Revenue

Add this code to enable sales, profitability and product analysis by creating the following tables:

• [drp.order_line_revenue]

• [drp.product_affinity]

Add missing tables in case Google Analytics has not been setup:

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4000_costs/4109_CST_BAS_order_channel_no_ga.sql

Calculate the appropriate cost per order based on the attribution and allocate to the item level:

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4000_costs/4110_CST_BAS_daily_first_click_cost_per_order.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4000_costs/4111_CST_BAS_daily_last_click_cost_per_order.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4000_costs/4112_CST_BAS_daily_attribution_cost_per_order.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4000_costs/4120_CST_BAS_order_marketing_cost.sql

Calculate the shipping and fulfillment costs per item

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4000_costs/4140_CST_BAS_fulfillment_cost.sql

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4000_costs/4150_CST_BAS_order_item_fulfillment_cost.sql

Create the final [drp.order_line_revenue] table

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4300_order_line_revenue/4310_ORL_BAS_order_line_revenue.sql

Update the [drp.order_status] table with the revenue amounts from this block

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4300_order_line_revenue/4315_ORL_BAS_update_amount_order_status.sql

Create the [drp.product_affinity] table

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/4300_order_line_revenue/4320_ORL_BAS_product_affinity.sql

Marketing Performance

This block is being deprecated to be replaced by the [dm_mkt.fct_vendor_level_performance] table that is part of the Marketing Data Mart

Create the [ums.marketing_performance] table

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/pro/4400_marketing_performance/4410_MKP_BAS_marketing_performance.sql