paypal.sql

WITH new_subscriptions AS (
	SELECT
		date_trunc('month', e.create_time)::date as date,
		e.recurrence,
		count(*) as new_subscriptions
	FROM (
		SELECT
			id,
			create_time,
			resource#>'{plan,payment_definitions,0,frequency_interval}' as recurrence
		FROM djpaypal_webhookevent
		WHERE
			livemode = true AND
			event_type = 'BILLING.SUBSCRIPTION.CREATED'
		ORDER BY create_time asc
	) e
	GROUP BY e.recurrence, date
), cancelled_subscriptions AS (
	SELECT
		date_trunc('month', e.create_time)::date as date,
		e.recurrence,
		count(*) as cancelled_subscriptions
	FROM (
		SELECT
			id,
			create_time,
			resource#>'{plan,payment_definitions,0,frequency_interval}' as recurrence
		FROM djpaypal_webhookevent
		WHERE
			livemode = true AND
			event_type = 'BILLING.SUBSCRIPTION.CANCELLED'
		ORDER BY create_time asc
	) e
	GROUP BY e.recurrence, date
), terminated_subscriptions AS (
	SELECT
		date_trunc('month', e.termination_time::timestamptz)::date as date,
		e.recurrence,
		count(*) as terminated_subscriptions
	FROM (
		SELECT
			id,
			create_time,
			resource#>'{plan,payment_definitions,0,frequency_interval}' as recurrence,
			(resource->'agreement_details'->>'next_billing_date')::timestamptz as termination_time
		FROM djpaypal_webhookevent
		WHERE
			livemode = true AND
			event_type = 'BILLING.SUBSCRIPTION.CANCELLED'
		ORDER BY create_time asc
	) e
	GROUP BY e.recurrence, date
)
SELECT
	COALESCE(ns.date, cs.date, ts.date) as date,
	COALESCE(ns.recurrence, cs.recurrence, ts.recurrence) as recurrence,
	ns.new_subscriptions,
	cs.cancelled_subscriptions,
	ts.terminated_subscriptions
FROM new_subscriptions ns
FULL OUTER JOIN cancelled_subscriptions cs ON cs.date = ns.date AND cs.recurrence = ns.recurrence
FULL OUTER JOIN terminated_subscriptions ts ON ts.date = ns.date AND ts.recurrence = ns.recurrence
ORDER BY date, ns.recurrence;

stripe.sql

WITH new_subscriptions AS (
	SELECT
		date_trunc('month', e.stripe_timestamp)::date as date,
		e.plan,
		count(*) as new_subscriptions
	FROM (
		SELECT
			stripe_id,
			stripe_timestamp,
			data#>'{object,plan,id}' as plan,
			data#>'{object,discount}' as discount
		FROM djstripe_event
		WHERE
			livemode = true AND
			type = 'customer.subscription.created'
		ORDER BY stripe_timestamp asc
	) e
	GROUP BY e.plan, date
), terminated_subscriptions AS (
	SELECT
		date_trunc('month', e.stripe_timestamp)::date as date,
		e.plan,
		count(*) as terminated_subscriptions
	FROM (
		SELECT
			stripe_id,
			stripe_timestamp,
			data#>'{object,plan,id}' as plan
		FROM djstripe_event
		WHERE
			livemode = true AND
			type = 'customer.subscription.deleted'
		ORDER BY stripe_timestamp asc
	) e
	GROUP BY e.plan, date
), fully_discounted_charges AS (
	SELECT
		date_trunc('month', e.stripe_timestamp)::date as date,
		e.plan as plan,
		count(*) as fully_discounted_charges
	FROM (
		SELECT
			stripe_id,
			stripe_timestamp,
			data#>'{object,discount,coupon,id}' as coupon,
			data#>'{object,subtotal}' as subtotal,
			data#>'{object,lines,data,0,plan,id}' as plan,
			data#>'{object,total}' as total
		FROM djstripe_event
		WHERE
			livemode = true AND
			type = 'invoice.payment_succeeded'
		ORDER BY stripe_timestamp asc
	) e
	WHERE total = '0'
	GROUP BY e.plan, date
), eop_cancellations AS (
	SELECT
		date_trunc('month', e.stripe_timestamp)::date as date,
		e.plan as plan,
		count(*) as eop_cancellations
	FROM (
		SELECT
			stripe_id,
			stripe_timestamp,
			data#>'{object,plan,id}' as plan,
			data#>'{previous_attributes,cancel_at_period_end}' as was_cancelled_before,
			data#>'{object,cancel_at_period_end}' AS cancel_at_period_end
		FROM djstripe_event
		WHERE
			livemode = true AND
			type = 'customer.subscription.updated'
		ORDER BY stripe_timestamp asc
	) e
	WHERE was_cancelled_before = 'false' AND cancel_at_period_end = 'true'
	GROUP BY e.plan, date
), eop_reactivations AS (
	SELECT
		date_trunc('month', e.stripe_timestamp)::date as date,
		e.plan as plan,
		count(*) as eop_reactivations
	FROM (
		SELECT
			stripe_id,
			stripe_timestamp,
			data#>'{object,plan,id}' as plan,
			data#>'{previous_attributes,cancel_at_period_end}' as was_cancelled_before,
			data#>'{object,cancel_at_period_end}' AS cancel_at_period_end
		FROM djstripe_event
		WHERE
			livemode = true AND
			type = 'customer.subscription.updated'
		ORDER BY stripe_timestamp asc
	) e
	WHERE was_cancelled_before = 'true' AND cancel_at_period_end = 'false'
	GROUP BY e.plan, date
)
SELECT
	ns.date,
	ns.plan,
	ns.new_subscriptions,
	ts.terminated_subscriptions,
	ds.fully_discounted_charges,
	ec.eop_cancellations,
	er.eop_reactivations
