It is no secret that I’m a huge fan of automation. I’ve previously discussed how to create items on your To Do list from form entries and how to automatically track all of your deliveries in an iPhone app. So when I decided to move my accounting to QuickBooks and employ my mother-in-law as my accountant to maintain my books, I sought to overhaul how my books were managed and to make the process as easy as possible for my new accountant.
This is where Zapier comes in. I had long known about Zapier since it was a fledgling service, aiming to join up the mountain of online services which provide and/or receive information but don’t necessarily speak directly to one another. For example, you might want to create a new tweet every time you posted a new image on Instagram or create a new message in Slack whenever you received an email matching a specific set of criteria, such as a new statement notification.
I always had the perception that Zapier was a bit messy and clunky and so had never particularly desired to use it but with my new needs, I sought to find a way to create these processes and Zapier was the first service that came to mind.
How glad I am that I gave it a chance. It has come a long way and is now very well thought out and very easy to use. Zapier allows you to create new Zaps (their term for workflows or processes) by asking you to define your trigger and the subsequent actions from their library of over 500 apps.
In order to make the most of Zapier, you need to first sit down and consider what information you’re handling (triggers) and what you need to do with it (actions). To make your Zaps even more efficient or powerful, Zapier now supports multiple steps, so a single trigger can result in multiple actions.
With my setup, I wanted to enter as much information into QuickBooks as was possible and provide very concise instructions to my accountant in a private Slack channel with any further changes that were required.
Adding new clients to Quickbooks
I use two primary triggers: Gravity Forms and Stripe. When a new user signs up for my website maintenance service I automatically create a new customer in QuickBooks. This is possible because my sign up form is built using Gravity Forms. As such, without lifting a finger I can automatically send all of the customer information to QuickBooks.
Adding new transactions to Quickbooks
My even bigger time-saver comes from integrating Zapier with Stripe. Stripe is a payment processor built for the modern web and it is quite simply the best option available today for online transactions. With Zapier now also supporting the ability to run code in your Zaps, meaning that you can perform calculations and transformations on your data to get it into the correct format, automating the entry of new Stripe charges into QuickBooks is incredibly easy.
Firstly, I use a filter to only proceed with transactions where the status is “paid” or “succeeded” (a distinction between individual and recurring transactions).
The data from Stripe is very raw and programmatic. For example, date and times are provided in UNIX time stamps, which is actually the best way to handle time since it is unambiguous and doesn’t care about time zones. But this means the time stamps need to be converted to have them in a usable format for QuickBooks.
Furthermore, the amounts are provided in the number of cents rather than the number of dollars, so to provide them in the format appropriate for bookkeeping, they need to be divided by 100. As such, some code like the following (written in Python) takes some of these inputs and converts them into the format needed for QuickBooks and Slack:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from datetime import datetime | |
| stripe_date = datetime.fromtimestamp( int( input['date'] ) ) | |
| transfer = datetime.fromtimestamp( int( input['date'] ) + 172800 ) | |
| if ' ' in input['full_name']: | |
| first, last = input['full_name'].split(' ', 1) | |
| else: | |
| first, last = input['full_name'], None # fallback | |
| return { | |
| 'amount': "%.2f" % round( float(input['amount']) / 100, 2), | |
| 'fee': "%.2f" % round( float(input['fee']) / 100, 2), | |
| 'net': "%.2f" % round( float(input['net']) / 100, 2), | |
| 'transaction_date': stripe_date.strftime('%m/%d/%y'), | |
| 'transfer_date': transfer.strftime('%m/%d/%y'), | |
| 'first_name': first, | |
| 'last_name': last | |
| } |
From there, I can check to see whether the customer exists in QuickBooks (and create the customer if not) and then create a new sales receipt, adding almost all of the data automatically. Unfortunately, not every single action or data point is supported. For example, you cannot specify the reference number when creating a sales receipt in QuickBooks.
Using Slack to fill in the gaps
However, not to fear: I use yet another action to create a message in my #accounting Slack channel. With it, I provide a link directly to the sales receipt in question and provide the information which needs to be modified. I also provide links to create a new expense for the Stripe transaction (since “Create a new expense” is not a supported QuickBooks action) and to create a new transfer from the Stripe account to my business checking account (for similar reasons). I reviewed these QuickBooks entry forms and provided all of the information in the order that it is needed to facilitate simple data entry.
My accountant can then add a “checkmark reaction” on the Slack message to indicate that the transaction has been handled in QuickBooks.
This is just one way in which I’m using Zapier to automate my business workflows but it certainly won’t be the last. I’ll be looking for every opportunity to convert repeatable, data-driven workflows into automated processes with Zapier.
