A provider of bedding plants for vegetable and herb gardens contacted Imaginet because they were starting to use Go Spot Check and wanted to bring its data into their data warehouse. Go Spot Check is a third-party cloud application that, like many supplementary tools, offers reporting in-app but doesn’t have a reliable or repeatable export method for the raw data. These types of tools can be very useful to organizations but can cause challenges when attempting to retrieve and integrate the data they collect. In this case, our client was in need of an inventory reporting tool.

They specifically mentioned their desire to use the Go Spot Check data in collaboration with the data they already had: GPS and sales data. As an organization, they employ drivers that deliver bedding plants to garden centers at large retailers like Home Depot and Lowe’s and want to ensure these stores are receiving inventory at appropriate times (not too soon, not too late) so the plants always look their best. Additionally, they wanted to optimize their stock, so inventory is provided strategically and monitored so stores aren’t missed or ignored.  

Previous Work and Updates 

We had previously built a system for their truck GPS trackers which they were also looking to use to drive additional value. When drivers were visiting a specific store location, the GPS only recorded that the drivers were there and how long they were there, but they needed more information about inventory levels. To gather inventory, the Go Spot Check mobile app was introduced to provide additional data such as inventory organization, upcoming store promos, plant care opportunities, and store visit history. Now, when drivers go to a store, they can log in to the app and submit a report for the store, even including a photo of the plant inventory. These reports can be cross-referenced with the existing GPS data to get a better picture of overall inventories and ensure stores are visited within their ideal thresholds. The drivers can then make scheduling and delivery adjustments based on this data. 

What We Did  

We wrote an Azure Function app to extract data from the platform using the Go Spot Check REST API. The Function app was written in Python and calls the Go Spot Check API to generate CSV files on a daily schedule.  We extracted the data relevant to the desired use cases such as store locations, users, missions and mission responses (the reports completed at each location). The CSV files were ingested and transformed by Azure Data Factory into the data warehouse where it was correlated with Sales and truck GPS data in their Power BI data model.  

Typically, when working with new data from a new system, the question is how to best align it with existing data. The more we can correlate data from multiple systems, the higher the value that data becomes. In this case, we can correlate the inventory data to the correct store and compare it to the sales at that store. If there are more sales, the driver should visit that store more often.  

Why Our Solution Was Helpful 

The insights enabled by the Go Spot Check data integration will allow the organization to create more effective trucking routes and improve the inventory levels in stores and quality of their product. The Azure Function app that extracts the GPS data was written in C#, but we chose to write the Function app for the Go Spot Check extractor in python, which is a more broadly known language among data engineers, so maintenance and enhancements of the python app will be faster and simpler for our data engineering team than the GPS function app. 

The client was also impressed with how quickly we were able to deliver. Our solution went live in November, which was about 4-6 weeks earlier than the January deadline. A key benefit of working with Imaginet is our agility. We delivered the data quickly which allowed us to tweak it and perfect it before the rollout date. One of the main benefits of being agile is obtaining value early and then adapting to change. 

Conclusion 

Integrating the inventory data from Go Spot Check into the data warehouse has allowed our client to improve the monitoring of stores in their peak selling seasons and optimize their sales and delivery processes. They will be able to find additional insights and predict when inventory shortages might occur and plan their growing schedules better. Due to the inventory reporting tool, they will now be able to know which stores need to be visited more often.  

Our team has been working hard these last few months, and we are excited to share our success with you. Don’t forget to subscribe to our newsletter to stay updated on our most recent Microsoft 365 projects. Fill out the form at the bottom of the page if your data is unreliable, you want to upgrade your data factory, or your data is difficult to manage.