2310_HSH_BAS_customer_order_lkp Transformation

Target Database Table

uos.customer_hshld_lkp, uos.order_hshld_lkp.

Script Description and Logic

Performs customer and order dimension lookup for the purpose of customer householding. Creates a temporary lookup table of customer and order dimensions including shop name, order ID and order code, customer email, first and last name, and mailing address, as well as a scoring system for orders with either null or non-null values used in the householding lookup. Those lookup values are email, first and last names, city, and zipcode.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
uos customers database Primary
uos orders database Primary
platform account_integrations database Primary
drp_staging tmp_uos_order_lkp database Incremental functionality
N/A address_info derived Pulls in customer address info
N/A address_score derived Creates a score for each of the match variables based on which variables are non-null for a particular order

SQL Flow

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

2310_HSH_BAS_customer_order_lkp Transformation
Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
matching_key VARCHAR uos.orders order_id CONCATENATE order_id and customer_id
unique_id VARCHAR uos.orders order_code CASE WHEN LENGTH(customers.first_name) > 1 AND
LENGTH(customers.last_name) > 1 AND
LENGTH(customers.city) > 1 AND
LENGTH(customers.zipcode) > 1
THEN LOWER(customers.first_name)||'-'||LOWER(customers.last_name)||'-'||LOWER(customers.city)||'-'||customers.zipcode
ELSE orders.order_code

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