I was slightly surprised that Intuit didn’t have a simple ‘out of the box’ solution for getting sales by zip code. After a lot of research and playing around with the reports in quickbooks I found a way to get the data from Quickbooks to an Excel spreadsheet for further processing. I wanted to use the income by zip code report to create a Google map in order to visually see where most of the sales were from and the weak areas.
Before we begin:
To start out, it’s very important to be entering your customer information correctly so that you have the data to work with in the first place. The proper way is to open up the edit customer window either through clicking the pencil (for existing customers) or using the ‘Set up’ customer option. Doing this allows you to get all of the address information in the right ‘containers’ for use later on.
The report that I use:
Go to Reports, Customers and Receivables, Transaction list by Customer. The filter it as follows:
Click the Customize Report tab. Under the display tab click the date range and make sure Name, Amount and Name Zip are checked.
Go to Filters find TransactionType choose Payment, and Detail Level – payment only.
Now that you have the report all set up we need to export it to Excel to refine it. Click the Excel button at the top of the report and create a new workbook.
Once in Excel we need to remove unwanted columns. Delete any empty columns, checked columns. The only columns that you want are name, Name Zip, and amount. If you need to title the name column, go ahead and do it now.
Third Step – Excel Pivot Table
Drag to select the Name Zip, and Amount columns then go to Insert, Pivot Table in Excel. You want to create a new worksheet or workbook.
In the PivotTable Field list, check off the two columns (Name Zip, and Amount). Drag the Amount to the value box, click the arrow on the amount and choose ‘Value Field Settings’, Sum, and then OK.
Voila! You now have a spreadsheet with sales by zip code! Not the simplest process but it works. You can now do further processing to make the data more useful like mapping it, or seeing which zip codes are most/least profitable. Post on doing this coming soon!