Skipping Excel

How to Take a Process from 30 to 0 Minutes

Excel is used for all sorts of things, including budgeting, reporting, reconciling bank statements and more. Sometimes, it’s used as a bridge between two applications … that is, to export, clean and import data between two different systems. As an example, maybe you export transactions from an e-commerce system, open it in Excel, clean up the data a bit and then upload it to an accounting system, such as QuickBooks. This type of process is so common it even has its own acronym: ETL (Extract, Transform and Load). When you use Excel like this, as a way to transport data from one system to another, you may be able to skip Excel all together. Wait, what?

Skip Excel? Yep

Let me illustrate by sharing a real-life story. My company uses an e-commerce system to take online orders. The orders need to be entered into my accounting system, QuickBooks Online. So, my first approach was manual, and it took about 30 minutes. Here is the basic idea.

30-minute Approach

I would open the e-commerce system in one browser window, open QuickBooks Online in another, and then I would copy the name field from the e-commerce system and paste it into QuickBooks Online. Then, I would copy/paste the address and continue like this through all fields, save the transaction—and repeat. All told, this would take roughly 30 minutes. I quickly grew tired of this manual approach, so I moved on to my next approach.

15-minute Approach

Here, I would export a batch of sales transactions from my e-commerce system into Excel. Then I would manually clean the data, including deleting unneeded columns, adding calculated columns and filtering out certain rows. Once the data was ready for import, I used Transaction Pro Importer to import the transactions in bulk. This was a huge improvement, and overall saved me 15 minutes. I was so happy to have cut the time in half. But then I discovered I could do it even faster by using a Get & Transform query in Excel. Check it out.

5-minute Approach

With this approach, I would still export the transactions into Excel. But, instead of cleaning the data manually, I used a Get & Transform query. With the click of a Refresh button, the query performed the data preparation instantly. Then, I would upload the results to QuickBooks Online with Transaction Pro Importer, and life was good. The 30-minute process was now down to five minutes— and I was thrilled to have saved so much time. But then, I improved the process even further.

0 Minutes!

There are many services that help move data from one system to another, including Zapier (www.zapier.com). Unfortunately, Zapier doesn’t support my e-commerce system, which is a bummer. Then I found the missing link I needed: Email parsing. My e-commerce system sends an email confirmation for each online sale. So, I had each sale confirmation email forwarded to mailparser (mailparser.io). This service is amazing. It takes an inbound email, and then parses it—meaning it processes it using rules you specify to retrieve the desired values, such as name, address, product id and price. Since Zapier integrates with mailparser.io, I was set.

Here is the process now. An online order is placed in my e-commerce system. An email confirmation is sent to the customer and to mailparser.io. Mailparser.io retrieves the needed customer data, and makes it available to Zapier, which forwards the information to QuickBooks Online and records the sales receipt, or, sends an invoice if needed. As a bonus, Zapier also forwards this information to my email platform for an onboarding email sequence. This all happens within seconds of the order being placed. And, my manual steps are gone! The process that started at 30 minutes is now down to zero minutes. Sweet.

Conclusion

Your business probably doesn’t use the same combination of apps as mine. But, there are tons of apps supported by Zapier, and you can set up automations between them. As you can imagine, this opens up many interesting possibilities. Plus, there are other services that help with this sort of thing as well, including Microsoft Flow, CData, Workato and more. So, if you frequently find yourself moving data from one system to another, you’ll definitely want to check out your options. You may be able to automate your task, getting it down to zero minutes!