5510_SEG_BAS_customer_segments Transformation

Target Database Table

drp.customer_segments.

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.

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.