View: UOS Table(s): uos.customer_hshld_lkp

Source Database Table

uos.customer_hshld_lkp, uos.order_hshld_lkp

Script that Populates Database Table


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.zipcode) > 1
THEN LOWER(customers.first_name)||'-'||LOWER(customers.last_name)||'-'||LOWER(||'-'||customers.zipcode
ELSE orders.order_code


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