I would like to do additional data visualization and reporting from both our Transactions and our Customers.
I’ve been doing this by exporting Transactions from squareup. com/dashboard/sales/transactions and importing into a Google Sheet, but the dataset is getting unmanageable in that way. Roughly 150,000 records for a year, which is about to triple as we open more stands.
I’d like to keep our Transactions (and Customers) in an outside db and be able to run queries and such on that data. I’m trying to find the best solution to structure this. It’d be nice to use the Square API to keep this outside solution up to date as well, so I don’t have to export and import often.
With our APIs and webhooks you can definitely keep up to date records in your external database. You’ll probably want to use the Orders, Payments, and Customers APIs. Also you can listen for webhook events for updates.
Thanks, I appreciate that, Bryan! That much I’d figured out.
I was more asking your team or the community for suggestions of how to structure that external database and a web app best.
Understanding we will have several hundred thousand records we’re working with, I’m asking for a tech stack folks would recommend.
Postgres vs Sql etc
We don’t really have a recommended structure for your database since the data is specific to your business needs. But maybe others in the community have suggestions based on what they’ve implemented.