From Manual Tracker to Dynamic Dashboard: Revolutionizing Grants Finance Reporting
We traded our manual spreadsheets for a real-time, automated Looker dashboard. Here’s how it eliminated hours of tedious work and changed the way we present financial data to our leadership team.
Role
Financial Analyst
Industry
Looker, NetSuite, SQL, LookML
Scope
Automated Monthly Process
The Old Way: A Cycle of Manual Effort and Static Reports
Our previous monthly reporting process was a familiar, labor-intensive cycle that, while effective, was fraught with inefficiency and limitations.
The Monthly Grind: The process began with manually exporting raw data from our system of record, NetSuite. This data was then painstakingly cleaned, formatted, and manipulated in spreadsheets—a process that consumed significant time and was susceptible to human error.
Static and Inflexible: The final output was a static report. To present this to leadership, including the CFO, we would take screenshots—literally snipping static images of tables and charts—and paste them into presentation slides.
A Brittle Review Process: If the CFO asked a question that the slides couldn't answer—"Can we see this broken down by project?" or "What was this number last quarter?"—the answer couldn't be provided in real-time. It required going back to the spreadsheet, creating a new view, taking a new screenshot, and updating the deck. This created a slow, cumbersome back-and-forth review cycle.
This method kept us locked in a cycle of preparing data, rather than analyzing it.
The Vision: A World of Real-Time, Interactive Insights
The vision was to create a "single source of truth" for grants finance—a dynamic, self-service Looker dashboard where data could be explored, not just viewed. This would be a place where the leadership team could ask questions and get answers instantly, transforming the monthly review from a static presentation into an interactive, data-driven conversation.
The Transformation: Building the Analytics Foundation
Achieving this vision required more than just connecting a tool; it required building a proper data foundation.
The Data Warehouse: Protecting the Engine The first step was establishing a data warehouse. We couldn't connect Looker directly to our live NetSuite system, as running complex analytical queries could slow it down for everyone doing their daily jobs. Instead, data is automatically copied from NetSuite at regular intervals into a separate, optimized reporting database. This ensures our analysis never impacts core business operations.
Data Modeling: The Critical Shift from Wide to Tall The most crucial preparatory work was in data modeling. Our NetSuite exports and spreadsheets were in a "wide" format—easy for humans to read, with dozens of metrics in their own columns. However, for Looker to function with the flexibility we needed, the data had to be transformed into a "tall" format.
This "unpivoting" process restructured the data so that instead of many metric columns, we have just two: one for the
Metric Nameand one for itsMetric Value. This seemingly simple change is the key that unlocks Looker's power, allowing us to treat metrics as data that can be filtered and analyzed, rather than as rigid column headers.LookML: The Central Rulebook With the data correctly modeled, the data team can build the LookML model. LookML is the code layer in Looker that acts as a central dictionary for our business logic. It's where we define, once and for all, what "Total Award" means or how "AR to Date" is calculated. This ensures that every chart, in every dashboard, across the entire organization, is using the exact same definitions, guaranteeing consistency and trust in our numbers.
The New Reality: A Strategic Shift in How We Work
This new, automated ecosystem fundamentally changes our monthly workflow and the value we deliver.
Zero Manual Prep: The monthly scramble of exporting, cleaning, and formatting data is eliminated. The data flows automatically from NetSuite to the warehouse to Looker. The grants tracker is always ready and waiting.
An Interactive Conversation with the CFO: The monthly review meeting is transformed. Instead of presenting static screenshots, we present a live, interactive dashboard. When the CFO asks, "Can we see the budget for just these three grants?", we can apply a filter and provide the answer in seconds. This allows for deeper, more strategic conversations driven by curiosity, not by the limitations of a slide deck.
From Reporting to Analysis: The time our team once spent on manual prep is now freed up for high-value analysis. We can proactively identify trends, flag potential issues, and explore opportunities, becoming true strategic partners to the business.
This transition is more than an upgrade of tools; it's an upgrade in our capability. We are moving from being reporters of historical data to becoming enablers of real-time, data-driven decisions.
Other projects
Case Study – Saint Mary School, Rajula
Pro Bono Financial Health Review
From Manual Tracker to Dynamic Dashboard: Revolutionizing Grants Finance Reporting
We traded our manual spreadsheets for a real-time, automated Looker dashboard. Here’s how it eliminated hours of tedious work and changed the way we present financial data to our leadership team.
Case Study: Bridging the Data Gap Between Coupa and NetSuite
How a Missing Field Became the Key to Unlocking Financial Efficiency. Re-engineering the Coupa–NetSuite Integration for Accuracy, Efficiency, and Audit Readiness