Workflow Automation with Zapier: QuickBooks, Google Sheets, Housecall Pro

Carpet Retailer

This carpet retailer manages a dozen territories and associated sales reps throughout Texas with each requiring commission payments at the end of each month. For years the company would spend two days each month going through the process of calculating commissions and preparing the associated reporting. Not only was it taking too much time, it was cumbersome and prone to error.

Key Goals

This project had three primary goals:

1. Create a new Commission Calculator in Google Sheets that automatically retrieves data from Housecall Pro and QuickBooks Online which allows the accounting department to select a territory and immediately prepares a commission report with the required payout for a given period.
2. Automatically update the Commission Calculator whenever “job” data is added / edited in Housecall Pro or “invoices” and “invoice payments” are added / edited in QuickBooks Online.
3. Automatically update the invoice “class” in QuickBooks Online with data from the associated “scheduled job” in Housecall Pro.
4. Eliminate the need for any human intervention to calculate commissions.

Solutions

1. Conducted an initial assessment to document existing human workflows, data usage, formulas, and outputs.
2. Presented a roadmap outlining a workflow automation built in Zapier that would acquire data from Housecall Pro and QuickBooks Online then store the data in Google Sheets to power the Commission Calculator.
3. Created the workflow automation in Zapier containing all of the business logic for managing the Commission Calculator.
   1. Capture new “scheduled jobs” from Housecall Pro and record detail in Google Sheets.
   2. Capture new QBO invoices created by Housecall Pro integration and record detail in Google Sheets.
   3. Auto update the “class” in QBO with data from the Housecall Pro scheduled job (The native Housecall Pro integration with QBO does not set the class during invoice creation so automation was required to set the class equal to the territory after the invoice had already been created.)
   4. Capture new customer payments from QBO and record in Google Sheets.
   5. Update the Commission Calculator to reflect when an invoice was fully paid and ready for commission payout.
4. Designed a new Commission Calculator UI that allowed the accounting department to choose a Period and Territory which automatically presents the Commission Total and all the detail associated with the total.
5. Completely eliminated the need for human intervention to calculate commissions.

Project Artifacts

Commissions Calculator