Back Up Client History From QuickBooks Online To Airtable

It comes up a lot. A client wants to upgrade from QuickBooks Desktop to QBO. My suggestion is always to start a new, clean company and NOT migrate the data. Some clients like the idea, but the ones who hesitate are usually most hesitant because they want a way to access customer history. 

It makes sense. You want to be able to look up what a customer bought and when. The reasons for wanting this are obvious.

What if I could show you a really useful way to archive this information such that it is organized, categorized, and easily referenced, without having to bring that accounting spaghetti you have in your old QBDT file along?

QuickBooks Online (like Desktop) is a database. All you really need then is a good database where you can archive all of the details of every customer transaction: invoices, sales receipts, credit memos, etc…

Run a report in QuickBooks Online and customize it.

Run a Sales By Customer Detail Report.

Then add the Customer and Account Columns.

Now we’ll customize the report to reorder the columns and remove the grouping.

The new columns will be at the very bottom. Drag them up to the position where you want them. Here’s how I like to order them:

Then change your date range to “All Dates” and change your Group By to “None”.

Then save the customization so you don’t have to recreate this if you need it again. 

Now we’re going to set this up in Airtable.

For the purposes of this article, I am going to assume you have the basics of Airtable down. In the video, you can see how to build this from scratch.

The process from here is pretty simple. 

Export the report from QuickBooks to Excel.

Set up your columns in Airtable.

Then copy and paste the data into Airtable. 

You’re going to set up most of your columns as “Single Line Text”, so that it’s easy to paste in the data. Then you can change columns to different types as appropriate.

The first column is a special column. My suggestion is to set it up as an Autonumber column and call it “Trx No.”

Leave the Balance Column out. You don’t need it. 

Here’s a link to my database:

https://airtable.com/shraqh71Mu4lB6xaZ

You’ll note that I’ve converted a few of the columns from Text to Single Select. To do this, you double click the column and change the type to Single Select. 

Then note the “Views” on the left. These are like reports you can run. 

The “By Customer” view is configured to be Grouped by customer. The “By Customer and Product/Service” view has a subgrouping based on the Product/Service. You can start to see how you can slice and dice these different ways.

When I show this to clients and review it with them, they can instantly see how they not only won’t lose their data, but how they will arguably have a better way of analyzing and retrieving said data. 

In the video, I will show you a number of tips and tricks for how you can make this way of backing up your client history with Airtable even more dynamic.