CRM Analytics Dashboard — From Scattered Data to Real-Time Sales Visibility

The Problem

The client — a mid-sized service business with a CRM system — had no clear, centralized view of what was happening with their sales funnel. Teams depended on scattered reports, manual follow-ups, and weekly Excel sheets that took hours to compile.

• Sales team had no live view of appointments, conversions, or deal status
• Management needed 2–3 hours of manual work every Friday just to compile the weekly report
• Department performance was tracked in separate files with no unified view
• Revenue tracking was inconsistent — different people used different formulas

The Solution

I built a complete reporting pipeline: a Python script extracts CRM data from the API, cleans and loads it into PostgreSQL, and Power BI connects to the database for a fully automated, always-current dashboard.

• Python script runs on schedule — pulls data from CRM API, handles pagination and auth
• Data is cleaned, transformed, and loaded into a structured PostgreSQL database
• Power BI connects directly to PostgreSQL via DirectQuery for real-time updates
• DAX measures handle all business-logic calculations consistently
• Role-level security ensures sales reps only see their own data

What Was Built

• Executive overview page — total revenue, appointments, conversion rate, MoM growth
• Sales funnel — stage-by-stage breakdown from lead to close
• Department performance — individual and team-level KPIs side by side
• Appointment calendar view — daily/weekly booking trends
• Drillthrough pages — click any KPI to see the underlying records

Tech Stack

• Power BI Desktop + Service — Dashboard design and cloud publishing
• DAX — Revenue, conversion %, MoM growth, and custom KPI calculations
• PostgreSQL — Central analytics database with star schema design
• Python (pandas, requests, psycopg2) — API extraction and ETL pipeline
• REST API — CRM data extraction with OAuth authentication

Results