SupportOps Intelligence Analytics is an end-to-end Customer Support Analytics solution designed to transform raw customer support ticket data into actionable business insights.
The project simulates a real-world Customer Support Analyst workflow by combining:
- Data cleaning and preparation
- Data modeling using dbt
- Analytical warehouse development using DuckDB
- Data quality validation
- Business metric creation
- Interactive Power BI reporting
The solution enables support teams to monitor:
- Ticket volumes
- Resolution performance
- SLA compliance
- Customer satisfaction
- Agent effectiveness
- Support channel performance
Customer support teams generate large amounts of operational data through customer interactions.
However, without proper analytics infrastructure, organizations struggle to answer important questions:
- How many tickets are we receiving?
- Are support teams meeting SLA commitments?
- Which agents perform best?
- Which issues take the longest to resolve?
- Are customers satisfied with support?
- Which channels create the highest workload?
SupportOps Intelligence Analytics addresses these challenges by creating a structured analytics system that converts raw ticket data into decision-ready insights.
The project follows a modern analytics engineering workflow.
Raw Data
|
|
Data Cleaning (Python / Pandas)
|
|
DuckDB Data Warehouse
|
|
dbt Transformation Layer
|
|
Star Schema Analytics Model
|
|
Parquet Data Exports
|
|
Power BI Dashboard
| Area | Technology | Purpose |
|---|---|---|
| Data Processing & Analysis | Python | Data cleaning, transformation, automation, and analytical workflows |
| Data Processing & Analysis | Pandas | Data manipulation, cleaning, and preparation |
| Data Processing & Analysis | NumPy | Numerical computations and data processing operations |
| Data Processing & Analysis | Matplotlib | Data visualization during exploratory analysis |
| Data Processing & Analysis | Seaborn | Statistical visualization and exploratory data analysis |
| Data Warehouse | DuckDB | Lightweight analytical database used for storing and querying transformed data |
| Data Warehouse | Parquet | Columnar storage format used for analytical data exports |
| Analytics Engineering | dbt Core | Data transformation framework for building modular SQL models |
| Analytics Engineering | dbt-duckdb | Adapter enabling dbt transformations on DuckDB |
| Analytics Engineering | SQL | Data transformation logic, modeling, and analytical queries |
| Business Intelligence | Power BI | Interactive dashboard development, KPI reporting, and business insights |
| UI/UX | Figma | User-friendly BI report backgrounds design |
| Development Environment | Jupyter Notebook | Data profiling, exploration, and documentation of analysis |
| Development Environment | VS Code | Primary development environment |
| Version Control | Git | Source code management and project version tracking |
SupportOps Intelligence Analytics/
│
├── dashboards/
│ └── SupportOps Intelligence Analytics.pbix
│
├── data/
│ ├── raw/
│ │ └── customer_support_tickets.csv
│ │
│ └── cleaned/
│ └── customer_support_tickets_clean.csv
│
├── database/
│ └── supportops.duckdb
│
├── dbt/
│ ├── models/
│ │
│ ├── staging/
│ │ └── stg_ticket.sql
│ │
│ ├── intermediate/
│ │ └── int_ticket_metrics.sql
│ │
│ └── marts/
│ ├── fact_ticket.sql
│ ├── dim_customer.sql
│ ├── dim_agent.sql
│ ├── dim_category.sql
│ ├── dim_channel.sql
│ └── dim_priority.sql
│
├── docs/
│ ├── architecture.md
│ ├── business_metrics.md
│ ├── data_dictionary.md
│ └── screenshots/
│
├── exports/
│ └── parquet files
│
├── notebooks/
│ ├── 01_data_profiling.ipynb
│ └── 02_data_cleaning.ipynb
│
├── python/
│ ├── load_to_duckdb.py
│ └── export_to_parquet.py
│
├── requirements.txt
│
└── README.md
The analytical model follows a star schema.
dim_customer
|
|
dim_agent ---- fact_ticket ---- dim_priority
|
|
dim_category
|
|
dim_channel
Contains one row per support ticket.
Main measures:
- Resolution time
- Satisfaction score
- SLA performance
- Ticket complexity
Customer attributes.
Used for:
- Customer analysis
- Ticket frequency analysis
Support agent information.
Used for:
- Agent performance
- Workload analysis
Ticket issue categories.
Used for:
- Problem identification
- Trend analysis
Support interaction channels.
Used for:
- Channel performance analysis
Ticket urgency classification.
Used for:
- SLA monitoring
- Escalation analysis
Data quality validation is implemented using dbt tests.
Implemented checks include:
Ensures primary keys contain no duplicates.
Examples:
ticket_id
customer_key
agent_key
channel_key
priority_key
Ensures critical fields are populated.
Examples:
customer_email
assigned_agent
ticket_id
Ensures foreign keys correctly map to dimension tables.
Example:
fact_ticket.customer_key
references
dim_customer.customer_key
The Power BI report contains three analytical pages.
Purpose:
Provides leadership-level visibility into overall support performance.
Key KPIs:
- Total Tickets
- Average Resolution Hours
- Average Satisfaction Score
- SLA Success Rate
- Total Customers
- Total Agents
Visuals:
- KPI cards
- Ticket trend analysis
- SLA performance charts
- Resolution distribution
Purpose:
Evaluates support team productivity and effectiveness.
Key Metrics:
- Tickets handled per agent
- Average resolution time
- Agent SLA compliance
- Agent satisfaction score
Visuals:
- Agent ranking table
- Workload comparison
- SLA performance charts
Purpose:
Analyzes customer behavior and ticket patterns.
Key Metrics:
- Customer ticket volume
- Issue category performance
- Support channel distribution
- Ticket priority analysis
Visuals:
- Category breakdown
- Channel analysis
- Priority distribution
- Customer trends
The solution allows stakeholders to identify:
- Resolution bottlenecks
- Support workload patterns
- SLA performance issues
- High-performing agents
- Coaching opportunities
- Workload imbalance
- Satisfaction trends
- Frequent customer issues
- Areas requiring improvement
- Best-performing channels
- Common issue categories
- Resource allocation opportunities
Create a virtual environment:
python -m venv venvActivate environment:
Windows:
venv\Scripts\activateInstall packages:
pip install -r requirements.txtRun:
python python/load_to_duckdb.pyNavigate into dbt folder:
cd dbtBuild models:
dbt runRun tests:
dbt testRun:
python python/export_to_parquet.pyOpen:
dashboards/SupportOps Intelligence Analytics.pbix
using Power BI Desktop.
Additional documentation:
| Document | Description |
|---|---|
| architecture.md | Technical architecture and workflow |
| business_metrics.md | KPI definitions and calculations |
| data_dictionary.md | Table and column definitions |
This project demonstrates practical experience with:
- Data cleaning
- Exploratory analysis
- KPI development
- Business reporting
- SQL transformations
- Data modeling
- Star schema design
- dbt development
- Power BI dashboards
- Data storytelling
- Executive reporting
- Analytical warehouse creation
- Data pipelines
- Data validation
- SLA analysis
- Agent performance
- Customer experience metrics
Potential enhancements:
- Automated dashboard refresh pipeline
- Cloud warehouse deployment
- Real-time ticket monitoring
- Predictive SLA breach detection
- Customer churn prediction
- Sentiment analysis from ticket descriptions
Reginald Erzoah
Data Analyst | Business Intelligence Analyst
GitHub: https://github.com/ReginaldErzoah
Portfolio: https://reginalderzoah.github.io
SupportOps Intelligence Analytics demonstrates how raw operational data can be transformed into a complete analytics product.
By combining analytics engineering, SQL, Python, dbt, DuckDB, and Power BI, the project provides a reliable foundation for customer support teams to make faster and more informed decisions.