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
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 missing tables in case Google Analytics has not been setup:
Calculate the appropriate cost per order based on the attribution and allocate to the item level:
Calculate the shipping and fulfillment costs per item
Create the final [drp.order_line_revenue] table
Update the [drp.order_status] table with the revenue amounts from this block
Create the [drp.product_affinity] table
Marketing Performance
Create the [ums.marketing_performance] table
Was this helpful?