Target Database Table
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_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|
|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|
To View Larger Image, Right Click and Choose Open Image In New Tab.
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.