View: UOS Table(s): uos.customer_hshld_lkp

Source Database Table

uos.customer_hshld_lkp, uos.order_hshld_lkp

Script that Populates Database Table

2310_HSH_BAS_customer_order_lkp.sql

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

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