Customer Lifetime Value (CLTV) Analytics – Real Estate Investment Platform

📊 Project Overview

This project focused on building a robust CLTV analytics framework for a real estate investment platform to better understand investor behavior and long-term value.

The objective was to provide leadership with visibility into:

  • Which investors generate the highest lifetime value
  • Repeat investment behavior patterns
  • Revenue per agreement
  • Customer lifecycle duration
  • Investment frequency trends

The entire solution was built using SQL and Metabase, directly querying production-grade datasets to ensure accuracy and real-time reporting.

🧩 The Challenge

The business lacked a structured way to measure long-term investor value. Reporting was fragmented across systems, making it difficult to:

  • Identify high-value repeat investors
  • Track investor lifecycle duration
  • Measure revenue per agreement
  • Understand retention trends
  • Segment customers based on contribution

There was no centralized dashboard to support strategic decision-making.

🛠️ Solution Architecture

I designed and implemented an end-to-end analytics framework that included:

1️⃣ Data Modeling & Aggregation

  • Structured agreement-level and customer-level datasets
  • Built CLTV logic using repeat customer flags
  • Calculated lifetime funds received and revenue per agreement
  • Measured lifecycle duration in customer lifetime days

2️⃣ Investor Segmentation

  • Segmented repeat vs non-repeat investors
  • Identified high-value investor clusters
  • Analyzed investment timing and frequency patterns

3️⃣ Dashboard Development (Metabase)

Created interactive dashboards to visualize:

  • Total lifetime funds received
  • Average funds per agreement
  • First product engagement trends
  • Investment recurrence behavior
  • Customer lifecycle duration
  • Agreement count distribution

All dashboards were built for business users with filtering capabilities and drill-down analysis.

📈 Key Insights Delivered

  • High repeat investors contributed disproportionately higher lifetime revenue
  • Investor lifecycle duration strongly correlated with agreement frequency
  • Revenue per agreement varied significantly across segments
  • Early investment engagement influenced long-term retention
  • Repeat investors demonstrated predictable reinvestment patterns

These insights helped the business prioritize high-value investors and optimize retention strategy.

💼 Business Impact

  • Enabled identification of high-lifetime-value investors
  • Improved strategic decision-making using CLTV segmentation
  • Reduced manual reporting through automated dashboards
  • Provided clear visibility into revenue distribution and investor retention
  • Created a scalable analytics foundation for future growth modeling

🧠 Technical Highlights

  • Advanced SQL aggregations and window functions
  • Production-grade dataset querying
  • Clean data modeling for lifecycle tracking
  • Repeat customer logic implementation
  • Scalable dashboard architecture

🎯 Outcome

This CLTV analytics framework transformed fragmented data into a structured decision-making system, allowing leadership to:

  • Focus on investor retention strategy
  • Improve capital allocation targeting
  • Strengthen revenue forecasting capabilities
  • Understand long-term investor behavior

The solution now serves as a core analytics layer for performance monitoring and strategic planning.