A real-world SQL project focused on customer analytics, revenue intelligence, RFM segmentation, and cohort retention analysi

Introduction

Every online purchase generates valuable business data.

From customer behavior and purchasing frequency to revenue trends and retention patterns, e-commerce companies rely heavily on analytics to make better business decisions.

However, raw transactional data alone is not useful unless it is transformed into meaningful business insights.

To understand how real-world analytics systems are built, I developed a complete E-Commerce Customer Intelligence & Business Analytics System using MySQL on top of a real-world retail dataset containing more than 500,000 transactions.

This project focuses on:

• Customer behavior analysis
• Revenue analytics
• Customer segmentation
• Cohort retention analysis
• KPI reporting
• Business intelligence reporting

The goal was not just to practice SQL queries, but to simulate how real analytics systems are designed and implemented inside companies.

Project Objectives

The primary objectives of this project were:

• Analyze customer purchasing behavior
• Identify high-value customers
• Measure customer retention trends
• Evaluate sales and revenue performance
• Create reusable analytical SQL systems
• Generate business insights and strategic recommendations

Dataset Information

For this project, I used the Online Retail Dataset from Kaggle.

Dataset Details

• 500K+ e-commerce transactions
• Real-world retail purchase records
• Customer transaction history
• Product-level purchase data

Main Dataset Columns

Column

InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice CustomerID, Country

Description

Unique invoice number, Product identifier, Product description, Quantity purchased, Transaction timestamp, Product unit price, Unique customer ID, Customer country

This dataset is ideal for customer analytics and business intelligence projects because it contains both transactional and customer-level information.

Tech Stack

• MySQL
• SQL
• VS Code
• Kaggle Dataset
• GitHub

Project Architecture

The project was designed as a structured analytics workflow:

1. Data Ingestion
2. Data Cleaning
3. Data Transformation
4. Revenue Analysis
5. Customer Segmentation (RFM)
6. Cohort Retention Analysis
7. KPI Reporting
8. SQL Views & Stored Procedures
9. Business Insights & Recommendations

This workflow closely resembles real-world business analytics pipelines used in retail and e-commerce companies.

Data Cleaning & Preparation

Raw datasets are rarely analysis-ready.

Before performing analytics, I cleaned and standardized the dataset using SQL.

Data Cleaning Steps

• Removed records with missing customer IDs
• Removed invalid transactions
• Standardized date formats
• Converted invoice dates into DATETIME format
• Validated transaction integrity
• Handled datatype inconsistencies

This stage was important because inaccurate or incomplete data can significantly impact business reporting and customer analytics.

Data Transformation

To improve reporting efficiency, I created additional business-focused analytical columns.

Revenue Calculation

I created a new Revenue column using:

Revenue = Quantity * UnitPrice

This enabled easier revenue tracking and KPI calculations.

Order Month Feature

I also created an OrderMonth field to support:

• Monthly revenue analysis
• Trend reporting
• Cohort analysis
• Customer activity tracking

Feature engineering is a critical step in real-world analytics workflows because it improves reporting flexibility and business intelligence capabilities.

Revenue Analysis

One of the main goals of the project was to understand overall business performance.

I performed multiple revenue-focused analyses, including:

• Total Revenue
• Average Order Value
• Monthly Revenue Trends
• Daily Revenue Trend
• Top Customers
• Top Products
• Country-wise Revenue

Key Findings

Some of the most interesting findings included:

• A significant portion of revenue came from repeat customers
• Certain products consistently generated high sales volume
• The United Kingdom contributed the highest overall revenue
• Revenue spikes were observed during holiday periods

These analyses help businesses understand both customer behavior and operational performance.

RFM Customer Segmentation

One of the strongest parts of this project was implementing RFM Analysis.

RFM stands for:

Recency — How recently a customer purchased

Frequency — How often they purchase

Monetary — How much they spend

Using these metrics, customers were classified into segments such as:

