5110_LTV_BAS_ltv_time_series Transformation

Target Database Table

drp.ltv_time_series.

Script Description and Logic

Pulls ltv id(unique customer id + time of buy), customer-related flags and metrics at the unique customer tob level. Customers from all integrations are included in the primary source table, drp.order_line_revenue, and reported by unique ltv id.

From the drp.ltv_time_series table, a lookup is made to generate a list of last tob end dates. The code looks back 45 days from last_tob_end_date to get a list of customers, joins them to the list of last tob end dates, and then stores this list in a staging table as tob_to_update. A lookup is performed to generate a list of valid order lines as valid_order_lines.

Next tob_to_update and valid_order_lines are joined together and all order metrics are rolled up to the unique customer tob level. The resulting table is then staged for insertion. Incremental functionality is included such that only the records that require an update or are new get inserted into the final drp.ltv_time_series table. The records in staging are compared to those in the final table.

Any ltv ids that match are removed from the final table and updated by way of insertion from staging (upsert). The records that appear in staging, but not in the final table are inserted into drp.ltv_time_series.

Source Tables Used In Script

Schema Table (or Derived Table) Name Table Type Purpose
drp order_line_revenue database Primary
drp customer_profiles database Primary
drp order_business_unit database Lookup
drp_staging ltv_time_series database Pre-insert
N/A last_tob_end_date derived Incremental functionality
N/A tob derived Lookup
N/A tob_user derived Obtain unique records at each customer and TOB
N/A tob_to_update derived Incremental functionality
N/A valid_order_lines derived Obtain valid order ever made by the customer in each store

SQL Flow

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

5110_LTV_BAS_ltv_time_series Transformation
Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
tob_end_date TIMESTAMP WITHOUT TIME ZONE drp.customer_profiles first_order_date WHEN tob = 0 then tob_end_date = first_order_date, ELSE (first_order_date + (tob * 30))
tob_start_date TIMESTAMP WITHOUT TIME ZONE drp.customer_profiles first_order_date WHEN tob = 0 then tob_start_date = first_order_date, ELSE (first_order_date + ((tob - 1) * 30)) + 1
total_gross_sales DECIMAL drp.order_line_revenue gross_sales SUM(gross_sales) by unique customer tob
total_gross_margin DECIMAL drp.order_line_revenue gross_margin SUM(gross_margin) by unique customer tob
total_contribution_margin DECIMAL drp.order_line_revenue lc_net_contribution SUM(lc_net_contribution) by unique customer tob
total_orders INT drp.order_line_revenue order_id COUNT(order_id) by unique customer tob, includes valid orders only
total_items INT drp.order_line_revenue num_units SUM(units) by unique customer tob
direct_gross_sales DECIMAL drp.order_line_revenue gross_sales SUM(gross_sales) by unique customer tob WHERE store_type = ECOMMERCE
direct_gross_margin DECIMAL drp.order_line_revenue gross_margin SUM(gross_margin) by unique customer tob WHERE store_type = ECOMMERCE
direct_contribution_margin DECIMAL drp.order_line_revenue lc_net_contribution SUM(lc_net_contribution) by unique customer tob WHERE store_type = ECOMMERCE
direct_orders INT drp.order_line_revenue order_id COUNT(order_id) by unique customer tob WHERE store_type = ECOMMERCE, includes valid orders only
direct_items INT drp.order_line_revenue num_units SUM(units) by unique customer tob WHERE store_type = ECOMMERCE
retail_gross_sales DECIMAL drp.order_line_revenue gross_sales SUM(gross_sales) by unique customer tob WHERE store_type = RETAIL
retail_gross_margin DECIMAL drp.order_line_revenue gross_margin SUM(gross_margin) by unique customer tob WHERE store_type = RETAIL
retail_contribution_margin DECIMAL drp.order_line_revenue lc_net_contribution SUM(lc_net_contribution) by unique customer tob WHERE store_type = RETAIL
retail_orders INT drp.order_line_revenue order_id COUNT(order_id) by unique customer tob WHERE store_type = RETAIL, includes valid orders only
retail_items INT drp.order_line_revenue num_units SUM(units) by unique customer tob WHERE store_type = RETAIL
marketplace_gross_sales DECIMAL drp.order_line_revenue gross_sales SUM(gross_sales) by unique customer tob WHERE store type = AMAZON or MARKETPLACE
marketplace_gross_margin DECIMAL drp.order_line_revenue gross_margin SUM(gross_margin) by unique customer tob WHERE store type = AMAZON or MARKETPLACE
marketplace_contribution_margin DECIMAL drp.order_line_revenue lc_net_contribution SUM(lc_net_contribution) by unique customer tob WHERE store type = AMAZON or MARKETPLACE
marketplace_orders INT drp.order_line_revenue order_id COUNT(order_id) by unique customer tob WHERE store type = AMAZON or MARKETPLACE, includes valid orders only
marketplace_items INT drp.order_line_revenue num_units SUM(units) by unique customer tob WHERE store type = AMAZON or MARKETPLACE
customer_purchased INT drp.order_line_revenue gross_sales if SUM(gross_sales) per unique customer and tob > 0 then flag = TRUE
customer_ever_repurchased INT drp.order_line_revenue second_order_date if (first_order_date <> last_order_date) and (second_order_date <= date at tob = 0) then flag = TRUE


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