Recruiting Metrics Report Generator
Python automation that reduces a 4-5 hour monthly reporting process to a single click. Pulls Workday data, calculates recruiting metrics, and outputs formatted Excel reports.
Background
I had a recruiting report that was refreshed monthly. The process took 4-5 hours: pull reports from Workday, clean the data, update the Excel workbook, apply formatting. It was time-consuming and easy to make mistakes.
What I Built
A Python script that automates the entire workflow. It takes four input files (pipeline activity, monthly targets, headcount changes, and open requisitions) and generates a formatted Excel workbook with seven analysis sheets.
The script is wrapped in an Apple Shortcut, so running it is a single click. The report now goes out weekly instead of monthly.
What It Calculates
Pipeline Metrics
- Applications, interviews, offers, and hires
- Conversion rates between each stage
- Variance from targets with color-coded highlighting
Headcount Tracking
- Employee starts and terminations
- Net headcount changes
- FTE calculations
Time Period Analysis
- Month-to-date, quarter-to-date, and year-to-date views
- Year-over-year comparisons
Technical Notes
Built with Python, pandas, and openpyxl. The architecture separates reusable metric functions from the report generation logic, so the calculations can be imported into other contexts if needed. Date handling is dynamic throughout, with support for generating historical reports via a reference date parameter.
Why It's Here
The script is tailored to my specific dataset and won't work out of the box for anyone else. I'm sharing it as an example of the kind of automation our People Analytics team is building. The patterns - Workday data extraction, modular calculations, formatted Excel output - are applicable to any recurring report.