Fill in missing mobile numbers on HubSpot contacts overnight

Every night at 2am on a cron schedule, fill in the missing mobile numbers on my HubSpot contacts so my reps stop hitting dead ends. This is a deterministic pipeline with a fixed query, a fixed enrichment job, and a fixed write-back. There are no judgement calls anywhere in it.

Step 1, select the records. Use HubSpot Search Contacts to pull contacts where the mobile phone property (mobilephone) is empty, the lifecycle stage (lifecyclestage) is one of the stages I care about (default to lead and salesqualifiedlead, and make the list configurable), and my enrichment attempted date property has never been set. Cap the result at a fixed number of records per run, default 500, exposed as config so credit spend per run is predictable. Request the properties needed to identify a person for lookup: first name, last name, email, company, and LinkedIn URL if I store one. If the search returns zero contacts, post nothing and end the run quietly.

Step 2, build the CSV. Turn those rows into a CSV with a header row and one column per identifying field, plus a hubspot_contact_id column carrying the record id. Keep an in-workflow map from row to HubSpot contact id as well, keyed on record id and on email as a fallback, so matching back does not depend on Boomerang echoing the id column. Because the CSV has a header row, omit columnMappings and let Boomerang auto-detect the columns. If the submit returns a 400 with code COLUMN_MAPPING_ERROR, read details.missingRequired, details.outOfRange and details.duplicateColumns, report them in the Slack failure message, and make no CRM writes.

Step 3, submit the job. Use the Boomerang Submit Mobile Finder Enrichment operation, which is a bulk multipart form upload with the CSV as the file part. Configure whether US numbers and UK numbers should be included, as two booleans, defaulting both to true. Capture the returned request id and the credits reserved at submit time.

Step 4, poll for completion. Call Get Request Status on that request id until the status leaves "In queue". The status values are exactly "In queue", "Completed" and "Issue", capitalized and with a space, so compare against them exactly. Poll about once a minute with a sensible backoff and an overall timeout of roughly two hours, after which treat the run as failed. Boomerang is rate limited to 60 requests per minute and 600 per hour per key, so if any call returns 429, honor the Retry-After header value in seconds before retrying.

Step 5, on "Completed", download the output. Read outputUrl off the status response and fetch the CSV. That link is a time-limited signed URL, and outputUrlExpiresAt gives its expiry, so if it has expired just re-poll the request to mint a fresh one. Important: do not read the emailFound or phoneFound fields to decide whether the run worked. Those stay null even on completed jobs until Boomerang's data team populates them. The output CSV is the source of truth for what was found.

Step 6, write the numbers back. Match each output row back to its HubSpot contact id using the id column and the fallback map, then write the mobile numbers in with HubSpot Batch Update Contacts, chunked into batches of at most 100 records per request, which is that operation's limit. Only set the mobile phone property on rows that actually came back with a number.

Step 7, stamp everything that was attempted. Set the enrichment attempted date property (for example mobile_enrichment_attempted_date) to today's date on every contact that was submitted, including the ones that came back empty. This is the part that matters most: it is what makes the step 1 query skip them next time, so I never pay twice to look up the same dead record. Fold these into the same batches of 100 where possible.

Step 8, log every result. Append one row per submitted contact to a Google Sheets audit tab using Append Values, whether or not a number was found. Include the run date, contact id, name, company, lifecycle stage, a found or not found flag, and the number that was written when there is one. This is what lets me see hit rate over time and work out which segments are worth enriching.

Step 9, summarize to Slack. Use Slack Bot Send a Message to post a short overnight summary to my chosen channel with records submitted, numbers found, hit rate as a percentage, and credits used. Take credits used from creditsUsed on the status response, which is the amount actually charged, rather than the credits reserved at submit.

Failure path. If the job comes back with status "Issue", or the submit fails with a 402 meaning insufficient credits or an exhausted free-tier cap, or the poll times out, post that to Slack instead of the summary and make no CRM writes at all. Do not stamp the attempted date on contacts that were never actually looked up, because that would silently blacklist them from every future run.

Build the nightly batch rather than a per-contact version. The mobile finder is a bulk CSV job with batch pricing and a per-key hourly request budget, so firing one asynchronous job per newly created contact burns the budget and wastes the batch rate. A HubSpot triggered per-contact variant is a reasonable customization to mention, but the scheduled batch is the shape to build.