0952_UOS_BAS_BSD_customers Transformation

Target Database Table

uos.customers. 

Script Description and Logic

Pulls customers from the BSD orders sheet in your Daasity account created or modified in the 7 days prior to the script run date along with customer ids, email and other associated data. The data is loaded into a unified schema that combines customers from the BSD with data from any other customer source integrations.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
bsd orders database Pulls order ID and channel from BSD orders
uos orders database Primary
drp_staging uos_orders_to_update database Lookup
drp_staging order_business_unit database Pre-insert
drp integration_mapping database Primary
uos locations database Inserts retail location for brick and mortar retail sales
N/A bsd_store_name derived Pulls order ID and channel from BSD orders, and deduplicates on order ID

SQL Flow

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

0952_UOS_BAS_BSD_customers Transformation

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
store_type VARCHAR drp.integration_mapping type CASE
WHEN orders.__uos_source = 'BSD' THEN 'MANUAL ORDERS'
WHEN integration_mappings.type = 'ECOMMERCE' AND locations.location_id IS NOT NULL THEN 'RETAIL'
ELSE integration_mappings.type
END
store_name VARCHAR drp.integration_mapping type CASE
WHEN orders.__uos_source = 'BSD' THEN bsn.business_channel
WHEN integration_mappings.type = 'ECOMMERCE' THEN NVL(locations.location_name, INITCAP(integration_mappings.name))
ELSE integration_mappings.name
END
business_unit VARCHAR drp.integration_mapping business_unit CASE
WHEN orders.__uos_source = 'BSD' THEN 'BSD'
ELSE integration_mappings.business_unit
END


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