FROM new_subscriptions ns
FULL OUTER JOIN terminated_subscriptions ts ON ts.date = ns.date AND ts.plan = ns.plan
FULL OUTER JOIN fully_discounted_charges ds ON ds.date = ns.date AND ds.plan = ns.plan
FULL OUTER JOIN eop_cancellations ec ON ec.date = ns.date AND ec.plan = ns.plan
FULL OUTER JOIN eop_reactivations er ON er.date = ns.date AND er.plan = ns.plan
WHERE ns.plan != '"hearthsim-pro-mvp"'
ORDER BY ns.date, ns.plan;

update_gsheets.py

#!/usr/bin/env python3

import os
import gspread
import psycopg2


PAYPAL_SQL = """
WITH new_subscriptions AS (
	SELECT
		date_trunc('month', e.create_time)::date as date,
		e.recurrence,
		count(*) as new_subscriptions
	FROM (
		SELECT
			id,
			create_time,
			resource#>'{plan,payment_definitions,0,frequency_interval}' as recurrence
		FROM djpaypal_webhookevent
		WHERE
			livemode = true AND
			event_type = 'BILLING.SUBSCRIPTION.CREATED'
		ORDER BY create_time asc
	) e
	GROUP BY e.recurrence, date
), cancelled_subscriptions AS (
	SELECT
		date_trunc('month', e.create_time)::date as date,
		e.recurrence,
		count(*) as cancelled_subscriptions
	FROM (
		SELECT
			id,
			create_time,
			resource#>'{plan,payment_definitions,0,frequency_interval}' as recurrence
		FROM djpaypal_webhookevent
		WHERE
			livemode = true AND
			event_type = 'BILLING.SUBSCRIPTION.CANCELLED'
		ORDER BY create_time asc
	) e
	GROUP BY e.recurrence, date
), terminated_subscriptions AS (
	SELECT
		date_trunc('month', e.termination_time::timestamptz)::date as date,
		e.recurrence,
		count(*) as terminated_subscriptions
	FROM (
		SELECT
			id,
			create_time,
			resource#>'{plan,payment_definitions,0,frequency_interval}' as recurrence,
			(resource->'agreement_details'->>'next_billing_date')::timestamptz as termination_time
		FROM djpaypal_webhookevent
		WHERE
			livemode = true AND
			event_type = 'BILLING.SUBSCRIPTION.CANCELLED'
		ORDER BY create_time asc
	) e
	GROUP BY e.recurrence, date
)
SELECT
	COALESCE(ns.date, cs.date, ts.date) as date,
	COALESCE(ns.recurrence, cs.recurrence, ts.recurrence) as recurrence,
	ns.new_subscriptions,
	cs.cancelled_subscriptions,
	ts.terminated_subscriptions
FROM new_subscriptions ns
FULL OUTER JOIN cancelled_subscriptions cs ON cs.date = ns.date AND cs.recurrence = ns.recurrence
FULL OUTER JOIN terminated_subscriptions ts ON ts.date = ns.date AND ts.recurrence = ns.recurrence
ORDER BY date, ns.recurrence;
"""


