🚀 SQL Project Series #26

SaaS Product Analytics 💻

Analyze users, subscriptions, feature usage, payments, and customer behavior using SQL to improve product adoption, retention, and revenue.

🎯 Business Objectives
✅ Analyze user growth
✅ Track subscription plans
✅ Monitor feature adoption
✅ Measure Monthly Recurring Revenue (MRR)
✅ Analyze customer retention
✅ Identify customer churn
✅ Measure product engagement
✅ Build executive SaaS dashboards

📂 Step 1: Create Database
CREATE DATABASE saas_analytics_db;
USE saas_analytics_db;

📂 Step 2: Create Users Table
CREATE TABLE users (
user_id INT PRIMARY KEY,
user_name VARCHAR(100),
company_name VARCHAR(100),
country VARCHAR(50),
signup_date DATE
);

📂 Step 3: Create Subscription Plans Table
CREATE TABLE subscription_plans (
plan_id INT PRIMARY KEY,
plan_name VARCHAR(50),
monthly_price DECIMAL(10,2)
);

📂 Step 4: Create Subscriptions Table
CREATE TABLE subscriptions (
subscription_id INT PRIMARY KEY,
user_id INT,
plan_id INT,
start_date DATE,
end_date DATE,
subscription_status VARCHAR(20),
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (plan_id) REFERENCES subscription_plans(plan_id)
);

📂 Step 5: Create Feature Usage Table
CREATE TABLE feature_usage (
usage_id INT PRIMARY KEY,
user_id INT,
feature_name VARCHAR(100),
usage_date DATE,
usage_count INT,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);

📂 Step 6: Create Payments Table
CREATE TABLE payments (
payment_id INT PRIMARY KEY,
subscription_id INT,
payment_date DATE,
amount DECIMAL(10,2),
payment_status VARCHAR(20),
FOREIGN KEY (subscription_id) REFERENCES subscriptions(subscription_id)
);

📂 Step 7-11: Sample Data
Users, Subscription Plans, Subscriptions, Feature Usage, and Payments tables are populated with sample data for Rahul, Priya, John, Emma, and Amit across Free, Basic, Pro, and Enterprise plans.

🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ INNER JOIN / LEFT JOIN
✔ Aggregate Functions
✔ GROUP BY / HAVING
✔ CASE WHEN
✔ Date Functions
✔ CTEs
✔ Window Functions
✔ Ranking Functions

📊 Business KPIs You Can Build
📈 Growth: Total Users, Active Users, Paid Users, Free vs Paid Users, Subscription Growth
📈 Revenue: MRR, ARR, ARPU, CLV, Revenue by Subscription Plan, Payment Success Rate
📈 Retention: Customer Churn Rate, Customer Retention Rate, DAU, MAU, DAU/MAU Ratio
📈 Product: Feature Adoption Rate, Most Used Features, Feature Usage by Plan
📈 Customers: Top Enterprise Customers, Executive SaaS Dashboard

This project reflects real-world SQL analysis performed by Product Analysts, Growth Analysts, Customer Success teams, Revenue Operations teams, and BI professionals at SaaS companies like Salesforce, HubSpot, Atlassian, Notion, Slack, and Zoom.

Double Tap ❤️ For More