4820_SUB_BAS_subscription_profile Transformation

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
recharge subscriptions database Primary
recharge orders database Lookup
drp order_status database Lookup
recharge charges database Lookup
recharge subscription_properties database Lookup
drp customer_profiles database Lookup
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

SQL Flow

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

4820_SUB_BAS_subscription_profile Transformation

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.