DEV Community

Vhub Systems
Vhub Systems

Posted on

How to Build a Subscription Churn Early-Warning System With Apify and Google Sheets

#javascript #python #saas #automation

Most SaaS founders discover churn the same way: the cancellation email arrives. By that point, the decision was made weeks ago. The customer had already stopped logging in, stopped using key features, maybe even clicked "downgrade" before closing the tab. You just didn't know.

This guide shows you how to build a churn early-warning system that catches those signals — before the cancellation — using Apify to pull usage data and Google Sheets to score risk automatically.

The Problem: You're Diagnosing Churn Retrospectively

Most subscription businesses track churn as a lagging metric — customers leave, you count them, you calculate the rate. This is useful for reporting but useless for retention.

The decision to cancel is rarely impulsive. It follows a pattern:

  • Login frequency drops
  • Core feature usage declines
  • The user stops completing their primary workflow
  • Sometimes they try to downgrade first

This behavioral decay typically unfolds over 2 to 4 weeks before cancellation. That window is your intervention opportunity — and right now, most founders are missing it entirely.

The Signal: What Usage Decay Looks Like

If you have any form of event tracking (Mixpanel, Amplitude, your own database logs, or even server-side API call logs), you're already collecting the data you need. The three highest-signal indicators:

1. Last login date
A user who hasn't logged in for 10+ days on a weekly-use tool is at elevated risk. The threshold depends on your product's natural usage cadence — daily tools have a different baseline than monthly tools.

2. Feature usage count (trailing 14 days)
Count how many times each account triggered your core feature in the last two weeks. A drop of 50%+ vs their prior 14-day baseline is a meaningful signal.

3. Downgrade attempt
Any click on a downgrade CTA, pricing page revisit, or billing portal open — even if not completed — is a strong behavioral signal. These events are often tracked but rarely surfaced to the team.

Alone, each signal is noisy. Together, two or more active at the same time identifies accounts that deserve a proactive outreach.

The Setup: Apify + Google Sheets Pipeline

Here's the architecture:

Usage data source → Apify HTTP Actor → Google Sheets → Conditional risk score → Alert
Enter fullscreen mode Exit fullscreen mode

Step 1: Export Usage Data

Your usage data likely lives in one of three places:

  • A product analytics platform with a JSON/CSV export API
  • Your own database (Postgres, MySQL) exposed via an internal API endpoint
  • A third-party tool like Mixpanel or Amplitude with REST API access

For this walkthrough, assume you have an internal endpoint that returns a JSON array of account activity:

[
  {
    "account_id": "acc_001",
    "email": "user@company.com",
    "last_login_days_ago": 12,
    "feature_uses_14d": 3,
    "downgrade_attempt": false
  },
  {
    "account_id": "acc_002",
    "email": "founder@startup.io",
    "last_login_days_ago": 18,
    "feature_uses_14d": 1,
    "downgrade_attempt": true
  }
]
Enter fullscreen mode Exit fullscreen mode

Step 2: Pull Data With Apify

Create an Apify actor using the HTTP request source to fetch from your internal endpoint. If your endpoint requires auth, pass the token as a header input.

Basic actor input:

{
  "url": "https://yourapp.com/api/usage-export",
  "method": "GET",
  "headers": {
    "Authorization": "Bearer YOUR_API_TOKEN"
  }
}
Enter fullscreen mode Exit fullscreen mode

The actor runs, fetches the JSON array, and stores results in the Apify dataset. You can then push those results directly to a Google Sheet using Apify's built-in Google Sheets integration — or export as CSV and import manually for a first test.

Step 3: Score Each Account in Google Sheets

Once your data is in Google Sheets, add a Churn Risk Score column using a simple formula:

=IF(B2>10,1,0) + IF(C2<5,1,0) + IF(D2=TRUE,1,0)
Enter fullscreen mode Exit fullscreen mode

Where:

  • B2 = last login days ago (threshold: >10)
  • C2 = feature uses in 14 days (threshold: <5)
  • D2 = downgrade attempt (TRUE/FALSE)

This gives each account a score from 0 to 3. Accounts scoring 2 or 3 are your intervention list.

Add a conditional formatting rule: highlight rows where the risk score column is ≥ 2 in red. Now your weekly review starts with a visible list of at-risk accounts.

Calculating Thresholds for Your Product

The numbers above (10 days since login, <5 feature uses) are starting points, not universal rules. Calibrate them against your own data:

  1. Pull the last 90 days of usage for all accounts that churned
  2. Calculate their average last_login_days_ago and feature_uses_14d in the 2 weeks before they cancelled
  3. Set your thresholds at the median values from that cohort

This turns guesswork into a data-derived baseline. Even a rough calibration will outperform no system.

Step 4: Automate the Weekly Run

Schedule your Apify actor to run every Monday morning:

  1. Open your actor in Apify Console
  2. Go to Schedules → New Schedule
  3. Set: 0 8 * * 1 (8am every Monday)
  4. Enable Automatically push results to Google Sheets or trigger a webhook to your sheet

Now every Monday you have an updated risk dashboard without touching it manually.

For the alert: add a Google Sheets formula that emails you when any account hits score ≥ 2:

// Google Apps Script — trigger on sheet edit or time-based
function sendChurnAlert() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const atRisk = [];

  for (let i = 1; i < data.length; i++) {
    const score = data[i][4]; // column E = risk score
    if (score >= 2) {
      atRisk.push(data[i][1]); // column B = email
    }
  }

  if (atRisk.length > 0) {
    MailApp.sendEmail(
      "you@yourcompany.com",
      `Churn Alert: ${atRisk.length} at-risk accounts`,
      `Accounts needing outreach this week:\n${atRisk.join("\n")}`
    );
  }
}
Enter fullscreen mode Exit fullscreen mode

Run this script on a Monday trigger, 30 minutes after the Apify actor fires.

The Cost: $0 Until It Saves Your First Subscriber

Apify charges based on compute. A simple HTTP fetch actor pulling a few hundred records runs in under 5 seconds. At Apify's pay-per-use pricing, that's fractions of a cent per run — roughly $0.00 to $0.02 per weekly run depending on dataset size.

Google Sheets is free. Apps Script is free.

The total infrastructure cost rounds to zero. The ROI depends entirely on what a retained subscriber is worth to you — and whether the outreach works.

What to Do With the Alert

When you get the Monday email listing at-risk accounts, the response doesn't need to be elaborate:

  • Score 2: A short personal email. "Hey — noticed you haven't been in recently. Anything blocking you?"
  • Score 3: Direct offer — a 15-minute call, an extended trial, or a feature unlock if relevant

The goal isn't to beg customers to stay. It's to have the conversation before they've made the decision. A customer who's fading out is still recoverable. One who's already cancelled is a much harder conversation.

Summary

The system has three parts:

  1. Data pull — Apify actor fetches your usage JSON weekly
  2. Risk scoring — Google Sheets formula flags accounts with 2+ warning signals
  3. Alert — Apps Script emails you the intervention list

Setup time: 2 to 3 hours. Running cost: effectively zero. The payoff shows up the first time you send an email to an at-risk account and they reply.

If you're building on Apify or want to explore HTTP-source actors for other internal data pipelines, the Apify actor library has HTTP and API-based actors you can fork and adapt without writing infrastructure from scratch.

Built with Apify and Google Sheets. No proprietary tooling required.

Top comments (0)