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.
/SQL%20Transformations/5510_SEG_BAS_customer_segments%20Transformation.png?width=688&name=5510_SEG_BAS_customer_segments%20Transformation.png)
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.