Marketing performance was evaluated mainly on revenue and customer volume, making it difficult to identify which acquisition channels truly drive profitable, long-term customer value versus expensive short-term growth.
I analyzed customer acquisition channels using Net LTV, CAC, ROI, and profitability, applying median-based metrics to reduce outlier bias and monthly trend analysis to assess value stability. An interactive Power BI dashboard separates scale from efficiency, clearly showing which channels generate sustainable ROI.
Marketing teams often optimize acquisition channels based on revenue and volume, which can mask inefficient spending. High-revenue channels may appear successful while quietly destroying long-term value due to high acquisition costs. The business lacked a clear, customer-level view of which channels truly drive profitable, sustainable growth over time. Which acquisition channels generate the most profitable customers? and Are high-revenue channels also high-ROI?
- Extracted customer, transaction, and acquisition data using SQL with CTEs and window functions.
- Joined customer acquisition attributes with full transaction history to enable customer-level lifetime analysis.
- Modeled Net LTV as total customer revenue minus CAC and calculated ROI using median-based metrics to reduce outlier bias.
- Evaluated channel performance across profitability, revenue contribution, and time-based Net LTV trends.
- Applied RFM segmentation to support behavioral insights and repeat-purchase analysis.
- Built an interactive Power BI dashboard using DAX measures, custom tooltips, and cross-filtering.
- Designed visuals to clearly separate scale (revenue), efficiency (ROI), and stability (time trends) for executive decision-making.
- SQL: CTEs, window functions, customer-level LTV modeling, RFM segmentation.
- Power BI: DAX measures (median-based ROI), custom tooltips, interactive visuals, trend analysis.
- Analytics Focus: Separation of value (LTV), cost (CAC), scale (revenue), and stability (time trends).
The analysis reveals clear differences in acquisition channel performance when evaluated through long-term customer value rather than revenue alone. While Organic Search contributes the highest total revenue and customer volume, it does not deliver the strongest return on investment once acquisition costs are considered. In contrast, Direct and Email Marketing channels consistently generate higher Net Lifetime Value relative to CAC, indicating more efficient and sustainable customer acquisition. Paid Search shows the weakest performance, with the highest acquisition costs and the lowest ROI, suggesting diminishing returns despite scale.
Social Media falls between these extremes, contributing moderate revenue but producing lower long-term customer value compared to top-performing channels. Overall, more than 93% of customers are profitable, though profitability and value stability vary significantly by acquisition channel.
Marketing spend should be shifted toward Direct and Email Marketing channels, which consistently deliver the strongest return on acquisition spend and higher long-term customer value. Paid Search investment should be reduced or tightly optimized due to high acquisition costs and weak Net LTV performance. Organic Search should be optimized for customer intent and conversion quality rather than traffic volume to improve efficiency at scale, while Social Media should be maintained as a supporting channel focused on retargeting and brand reinforcement. Future budget allocation decisions should prioritize Net LTV-to-CAC ratios over revenue alone to ensure sustainable, profitable growth.
analytics_queries.sql- Complete SQL analysis (CTEs, window functions)Customer Acquisition Analytics.pbix- Power BI dashboardREADME.md- Full project documentation