Skipping Excel 3

In this third and final post of the Skipping Excel series, we’ll connect all the dots and use Zapier to complete our automation.

In case you are just joining the series … I’ve been documenting my journey for getting my e-commerce sales into QuickBooks Online. In an ideal situation, there would be a direct built-in integration between my e-commerce system and QuickBooks Online. If there was, I’d use it and be done. But, there isn’t. So, I had to use some intermediate steps.

In the first post, Skipping Excel 1, I demonstrated how to use Power Query to take it down from about 30 minutes per day to about 5 minutes per day. In Skipping Excel 2, I demonstrated how to get the e-commerce confirmation emails parsed by Mailparser. In this post, we’ll bring it home and I’ll show you how to use Zapier to get the data from Mailparser into QuickBooks Online … completely hands-free!

Video

Summary

Let’s take a look at the data flow diagram once again to refresh our memory:

So far, we’ve got our e-commerce system sending a copy of the email confirmation to Mailparser. Mailparser then applies a series of rules and filters to extract the specific data fields we need, such as name, address, product, amount, and so on. Now we need to convince Mailparser to send these data fields to Zapier, and then convince Zapier to send them to QuickBooks Online.

Zapier

After creating your Zapier account, you will want to create connections to the apps you use. In this case, we’ll start by connecting Mailparser. Go to My Apps, search for Mailparser, and click Connect.

Once connected, you can use it in a zap.

While we are here, we may as well connect QuickBooks Online using the same basic process.

Once we’ve connected the apps we want to talk to, it is time to create a new zap.

New Zap

After setting up your Zapier account, you can create a new zap by clicking the Create Zap button.

The first step will be to identify the Trigger … which is the action that happens within an app to start the zap. In this case, the trigger is a new parsed email in Mailparser. So, we simply select Mailparser as the Trigger and New Email Parsed as the event:

You select your connection account and then you’ll need to identify the Mailparser inbox.

Zapier will prompt you to test the trigger, and it will look over to Mailparser to grab a sample email from the Mailparser inbox.

Once the test is complete, you’ll be able to use the parsed email data fields in the zap … and send them to another Zapier-connected app. In our case, this is QuickBooks Online.

So, we select QuickBooks Online and we opt to have it create a new invoice (but there are many other options available):

Once you click Continue, you’ll be able to assign each parsed data field to a QuickBooks Online field and then run a test.

Once the zap is set up as desired, you click Publish to make it active.

Now, anytime a new order is placed in the e-commerce system, it immediately sends a copy of the confirmation email to Mailparser. Mailparser immediately does it’s thing and extracts the data fields. Zapier immediately picks up the fields and creates a new Invoice in QuickBooks accordingly.

It is important to note that not only does this happen automatically without human intervention, it also happens immediately in near real-time. Since it happens immediately, we can create more impactful zaps such as immediately sending a welcome email to the new customer. Or immediately tagging the customer as Paid in your CRM system in order to suppress additional marketing emails. Or, immediately creating a calendar event or adding them to a new system or database. And on and on. In the old process, where I would do daily batches, these real-time actions weren’t really possible.

Conclusion

And that is how I took a 30 minute per day process down to 15, then down to 5, and then down to 0 🙂

Most likely, your company doesn’t use the same set of apps that I do. However, Zapier supports a huge number of apps, and you can set up automations between them. As you may expect, this creates a lot of intriguing options.

Additionally, there are other services besides Zapier that can assist with this type of thing, such as Microsoft Flow, Power Automate, CData, Workato, and others. Therefore, you may want to investigate these possibilities if you routinely transfer data between systems. Many of your manual tasks may be automatable!