From HubSpot deal to monthly usage invoice for an advertising business

#The situation

An advertising business sells campaigns in HubSpot and bills them from NetSuite. Each closed deal was re-keyed as a sales order, with its flight dates, billing frequency, rebates and line items. Billing then depended on delivery. A campaign is invoiced on the impressions it actually served, and those numbers live in the demand-side platform (DSP), outside NetSuite. Month end meant exporting delivery reports and working out each invoice by hand.

#What we built

HubSpot to NetSuite. Scheduled Map/Reduce jobs bring HubSpot companies in as customers, and deals modified since the last run in as sales orders. For each deal the job reads its company and line items. It then updates the sales order that already carries that deal id, or creates one with the deal id as its external id. The job maps campaign dates, billing frequency, rebates and product types. It also maps the deal owner to a sales rep and the HubSpot entity to a NetSuite subsidiary. A Suitelet lets finance pull specific deals on demand, outside the schedule.

DSP delivery to NetSuite. A daily Map/Reduce runs the DSP's saved delivery report and stores impressions, clicks and spend per campaign line as usage records, each tied to its deal line. The DSP calls pass through a small serverless proxy that handles the platform's session login.

Usage to invoices. A second Map/Reduce collects usage records not yet invoiced, groups them by sales order and by month, and transforms the sales order into one invoice per month. It bills quantities per thousand impressions, capped at what remains on the order line, and drops lines with nothing left to bill. Each usage record then links to the invoice that billed it.

#What it does now

Deals reach NetSuite without re-keying, and monthly invoices follow delivered volume. A checkbox on a customer or a deal excludes it from automatic billing, and the invoicing job respects both. When a deal fails to sync, the job writes an error log record with the deal id and the reason, and a sync status record tracks each run.

#Under the hood

• Sales orders are upserted by HubSpot deal id held as the external id, so a deal edited ten times is still one order.
• Line items carry the HubSpot line id, which lets usage map to the exact order line.
• The invoicing job picks up only usage records without an invoice link, so a rerun bills nothing twice.
• Quantities stop at the ordered amount, so a campaign that over-delivers is not billed beyond its budget.
• HubSpot credentials are NetSuite API secrets.
• Custom records, fields, scripts and the error log ship as one SDF project.