Problem
A client has approached me with a seemingly simple problem – he wanted to connect GoFlow (on order management software) with QuickBooks (an accounting software). While a native integration between the systems already exists, it offers almost no customization and was simply not a fit for my client.
I was tasked to:
- Build an API connection to GoFlow via Make without a native app in Make.
- Create a new invoice in QuickBooks from a new order in GoFlow.
- Develop highly customized calculation of commissions based on order values coming from various stores connected to GoFlow.
Solution
Originally, I was planning to simply build a direct connection between GoFlow and QuickBooks with Make but after a few test runs, I realized I would be consuming several millions operations per month in Make which was not really a viable option. The client is processing tens of thousands invoices every month and it would take almost 100 operations to get one invoice from GoFlow to QuickBooks with all the custom calculations.
So the direct connection was a no-go – what then? I resorted to use BigQuery as a middleman to store all raw order data from GoFlow. In BigQuery, I run all crazy calculations designed in SQL. Then I simply get the “preprocessed” invoice line item data from BigQuery with Make and use this data to create (or update) new (or existing) invoices in QuickBooks with a low number of consumed operations for each invoice created (or updated) – roughly ~10-12 operations per invoice in Make).
I also keep track of each operation in QuickBooks by storing various metadata (IDs, invoice numbers, purchase order numbers, totals) about each invoice in a log table (also in BigQuery) which allows me to construct a very helpful “change history”.
Scenarios
The project is running via 3 main scenarios in Make plus there is a ton of SQL sitting in various BigQuery views.
The first scenario is downloading reference data both from GoFlow and from QuickBooks. By reference data, I mean various product IDs, product names, and product classes from both systems. I use this data for mapping product names coming from GoFlow to product IDs in Quickbooks which must be provided when creating or updating an invoice.
The second scenario is ingesting GoFlow orders to BigQuery. In case you are wondering what the route with Resume directive is doing – it’s handling situations where GoFlow API returns rate limit errors – in that case, the scenario waits and resumes where it previously failed so no data is lost. The results from GoFlow are paginated and if you try to retrieve too many pages quickly, you will face the rate limit error which you need to handle.
The third scenario is the crème de la crème – it takes the precalculated data from BigQuery and creates (or updates) invoices in QuickBooks. At the end of the scenario, invoice metadata is pushed into a BigQuery table.
Conclusion
This project was one of the most complicated challenges I’ve ever solved with Make. The paradox is the Make part was the easier one – the main challenge was to get all the calculations right in BigQuery where advanced SQL knowledge was needed.
Do you want to replicate this for your business yourself?
GO TO MAKE
Or looking for someone to build your integrations?
HIRE ME