• Champions
• Loyal Customers
• Big Spenders
• At Risk Customers
• Regular Customers

Why RFM Matters

RFM analysis is widely used by e-commerce companies because it helps businesses:

• Identify high-value customers
• Improve customer retention
• Reduce churn
• Personalize marketing campaigns
• Increase customer lifetime value

Key Insight

The analysis revealed that a relatively small group of high-value customers contributed disproportionately to total revenue, which highlights the importance of retention-focused marketing strategies.

RFM Segmentation Output

Cohort Retention Analysis

Another major component of the project was Cohort Analysis.

A cohort is a group of customers who made their first purchase during the same time period.

The objective was to measure how customer retention changes over time.

What I Analyzed

• Repeat purchasing behavior
• Customer retention rates
• Cohort-based engagement trends
• Long-term customer activity

Key Findings

Some cohorts demonstrated significantly stronger retention than others.

Customers acquired during holiday periods showed better long-term engagement and repeat purchasing behavior.

Retention gradually declined over time for several customer groups, which is common in retail businesses.

Business Value

Cohort analysis helps companies:

• Evaluate customer loyalty
• Improve lifecycle marketing
• Measure long-term engagement
• Optimize customer retention strategies

This type of analysis is heavily used in product analytics and subscription-based businesses.

Cohort Retention Output

Executive KPI Reporting

To simulate executive-level business reporting, I developed multiple KPI metrics such as:

• Total Revenue
• Total Orders
• Total Customers
• Average Order Value
• Repeat Customer Percentage
• Monthly Active Customers
• Top Revenue Markets

These KPIs provide management teams with quick visibility into overall business performance.

SQL Views & Stored Procedures

To make the project more production-oriented, I created reusable SQL Views and Stored Procedures.

SQL Views

Views were created for:

• Monthly Sales Reporting
• Customer Summaries
• Product Performance
• Country-wise Revenue
• Customer Segments

Stored Procedures

Stored procedures automated common business reports such as:

• Top Customer Reporting
• Monthly Revenue Reporting
• Country Revenue Analysis
• Customer Segment Reporting

These components simulate how enterprise reporting systems are designed inside real organizations.

Final Business Insights

After completing the analytics workflow, several business insights became clear.

Customer Insights

• Loyal customers generated a significant portion of overall revenue
• Several high-value customers showed churn-risk behavior
• Repeat customers were essential for business stability

Revenue Insights

• Seasonal purchasing patterns strongly impacted revenue
• Top-selling products consistently drove business growth
• Revenue concentration existed among a limited customer group

Retention Insights

• Holiday-period customer cohorts showed stronger retention
• Customer engagement gradually declined after initial purchases

Strategic Recommendations

Based on the analysis, several business recommendations can be made:

• Launch targeted retention campaigns for high-value customers
• Improve loyalty and rewards programs
• Personalize customer marketing strategies
• Focus marketing investments on high-performing customer cohorts
• Expand top-performing products into additional markets

Key Skills Demonstrated

This project helped me strengthen multiple analytical and technical skills, including:

• SQL Query Optimization
• Data Cleaning & Transformation
• Business Intelligence Reporting
• Customer Segmentation
• Cohort Retention Analysis
• KPI Development
• SQL Views & Stored Procedures
• Business Problem Solving

One of the most valuable parts of this project was understanding how SQL can be used not just for querying data, but for solving real business problems through analytics and customer intelligence.

Conclusion

This project was an excellent opportunity to apply SQL in a real-world business analytics environment.

Rather than focusing only on basic SQL queries, the project emphasized customer intelligence, revenue analysis, retention modeling, and business decision-making.

Building this system helped me better understand how analytics workflows are designed in real organizations and how data can be transformed into meaningful business insights.

Overall, this project strengthened both my SQL skills and my understanding of customer analytics, business intelligence, and data-driven decision-making.

GitHub Repository

https://github.com/niranjannandams99-droid/E-Commerce-Customer-Analytics

