6530_PLN_BAS_sku_performance

Target Database Table

drp.sku_performance

Script Description and Logic

Pulls the performance id, launch date, days since launch, and metrics of an SKU at the store level. Products from all integrations are included in the primary source table, drp.order_line_revenue, and reported by unique SKU performance id and shop.

From the drp.order_line_revenue table, a join is made to drp.order_business unit variants to get every unique SKU and store combination. The resulting table is then staged as sku_combinations.

Next, order_line_revenue and sku_combinations are joined to generate a list of SKU, their unique dates since the product was the first order, and then is stored as sku_date. Sales metrics are rolled up to the daily SKU level through a join between order_line_revenue and order_business_unit as sku_sales. Sku_sales and sku_dates are then joined together to generate a table of unique SKU and performance dates.

The table is then staged for insertion as sku_performance. Incremental functionality is included such that only the records that require an update or are new get inserted into the final drp.sku_performance table.

The records in staging are compared to those in the final table. Any SKU performance ids that match are removed from the final table and updated by way of insertion from staging (upsert). The records that appear in staging, but not in the final table are inserted into drp.sku_performance.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
drp order_line_revenue database Primary
drp order_busuiness_unit database Lookup
drp sku_launch_date database Lookup
drp calendar database Lookup
drp_staging sku_combinations database Rollup of order_line_revenue and order_business_unit to find unique combinations of sku and store
drp_staging sku_sales database Rollup sku sales by unique store, sku and order date
drp_staging sku_dates database Holds sku_performance_date
drp_staging sku_performance database Pre-insert
N/A first_order_date derived Obtain the first date an order was made in a store
N/A sku_performance_date derived Obtain a list of skus for every date from the sku's first order date

SQL Flow

To View Larger Image, Right Click and Choose Open Image In New Tab.

6530_PLN_BAS_sku_performance Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
days_since_launch INT drp.sku_launch_date sku_launch_date difference in days between sku_launch_date and current date
product_sales_amount DECIMAL drp.order_line_revenue product_sales_amount SUM(product_sales_amount) by each unique store, sku and date combination
paid_units INT drp.order_line_revenue num_feature_items SUM(num_featured_items) by each unique store, sku and date combination
total_units INT drp.order_line_revenue num_units SUM(num_units) by each unique store, sku and date combination


All content © Daasity 2021. Do not copy, share or distribute.