Tools : Looker Studio, Google BigQuery, Google Cloud Function, Google Cloud Scheduler
Tags : Finance Settlement
Created : 2024

💡 "This is one of the most complex dashboards I've worked on, spanning multiple pages that are crucial for the finance department and its sub-divisions. Since the company's main operational management system lacks a raw data download feature due to high costs, this functionality is shifted to BI dashboards, significantly reducing expenses. Many pages provide pre-processed data for the finance team to refine for billing, while others offer aggregated insights for quick analysis."

This dashboard ensures precise financial reconciliation by structuring raw transaction data into an optimized BI solution. It enables finance teams to validate settlements, match aggregated figures with raw data, and download reports seamlessly via Google Cloud Storage

🔹 Crucial Raw Data for Reconciliation

Settlement reconciliation requires precise transaction data to ensure all financial records match operational reports. Since the necessary data is scattered across multiple sources—transaction records, payment logs, shipping fees, and platform deductions—I developed a structured datamart by integrating these datasets. This transformation reduces manual effort, ensures data accuracy, and provides finance teams with a final dataset tailored for reconciliation and revenue validation.

🔹 Accurate Aggregation Matching Raw Data

To maintain financial accuracy, I designed a monitoring dashboard that tracks the number of raw records inserted into each datamart. This additional validation layer ensures that every transaction processed aligns with the aggregated totals in the final reports. By continuously tracking data flows, I can detect anomalies early, prevent revenue misallocation, and ensure compliance with financial audits.

🔹 Automated CSV Download via Google Cloud Storage

This project was initially developed by Data Engineers to extract raw data into CSV files for finance reconciliation. Over time, I gained access and became involved in monitoring, maintaining, and troubleshooting the process. If errors occur in file generation, I can identify issues, backfill missing data, and ensure finance teams receive complete reports. With processed settlement data automatically stored in Google Cloud Storage, finance teams can seamlessly download structured reports for audits, analysis, and system integration—reducing manual workload and improving efficiency.

🔹 Additional Features & Impact

• Multi-Level Filtering & Drilldowns – Users can segment financial data by platform, payment type, or region for deep-dive analysis.

• Real-Time Data Processing – Google Cloud Functions and BigQuery ensure near-instant data availability, minimizing reporting delays.

• Operational Cost Savings – By shifting data processing from the main system to BI dashboards, the company significantly cuts costs associated with raw data extraction.

By integrating structured financial data, automated reporting, and scalable reconciliation tools, this Finance Settlement Report dashboard has become an essential asset for finance teams—improving efficiency, ensuring accuracy, and optimizing operational costs. 🚀

You can find the SQL code and technical breakdown in the GitHub Repository.