ESG Report Automation for Large Enterprise

Approach

This large enterprise client operates multiple plants, each producing various materials with associated wastage or recycled products. Monthly data is collected and analyzed to manage and reduce wastage, increasing profitability. The ESG (Environmental, Social, and Governance) report is crucial for auditing and financial departments to monitor losses and sustainability efforts.

Problem Statement

Data from each plant supervisor/assistant is collected monthly, stored in Excel files, and shared via One Drive. The data is organized in a hierarchical folder structure – State > Plant > Department Head > Report Head > Excel File. With over 80 files in different shared folders, generating a consolidated monthly report becomes challenging. Data updates are not fixed, causing delays, and reminders need to be sent on the 5th, 10th , 15th , 20th , 25th day of each month until data is updated. Tracking and reporting on user data updates are essential.

Proposed Solution

The proposed solution involves using Alteryx for ETL (Extract, Transform, Load) processes, automation, and Power BI for visualization.

Briefly

Data Extraction

Alteryx fetches data from all One Drive folders using a single input tool.
Data is merged and segregated based on materials and plant-wise.

Data Transformation

Files are processed based on their updating date.
Data is split into two categories: updated and non-updated.
Addressing formatting issues, errors, and wrong entries using Alteryx.

Automated Reporting

For updated data, Alteryx generates the ESG report.
Reminder emails are sent for non-updated data after every 5 day of each month.

Monitoring and Analysis

Alteryx outputs a Power BI data extract for KPI monitoring.
Power BI dashboard tracks updated and pending data, material quantities, top/bottom 5 plants, and other relevant metrics.

Challenges Faced

Inconsistent report formats and incorrect data entries.
Alteryx-level challenges in handling diverse data formats.
Ongoing user education to minimize errors in master data entries.

Status

The proposed solution is in the Proof of Concept (POC) phase, where all requirements have been fulfilled using Alteryx. Challenges are being addressed, and ongoing user education is reducing unnecessary data entry errors.

Future Steps

Continue refining the solution based on user feedback, fully integrate Alteryx processes, and deploy the automated ESG reporting system for wider use across all plants. Regular updates and optimizations will be implemented to enhance efficiency and accuracy.