Introduction
Customer segmentation is more than just slicing your database into age groups or geographic regions. True cohort analysis—based on behaviour, time, and engagement—can unlock powerful insights that drive retention, churn prevention, and campaign success. I’ve worked on daily cohort analysis for a call centre in the insurance industry, where understanding how the interaction between agents and customers influenced acquisition and sped up the decision was critical. Those lessons—tracking behavioural nuance over time, not just static traits—translate directly to telecom, where similar complexity exists. In this post, I’ll share how I approach building meaningful customer cohorts using SQL in GCP BigQuery, with examples rooted in real-world telco use cases.
Why Cohorts Matter in Telecom?
Marketing and retention teams often ask for segments like “churn risks” or “high-value customers,” but without a data-driven foundation, these groups can be unclear and impractical. A cohort, done right, aligns customer behaviour over time, helping us:
- Understand lifecycle trends (e.g. early churners vs loyalists)
- Evaluate the impact of acquisition channels.
- Target customers with relevant messaging based on tenure and habits
Lessons from the call centre cohorts
In a call centre setting, I analysed how behavioural dynamics as well as metrics related to signal quality —call duration, agent persistence, and customer hesitation—impacted acquisition. Cohorting customers by day of first interaction revealed hidden variables, like how regional agents influenced policy uptake or how specific time-of-day windows correlated with higher conversions. This taught me the danger of relying on demographics alone and the importance of multi-dimensional cohort design. This, of course, created potential over-segmentation, so after spotting similar cohorts based on the underlying characteristics, I have created a mechanism that would specify the caller in the precise group in a short interval of time. This allowed for to improvement of the service for an individual customer.
Cohorting That Works
Here are the cohort types that have delivered real value in telco environments:
- Time-Based Cohorts
- Grouping by acquisition date, activation date, or plan renewal cycle. In the example explained above it was the first point of contact that was crucial for the customer acquisition.
- Behavioural Cohorts
- Based on data usage, recharge frequency, and service engagement. Metrics like time-to-first-response, repeated connection attempts, or whether the caller disconnected before reaching an agent (“cold hang-ups”) provided much richer behavioural signals than simple call volume.
How to Build These Cohorts in SQL (BigQuery)
Here’s a sketch of a query to build a behavioural cohort:
Final Thoughts
Cohort analysis isn’t just for growth hackers or subscription businesses—it’s a core tool for telco marketers, product owners, and analysts. Start with clean data, focus on lifecycle behaviour, and make your segments actionable. With SQL and a bit of creativity, you can move beyond generic personas to real, measurable impact.
Unfortunately, I am not allowed to share dashboard view from the cohorts I have created, so this image comes from: https://www.engati.com/glossary/cohort-analysis
