One of our client was accessing his financial dashboard developed in Google Sheet to track his Ads and sells performance of his Meta Ads and Shopify stores. The dashboard did work properly, but there was becoming to be a frustrating experience to obtain the latest numbers.

A Script was being used in there to retrieve data from each platform, manipulate it, and feed it straight into the dashboard. The more data there was, the slower this process was. In some instances, the client would have to wait in front of the dashboard for 5-10 minutes or more to see his data properly.

Having to wait a few days to get financial advice was an issue for someone who commonly needs it for business decisions. The client had to wait for the processing to be completed before he could obtain the information. And it was the main headache for him to be solved asap.

So, we needed to find a better way to handle the data without making the client wait every time he wanted to see the numbers.

Why We Changed the Data Processing Approach

Our first consideration was the existing data flow.

The Google Apps Script was responsible for almost everything: requesting data from Shopify and Meta Ads, handling the responses, processing the information, and delivering the final results to the dashboard. This meant the entire process had to run again whenever fresh data was requested.

Instead of making the same script do all the work for each time, we decided to separate the data collection process from data delivery. That meant introducing a central storage and processing layer where data could be collected and maintained before it could be presented into the dashboard.

How We Solved It: Introducing BigQuery into the Workflow

We re-designed the workflow so that data no longer needed to travel directly from Shopify and Meta Ads to Google Sheets every time the client refresh the dashboard. Rather, we’ve made BigQuery as the hub for storing the gathered data. The new workflow follows a much simpler approach:

Collecting data from the original sources

The system fetches the necessary financial and business data from the client's Shopify stores and Meta Ads accounts via API. This data collection process will be automatically activated at regular intervals by scheduler.

Storing and maintaining data in BigQuery

The collected data needed to be processed as per the dashboard needed to be shown. So we calculated the specific formulas directly into the BigQuery so that while fetching the data from the BigQuery, the system do not need extra times for it. Now all the data are being collected and processed one after another on a single blow.

Providing data on dashboard

Now whenever needed, client can just input the specific date from the dashboard to fetch the data and the data will come directly from BigQuery to the dashboard. It no longer has to await gathering API responses and process the data from scratch.

This separation made a significant difference. Data collection now perfoms independently in the background, while the dashboard can focus on retrieving and displaying the information the client needed.

The Result

The biggest improvement was the time saved when accessing the dashboard.

The client used to wait for 5-10 minutes (or longer) for the script to complete the data collection and processing. With the help of BigQuery, the dashboard can now get the needed information within a few seconds. However, speed wasn't the only change.

The new workflow also resulted in a more organised data management. The system had a centralised information base which could be automatically updated, rather than the need to keep sending the same information to several platforms with each update of the dashboard. Most significantly, the dashboard is much more functional in real use.

Value Proposition

This project was one such instance where the dashboard worked flawlessly but the reporting system was a bit slow. Occasionally, it's not about the way information is presented, but the way it is gathered, processed, and presented.

We re-structured the whole workflow, completely altering how the client's dashboard was going to manage the data. We completely decreased the heavy lifting ofa single script, isolated data gathering from reporting and provided financial information much sooner.