E-Commerce Lakehouse Purchase Prediction

Project Overview

This project demonstrates a complete lakehouse-style workflow for e-commerce behavioral analytics and session-level purchase prediction. Raw clickstream-style event data is stored in AWS S3, queried and transformed with Amazon Athena, materialized as Parquet, and then used in Databricks for PySpark-based analytics, feature engineering, train/test preparation, and machine learning.

The project was designed as a professional portfolio project for data engineering and cloud data engineering roles. It combines cloud data lake design, SQL-based transformation, Spark processing, machine learning, documentation, and cost-conscious architecture decisions.

Objective

The main goal is to predict whether a user session ends in a purchase based on behavioral signals such as views, cart activity, session duration, product diversity, price statistics, and engineered session features.

Main engineering goal: build a reproducible end-to-end pipeline from raw e-commerce events to an ML-ready curated dataset and evaluated purchase prediction models.

Architecture

The project separates the workflow into raw, processed, curated, and machine learning layers. AWS provides the storage, serverless SQL, and catalog foundation. Databricks provides the PySpark and ML workspace used for EDA, feature engineering, and model evaluation.

Raw CSV events stored in S3.

Athena external table and SQL views for cleaning, validation, and transformation.

Processed and curated layers materialized as Parquet files.

Databricks/PySpark used for EDA, ML feature engineering, train/test split, and Spark ML models.

Pipeline Flow

Upload raw e-commerce event data to Amazon S3.
Create an Athena external table over raw CSV data.
Validate data quality and create clean/processed Athena views.
Aggregate events into session-level behavioral features.
Materialize processed and curated datasets as Parquet in S3.
Import the curated ML-ready dataset into Databricks.
Run PySpark EDA, funnel analysis, feature engineering, and train/test split.
Train baseline and leakage-aware Logistic Regression models.
Evaluate models using ROC-AUC, accuracy, precision, recall, F1, and confusion matrices.

Dataset and Data Quality

The source data contains e-commerce event records with timestamps, event types, product identifiers, category and brand metadata, price, user ID, and user session ID. Valid event types are view, cart, and purchase.

Data quality work included trimming string values, removing invalid rows, standardizing missing product metadata, converting timestamps, creating event-level purchase indicators, and filtering unrealistic sessions longer than 24 hours before modeling.

884K+

Processed e-commerce events

480K+

ML-ready user sessions

4.71%

Purchase rate

Metric	Value	Interpretation
Processed events	884,964	Cleaned event-level records after validation and session filtering.
ML-ready sessions	480,036	Final one-row-per-session dataset used for modeling.
Purchased sessions	22,601	Sessions that ended with at least one purchase.
Non-purchased sessions	457,435	Sessions without a purchase event.
Purchase rate	4.71%	Positive-class share in the ML-ready dataset.

Exploratory Analysis and Funnel Insights

The funnel analysis showed a strong behavioral progression from product views to cart activity and purchases. Cart activity was the strongest direct purchase signal, while session engagement and duration also contributed meaningfully.

Funnel Metric	Value
Total sessions	480,036
Sessions with view	478,004
Sessions with cart	38,544
Sessions with purchase	22,601
View rate	99.58%
Cart rate	8.03%
Purchase rate	4.71%

Sessions with cart activity had a 49.62% purchase rate.
Sessions without cart activity had only a 0.79% purchase rate.
Purchased sessions had more events, longer duration, and higher product diversity.
Price-related features were less influential than behavioral features.

Feature Engineering

Session-level features were engineered with PySpark to capture behavioral intensity, product diversity, price exposure, cart/view ratios, binary activity flags, log-transformed numeric variables, and bucketed duration/price features.

Feature Group	Examples
Behavioral counts	total_events, view_events, cart_events, purchase_events
Session timing	session_start_ts, session_end_ts, session_duration_seconds, log_session_duration
Diversity	distinct_products, distinct_categories, distinct_brands
Price statistics	min_price, max_price, avg_price, log_avg_price, log_max_price
Ratios and flags	cart_event_ratio, view_event_ratio, cart_to_view_ratio, has_cart, has_multiple_events
Buckets	duration_bucket_id, price_bucket_id

Machine Learning Models

Two Logistic Regression models were trained to show both a high-performing retrospective classifier and a more defensible leakage-aware evaluation.

Baseline Full-Session Model

Uses full-session behavioral features, including cart-related signals. This model performs extremely well but should be interpreted as a retrospective session classifier.

Leakage-Aware Model

Removes direct cart-related and full-session aggregate features such as cart_events, has_cart, cart_event_ratio, cart_to_view_ratio, and total_events to create a more realistic evaluation.

Model	ROC-AUC	Accuracy	Precision	Recall	F1
Baseline Full-Session LR	0.9995	0.9963	0.9896	0.9315	0.9597
Leakage-Aware LR	0.9973	0.9872	0.9312	0.7868	0.8530

Business Interpretation

The results show that user behavior is highly predictive of session purchase outcomes. Cart activity is the strongest direct signal, but even after removing direct cart-related features, session duration, engagement, and product diversity remain strong predictors.

Cart activity is the clearest behavioral indicator of purchase intent.
Longer and more engaged sessions are more likely to convert.
Product diversity helps distinguish purchase sessions from non-purchase sessions.
A leakage-aware evaluation is important when moving from retrospective analysis toward earlier prediction.

Cloud and Cost-Conscious Design

The project was intentionally designed to avoid always-on infrastructure. AWS S3 and Athena were used for low-cost storage and serverless SQL, while Databricks was used only for interactive PySpark analytics and ML work.

No long-running EC2 or EMR cluster was required.
No Redshift cluster or always-on warehouse was used.
Athena CTAS was used to create efficient Parquet layers.
The final workflow documents a practical Databricks Serverless limitation: direct S3 access was restricted, so the curated dataset was imported into Databricks.

Selected Project Evidence

The following screenshots can be displayed when the corresponding image files are uploaded to the website assets folder.

AWS S3 lakehouse structure with raw, processed, curated, and Athena result layers.

Curated ML-ready Parquet output materialized in Amazon S3.

Databricks/PySpark validation of the session-level ML dataset.

Funnel analysis from product views to cart activity and purchase.

Comparison of baseline and leakage-aware Logistic Regression models.

Limitations and Future Work

The current models are based on full-session aggregates and are better suited for retrospective session classification than real-time prediction.
Future work should build features only from pre-purchase or early-session events.
MLflow could be added for experiment tracking and model registry.
AWS Glue, Step Functions, or Databricks Workflows could automate the pipeline.
A dashboard in Power BI or QuickSight could communicate funnel and model results to business users.

Outcome

This project strengthened my hands-on experience with cloud data lake design, Athena SQL, Parquet materialization, Databricks, PySpark, feature engineering, and Spark ML model evaluation.

It is one of my strongest portfolio projects for data engineering and cloud data engineering applications because it demonstrates both infrastructure thinking and practical machine learning workflow design.