Target Database Table
Script Description and Logic
Pulls product affinity id, days between first and second order, unique customer id, order sequence number and some product-related attributes at the order level. Orders from all integrations are included in the primary source table, drp.order_line_revenue, and reported by unique product_affinity_id.
From the drp.product_affinity and drp.order_line_revenue, a lookup is made to return a list of order_ids in order_line_revenue that have been updated in the last seven days. This list is then inserted into drp_staging.olr_orders_updated.
From the order_line_revenue table, a join is made to itself and to olr_orders_updated to determine the first and second products in a cart and which order_ids have been updated. The unique product combination of first and second products are then flagged as same cart products. The sum of number of units in each order and combination is aggregated across all time and rolled up to the unique order and product level.
The unique combinations of order id and first and last products are then placed in a staging table in preparation for incremental functionality. Incremental functionality is included such that only the records that require an update or are new get inserted into the final product_affinity table.
The records in staging are compared to those in the final table. Any order ids that match are removed from the final table and updated by way of insertion from staging. The records that appear in staging, but not in the final table are inserted into drp.product_affinity.
Source Tables Used In Script
|Schema||Table (or Derived Table) Name||Table Type||Purpose|
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|
|product_affinity_id||CHAR||drp||order_line_revenue.product_name||Combines and encrypts first and second order product_name, product_id, sku and sequence_number into a unique id|
|days_between_order_tiers||VARCHAR||drp||order_line_revenue.order_date||If first order_date - second order_date > 0 then assign a bucket to the order sequence. ex: 1-10 day, 41-50 days|
All content © Daasity 2021. Do not copy, share or distribute.