Telecom Growth Strategies: Unlocking Customer Lifetime Value (CLV) Through Smart Segmentation

Iman AdekoIman Adeko
11 min read

Table of contents

In this project, I utilized SQL in Snowflake to analyze NexaSat’s customer data and segment customers based on their lifetime value. By identifying opportunities for up-selling and cross-selling, I contributed to increasing the Average Revenue Per User (ARPU) and Customer Lifetime Value (CLV) at NexaSat.

Business Introduction

NexaSat is a leading telecommunications provider in West Africa, offering a comprehensive suite of mobile, internet, and television services. Headquartered in Abuja, Nigeria, NexaSat is dedicated to delivering reliable and innovative solutions to meet the evolving needs of its customers. With a strong focus on customer satisfaction and technological advancement, NexaSat is committed to shaping the digital landscape in the region.

Business Problem

NexaSat seeks to optimize marketing strategies and resource allocation to address a diverse customer base. Despite a strong market presence, the company faces challenges in effectively engaging customers and maximizing revenue.

Key challenges include:

  • Ineffective customer segmentation: A one-size-fits-all approach fails to cater to the diverse needs of NexaSat's customers.

  • Missed revenue opportunities: The company aims to increase average revenue per user (ARPU) through up-selling and cross-selling, but lacks a clear understanding of customer segments and preferences.

  • Lack of structured approach: NexaSat needs a systematic method to identify high-potential customer segments for targeted marketing efforts.

  • Underutilized customer data: The company possesses valuable customer data but lacks the tools to leverage it effectively.

To overcome these challenges, NexaSat requires a solution that can categorize customers based on their value and identify opportunities for tailored offerings.

Why CLV Segmentation

Customer Lifetime Value (CLV) segmentation is a data-driven approach that categorizes customers based on their potential long-term profitability.

CLV helps businesses understand the total value a customer brings over their entire relationship, enabling more informed marketing and service decisions.

By identifying high-opportunity segments, businesses can tailor marketing efforts and services to maximize revenue and strengthen customer relationships.

For NexaSat, CLV segmentation offers several key benefits:

  • Targeted marketing: Identifies high-potential segments for personalized offers.

  • Optimized resource allocation: Focuses efforts on segments with the greatest return on investment.

  • Revenue growth: Boosts revenue through up-selling and cross-selling.

  • Enhanced customer loyalty: Builds stronger relationships by addressing specific needs.

By adopting CLV segmentation, NexaSat can unlock the full potential of its customer base and gain a competitive edge in the telecom market.

Aim of the Project

The primary goal of implementing CLV segmentation at NexaSat is to drive revenue growth through targeted marketing and personalized service offerings. By identifying high-opportunity customer segments, NexaSat aims to:

  • Maximize ARPU: Increase revenue per customer.

  • Enhance customer satisfaction: Tailor services to meet specific needs.

  • Foster loyalty: Build stronger relationships with customers.

By achieving these objectives, NexaSat will gain a competitive edge in the telecom industry and ensure long-term profitability.

To address NexaSat's business problem, we will analyze customer data from July 2023.

Project Scope

  1. Exploratory Data Analysis:

    • Conduct a comprehensive analysis of customer data, including demographics and usage patterns.

    • Gain insights into customer behavior and preferences.

  2. Feature Engineering:

    • Create relevant features like CLV and CLV Scores.

    • Unilize these features as inputs for the CLV segmentation model.

  3. Segmentation:

    • Categorize customers into segments based on CLV scores.

    • Further segment customers by demographic data, usage patterns, and service plans.

  4. Segment Profiling:

    • Analyze the distinct characteristics of each segment.

  5. Strategy Formulation:

    • Develop tailored marketing and communication strategies for weach segment.

    • Offer personalized promotions, exclusive services, or targeted communications.

Data Source

