Data Analytics Portfolio

Project 1

Customer Profitability & Revenue Risk Analysis

Lane: Finance / Revenue Analytics

Tools: SQL, Power BI

Github: Check out my SQL scripts for this project here!

Business Problem

A telecommunications company wanted to understand which customer segments generate sustainable profitability, how customer churn impacts revenue, and where the business is financially exposed to risk.

While revenue was being tracked regularly, finance leadership lacked visibility into customer lifetime value (LTV) and how service costs and churn affected long-term profitability.

Data & Assumptions

  • Customer subscription and billing data from a Telco dataset
  • Monthly charges, tenure, contract type, internet service, and churn status
  • Service costs were not provided, so I created a transparent cost model based on service type:
    • Fiber optic: $45/month
    • DSL: $30/month
    • No internet: $15/month

These assumptions were documented and used consistently to estimate net customer profitability.

Analysis Approach

  1. Built a customer-level profitability model in SQL calculating:
    • Gross Lifetime Value (LTV)
    • Estimated service costs
    • Net LTV (profit proxy)
  2. Segmented customers by:
    • Contract type
    • Internet service
    • Tenure bands
  3. Quantified revenue at risk by isolating churned customers and their associated net LTV
  4. Aggregated results to identify high-risk and loss-making segments

Key Insights

  • Month-to-month fiber customers generated high revenue but negative net LTV due to elevated churn and service costs
  • Long-term contracts consistently produced higher net LTV despite lower monthly charges
  • A small number of high-churn segments accounted for a disproportionate share of revenue at risk
  • Customer tenure was a stronger driver of profitability than monthly pricing alone

Dashboard & Deliverables

  • Executive-level dashboard highlighting:
    • Total net LTV
    • Revenue at risk from churn
    • Profitability by segment
  • Heatmaps and bar charts to compare profitability and risk across customer groups
  • Action-oriented recommendations embedded directly in the dashboard for leadership review

Recommendations

  • Incentivize contract conversions for high-cost services to improve customer lifetime value
  • Re-evaluate pricing or service strategies for consistently loss-making segments
  • Prioritize retention investments where net LTV justifies intervention
  • Use LTV-based metrics alongside revenue for finance and planning decisions