Target Database Table
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|
|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|
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|
|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.