Which enquiries became sales: matching quotes and invoices back to the lead

Each form entry is written to a leads log with the email and phone exactly as typed and in a canonical form: the email trimmed and lower-cased, the phone stripped to digits and cut to its last nine, so a local and an international form of the same number are identical. The ad-click identifier from a hidden field is kept with the lead. Every later match is a cheap equality search on the canonical column.

The accounting system offers no watch trigger for quotes in Make, so a scheduled raw API call asks for quotes changed in the last six hours, using an If-Modified-Since header so a quiet period costs about two operations. Only quotes that have actually gone to the customer (sent, accepted, declined or invoiced) and carry an email go on to be matched.

The quote pass runs every four hours over a six-hour window, so a late run loses nothing, and before writing a match it searches for the quote number and skips one already logged. The window overlaps; the log never duplicates.

A separate pass keeps each matched quote's status current. It reads two pages of recently changed quotes, aggregates them, reads the match log once, and joins the two in memory with a map expression, writing a row only when its status has moved. A quiet run costs a flat five operations however long the log grows.

Invoices from the start of the tracking period are watched every four hours. An invoice with a value and a contact email is matched to the earliest lead with that email; if the lead already has a row, the invoice number is appended to a list only if it is not already there, so re-seeing an invoice when its status changes never duplicates it. A customer who buys twice is still one converted lead.