A Data-Driven Look at the City’s Food Market

Introduction
Bangalore is often called the startup capital of India, but anyone who has spent some time in this city knows there’s another thing the city is famous for food.
From small street-side stalls to premium restaurants and trendy cafes, Bangalore’s food scene is incredibly active. Every neighborhood seems to have its own cluster of restaurants, and it’s not unusual to see new places opening every month.
As someone who recently moved to Bangalore, I noticed this very quickly. No matter where you go BTM, Koramangala, or Indiranagar restaurants are everywhere.
This made me curious.
If someone actually wanted to open a new restaurant in Bangalore today, how would they decide where to open it?
Would it make sense to open a restaurant in areas that already have many restaurants? Or would it be better to find places where competition is lower?
Instead of guessing, I decided to explore this question using data.
This analysis uses Zomato’s restaurant data to find patterns in restaurant locations, popular cuisines, pricing strategies, and how customers interact with restaurants in Bangalore.

The goal is simple:
Can data help identify potential opportunities for opening a new restaurant in Bangalore?

Restaurant Competition Across Bangalore

The first thing I wanted to understand was where restaurants are concentrated in the city.

When I analyzed the number of restaurants by location, certain areas clearly stood out.

Locations like BTM, HSR Layout, and Koramangala 5th Block have a very high number of restaurants.

This is not surprising, since these areas are well known for their food culture and attract a large number of students, professionals, and visitors.

However, this also indicates something important — these areas are highly competitive markets.

Insight

• High restaurant density = strong competition
• Opening a restaurant in these areas would require a strong concept or differentiation.

Where Is Customer Demand Highest?

Next,I wanted to understand which areas show the highest customer engagement.

To estimate demand, I used the number of votes that restaurants receive on Zomato. Although votes are not a perfect measure of demand, they provide a good indication of how much attention a restaurant receives from customers.

When analyzing average votes by location, several areas showed particularly high engagement.

Some of the locations with the highest average votes include:

• Church Street
• Lavelle Road
• Koramangala 5th Block
• St Marks Road
• Cunningham Road

These areas are known for their vibrant food culture and nightlife.

Insight

Locations with higher votes indicate stronger customer demand and higher dining activity

Cuisine Trends in Bangalore

Another interesting question was understanding what kinds of cuisines dominate the Bangalore restaurant market.

After splitting and analyzing the cuisine data, some clear trends appeared.

The most common cuisines in Bangalore include:

• North Indian
• Chinese
• South Indian
• Fast Food
• Biryani

This suggests that customers in Bangalore tend to prefer flavorful and familiar cuisines, especially those that are suitable for both dine-in and delivery.

Biryani appearing among the top cuisines also highlights how popular this dish has become across different parts of the city.

Insight

North Indian and Chinese cuisines dominate the market, followed by South Indian and fast food options.

Pricing Strategy in the Restaurant Market

Pricing is another important factor that influences restaurant success.

To understand how restaurants are priced in Bangalore, I analyzed the distribution of approximate cost for two people.

The results revealed a clear trend.

Most restaurants fall within the ₹200 to ₹800 range, with a strong concentration around mid-range dining. While there are some premium restaurants with prices above ₹2000, they represent a relatively small portion of the market.

Insight

The Bangalore restaurant market is largely driven by mid-range dining experiences.

Strategic Suggestion

New restaurants may benefit from targeting the ₹300–₹700 price range, which aligns with the spending behavior of a large segment of customers.

Do Ratings Influence Popularity?

Customer ratings often influence how people choose restaurants.

To explore this, I compared restaurant ratings with the number of votes they received.

The scatter plot revealed an interesting trend.

Restaurants with ratings above 4.0 tend to receive significantly more votes compared to lower-rated restaurants.

This suggests that customer satisfaction has a strong influence on restaurant popularity.

Insight

Higher ratings are generally associated with greater customer engagement and visibility.

Identifying Market Opportunities

