Sales Goal Tracking System
In a sales-oriented business, our client needed near real-time access to their revenue figures to compare with sales goals. However, they were undergoing a transition between two platforms where revenue was generated. Reports existed in their BI portal but were slow to run, put a strain on their production databases, and often would not provide a complete picture from both of their systems. The client asked, “How do we fix this and make it work for us?”
The Quad Nova team collaborated with the client to define common user roles and created user stories to ensure the system would be designed to benefit all users. Quad Nova's SQL experts analyzed the queries used by the traditional reports to identify inefficiencies and areas for potential performance gains. Quad Nova produced interactive wireframes of the potential web application and reviewed these with the client.
To prevent large numbers of connections from hitting their productional databases, we created a single job to pull revenue data from each system into a new MySQL database at defined intervals.
The MySQL database served as the source for a new web-based reporting application available from their corporate intranet to perform quick, efficient, near real-time reporting of the company's revenue alongside their goals.
Multiple views were created within the application to allow each user role to slice-and-dice their numbers in a meaningful way. Additional features include the ability to drill into summary views to reveal detail down to the transaction level and access to prior reporting periods to view historical data.
As data only changes at defined intervals, extensive caching of reporting data feeding the web application could be employed.
By reworking the SQL to take advantage of indexed fields, we were able to optimize the queries to decrease the processing time by 95%.
By taking advantage of caching, necessary database queries are reduced to a minimum enabling hundreds of users to view their revenue figures with ease.
On the day the application was deployed, numerous sales people responded to the announcement from the CAO, "I love it!".