← projects

project

Pokedex — Productivity Analysis and Support Technician Allocation

at Sage Networks

PythonDockerGitNginxMySQL

What it is

A Python-based system that accessed a third-party customer support platform's database directly, used by infrastructure and network technicians (contracted to handle and resolve customer tickets). With no API or documentation available, the goal was to turn that raw data into productivity insights and support team management decisions.

Challenge

There was no API or any structured way to access the data — only direct server and database access. The schema was inconsistent, with dirty, incomplete data and no documentation on how the tables related to each other. Reverse-engineering the database schema was a prerequisite before any reliable productivity or allocation metric could even be calculated.

Solution

  • Reverse-engineered the support platform's database structure, mapping tables and relationships with no available documentation.
  • Direct SQL connection and queries to the database to extract ticket and support data.
  • Cleaned and handled inconsistent/incomplete data in Python before calculating any metrics.
  • Processing to calculate per-technician productivity metrics (ticket volume, resolution time, estimated effort).
  • Visual reports and dashboards to track task distribution across the team.
  • Allocation-support logic, cross-referencing workload with each technician's availability to flag imbalances.

Result

A tool that gave management real visibility into the support team's productivity and helped balance ticket distribution across technicians, accounting for each one's capacity and availability — built on top of a data source with no clear structure or documentation.

Tech Stack

  • Python
  • SQL (direct database access)
  • Data processing and cleaning
  • Dashboard/report generation
  • Docker Swarm
  • Nginx

Technical Highlights

  • Reverse-engineered an undocumented database schema
  • Direct SQL data extraction with no API available
  • Handling inconsistent and incomplete data as a core part of the pipeline
  • Productivity and effort metrics calculated from raw support data
  • Visual dashboard for team tracking
  • Allocation logic accounting for individual technician availability
  • Deployed on a Docker Swarm cluster with Nginx as reverse proxy
ZZZZ