4810_SUB_BAS_subscribers Transformation

Target Database Table

drp.subscribers.

Script Description and Logic

Pulls the subscriber id, subscription-related dates and counts, customer id at the subscriber level from Recharge. Subscribers from all Recharge integrations are included in the primary source table, recharge.subscriptions, and reported by unique subscriber id. From the recharge.subscriptions table, a rollup is made to get every unique recharge customer id and a count of subscriptions at the recharge customer level. The resulting table is then staged as subscribers.

Next, recharge.subscriptions is joined to recharge.orders and then to uos.orders and customers to create a count of customers by their recharge customer id, this ties together Recharge emails to store order related tables, and is stored as customer_counts.

The subscriptions in staged subscribers table are sequenced the created_at date of the subscription by the subscriber id and stored as sub_sequence. Sub_sequence is then queried to pull a list of first subscriptions information and most recent subscription information.

The staged tables named subscribers, first_sub, most_recent_subs and customer_counts are then joined together to generate a table of unique subscribers and subscription related counts. All records are removed from the final table, subscribers, 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
uos customers database Lookup
N/A subscribers derived Obtain the count of subscriptions and active subscriptions from Recharge subscriptions
N/A customer_counts derived Obtain a count of customers and emails by Recharge subscriber
N/A sub_sequence derived Obtain a list of each subscriber with the order they have subscribed and unsubscribed
N/A first_sub derived Obtains information for subscribers first subscription
N/A most_recent_sub_id derived Obtain sequence number for subscribers most recent subscription
N/A most_recent_subs derived Obtain start and cancel dates for subscribers most recent subscription

SQL Flow

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

4810_SUB_BAS_subscribers Transformation

Calculated and Derived Fields

Target Column Target Column Data Type Source schema.table Source Column Transformation/Logic
store_customer_id_count BIGINT uos.customers store_customer_id COUNT(unique store_customer_id)
customer_id_count BIGINT uos.customers customer_id COUNT(unique customer_id)
email_count BIGINT uos.customers email COUNT(unique email)
first_sub_start_date TIMESTAMP WITHOUT TIME ZONE recharge.subscriptions created_at created_at date of a customers first subscription in Recharge
first_sub_end_date TIMESTAMP WITHOUT TIME ZONE recharge.subscriptions cancelled_at cancelled_at date of a customers first subscription in Recharge
total_subscription_count BIGINT recharge.subscriptions recharge_subscription_id COUNT(unique subscription_id) in Recharge
most_recent_sub_start_date TIMESTAMP WITHOUT TIME ZONE recharge.subscriptions created_at created_at date of a customers most recent subscription in Recharge
most_recent_sub_end_date TIMESTAMP WITHOUT TIME ZONE recharge.subscriptions cancelled_at cancelled_at date of a customers most recent subscription in Recharge
active_subscription_count BIGINT recharge.subscriptions recharge_subscription_id COUNT(unique subscription_id) WHERE subscription_state = active in Recharge


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