This repository contains the complete solution for a data analyst case study focused on analyzing the conversion funnel of a simple e-commerce website. The goal is to provide actionable insights and strategies to improve the site's conversion rates, especially for new users.
Scenario:
You are analyzing data from an e-commerce website with four main pages:
- Home Page: The entry point for all users.
- Search Page: Accessed from the home page when a user performs a search.
- Payment Page: Accessed 6D24 from the search page when a user clicks on a product.
- Confirmation Page: Accessed after a successful payment.
The CEO is concerned about low sales volume, particularly from new users, and wants to understand where users drop off in the funnel and how to improve conversion rates.
Investigate the conversion funnel, identify bottlenecks or issues, and suggest strategies to increase conversions.
-
data/
Contains raw and processed CSV files, including funnel metrics and user activity data. -
sql/
SQL scripts for schema creation, ETL, and funnel metrics calculation (both wide and long formats). -
notebooks/
Jupyter notebooks for data ingestion, analysis, and export, using Python and pandas. -
config/
Configuration files for data ingestion.
The analysis is based on the following daily data files:
Home_page_table.csv– Users landing on the home pageSearch_page_table.csv– Users landing on the search pagePayment_page_table.csv– Users landing on the payment pagePayment_confirmation_table.csv– Users landing on the confirmation pageUser_table.csv– User demographic and device information
-
Database Setup:
- Use the SQL scripts in
sql/to create the schema and compute funnel metrics. - Load the CSV data into the database using the ingestion notebook.
- Use the SQL scripts in
-
Analysis:
- Run the analysis notebook to explore conversion rates, drop-off points, and trends by device and gender.
- Use the generated metrics to identify bottlenecks and suggest improvements.
-
Export:
- Export processed data for reporting or further analysis using the export notebook.
- Python (pandas, SQLAlchemy, dotenv, Anaconda)
- MySQL
- Jupyter Notebooks
The repository provides:
- Conversion and drop-off metrics at each funnel stage
- Segmentation by device and gender
- Time-based trend analysis
- Identification of top-performing segments
- Actionable recommendations for funnel optimization
Note:
All scripts and notebooks are designed to be modular and reproducible. See the notebooks/README.md and sql/README.md for detailed instructions.