Source Database Table
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|
|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|
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
All content © Daasity 2021. Do not copy, share or distribute.