Every service business has a lifetime value problem they don't know they have. Revenue comes in, clients leave, and the numbers never quite explain why growth stalled. Here's how we connected the CRM, tracked every client relationship from start to finish, and built a system that finally showed — by acquisition channel — which marketing was actually winning.
4 Windows
LTV by Month, Quarter, 6-Mo, Year
Fortnightly
Automated CRM Sync
Source-Split
LTV by Acquisition Channel
The Problem
This client ran a service business with a regular roster of ongoing clients. New customers converted through the usual channels — Google Ads, trade shows, personal introductions — and those conversions were tracked in a CRM. But the moment a client signed up, the data story ended.
There was no system tracking how long each client stayed. No record of when a client relationship ended. No calculation of what any individual client was worth over their entire time as a customer. And critically, no way to connect those numbers back to the channel that first acquired them.
This meant decisions about where to invest in marketing were based purely on the cost of acquiring a new client — not on the value of keeping one. A client who came in through Google Ads and stayed for three years was counted the same as one who left after a month. A client brought in through a trade show introduction might be worth five times as much in lifetime value, but there was no way to know.
Churn was invisible too. Clients would drift away over time, but there was no clear picture of the rate at which it was happening, when it typically happened, or whether certain acquisition channels produced clients more likely to churn.
"They had all the relationship data sitting in their CRM — start dates, source notes, account status. What they didn't have was any system turning it into numbers they could act on. Everything about retention was invisible."
Our Solution
The data existed — it was all in the CRM. The challenge was pulling it out automatically, keeping it current, and organising it so that meaningful calculations could run on top of it. We built the system in layers: extraction, tracking, calculation, and reporting.
We built a Google Apps Script that connected directly to the CRM's API and pulled the full client roster into a Google Sheet on a fortnightly schedule. For each client, we extracted: their name, the date they became a customer, their acquisition source (Google Ads, trade show, personal referral, etc.), and their current account status.
At the time, building this kind of CRM-to-Sheets bridge via Apps Script was genuinely ambitious — the documentation was sparse, the API authentication flows weren't well documented for non-developers, and there was very little community guidance on handling the edge cases. We worked through it methodically, and the result was a script that ran without supervision every two weeks.
Each row in the Google Sheet represented one client. When they became a customer, their start date was populated. When they ended their relationship with the business — for whatever reason — their end date was recorded.
This sounds simple, but getting it right required a consistent process. The fortnightly sync pulled status changes from the CRM, and when a client's status moved from active to churned, the system captured that date automatically. The discipline of maintaining this meant that, over time, the sheet became a complete historical record of every client relationship the business had ever had.
With start and end dates in place, we built a set of calculated columns that gave a multi-period view of client value:
These four windows let the business see not just what a client was worth in total, but how value accumulated over time — which helped shape decisions about when to invest in retention efforts and at what point a client relationship was genuinely profitable.
With end dates captured reliably, churn rate became a straightforward calculation: clients lost in a period divided by the total active at the start of that period. This ran on a rolling monthly and quarterly basis, giving the business a clear view of whether retention was improving or deteriorating.
More importantly, because each client had a source attributed, it became possible to segment churn by acquisition channel. Were clients from trade shows churning faster than those from Google Ads? Were personally referred clients the most retained? These were questions the business had never been able to ask before — now they had answers.
The Google Sheet became the data source for a Google Data Studio (now Looker Studio) dashboard that presented all of this in a single, readable view. LTV by source, churn by period, active vs churned client counts, and average retention duration — all visible at a glance, filterable by acquisition channel, and updated automatically every two weeks.
This made the data usable for the whole team — not just whoever built the spreadsheet. Business owner, account manager, and marketing team could all open the same dashboard and immediately understand the health of the client base without needing to know how the underlying data was structured.
Why This Client Was Particularly Well-Positioned
Most businesses face a frustrating amount of "unknown" in their acquisition source data. Clients sign up without a clear digital trail, referral attribution is guesswork, and direct traffic swallows everything else.
This client had an unusual advantage. Because the owner personally knew a large proportion of their clients — many of whom came through trade show relationships and direct introductions — it was possible to go back and attribute those clients accurately in the CRM. The source field wasn't just filled with what a tracking pixel could capture; it reflected real-world knowledge of how each relationship started.
Combined with the clients who had a clear digital origin (Google Ads conversions, referral forms, campaign-tagged landing pages), this meant the overall attribution rate across the client base was far higher than most businesses achieve. That turned what might have been a directionally useful dashboard into a genuinely reliable one — the source-split LTV numbers weren't estimates, they were close to the real picture.
The Results
Full LTV
Visibility
Monthly, quarterly, 6-month, and annual lifetime value — calculated automatically for every client.
Churn
Rate Tracked
Rolling monthly and quarterly churn rate — visible for the first time, segmented by acquisition source.
Channel
LTV Breakdown
Google Ads vs trade show vs referral — which source produced clients that stayed longest and spent most.
This solution applies to any service business where clients stay for more than one transaction — agencies, consultancies, subscription services, professional services, or any recurring-revenue model.
You have ongoing client relationships but no systematic view of how long they last
You track conversions but have no data connecting acquisition source to long-term retention
You make decisions about ad spend based on acquisition cost alone — not on what clients are actually worth
You know clients are leaving but don't have a reliable number for your churn rate
Your CRM holds the client history — but no one has connected it to anything useful
Producing a retention or LTV report currently requires hours of manual work in a spreadsheet
If the CRM has a client start date, a status field, and an acquisition source — we can build this. The CRM doesn't need to be a particular platform. As long as the data exists and is accessible via API or export, the pipeline from CRM to Google Sheets to Looker Studio can be built and automated.
Google Sheets was the right choice here for several reasons. It gives you a transparent, auditable data store that the client can see and understand without a technical background. It's free to run and doesn't add a new platform subscription. And critically, Google Apps Script can connect to virtually any CRM via API, making the integration possible without expensive middleware or specialist software. For most small and mid-size service businesses, a well-structured Sheets setup connected to Looker Studio gives you 90% of what a paid BI tool would, at a fraction of the cost and complexity.
Any CRM that exposes a REST API or allows CSV/data export on a schedule. HubSpot, Salesforce, Pipedrive, Zoho CRM, Monday.com, ActiveCampaign, and most mid-market CRMs all support API access. Google Apps Script can call any API that supports standard authentication — OAuth2, API keys, or basic auth. If your CRM doesn't have a proper API but supports scheduled data exports to Google Drive or email, we can build the pipeline from there instead.
For a service business measuring client lifetime value, fortnightly is the right cadence. LTV and churn are slow-moving metrics — they don't change meaningfully hour-to-hour or even day-to-day. A fortnightly sync keeps the data current enough for accurate reporting while avoiding the API rate limit issues and infrastructure complexity that come with real-time pipelines. If your business has a faster-moving dataset (e.g. daily transaction volume), we can increase the cadence, but for most service businesses, fortnightly gives you all the accuracy you need at minimal operational overhead.
This is the most common issue, and it doesn't block the project — it just changes the value you get from the source-attribution part. We'd build the full LTV and churn tracking system regardless, since that has value even without perfect attribution. For improving source data going forward, we'd put a process in place to capture acquisition source at the point of entry into the CRM — and if your team has knowledge about how existing clients were acquired (as was the case with this client), a one-time enrichment exercise can often recover a surprisingly high proportion of historical attribution.
The most direct impact is on your target CPA (cost per acquisition). Without LTV data, most businesses set their CPA target based on the value of a single transaction or the first month of revenue. With LTV data, you know what a client acquired through Google Ads is actually worth over 6 or 12 months — which often justifies a much higher CPA than the business was previously comfortable with. It also tells you whether Google Ads clients retain better or worse than clients from other channels, which shapes both your bidding strategy and your creative messaging. Knowing that a client is worth £4,000 over their lifetime changes how aggressively you're willing to bid to acquire them.
If your CRM has the data and you don't have a system turning it into LTV and churn numbers — that's a gap we can close. Let's talk about what's in your CRM and what we could build from it.