STRIPE_SQL = """
WITH new_subscriptions AS (
	SELECT
		date_trunc('month', e.stripe_timestamp)::date as date,
		e.plan,
		count(*) as new_subscriptions
	FROM (
		SELECT
			stripe_id,
			stripe_timestamp,
			data#>'{object,plan,id}' as plan,
			data#>'{object,discount}' as discount
		FROM djstripe_event
		WHERE
			livemode = true AND
			type = 'customer.subscription.created'
		ORDER BY stripe_timestamp asc
	) e
	GROUP BY e.plan, date
), terminated_subscriptions AS (
	SELECT
		date_trunc('month', e.stripe_timestamp)::date as date,
		e.plan,
		count(*) as terminated_subscriptions
	FROM (
		SELECT
			stripe_id,
			stripe_timestamp,
			data#>'{object,plan,id}' as plan
		FROM djstripe_event
		WHERE
			livemode = true AND
			type = 'customer.subscription.deleted'
		ORDER BY stripe_timestamp asc
	) e
	GROUP BY e.plan, date
), fully_discounted_charges AS (
	SELECT
		date_trunc('month', e.stripe_timestamp)::date as date,
		e.plan as plan,
		count(*) as fully_discounted_charges
	FROM (
		SELECT
			stripe_id,
			stripe_timestamp,
			data#>'{object,discount,coupon,id}' as coupon,
			data#>'{object,subtotal}' as subtotal,
			data#>'{object,lines,data,0,plan,id}' as plan,
			data#>'{object,total}' as total
		FROM djstripe_event
		WHERE
			livemode = true AND
			type = 'invoice.payment_succeeded'
		ORDER BY stripe_timestamp asc
	) e
	WHERE total = '0'
	GROUP BY e.plan, date
), eop_cancellations AS (
	SELECT
		date_trunc('month', e.stripe_timestamp)::date as date,
		e.plan as plan,
		count(*) as eop_cancellations
	FROM (
		SELECT
			stripe_id,
			stripe_timestamp,
			data#>'{object,plan,id}' as plan,
			data#>'{previous_attributes,cancel_at_period_end}' as was_cancelled_before,
			data#>'{object,cancel_at_period_end}' AS cancel_at_period_end
		FROM djstripe_event
		WHERE
			livemode = true AND
			type = 'customer.subscription.updated'
		ORDER BY stripe_timestamp asc
	) e
	WHERE was_cancelled_before = 'false' AND cancel_at_period_end = 'true'
	GROUP BY e.plan, date
), eop_reactivations AS (
	SELECT
		date_trunc('month', e.stripe_timestamp)::date as date,
		e.plan as plan,
		count(*) as eop_reactivations
	FROM (
		SELECT
			stripe_id,
			stripe_timestamp,
			data#>'{object,plan,id}' as plan,
			data#>'{previous_attributes,cancel_at_period_end}' as was_cancelled_before,
			data#>'{object,cancel_at_period_end}' AS cancel_at_period_end
		FROM djstripe_event
		WHERE
			livemode = true AND
			type = 'customer.subscription.updated'
		ORDER BY stripe_timestamp asc
	) e
	WHERE was_cancelled_before = 'true' AND cancel_at_period_end = 'false'
	GROUP BY e.plan, date
)
SELECT
	ns.date,
	ns.plan,
	ns.new_subscriptions,
	ts.terminated_subscriptions,
	ds.fully_discounted_charges,
	ec.eop_cancellations,
	er.eop_reactivations
FROM new_subscriptions ns
FULL OUTER JOIN terminated_subscriptions ts ON ts.date = ns.date AND ts.plan = ns.plan
FULL OUTER JOIN fully_discounted_charges ds ON ds.date = ns.date AND ds.plan = ns.plan
FULL OUTER JOIN eop_cancellations ec ON ec.date = ns.date AND ec.plan = ns.plan
FULL OUTER JOIN eop_reactivations er ON er.date = ns.date AND er.plan = ns.plan
WHERE ns.plan != '"hearthsim-pro-mvp"'
ORDER BY ns.date, ns.plan;
"""


def get_document():
	import base64
	import json
	from oauth2client.service_account import ServiceAccountCredentials

	scopes = [
		"https://spreadsheets.google.com/feeds",
	]
	keyfile_data = json.loads(base64.b64decode(os.getenv("KEYFILE_DATA")).decode("utf-8"))
	credentials = ServiceAccountCredentials.from_json_keyfile_dict(keyfile_data, scopes)

	gc = gspread.authorize(credentials)

	return gc.open("HearthSim Premium Revenue & Forecasting")


def push_data_to_sheet(data, sheet, x, y):
	cell_list = sheet.range(
		x,
		y,
		x + len(data) - 1,
		y + len(data[0]) - 1
	)
	updated_cells = []

	for x, row in enumerate(data):
		for y, value in enumerate(row):
			cell = cell_list.pop(0)
			cell.value = value if value is not None else ""
			updated_cells.append(cell)

	sheet.update_cells(updated_cells)


def get_data():
	conn = psycopg2.connect(
		host=os.getenv("PGHOST"),
		port=os.getenv("PGPORT", 5432),
		user=os.getenv("PGUSER", "postgres"),
		password=os.getenv("PGPASS"),
		dbname=os.getenv("PGDATABASE")
	)

	with conn.cursor() as cursor:
		cursor.execute(STRIPE_SQL)
		stripe_data = cursor.fetchall()
		cursor.execute(PAYPAL_SQL)
		paypal_data = cursor.fetchall()

	return stripe_data, paypal_data


def main():
	document = get_document()

	stripe_sheet = document.worksheet("[Stripe] Raw Plan Data")
	paypal_sheet = document.worksheet("[Paypal] Raw Plan Data")

	stripe_data, paypal_data = get_data()

	stripe_data = [
		(
			"date", "plan", "new_subscriptions", "terminated_subscriptions",
			"fully_discounted_charges", "eop_cancellations", "eop_reactivations"
		),
	] + stripe_data

	paypal_data = [(
		"date", "recurrence", "new_subscriptions", "cancelled_subscriptions",
		"terminated_subscriptions"
	)] + paypal_data

	push_data_to_sheet(stripe_data, stripe_sheet, 2, 2)
	push_data_to_sheet(paypal_data, paypal_sheet, 2, 2)


main()