4620_CUS_BAS_customer_profiles Transformation

Target Database Table


Script Description and Logic

Pulls customer id, customer-related flags and metrics at the store level. Customers from all integrations are included in the primary source table, drp.order_line_revenue, and reported by unique customer id. From the customer_hshld_lkp table, a lookup is made to generate a list of unique customer ids and the records last load date. The code looks back 7 days from last_load_dt to get that list of customers and then holds them in a staging table as customer_profiles_to_update. From drp.order_status a join to order_channel and order_business_unit is made, then a lookup to generate order sequence numbers is performed and staged as order_sequence.

A lookup is performed against order_sequence to create 3 lists: first_order, second_order and last_order. Order_sequence is then joined to order_line_revenue to rollup order line level metrics to the unique customer level. These customer level metrics are then used to segment customers by recency group, frequency group and monetary group. From customer_profiles_to_update, a join is made between all other staged tables, the resulting table is then staged for insertion.

Incremental functionality is included such that only the records that require an update or are new get inserted into the final drp_customer_profiles table. The records in staging are compared to those in the final table. Any customers 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_customer_profiles.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
drp order_channel database Lookup
drp order_business_unit database Lookup
drp order_status database Lookup
drp order_line_revenue database Primary
platform customer_monetary_group database Lookup
platform customer_frequency_group database Lookup
platform customer_recency_group database Lookup
uos customer_hshld_lkp database Ensure unique records
drp_staging customer_profiles_to_update database Incremental functionality
drp_staging customer_profiles database Pre-insert
N/A last_sync_date derived Obtain last_sync_date of unique records in drp.customer_profile
N/A customers_since_last_sync derived Obtain which unique customers have been updated in the last seven
N/A order_sequence derived Generates a sequenced list of orders by customer
N/A first_order derived Obtain first valid order by unique customer and store
N/A second_order derived Obtain second valid order by unique customer and store
N/A last_order derived Obtain latest valid order by unique customer and store
N/A order_summary derived Roll-up of order_sequence to the customer level
N/A customer_recency_group derived Segment unique customers by most recent purchase date
N/A customer_frequency_group derived Segment unique customer by order frequency
N/A customer_monetary_group derived Segment unique customers by spend
N/A rfm_deciles derived Combines recency, frequency and monetary logic to score deciles
N/A all_customers derived Obtain unique customers list

SQL Flow

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

4620_CUS_BAS_customer_profiles Transformation

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
num_orders BIGINT drp.order_status order_id if CASE(valid_order_flag = TRUE) then COUNT(order_id)
num_sku BIGINT drp.order_line_revenue order_line_id if CASE(valid_order_flag = TRUE) then COUNT(order_line_id)
num_units BIGINT drp.order_line_revenue num_units SUM(num_units) by unique customer
discount_amount DECIMAL drp.order_line_revenue discount_amount SUM(discount_amount) by unique customer
gross_amount_charged DECIMAL drp.order_line_revenue gross_amount_charged SUM(gross_amount_charged) by unique customer
tax_amount DECIMAL drp.order_line_revenue tax_amount SUM(tax_amount) by unique customer
gross_sales DECIMAL drp.order_line_revenue gross_sales SUM(gross_sales) by unique customer
refund_amount DECIMAL drp.order_line_revenue refund_amount SUM(refund_amount) by unique customer
net_sales DECIMAL drp.order_line_revenue net_sales SUM(net_sales) by unique customer
shipping_amount DECIMAL drp.order_line_revenue shipping_amount SUM(shipping_amount) by unique customer
product_sales_amount DECIMAL drp.order_line_revenue product_sales_amount SUM(product_sales_amount) by unique customer
gift_card_sales_amount DECIMAL drp.order_line_revenue gift_card_sales_amount SUM(gift_card_sales_amount) by unique customer
gross_margin DECIMAL drp.order_line_revenue gross_margin SUM(gross_margin) by unique customer
fc_net_contribution DECIMAL drp.order_line_revenue fc_net_contribution SUM(fc_net_contribution) by unique customer
lc_net_contribution DECIMAL drp.order_line_revenue lc_net_contribution SUM(lc_net_contribution) by unique customer
retail_buyer BOOLEAN drp.order_business_unit store_type if store_type = RETAIL then SUM(gross_sales) by unique customer
ecommerce_buyer BOOLEAN drp.order_business_unit store_type if store_type = ECOMMERCE then SUM(gross_sales) by unique customer
marketplace_buyer BOOLEAN drp.order_business_unit store_type if store_type = AMAZON then SUM(gross_sales) by unique customer
omnichannel_buyer BOOLEAN drp.order_business_unit store_type if COUNT(gross sale) in RETAIL, ECOMMERCE and AMAZON > 0 then flag = TRUE
customer_recency_group VARCHAR drp.order_line_revenue order_date Segment unique customers by DIFF(recent purchase date - current date)
customer_frequency_group VARCHAR drp.order_line_revenue num_orders Segment unique customer into order frequency buckets
customer_monetary_group VARCHAR drp.order_line_revenue gross_sales Segment unique customers into spend buckets
rfm_score REAL drp.order_line_revenue N/A Applies additional logic to recency, frequency and monetary groups to create overall score
customer_recency_pentile BIGINT drp.order_line_revenue order_date Applies pentile logic to customer recency group
customer_frequency_pentile BIGINT drp.order_line_revenue num_orders Applies pentile logic to customer frequency group
customer_monetary_pentile BIGINT drp.order_line_revenue gross_sales Applies pentile logic to customer monetary group
rfm_score_decile BIGINT drp.order_line_revenue N/A Applies decile logic to customer rfm_score

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