To take the analysis further, I created a simple metric called the Market Opportunity Score.

The idea behind this metric is simple.

Opportunity Score = Demand /Competition

Where:

• Demand is represented by average votes
• Competition is represented by restaurant density

This approach helps identify locations where customer demand exists but competition is relatively lower.

When applying this metric, some interesting locations appeared as potential opportunities.

Locations such as Rajarajeshwari Nagar, West Bangalore, and Central Bangalore showed relatively strong opportunity scores.

Insight

These locations may represent areas where new restaurants could explore potential market opportunities.

Key Strategic Recommendations

Based on the analysis, a few important insights emerge for anyone planning to open a restaurant in Bangalore.

Location Strategy

Consider emerging areas where demand exists but competition is relatively lower.

Cuisine Strategy

Focus on popular cuisines such as North Indian, Chinese, and Biryani.

Pricing Strategy

Target the mid-range pricing segment between ₹300 and ₹700.

Quality Strategy

Maintain customer ratings above 4.0, since higher ratings significantly influence engagement.

Final Thoughts

Having worked on this analysis, I have gained a new perspective on the restaurant industry in Bangalore. I used to think that the success of a restaurant depends on the quality of the food they serve and the brand name. However, the analysis shows that the location of the restaurant, the demand for the restaurant’s products, the price charged by the restaurant, etc., are important factors too. Though success can never be guaranteed by the data, I think it can certainly help the restaurant make a better decision.

A Note on My Learning Journey

This project is part of my learning journey through the Post Graduation in Data Science and Analytics Program at Imarticus Learning.Through this program, I’ve been learning how to apply data analysis and visualization techniques to real-world problems.Working on this blog was actually one of those moments where everything started to connect turning raw data into insights that help explain how a real industry works.

Brewing ideas and crunching numbers, all thanks to Imarticus Learning.

There was a time when my world was filled with machines.

Control panels.
Sensors.
Production alarms.
Long shifts inside a manufacturing plant.

As an Instrumentation Engineer, my job was clear — if something failed, I had to fix it. Production couldn’t wait. Machines couldn’t stop.

It was real responsibility.

And I was good at it.

But somewhere between breakdown calls and production reports, a quiet thought started growing inside me.

Every failure had numbers behind it.
Every machine generated data.
Every report told a story.

Yet I didn’t know how to truly understand those stories.

We were surrounded by data — but I didn’t have the tools to analyze it.

That realization unsettled me.

Not because I hated my job.

But because I knew I was capable of more.

I didn’t want to just fix systems.

I wanted to understand patterns.
Predict outcomes.
Think deeper.

So I made a decision.

Not impulsive.
Not emotional.

Intentional.

I chose to transition into Data Science and Data Analysis.

And this time, I didn’t want to do it halfway.

I stepped away from my job and committed fully to learning.

Instead of balancing shifts and late-night studying, I decided to focus completely on building new skills and strengthening my analytical foundation.

Leaving a stable job and becoming a learner again felt risky.

There were doubts.

Was this the right move?
Was I starting over too late?

But I knew something clearly:

Growth requires commitment.

And I was ready to commit.

In my previous company, Excel was the primary tool used for reports and tracking.

So I began there — strengthening my fundamentals.

Then came MySQL.

Then Python.

Python threw errors I didn’t understand.
Pandas felt overwhelming at first.

Everything felt new.
Everything felt uncomfortable.

But every small breakthrough changed something inside me.

The first time a query worked.

The first time I cleaned a dataset properly.

The first time I visualized data and saw patterns clearly.

That feeling was different.

It wasn’t about escaping engineering.

It was about evolution.

From reacting to problems
To analyzing them.

From maintaining machines
To understanding data.

From plant floors
To Python scripts.

Today, this is no longer an experiment.

This is my direction.

The shift wasn’t easy.

But it was necessary.

Comfort would have kept me in the plant.

Growth pushed me into data.

And I chose growth.