Automated KPI Report
An OKR project of my team to evaluate the performance of all staff in the SME department
Highlights
- Developed an automation system to streamline KPI tracking and performance evaluation for the SME Department
- Built a Power BI dashboard with filters by month, role, and team, enabling leaders to monitor staff performance daily and reducing the workload of the C&B team
Context
SME Department includes multiple teams (Sale SGN, HAN, EXP; Business Analyst; User Loyalty) with monthly rewards based on predefined KPIs and targets
KPIs vary each month, requiring comparison between HR target sheets and actual performance to ensure accurate evaluation
Request
Developed the Automated HR Report project to streamline KPI tracking and automate performance-to-target comparisons
Enabled daily performance monitoring for team leaders and ensured accurate, transparent reward allocation
Target:
At the beginning of each month (before the 5th), SME Department submits KPI targets to the HR Manager
To integrate these targets into dashboards and ensure automatic updates, the raw sheets must be transformed and scheduled
Implemented Google App Scripts to:
- Auto-import monthly KPI sheets into separate tabs
- Reformat data into a standardized, dashboard-ready layout
- Add monthly tags and set recurring triggers.
Set recurring triggers (10th monthly)
2. Actual:
As KPIs may be added or adjusted monthly, performance data was processed with SQL on BigQuery.
Transformed actual results into a consistent format and integrated them into Power BI as the “actual” dataset
3. Dashboard illustration on Power BI:
Built an interactive Power BI dashboard with filters (month, staff level, role, team)
Applied weights for each KPI according to staff role and responsibility
Used color-coded visuals to highlight staff performance compared to team and role peers