The Duck Store: Buyer Reports
Monitoring the Bottom Line.
The Duck Store buyers needed a way to view the performance of a specific product class using key performance indicators (KPIs). After collaborating with the project's stakeholders, I developed an application that retrieved a series of data sets, parsed and stored them, and finally displayed the KPIs for product classes.
Since 1920, the University of Oregon Bookstore/The Duck Store has served the book and supply needs of the University of Oregon.
With locations in Eugene and Portland, The Duck Store advances and fosters the educational goals of the University of Oregon by creating an enduring sense of community among all Ducks — past, present and future.
Due to the flexibility of pulling data from The Duck Store’s Enterprise Resource Planning (ERP) platform, there wasn’t a single source of truth for determining KPIs when it came to product data. Further, some of the data for KPIs, such as Inventory at Cost and Inventory at Retail, needed to be stored on a daily basis. (As it stood before this project, those data points weren’t reported correctly.)
After collaborating with the stakeholders of the project, I developed a solution that not only allowed buyers to dynamically generate reports for a given month, but the application also recorded the daily data points.
This new application led to an immense amount of time each month–5-10 hours per buyer–when creating reports. Furthermore, this dashboard improved the confidence of our buying team and senior management’s when it came to making buying decisions. This is because our buyers had an accurate and reliable source of data using formulas everyone agreed upon.
- The Duck Store
- Application Architecture
- Information Architecture
For every report on a product class hierarchy’s performance (for example, men’s tee shirts), there were specific KPIs that need to be calculated. These were: Inventory at Cost, Inventory at Retail, Net Sales, Receiving at Retail, Receiving at Cost, Discounts, Initial Markup, and Maintained Markup.
In order to calculate the KPIs, different queries (saved searches in NetSuite terms) would need to be made to get the raw data. Then, one would have to apply different formulas, ultimately resulting in a ton of time ( 5–10 hours) spent fiddling with Excel and multiple CSV downloads from the ERP. Monthly.
What I needed to design was a system that could not only calculate the required KPIs but also needed to handle dynamic input for each report. For example, I needed to handle a scenario where one user could request a report on the entire accessories class for the fiscal year of 2021 while another could request one for kid’s hats for the fiscal year of 2020.
On top of that, the stakeholders requested email notifications for when the reports were finished generating.
First, I created a user interface (UI) for the stakeholders to generate a report with the specific class and the required class hierarchy. Upon submission, the form contents are sent via an HTTPS request to one of the custom application’s API endpoints. From there, a report record would be made in the database (MongoDB), and a response would be sent back to the UI indicating that an email would be sent once the report was finished processing.
Since the application could receive an arbitrary amount of reports requested at any time—and there could only be a certain amount of queries (saved searches) executing at a given time in the ERP—I designed a queueing system. Specifically, five different queues for different stages of processing.
This queue design, paired with multiple cron jobs, allowed my application to process multiple queues at regular intervals. The stages involved fetching CSVs from the ERP and storing it on the record; processing the data and updating KPI fields; deleting CSV records from the ERP; creating a custom record with the report’s URL in the ERP; and finally emailing the user the report has been completed.
Finally, in the email, there was a URL to a UI I designed using React.js. The UI dynamically generated a dashboard view for each buyer.