Google CRM script automation

A case study about saving time and operational cost by building a lightweight, automated CRM with Google Sheets and Google App Scripts for Cohort 6 Productions.

Cohort 6 Production is a web development company focuses on helping SMEs to increase revenue and profit by offering web/app development services. This case was created as part of the automation project where I built a cost-effective, lightweight CRM for Cohort 6 Product to streamline their sales operation.  

I designed the process and implemented the automation
  • 95% reduction of time spend on task per staff: Reduced time spend from 10 minutes → 30 seconds
  • Error free: 100% elimination of human operation error
  • Cost-effective: Low cost, tailor-made, highly standardized, full automation

What was the problem?

Pitching clients and managing project quotations are time-consuming and onerous, especially when the team is focusing on value-adding business activities, C6P wanted a CRM with automated document management functionality that helps them streamline their sales process.

  • Either building bespoke systems or existing alternatives are expensive
  • Need the solution to be flexible yet robust
  • Do not want to be tied down by subscriptions

Going through the process

As to keep the system simple, here are the business requirements.

  • Content management system with access permission control
  • Simple dashboard, easy to use
  • Use pre-build templates as a replica for all operations

In order to identify what I need to build, I created a flow diagram of the business process to identify where in the process the user needs to interact with the system, and what kind of results the user can expect at the end of the operation.

Business operation flow

What got shipped

Results and takeaways

The project was a success, not only it helps Cohort 6 Productions get their feet off the ground, but it is also a great way to introduce Google Script to the business, where business can leverage the power of Google's API to solve all kinds of business problems without investing into bespoke systems that build from scratch.

Some key takeaways from this project are:

  • Code smarter — Use recursive for reading folders and generate templates for scalable growth without limited by the codebase.
  • Allow user confirmation and self-correction —  By allowing the user to confirm their input before committing, not only it reduces the frustration when mistakes are made, it also reduces the chance where the user has to redo the procedure.