Case Study: How we reduced Single Use Alternatives monthly reporting from days to minutes

Before delving into the technical details, let's set the stage. For Single Use Alternatives (SUA), the monthly reporting process used to be quite a cumbersome task. It involved manually importing multiple spreadsheets and CSV files, and then painstakingly compiling and analysing the data. This process was not only time-consuming but also lacked the flexibility of real-time adjustments and reporting. The whole ordeal would take days, which was far from efficient. This was ripe for a level of automation and using more powerful analytical tools.

Firstly, we tackled the issue of automating the order processing from the SUA WooCommerce website. For this, we used the Serverless Stack (SST), a modern framework that allows you to build serverless applications quickly. The SST application we built serves two primary functions. One, it pulls all the new orders from the WooCommerce website and automates the data entry. Two, it allows for the uploading of CSV invoices. These uploaded invoices provide an additional data layer, offering more comprehensive information for analysis.

Once the data is gathered through SST, the next step is storing it in a secure and easily accessible place. This is where Google BigQuery comes into play. All the WooCommerce and invoice data gathered through SST is funnelled into our data warehouse in BigQuery. The benefits of using BigQuery are multifold; it offers quick query execution and is highly scalable, making it an ideal choice for businesses of any size.

But what about real-time adjustments? Often, raw data isn't enough; adjustments based on real-world events and costs are necessary for accurate reporting. To tackle this, we linked several Google Sheets to our BigQuery data warehouse. These Google Sheets are accessible by SUA team members and can be edited in real-time. Any changes made in these sheets are instantly reflected in BigQuery, ensuring that the data is always up to date.

Finally, for reporting, we turned to LookerStudio. The LookerStudio dashboard offers a visual representation of all the data stored in BigQuery. But it's not just a superficial overview; the dashboard provides high-level reporting features, such as tracking overall KPIs and financial health of the business. Moreover, it has the ability to drill down into specific data points, allowing for an in-depth analysis to resolve financial issues.

To sum it up, by harnessing the power of modern technologies like SST, Google BigQuery, Google Sheets, and LookerStudio, we've managed to transform SUA's monthly reporting process. What used to take days of manual effort is now accomplished in mere minutes. Plus, we've added layers of depth to the financial analysis by offering real-time adjustments and detailed reporting capabilities. This case study is not just a testament to the efficiency gains from automation but also showcases how existing, off-the-shelf, integrated technology solutions can bring about a transformative change in business operations.