4610_CUS_BAS_customers Transformation

Target Database Table


Script Description and Logic

Pulls customer name, phone, email, address and some order-related flags at the store level. Customers from all integrations are included in the primary source table, uos.customers, and reported by unique email address and store combination. From the customer table, a join is made to drp.order_status to lookup if the customer has ever made a valid order and if they have ever made a purchase greater than $0.

The has_purchased_flag and has_made_valid_order_flag by customer is aggregated across all time and rolled up to the unique customer and store level. Incremental functionality is included such that only the records that require an update or are new get inserted into the final drp.customers table.

The code looks back 7 days from last_load_dt to get that list of customers and then holds them in a staging 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.customers.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
uos customers database Primary
uos customer_hshld_lkp database Ensure unique records
drp integration_mapping database Ensure unique records
drp order_status database Lookup
drp_staging uos_customers_to_update database Incremental functionality
drp_staging customers database Pre-insert

SQL Flow

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

4610_CUS_BAS_customers Transformation
Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
has_purchased_flag BOOLEAN drp.order_status total if SUM(total) per unique customer id, __shop_id and integration name > 0 then flag = TRUE
has_made_valid_order_flag BOOLEAN drp.order_status valid_order_flag if COUNT(valid orders per unique customer id, __shop_id and integration name > 0 then flag = TRUE

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