How Webhooks and BigQuery Saved a Client's Procurement Express Reporting

Why purchase orders matter to a growing toy brand

Our client is a family-owned toy brand that sells to retailers and directly to customers, and releases new collections regularly. Behind every launch is a chain of purchase orders with shipping dates, payment terms and delivery deadlines that all have to line up. A late shipment can mean a missed launch or an upset retailer, so the team needs accurate, current purchase order data from Procurement Express at all times.

What we built: a central sheet and a daily dashboard

We built the client a reporting pipeline around a central Excel sheet, with one row for every purchase order. A summary dashboard on top of it gives the operations team their day-to-day view: what is on track, what is late, and which missed deadlines need to be raised with a vendor. The same data drives automatic supplier reminders and daily notices to the customer team about who needs an update.

The sheet is shared with the team, so the update respects their work:

• New orders are added at the bottom, in order.

• Existing orders are updated only where something changed.

• The team's own columns and formulas are never touched.

The problem: a full refresh cost 1,000+ API calls

An API call is one request from a piece of software to another for information. To keep the central sheet current, a job ran every day and worked through every purchase order in Procurement Express.

Procurement Express's list of purchase orders only returns a short summary of each order. The details the dashboard needs (line items, ship-by dates, payment terms, supplier information) have to be requested one order at a time. So every full refresh meant one API call for the list, then one more for every single purchase order, whether it had changed or not. That added up to more than 1,000 API calls.

Our daily job was automated, but it was making 1,000+ API calls to find out what had changed, when it could have been told. Automating that only repeated the waste every day.

Why that capped us at one refresh a day

Like most online platforms, Procurement Express limits how many requests it will answer in a short time. It doesn't publish the limit, so we learned where it sits by running into it. Go over, and it replies "too many requests" and makes you wait. That left us with a trade-off nobody wanted:

• Refresh once a day, and the dashboard is always behind. A date that slips in the afternoon isn't on the dashboard until tomorrow.

• Refresh more often, and we hit the limit. Procurement Express turns requests away, orders get skipped or left out of date, and we fall further behind, not closer to current.

It also got worse as the client grew. Every new purchase order added another API call to every refresh, so the room to run more often shrank month after month. Longer waits between retries wouldn't have fixed it. The job was making the same API calls over and over, and most of the answers hadn't changed. We needed to stop asking.

The fix: webhooks, so the updates come to you

Imagine phoning a supplier every few hours to ask whether your shipment has left. Most of the time the answer is "no change". It's far better for them to call you the moment something happens.

A webhook works the same way. Procurement Express sends an automatic notification, carrying the complete order, whenever a purchase order is created, approved, delivered, paid or cancelled. Instead of 1,000+ API calls to learn what changed, we receive one update per change. If 20 orders change today, we receive 20 updates and make no API calls at all.

Each update is saved into BigQuery, which keeps one current record for every purchase order. The central sheet, the dashboard, the supplier reminders and the customer notices all read from there. The sheet is updated the same way as before; what changed is that the data now comes from our own database, so the update can run as often as the team wants without using up Procurement Express's allowance.

Why BigQuery:

• Speed. Reading every purchase order takes moments, not minutes.

• No waiting on someone else. Reports never queue behind another platform's limits.

• Everything in one place. Purchase orders sit next to online store orders, so comparing purchasing with demand is straightforward.

We also made it dependable. Each update is acknowledged instantly and saved right after, test notifications are ignored, and a blank field in an update never erases information a team member entered by hand.

Future-proofing: growth no longer makes it worse

The old design had a built-in problem: the more successful the business became, the less current its data could be. More orders meant a more expensive refresh, which meant less room to refresh often.

Now, staying up to date depends on how many orders change, not how many exist. New reports, alerts and data sources plug into the same database without asking Procurement Express for anything. When you review any system, don't only ask whether it works today. Ask what happens to it as your business doubles.

The results

• Fresh data within seconds. When someone updates an order in Procurement Express, our database is current almost immediately.

• No more choosing between "once a day" and "hitting the limit". Procurement Express is no longer asked for routine refreshes.

• A dashboard the team can rely on. Missed deadlines show up when they happen, so vendors can be contacted straight away.

• New reports at no cost to speed.