In this project, we performed a Customer Lifetime Value (CLTV) analysis to understand the value of each customer over their lifetime. Using this insight, we aimed to determine how to best allocate a given marketing budget across different advertising channels.
The analysis involved the following steps:
- Data Preparation: We cleaned and preprocessed the sales data to ensure its quality and consistency. Missing values (highlighted in yellow on the "Data" sheet) were filled using various imputation techniques.
- CLTV Calculation: We calculated the expected lifetime value of customers based on their purchase history.
- Correlation Analysis: We calculated the correlation between each advertising channel and sales to identify the most effective platforms.
- Regression Analysis: We employed regression analysis to identify the key factors influencing CLTV and predict customer behavior.
- Outlier Detection & Standardization: Outlier analysis was performed, and standardization techniques were applied to outlier data points to ensure the reliability of the analysis.
- Trendline Visualization: We created trendline graphs for each advertising channel, providing visual insights into performance trends.
- Fixed Income Calculation: Fixed income values for each advertising method were calculated, allowing us to assess consistent revenue contributions.
Based on the insights derived from these analyses, we made data-driven decisions on how to allocate the marketing budget across different advertising platforms. This project demonstrates how data analysis techniques like CLTV, regression, correlation, and outlier detection, combined with Excel's capabilities, can guide strategic investment decisions.