5510_SEG_BAS_customer_segments Transformation

Target Database Table


Script Description and Logic

Pulls segment id, customer id ,and customer segments name and value at the unique segment id level. Customers from all integrations are included in the primary source table, drp.customer_profile, and reported by unique customer segment id.

From the drp.ltv_time_series table, a join is made to drp.customer_profile to rollup the tables the unique customer level, and then staged as ltv. Logic is performed on ltv to generate customer segments: aov_segment, repurch_seg, ltv_segment, dbo_segment(dates between orders), and churn_risk_segment. The code stores this table as drp_staging.customer_segments.

Next 5 lookups are performed on customer_segments to create a list of customers and segment values for each segment. These 5 lookups are then staged, each lookup will be inserted into the final table. All records are removed from the final table and updated by way of insertion from staging (upsert).

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
drp customer_profiles database Primary
drp ltv_time_series database Primary
N/A ltv derived Rollup of ltv to the customer level
N/A segments derived Segments customer in ltv by repurchase, ltv, churn risk and dates between orders
drp_staging customer_segments database Incremental functionality

SQL Flow

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

 5510_SEG_BAS_customer_segments Transformation
Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
segment_value VARCHAR drp_staging.customer_segments N/A Customers assigned 1 value from each segment. Values are low_aov or high_aov, low ltv or high ltv, low churn risk or high churn risk, and in past optimal or in optimal date between last order segments.

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