The source of the data for this project is a csv file which contains comprehensive information on NexaSat’s customers including:

  • customer_id: Unique identifier for each customer.

  • gender: Gender of the customer.

  • partner: Indicates if the customer has a partner (Yes/No).

  • dependents: Indicates if the customer has dependents (Yes/No).

  • senior_citizen: Indicates if the customer is a senior citizen (1 for yes, 0 for no).

  • call_duration: Total duration of calls made by the customer.

  • data_usage: Amount of data used by the customer.

  • plan_type: Type of plan subscribed by the customer (Prepaid/Postpaid).

  • plan_level: Level or tier of the subscribed plan (Basic/Premium).

  • monthly_bill_amount: The amount billed to the customer each month.

  • tenure_months: Number of months the customer has been with the company.

  • multiple_lines: Indicates if the customer has multiple phone lines (Yes/No).

  • tech_support: Indicates if the customer has tech support (Yes/No).

  • churn: Indicates if the customer has churned (1/0).

I imported the data into Snowflake using SnowSQL. You can check out this article on how to do that.

Plan Description

  • Prepaid premium- flat rate of ₦126 with tech support and 1 extra line. Lock in period of 24 months

  • Prepaid basic- flat rate of ₦80, additional 8 for tech support, additional ₦12 for 1 extra line.

  • Postpaid premium- Tech support and 1 extra line, pay as you use. Lock in period of 12 months

  • Postpaid basic- Pay as you use, additional ₦8 for tech support, additional ₦12 for 1 extra line.

Exploratory Data Analysis

Before I started EDA, I created another table that contains data for only existing customers (churn = 0).

-- Create another table for existing customers
CREATE OR REPLACE TABLE existing_customers AS
SELECT *
FROM nexa_sat
WHERE churn = 0;

  1. How does churn vary by plan type and plan level?

     -- Churn count by plan_type and plan_level
 SELECT plan_type,
         plan_level,
         SUM(churn) AS churn_count
 FROM nexa_sat
 GROUP BY ALL
 ORDER BY churn_count DESC;

    The postpaid plan type and basic plan level had more customer churn.

  2. How does average tenure vary by plan type and plan level?

     -- Average Tenure by plan_type
 SELECT plan_type,
         ROUND(AVG(tenure_months)) AS avg_tenure_months
 FROM nexa_sat
 GROUP BY ALL
 ORDER BY avg_tenure_months;

 --Average tenure by plan_level
 SELECT plan_level,
         ROUND(AVG(tenure_months)) AS avg_tenure_months
 FROM nexa_sat
 GROUP BY ALL
 ORDER BY avg_tenure_months;

    Although there was no significant difference in average tenure across the plan types, customers subscribed to the basic plan level typically remained with the company for almost half the duration as those on the premium plan level.

  3. How many customers are left, and what is the current total monthly revenue?

     -- Total existing customers and total monthly revenue
 SELECT COUNT(customer_id) AS existing_customers,
         ROUND(SUM(monthly_bill_amount)) AS monthly_revenue
 FROM existing_customers;

    There are over 4,000 current customers, and the current monthly revenue is over ₦670,000.

  4. How does the number of current customers vary by plan type and plan level?

     -- Number of existing customers by plan_type and plan_level
 SELECT plan_type,
         plan_level,
         COUNT(customer_id) AS customer_count
 FROM existing_customers
 GROUP BY ALL;

    There are more postpaid customers than prepaid customers, and there are significantly more customers subscribed to the premium plan level than basic plan level.

  5. How does total monthly revenue vary by plan type and plan level?

     -- Total revenue by plan_type and plan_level
 SELECT plan_type,
         plan_level,
         ROUND(SUM(monthly_bill_amount)) AS monthly_revenue
 FROM existing_customers
 GROUP BY ALL
 ORDER BY monthly_revenue;

    Postpaid plans and premium plan levels generate higher monthly revenue compared to prepaid plans and basic plan levels.

  6. What is the average revenue per user (ARPU) of existing customers?

     -- Average Revenue per User (ARPU) for existing customers
 SELECT ROUND(AVG(monthly_bill_amount)) AS ARPU
 FROM existing_customers;

    The average revenue per user for existing customers is ₦158.

Feature Engineering

To segment customers based on their lifetime value, I introduced a new column, clv. This column was calculated by multiplying monthly_bill_amount by tenure_months.

-- Create and update clv: monthly_bill_amount * tenure_months
ALTER TABLE existing_customers
ADD COLUMN clv NUMBER(10, 2);

UPDATE existing_customers
SET clv = monthly_bill_amount * tenure_months;

-- View customers and their clv
SELECT customer_id, clv
FROM existing_customers
LIMIT 5;

