From CSV to Dashboard: Customer Shopping Trends Analysis with Python, PostgreSQL, and Power BI

#python #postgres #powerbi #dataanalysis

A retail company wants to understand its customers' shopping behavior to improve sales, satisfaction, and loyalty. I built this end-to-end analytics project to answer that question with a modern data-analyst stack: Python → PostgreSQL → Power BI.

The business problem

How can the company leverage consumer shopping data to identify trends, improve customer engagement, and optimize marketing and product strategies?

The analysis answers 10 business questions covering revenue by gender and age group, discount behavior, subscriber economics, shipping preferences, product ratings, and loyalty segmentation.

What the data looks like

3,900 synthetic retail purchase records
18 columns: purchase amount, review rating, gender, age, category, item purchased, payment method, subscription status, shipping type, etc.

Pipeline

CSV (3,900 rows)
  ↓ pandas — cleaning & feature engineering
PostgreSQL via SQLAlchemy
  ↓ 10 business questions solved in SQL
Power BI Desktop
  ↓ KPI cards, donut, 4 charts, 4 slicers, explicit DAX measures
Report + presentation

What I fixed vs. the tutorial

The tutorial this project was based on plotted some visuals using Sum(customer_id), which produced meaningless totals in the millions. I audited every binding and replaced those with explicit DAX measures like:

Number of Customers      = COUNT('public customer'[customer_id])
Average Purchase Amount  = AVERAGE('public customer'[purchase_amount])
Average Review Rating    = AVERAGE('public customer'[review_rating])

Five key findings

Finding	What the data shows
Clothing drives revenue	$104,264 of $233,081 total (44.7%)
The gender gap is audience size	Male $157,890 total, but $59.54 vs $60.25 per customer
Subscriptions don't raise basket size	$59.49 subscribed vs $59.87 non-subscribed
Discounts don't grow baskets either	$59.28 discounted vs $60.13 full-price
Loyal base, thin acquisition funnel	3,116 loyal (79.9%) vs 83 first-time buyers (2.1%)

Tech stack

Layer	Tool
Cleaning & ETL	Python (pandas)
Storage & SQL	PostgreSQL
Visualization	Power BI Desktop (DAX)
Reporting	PDF + PowerPoint

Repo, report, and reproduction

GitHub: Customer-Shopping-Trends-Analysis
Report: included as PDF and executive deck in the repo
Reproduce: README has exact steps, including setting PG_PASSWORD before running the notebook

This was my first end-to-end portfolio project. The biggest lesson was not the syntax — it was learning how to translate a business question into an ETL + SQL + dashboard chain that produces an actionable answer.

DEV Community