Enhancing Operational Visibility Within BI Tool

Case Study

About the client

The Client specializes in providing sustainable solutions for businesses, focusing on carbon reduction strategies and implementing environmentally conscious practices. Their expertise includes creating detailed reports on contracted KW, registered KW, Email Campaigns, Invoicing, and Employee Performance within the BI platform.

Industry

Environmental Consultancy

Revenue (USD)

Million

Head Count

Employees

Countries Of Operation

US

Overview

Establish reporting mechanisms to track registered kilowatts, ensuring accurate measurement and accountability.
Implement reporting tools for Email Campaign metrics to assess the effectiveness of marketing efforts.
Create comprehensive reports on invoicing data, streamlining financial tracking and analysis.

Challenges

The existing procedure relies on Google Sheets as the reporting source, utilizing only table charts.
However, this approach hinders effective performance evaluation over time due to limitations in comparative analysis.

KPIs

Four different dashboards were created considering the existing issue. The KPIs according to dashboards are-

Dashboard for Solar Usage-

Contracted kW comparison (CMTD to PMTD)
Registered kW comparison (CMTD to PMTD)
Contracted kW by month (YoY)
Contracted kW by partner company
Registered kW by month (YoY)
Registered kW by partner company

Dashboard for Email Campaigns-

Total Emails Sent, Unique Opens, Clicks, Click rate and Open rate
Engagement Over Time (YTD)
Click rate vs. open rate
Top and Bottom 20 subject line by Unique opens and clicks
Consolidated Campaigns report by Subject Line

Dashboard for Invoicing-

Total Unpaid Invoices & Outstanding Amount
Invoice Paid Trend by Month (CYTD)
Top 20 Partner Companies by Unpaid Invoices and Outstanding Amount
Paid vs. Unpaid Invoices
Invoice report table

Dashboard for Employee Performance-

Completed task items by week
Talk time hours by week
Number of open and pending tickets by Employee
Number of tickets updates, solved and created (Last 30 Days)
Response time on ticket by Employee

Tools and Technologies

The relevant tools and technologies used in conjunction with BI platform to improve the data processes are :

Google Sheet Connector

Transforming using Magic ETL

Business Functions Benefited

Data Integration: Seamless integration with Google Sheets eliminated manual data entry, enhancing data accuracy and consistency in reporting.
Real-time Reporting: BI tool facilitated the creation of consolidated reports, providing real-time insights for timely decision-making.
Customizable Dashboards: Leveraging customizable dashboards, we tailored intuitive visualizations, simplifying data analysis for stakeholders to identify trends and gain actionable insights.
Report Automation: BI tool automated report generation and distribution, saving time and ensuring timely delivery of key updates, such as monthly ticket summaries, for stakeholders, etc.