To assign CLV scores, I considered factors such as monthly bill amount, tenure, call duration, data usage, and premium customer status. Since there's no standardized formula for calculating CLV scores, I adopted a weighted average approach. The weights were as follows:

  • Monthly bill amount: 40%

  • Tenure: 30%

  • Call duration: 10%

  • Data usage: 10%

  • Premium customer status: 10%

    The weights were determined based on the assumption that monthly bill amount and tenure have the greatest influence on customer value, making them key factors for revenue and retention.

      -- Create and update clv_score
  ALTER TABLE existing_customers
  ADD COLUMN clv_score NUMBER(10, 2);

  UPDATE existing_customers
  SET clv_score = 
          (0.4 * monthly_bill_amount) +
          (0.3 * tenure_months) +
          (0.1 * call_duration) +
          (0.1 * data_usage) +
          (0.1 * CASE WHEN plan_level = 'Premium'
                  THEN 1
                  ELSE 0
                  END);

  -- View customers and their clv and clv scores
  SELECT customer_id,
          clv,
          clv_score
  FROM existing_customers
  LIMIT 5;

Segmentation

I created a new column, clv_segment, to group customers based on their CLV scores. This helps to identify high-value customers and those at risk of leaving.

  • High Value: Customers with CLV scores above the 85th percentile.

  • Moderate Value: Customers with CLV scores between the 50th and 85th percentiles.

  • Low Value: Customers with CLV scores between the 25th and 50th percentiles.

  • Churn Risk: Customers with CLV scores below the 25th percentile.

      -- Create and update clv_segment
  ALTER TABLE existing_customers
  ADD COLUMN clv_segment VARCHAR(20);

  UPDATE existing_customers
  SET clv_segment =
      CASE WHEN clv_score > (
                  SELECT PERCENTILE_CONT(0.85)
                          WITHIN GROUP (ORDER BY clv_score)
                  FROM existing_customers
                          )
              THEN 'High Value'
          WHEN clv_score >= (
                  SELECT PERCENTILE_CONT(0.5)
                          WITHIN GROUP (ORDER BY clv_score)
                  FROM existing_customers
                          )
              THEN 'Moderate Value'
          WHEN clv_score >= (
                  SELECT PERCENTILE_CONT(0.25)
                          WITHIN GROUP (ORDER BY clv_score)
                  FROM existing_customers
                          )
              THEN 'Low Value'
          ELSE 'Churn Risk'
      END;

  -- View customers and their clv segments
  SELECT customer_id,
          clv_segment
  FROM existing_customers
  LIMIT 5;

Segment Analysis

  1. What is the percentage of customers in each segment?

     -- Percentage of customers in each segment
 SELECT clv_segment,
         ROUND(
             COUNT(customer_id) / (
             SELECT COUNT(*)
             FROM existing_customers),
                 2
             ) AS percentage
 FROM existing_customers
 GROUP BY clv_segment
 ORDER by percentage;

    Half of all customers are categorized as either Low Value or Churn Risk.

  2. What is the average monthly bill amount and tenure for each segment?

     -- Avg monthly bill and tenure by segment
 SELECT clv_segment,
         ROUND(AVG(monthly_bill_amount)) AS avg_monthly_bill,
         ROUND(AVG(tenure_months)) AS avg_tenure
 FROM existing_customers
 GROUP BY clv_segment
 ORDER BY avg_monthly_bill;

    Customers categorized as 'Churn Risk' and 'Low-Value' have lower average monthly bills and shorter tenures compared to customers in the 'High-Value' and 'Moderate-Value' segments.

  3. What percentage of customers in each CLV segment utilize tech support and have multiple lines?

     -- Tech support and multiple lines percentage by segment
 SELECT clv_segment,
         ROUND(AVG(
                 CASE WHEN tech_support = 'Yes'
                         THEN 1
                         ELSE 0 END), 2) AS perc_tech_support,
         ROUND(AVG(
                 CASE WHEN multiple_lines = 'Yes'
                         THEN 1
                         ELSE 0 END), 2) AS perc_multiple_lines
 FROM existing_customers
 GROUP BY clv_segment
 ORDER BY perc_tech_support;

    Tech support and multiple line usage were significantly lower among customers identified as Churn Risk.

