Target Database Table
Script Description and Logic
Pulls the subscriber id, subscription-related dates, customer id and generates subscription flags at the subscriber level. Subscribers from all Recharge integrations are included in the primary source table, recharge.subscriptions, and reported by unique subscriber id and customer id.
From the recharge.subscription_properties table, two lookups are made to generate a list shipping types and a list containing shipping frequency.
These looks are then joined together to match subscription ids to their types and frequency as shipping_type_frequency.
Next recharge.subscriptions is queried to create flags then is rolled-up to the order unit and frequency level as subscription_level_data. Recharge.orders is then joined to order related information in uos.orders and order related tables in drp and stored as sub_orders. sub_orders is then queried to pull the maximum order count for each subscription id as max_orders.
Recharge.subscriptions is then joined to sub_orders to pull recharge_first_recurring_order and then joined to drp.customer_profiles to generate recharge_first_order.
Sub_orders is then rolled-up to the subscription id. subscription_level_data, shipping_type_frequency, recharge_first_order, recharge_first_recurring_order, order_rollup, max_orders and sub_orders are then joined together to generate a table of unique subscribers , subscriptions and flags.
All records are removed from the final table and updated by way of insertion from the query (upsert).
Source Tables Used In Script
|Schema||Table (or Derived Table) Name||Table Type||Purpose|
|N/A||shipping_type||derived||Obtains subscription shipping values where name = shipping_interval_unit_type|
|N/A||shipping_frequency||derived||Obtains subscription shipping values where name = shipping_interval_frequency|
|N/A||shipping_type_frequency||derived||A joining of shipping_type and shipping_frequency derived tables|
|N/A||subscription_level_data||derived||Obtains subscription frequency and creates quick_cancel_flag|
|N/A||sub_orders||derived||Obtains a matching of subscriptions to order related information|
|N/A||max_orders||derived||Obtains max orders in subscription orders|
|N/A||recharge_first_order||derived||Obtains first order in a subscription|
|N/A||recharge_first_recurring_order||derived||Obtains second order in a subscription where recurring_sub_order_flag = TRUE|
|N/A||order_rollup||derived||Rollup of derived sub_orders to the recharge subscription level|
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|
|quick_cancel_flag||BOOLEAN||recharge.subscriptions||cancelled_at, created_at||if( created_at date - cancelled_at) < 24 hours then flag = TRUE|
|recurring_order_completion_flag||BOOLEAN||drp.order_status||order_date||if(first_recurring_order_date) = NULL then flag = TRUE|
|daily_shipping_interval_frequency||INT||recharge.subscriptions||order_interval_frequency||Converts shipping frequency into days, weeks, months or years based on shipping_interval_unit_type|
|first_order_subscriber_flag||BOOLEAN||drp.customer_profile||first_order_id||if(first_order_id) is populated then flag = TRUE|
|days_active||INT||recharge.subscriptions||created_at||difference in days between created_at and subscription_end_date|
|cancel_before_first_purchase||BOOLEAN||recharge.subscriptions||cancelled_at, subscription_status||if(subscription_end_date < order_date) or (order_date = NULL and subscription_status = cancelled) then flag = TRUE|
All content © Daasity 2021. Do not copy, share or distribute.