Nightly HubSpot contact backfill with LeadMagic and Google Sheets

Every night at 2am, run a CRM backfill job that fills in missing contact data on HubSpot using a Google Sheet as the work queue. This is a deterministic pipeline with no judgement calls, so build it as a code workflow.

Trigger: cron, every day at 02:00 in the workspace timezone.

Step 1. Read the queue from Google Sheets. Use Get Values to read rows from the configured spreadsheet (default tab name 'CRM Backfill Queue', tab name configurable). Each row has columns: first_name, last_name, company_domain, status, email, credits_consumed, last_run_at. Select only rows where status is blank or equal to 'pending'. Take at most 100 rows per run to stay well under LeadMagic's 300 requests per minute limit.

Step 2. For each candidate row, call LeadMagic Email Finder with the first name, last name, and company domain. If Email Finder returns a verified deliverable work email, also call LeadMagic Mobile Finder using that email. If Email Finder returns not_found or the email is not verified, skip Mobile Finder for that row and mark the row 'not_found'.

Step 3. Track the X-Credits-Remaining response header on every LeadMagic call. If it drops below a configured floor (default 500), stop processing new rows for this run and leave the remaining rows as 'pending' so the next run picks them up. This guards against surprise zero balances for the ops team.

Step 4. Send the enriched contacts to HubSpot using Batch Upsert Contacts, matched on the email property, in batches of up to 100. Write first name, last name, phone (when Mobile Finder returned a number), and stamp a custom property called 'last_enriched_at' with today's date in YYYY-MM-DD. Upsert means missing contacts get created and partial contacts get filled in.

Step 5. Write each row's outcome back to the same Google Sheet using Update Values. Set the status column to 'enriched', 'not_found', or 'error'. Write the resolved email into the email column. Write credits_consumed as the sum of the credits_consumed values returned by Email Finder and Mobile Finder for that row. Write today's date into last_run_at. On error, set status to 'error' and include a short reason.

Configurable inputs: spreadsheet id, tab name, daily run time, max rows per run, LeadMagic credit floor, the name of the HubSpot custom property used for last enriched date, and which HubSpot phone property to write to.

Additional information

What does this prompt do?

Reads pending rows from a Google Sheet you control, so you decide which contacts get worked next.
Looks up verified work emails and direct mobile numbers for each row from LeadMagic.
Updates HubSpot in bulk: creates the contact if missing, fills the gaps if partial, and stamps today's date as the last enriched date.
Writes the outcome back to the sheet (enriched, not found, or error) plus credits used, so your ops team has a clean audit trail every morning.

What do I need to use this?

A LeadMagic account with credits available and an API key.
A HubSpot login with permission to create and update contacts and write to a custom property.
A Google Sheets workbook with a tab listing first name, last name, company domain, and a status column.

How can I customize it?

Change the nightly run time, the batch size per run, or the maximum number of rows processed in one go.
Set a credit floor so the job pauses itself before your LeadMagic balance gets close to zero.
Pick which HubSpot fields get written, including the custom property that holds the last enrichment date.

Frequently asked questions

Do I need to clean the spreadsheet by hand each night?

No. The job only touches rows where the status column is blank or set to pending, and writes the outcome back so you can re-run it any night without working the same row twice.

What happens to rows where LeadMagic cannot find an email?

They are marked not found in the sheet and skipped on the next run. You can edit the row by hand and reset the status to pending if you want to retry.

How does this avoid burning all my LeadMagic credits?

You configure a floor. When your remaining balance drops below it, the job stops picking up new rows for the night and the rest of the queue waits for the next run.

Will this work on existing HubSpot contacts, not just new ones?

Yes. That is the whole point. The match key is the resolved email, so existing contacts get filled in rather than duplicated.

Does it work with HubSpot Free?

Yes, as long as the HubSpot user you connect can create and update contacts and write to a custom property.