4320_ORL_BAS_product_affinity Transformation

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
drp order_line_revenue database Primary
drp order_status database Lookup
drp_staging olr_orders_updated database Incremental functionality
drp_staging product_affinity database Pre-insert

SQL Flow

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

 4320_ORL_BAS_product_affinity Transformation

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.