How to Build an End-to-End Data Analytics Project Using Excel, SQL, Python and Power BI

-- PostgreSQL Relational Schema & Pre-Aggregated View
CREATE TABLE dim_customers (
    customer_id VARCHAR(50) PRIMARY KEY,
    customer_name VARCHAR(150),
    region VARCHAR(100),
    signup_date DATE
);

CREATE TABLE fact_orders (
    order_id VARCHAR(50) PRIMARY KEY,
    customer_id VARCHAR(50) REFERENCES dim_customers(customer_id),
    order_date DATE,
    order_status VARCHAR(50),
    order_amount NUMERIC(12, 2)
);

-- Executive Pre-Aggregated View for Power BI Direct Connection
CREATE OR REPLACE VIEW v_customer_lifetime_kpis AS
SELECT 
    c.customer_id,
    c.customer_name,
    c.region,
    COUNT(o.order_id) AS total_orders,
    SUM(o.order_amount) AS total_spend,
    ROUND(AVG(o.order_amount), 2) AS avg_basket_size,
    MIN(o.order_date) AS first_order_date,
    MAX(o.order_date) AS last_order_date
FROM dim_customers c
JOIN fact_orders o ON c.customer_id = o.customer_id
WHERE o.order_status = 'DELIVERED'
GROUP BY c.customer_id, c.customer_name, c.region;

# Phase 3: Python Pandas Analysis & SQL Integration
import pandas as pd
from sqlalchemy import create_engine
import seaborn as sns
import matplotlib.pyplot as plt

# Connect to PostgreSQL warehouse
engine = create_engine('postgresql://datadrix_user:secret@localhost:5432/enterprise_dw')
df_kpis = pd.read_sql("SELECT * FROM v_customer_lifetime_kpis", engine)

# Customer Lifetime Value Quartile Distribution
df_kpis['clv_quartile'] = pd.qcut(df_kpis['total_spend'], q=4, labels=['Low', 'Medium', 'High', 'VIP'])

# Display Key Segment Breakdown
segment_summary = df_kpis.groupby('clv_quartile', observed=False).agg(
    total_customers=('customer_id', 'count'),
    mean_spend=('total_spend', 'mean'),
    mean_basket=('avg_basket_size', 'mean')
)
print("Customer Segment Summary:
", segment_summary)