Strategy Formulation

  1. Cross-selling tech support to senior citizens

    • Many senior citizens lack familiarity with technology which can make it difficult to use telecom services effectively.

    • Those without tech-savvy dependents are particularly vulnerable.

    • Cross-selling tech support to these seniors can enhance customer satisfaction, reduce churn risk and increase CLV.

      Below is the query that identifies such customers.

        -- Cross-selling tech support to senior citizens
  SELECT customer_id
  FROM existing_customers
  WHERE senior_citizen = 1              -- Senior citizens
      AND dependents = 'No'             -- Without dependents
      AND tech_support = 'No';          -- Don't have tech support                                -- Low value or churn ris

  2. Cross-selling multiple lines to customers with partners and dependents

    • Multiple lines can be beneficial to customers with partners and dependents, providing flexibility, convenience, and cost savings.

      Below is the query that identifies such customers.

        -- Cross-selling multiple lines to customers with partners and dependents
  SELECT customer_id
  FROM existing_customers
  WHERE multiple_lines = 'No'            -- Don't have multiple lines
      AND(
          dependents = 'Yes' OR          -- Have dependents
          partner = 'Yes'                -- Have a partner
          );

  3. Upselling discounted premium plans to customers on the basic plan

    • Customers on the basic plan typically remain with the company for almost half as long as those on the premium plan.

    • Upselling the premium plan, at a discount, to basic customers that are either low value or churn risk can increase ARPU and CLV.

      Below is the query that identifies such customers.

        -- Upselling discounted premium plans to basic customers
  SELECT customer_id
  FROM existing_customers
  WHERE plan_level = 'Basic'
      AND(
          clv_segment = 'Low Value' OR
          clv_segment = 'Churn Risk'
      );

  4. Upselling premium plans to high value customers on the basic plan

    • High-value customers generally spend significantly more per month than other customer segments.

    • Upselling premium plans to those on the basic plan will ensure longer lock-in periods and increase ARPU.

      Below is the query that identifies such customers.

        -- Upselling premium plans to high value basic customers
  SELECT customer_id
  FROM existing_customers
  WHERE plan_level = 'Basic'
      AND clv_segment = 'High Value';

Outcome

By targeting the identified up-selling and cross-selling opportunities, NexaSat can potentially enhance the customer experience for 28% of its customer base, leading to increased ARPU and CLV.

SELECT ROUND(
        COUNT(customer_id) / (
            SELECT COUNT(customer_id)
            FROM existing_customers
                ),
                2
            ) AS perc_potential_customers
FROM(
    SELECT customer_id
    FROM existing_customers
    WHERE senior_citizen = 1             
        AND dependents = 'No'  
        AND tech_support = 'No'
    UNION
    SELECT customer_id
    FROM existing_customers
    WHERE multiple_lines = 'No'  
        AND(
            dependents = 'Yes' OR       
            partner = 'Yes'
            )
    UNION
    SELECT customer_id
    FROM existing_customers
    WHERE plan_level = 'Basic'
        AND(
            clv_segment = 'Low Value' OR
            clv_segment = 'Churn Risk'
        )   
    UNION
    SELECT customer_id
    FROM existing_customers
    WHERE plan_level = 'Basic'
        AND clv_segment = 'High Value'
    );

Conclusion

This project successfully demonstrated the value of CLV segmentation in optimizing marketing strategies and enhancing revenue potential at NexaSat.

By utilizing SQL in Snowflake to analyze NexaSat’s customer data and identify high-opportunity segments, I was able to:

  • Tailor marketing efforts: Develop personalized promotions and offers for each customer segment.

  • Optimize resource allocation: Focus on segments with the greatest potential for revenue growth.

  • Enhance customer satisfaction: Deliver tailored services that meet specific customer needs.

  • Drive revenue growth: Increase ARPU and CLV through up-selling and cross-selling.

10
Subscribe to my newsletter

Read articles from Iman Adeko directly inside your inbox. Subscribe to the newsletter, and don't miss out.

TelecommunicationsTelecomsegmentationcustomer segmentationSQLsnowflakeDatabasesdata analysisdataanalyticsdata analytics

Written by

Iman Adeko
Iman Adeko