The weekly sales report in 15 minutes instead of 6 hours
Weekly reports for 3 sales teams, built by hand in 6 h, now produced in 15 min by PowerShell and Python: 17 charts every week.
Situation
Every week, management and team leads follow the activity of 3 sales teams: 5 business account managers, 2 training advisers and 2 job-placement advisers, across 2 sites.
The figures are scattered: one Excel file per adviser on SharePoint, and the CSV exports of the training management software, one per site. From April to October 2023, the reports were built by hand every week, for about 6 hours of work.
Approach
- Collection: a PowerShell script (PnP module) downloads the advisers’ Excel files from SharePoint.
- Preparation: Python (pandas) merges them with the CSV exports of both sites and keeps only the past week.
- Calculation: calls made, job offers collected by source, appointments set and planned per adviser, matched against attendance and pre-registrations.
- Constraint: the phone system’s call logs, behind two-factor authentication, stay out of scope; calls are counted from the advisers’ files.
- Output: 17 charts generated (matplotlib), ready to send.
Impact
Operational outcomes
- The report goes out every week to management and team leads, until the end of 2024.
- The same indicators for every adviser, comparable from one week to the next.
- In February 2024, two more people added to the report.
In figures
- 6 h → 15 minfor the weekly report
- −96%of time spent on reporting
- 17charts produced every week
- 9advisers tracked, across 3 teams and 2 sites
- 7 weeksof development, from 16 October to 2 December 2023
- 13 monthsin service, until the end of 2024
A similar project?
Berfay Hunalp
Admin automation for small and mid-sized companies