In our previous project, we helped our client to establish inventory shrink visibility across its stores. By combining purchase orders, sales records, and inventory counts, we developed a reporting process that could identify the difference between expected and actual stock.That work added visibility into inventory shrink to the business which it didn't have before.

However, there was a problem. A new report was required every time and someone was always required to go through the process of gathering the data, verifying the records and computing the numbers. It did, but it was time consuming, and they needed a better way to deal with that.

So, we took the same reporting process, and began to try to automate it.

Bringing Different Data Sources Together

We had to work out how to get all the information we needed without having to have someone manually collect it.

The data was spread out on various platforms. Information on sales and products was available in Shopify, and purchase orders were saved as PDFs in Google Drive. We also had to have accurate counts on stock to know what we really had at the start and end of each month.

It wasn't easy to get everything together, it was a matter of connecting a few APIs.

In the case of Shopify, for instance, they didn't have an easy to access place to get purchase order details. To solve this, we connected PDF Parser in Google Cloud, which enabled us to pull out the product information that was relevant and the quantity of each product from those PDFs and put them into Google BigQuery.

On the otherhand, We got the sales and product data from Shopify's API. But, off-the-shelf inventory solutions required a different solution. We were not getting the level of reliability in the calculations from Shopify's inventoryAdjustmentHistory API.

Instead of attempting to perform a work around to inconsistent historical records, we created a custom script to take inventory snapshots at the end of each month. This provided a uniform documentation of "on-hand" stock and made shrink calculations a more reliable process.

We also did a couple of minor, but significant improvements en route. For instance: Shoptify location names were being used instead of their IDs in our queries. Using stable IDs allowed for an integration less dependent on naming conventions.

With these elements in place, we made BigQuery the central hub to compile the data and perform the calculations.

Entering into the Automation

With the data pipeline working, the next question was how the client's team would actually use it. We didn't want them to deal with APIs, SQL queries, or cloud services just to get an inventory shrink report. They were already familiar with Excel, so we decided to keep the reporting interface there.

So We designed the dashboard in Excel Online and added some new addition. The dashboard has now a “Refresh” button to click.

When client want to see the data for a specific month, he simply have to input start data and end date and specify the desired Store name and then have to click the “Refresh” button, by which an Office Script will trigger a JavaScript API hosted on Google Cloud to run. That API will execute our BigQuery queries, collect the latest available records based on the inputs, and return the processed results to Excel.

The data will be then loaded into a backend table which is also in Microsoft Excel online, will power the dashboard. For the authorization handlling needed to connect with Excel Online, we used Microsoft Azure.

There were quite a few moving parts behind that single button, but that was the point. The client didn't need to know what was happening in the background. They just needed a reliable and simple easier way to access what thier data insights.

What the Client Can Do With it

The dashboard is now enabled the client to dive into shrink report by pick a reporting period, store, or individual SKU. And all comes to in display automatically.

It displays the value of each products stock count on that selected period of month, both MSRP and COGS, the percentage of shrink and shrinkage amount. This enabled the client to not only see the number of missing products, but also what those discrepancies represented monetarily.

What was more important, however, was that they no longer needed to start the manual reporting process all over again if they needed to investigate inventory shrink. Data was gathered and processed and calculations were performed with one automated workflow. Every month end, the system collect a snapshot of the product’s availabiliity from thei data source, which can be later on compare with the actual calculation. All this now is being operated automatically.

Value Proposition

The biggest value wasn't simply knowing how many products were missing. It was the moment when they finally could explore the reasons for the numbers not matching. Our client had now acquired the first of their structured inventory shrink monitoring capabilities.

And here where we Autobizz placed the value for real.