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
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": "[email protected]",
"last_login_days_ago": 12,
"feature_uses_14d": 3,
"downgrade_attempt": false
},
{
"account_id": "acc_002",
"email": "[email protected]",
"last_login_days_ago": 18,
"feature_uses_14d": 1,
"downgrade_attempt": true
}
]
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"
}
}
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)
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:
- Pull the last 90 days of usage for all accounts that churned
- Calculate their average
last_login_days_agoandfeature_uses_14din the 2 weeks before they cancelled - 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:
- Open your actor in Apify Console
- Go to Schedules → New Schedule
- Set:
0 8 * * 1(8am every Monday) - 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(
"[email protected]",
`Churn Alert: ${atRisk.length} at-risk accounts`,
`Accounts needing outreach this week:\n${atRisk.join("\n")}`
);
}
}
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:
Data pull — Apify actor fetches your usage JSON weekly
Risk scoring — Google Sheets formula flags accounts with 2+ warning signals
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.