Get data from Stripe API and calculates MRR (Monthly Recurring Revenue) for every customer. Each customer could have one or more subscriptions. A subscription could be monthly or yearly, also it could have a discount (percent or fixed amount). Also the data source could return with not paid subscriptions (delinquency)
require "stripe"
require "terminal-table"
class Metrics
def initialize(api_key:)
Stripe.api_key = api_key
@results = []
@last_customer_id_processed = nil
end
def get_mrr
customers = Stripe::Customer.list({limit: 3, starting_after: @last_customer_id_processed})
customers.auto_paging_each do |customer|
@results << [
customer.id,
customer.name,
customer.email,
get_cus_revenue(customer.subscriptions, customer.discount, customer.delinquent)
]
# Save last processed, to resume processing when fails
@last_customer_id_processed = customer.id
end
print_results
end
def get_cus_revenue(cus_subscriptions={}, cus_discount={}, cus_delinquent=false)
return 0.0 if (cus_subscriptions.empty? or cus_subscriptions.total_count == 0)
# Sum for all customer subscriptions
cus_subscriptions_total = 0
cus_subscriptions.data.each do |cus_subscription|
next unless sub_is_valid_for_mrr?(cus_subscription, cus_delinquent)
# Sum for all subscription items
subscription_items_total = 0
cus_subscription.items.data.each do |subscription_item|
subscription_items_total += get_subsciption_item_total(subscription_item)
end
# A discount applied to a subscription overrides a discount applied on a customer-wide basis
if (cus_subscription.discount != nil)
cus_subscriptions_total += apply_discount(subscription_items_total, cus_subscription.discount)
else
cus_subscriptions_total += apply_discount(subscription_items_total, cus_discount)
end
end
cus_subscriptions_total / 100.0
end
def sub_is_valid_for_mrr?(subscription={}, cus_delinquent=false)
# Subscription.status:
# active: valid for MRR
# past_due: valid for MRR (<30 days as delinquent, max duration of payment intents is 21 days)
# unpaid: valid for MRR (unless >30 days as delinquent)
# canceled: valid for MRR (unless >30 days as delinquent)
# trialing: not a customer yet
# incomplete: not a customer yet
# incomplete_expired: not a customer yet
return false if ["trialing", "incomplete", "incomplete_expired"].include?(subscription.status)
return false if ["canceled", "unpaid"].include?(subscription.status) and cus_sub_delinquent_gt_30d?(subscription.id, cus_delinquent)
true
end
# Check if a customer has been delinquent for more than 30 days
# if true, customer not count for MRR
def cus_sub_delinquent_gt_30d?(subscription_id, cus_delinquent)
if cus_delinquent == true
# Retrieve all invoices not payed from the subscription (open and uncollectible)
# each invoice represents a period, so if there are 2 or more unpayed invoices
# means that the customer as churned
invoices_open = Stripe::Invoice.list({
limit: 2,
subscription: subscription_id,
status: "open"
})
invoices_uncollectible = Stripe::Invoice.list({
limit: 2,
subscription: subscription_id,
status: "uncollectible"
})
return (invoices_open.data.count + invoices_uncollectible.data.count) > 1
end
false
end
def get_subsciption_item_total(subscription_item={})
sub_item_total = subscription_item.plan.amount * subscription_item.quantity
if subscription_item.plan.interval == "year"
sub_item_total = sub_item_total / 12
end
sub_item_total
end
def apply_discount(amount, discount)
return amount if discount == nil
if discount.coupon.amount_off != nil
(amount - discount.coupon.amount_off).round
elsif discount.coupon.percent_off != nil
(amount - (amount * discount.coupon.percent_off * 0.01)).round
end
end
def print_results
headings = ["customer_id", "name", "email", "MRR"]
table = Terminal::Table.new(headings: headings, rows: @results)
puts table
end
end