Track PayPal subscription revenue and churn every Monday

Every Monday at 8am on a cron schedule, build the recurring revenue report that PayPal does not give me natively, append it to a running history in Google Sheets, and post a narrative summary to Slack.

Start in Google Sheets. Use Get Values to read the tracking tab that lists the PayPal subscription IDs we monitor. This sheet is the registry of subscriptions. PayPal has no operation that lists every subscription on an account, so never try to fetch all subscriptions directly. Always iterate over the IDs read from the sheet.

For each subscription ID, call PayPal Get Subscription to capture the current status (ACTIVE, SUSPENDED, CANCELLED, or EXPIRED), the plan ID, the billing amount and currency, the subscriber name or email, and the next billing date. If the plan name or pricing detail is not present on the subscription record, call Get Plan for that plan ID to fill it in.

Then call List Subscription Transactions for each subscription over the last seven days to confirm which subscriptions actually collected money in that window. A subscription can read as ACTIVE while quietly failing to bill, so treat the transaction list as the source of truth for money collected and the status field as the source of truth for lifecycle state. Total up what was genuinely collected across all subscriptions.

Read last week's snapshot row from the same sheet and compare it against what you just gathered. Compute the total active count, new subscriptions that were not present in last week's snapshot, subscriptions that moved to CANCELLED, subscriptions that moved to SUSPENDED, and estimated monthly recurring revenue. When estimating MRR, normalize every active subscription's billing amount to a monthly figure: divide annual plans by 12, multiply weekly plans by 52 and divide by 12, and count monthly plans as they are. Only count subscriptions that are currently active.

Append this week's snapshot to the same sheet with Append Values, one row per week, containing the run date, active count, new, cancelled, suspended, total collected in the last seven days, and estimated MRR. Keep the column order identical every week so the tab becomes a running MRR history that can be charted.

Finally, post the narrative summary to Slack with Send a Message. Write a few short sentences someone can read on their phone, leading with estimated MRR and how it moved against last week. Name every subscription that moved to SUSPENDED or CANCELLED, with the subscriber name or email and the monthly amount at risk. Frame the suspended ones as recoverable revenue rather than lost revenue: PayPal automatically retries failed recurring payments and suspends the subscription after repeated failures, so a suspended subscription usually means a payment problem that someone can fix this week by reaching out. Cancelled subscriptions should be reported as confirmed churn.

If the sheet contains no prior snapshot row, treat this run as the baseline: append the row as normal and say in the Slack message that this is the first snapshot, so there is no week over week comparison yet. If an individual subscription ID cannot be read from PayPal, keep going with the rest and list the failed IDs at the end of the Slack message rather than failing the whole report.