Automating tasks is a great way to save employees time, frustration, and foster a more efficient work environment. For a large portion of enterprises, Google Workspace and Googles Sheets is an easy to use, cheap option for simple data analysis. However, far too often organizations find themselves manually manipulating data in Google Sheets to meet an urgent deadline. When urgent deadlines pile up, employees can find themselves spending hours a day maintaining simple spreadsheet reports when a more efficient solution is possible.
Google Sheets competitors, like Excel, have made great progress incorporating python functions and components into spreadsheet applications. However, for organizations built on Google Workspace the option to bring in Python is more technical.
Background:
In this post, we’ll examine how an small organization using Google Workspace can optimize operational workflows to cut an employee’s time and opportunity cost while maintaining reports in Google Sheets.
The solution outlined in this post is by no means best practice when it comes to cloud data architecture and infrastructure. However, not all businesses have the budget and person-power to implement and scale enterprise cloud solutions. This process is simply an alternative to aid smaller businesses interested in optimizing workflows and gaining efficiencies.
Context:
Company123 – a small business – is running a series of advertisements across Facebook and Instagram in preparation for the summer holidays. Their technical staff includes 2 IT professionals working to provision technical equipment and monitor device security across the organization. The rest of their staff has extensive experience with Excel and Google Sheets, however there are no engineering or data professionals within the organization.
Company123 wants to aggregate their marketing campaign performance data across the different platforms they are advertising on. Currently, their employees are spending 1-2 hours a day going to each platform, downloading the performance report, standardizing this report, and updating a Google Sheet with the performance from the last day.
Solution:
The process of extracting information from an external system and loading this information into a data lake for analytics is common practice for many organizations. However Company123 doesn’t have the expertise or budget to facilitate a cloud solution implementation. As a result of these limitations, Company123 is looking to automate portions of their employee workflow where possible.
The following solution utilizes: Google Cloud Console, Google Sheets API, Python, and Google Sheets.
Current Process
Updated Process
Between data retrieval, consolidation, entry, and validation, the deprecated process takes hours of manual effort a day. Once the updated process is implemented, the consolidation, transformation, and entry is automated. At that point, an employee’s only daily task is to download the performance files into a specific directory and review the dashboard.
Data quality and validation checks can be applied in this process, but are not included in this demo.
To begin, an employee begins their manual tasks per usual. They navigate to each of the ad platforms and download the performance reports to specific directory. Then, the python function can be executed automatically with a directory polling package like, watchdog.
One alternative to directory polling could be to build a process which load the files into cloud storage and build a Google Cloud Function (which might lead down a path of full scale cloud architecture). If you store the files in cloud storage, why not build a pipeline from the external data source directly to cloud storage? Why not model directly in BigQuery? Why not build the Google Sheets dashboard in Looker?
For this use case, we’ll assume the answer to all of these questions is cost.
This semi-automated workflow mimics features and structure of data engineering in a cloud platform for a smaller business. For organizations looking to obtain similar efficiencies of cloud architecture, without breaking the bank, automation processes like this one can serve as a functional and efficient short term solution.
Key Benefits:
- Automating sections of employee workflow saving time and effort.
- Limiting manual data entry, reducing errors.
Downsides:
- This is not a scalable solution and cannot be productionized.
- Prescribed solutions require clearly defined inputs and objectives.
If you’re interested in discussing larger enterprise cloud solutions, like data warehouses and data lakes, let’s chat!
The entire script is listed below: