How to Enrich a BigQuery Table With LeadOcean

Q: Can I enrich by name and company domain?

Not directly. Enrich takes exactly one of linkedin_url, email, phone or person_id. Search by domain first, then enrich by person_id.

Q: Will the job re-spend records on rows it already enriched?

No. It only selects rows where enriched_at is null, and the MERGE sets that column on a match and on a 404.

Q: How do I size a company list before I spend anything?

Use GET /v1/companies/count or the leadocean_count_companies MCP tool. Both are free and return an exact total.

You enrich a BigQuery table by running a small Python job on a schedule. It selects rows with no enrichment yet, calls LeadOcean once per row, and merges the email, phone and profile back with one SQL statement.

Google documents executing Cloud Run jobs on a schedule, September 2026. This guide uses that pattern.

TL;DR

It takes about 45 minutes: 10 for the tables, 15 for the script, 20 to deploy, schedule and test on a small batch.
It spends 1 record per matched row. A batch of 50 costs up to 50 of your 1,000 free records. A row we hold nothing for is a 404 and costs 0.
Each row needs a linkedin_url or an email. There is no name plus domain lookup, so keep one of them on every row.

Prerequisites

A Google Cloud project with BigQuery, Cloud Run and Cloud Scheduler enabled, and the gcloud and bq CLIs installed.
A LeadOcean API key from app.leadocean.io. The free plan has no card and 1,000 records, one-off.
The key as $LEADOCEAN_API_KEY in your shell. It never goes in a table or in Git.
Python 3.11 with google-cloud-bigquery and requests.

bash

export LEADOCEAN_API_KEY="paste-your-key-here"

Step 1: Create the tables

Keep the raw answer in a JSON column and pull out the fields you query. A row with enriched_at empty is pending work.

sql

CREATE TABLE IF NOT EXISTS crm.leads (
  id            INT64,
  linkedin_url  STRING,
  email         STRING,
  enriched_at   TIMESTAMP,
  enrich_status STRING,   -- matched | not_found
  work_email    STRING,
  email_status  STRING,
  phone         STRING,
  profile       JSON
);

CREATE TABLE IF NOT EXISTS crm.leads_stage LIKE crm.leads;

INSERT INTO crm.leads (id, linkedin_url)
VALUES (1, 'https://www.linkedin.com/in/janedoe-example');

The row uses a placeholder. Swap in your own data.

Step 2: Write the job

The script takes up to 50 pending rows, calls POST /v1/people/enrich with reveal_email true, and loads the answers into the staging table. It sends one request at a time with a 0.25 second pause, which stays under the 5 requests a second on Free.

python

import json, os, sys, time
from datetime import datetime, timezone
import requests
from google.cloud import bigquery

API = "https://api.leadocean.io"
HEADERS = {"x-api-key": os.environ["LEADOCEAN_API_KEY"],
           "Content-Type": "application/json"}
bq = bigquery.Client()

rows = bq.query("""
  SELECT id, linkedin_url, email FROM crm.leads
  WHERE enriched_at IS NULL AND (linkedin_url IS NOT NULL OR email IS NOT NULL)
  LIMIT 50
""").result()

now = datetime.now(timezone.utc).isoformat()
out = []
for r in rows:
    key = {"linkedin_url": r.linkedin_url} if r.linkedin_url else {"email": r.email}
    resp = requests.post(f"{API}/v1/people/enrich",
                         headers=HEADERS, json={**key, "reveal_email": True})
    if resp.status_code in (401, 402, 429):
        print(f"stop: {resp.status_code}", file=sys.stderr)
        break
    if resp.status_code == 404:
        out.append({"id": r.id, "enriched_at": now, "enrich_status": "not_found"})
        continue
    resp.raise_for_status()
    d = resp.json().get("data", {})
    c = d.get("contact_data", {})
    out.append({
        "id": r.id, "enriched_at": now, "enrich_status": "matched",
        "work_email": (c.get("contact_emails") or [{}])[0].get("email"),
        "email_status": c.get("email_status"),
        "phone": (c.get("contact_phones") or [{}])[0].get("phone"),
        "profile": d.get("profile_data"),
    })
    time.sleep(0.25)  # stays under 5 requests a second on Free

if out:
    bq.load_table_from_json(
        out, "crm.leads_stage",
        job_config=bigquery.LoadJobConfig(write_disposition="WRITE_TRUNCATE",
            schema=bq.get_table("crm.leads_stage").schema),
    ).result()
    bq.query("""
      MERGE crm.leads t USING crm.leads_stage s ON t.id = s.id
      WHEN MATCHED THEN UPDATE SET
        enriched_at = s.enriched_at, enrich_status = s.enrich_status,
        work_email = s.work_email, email_status = s.email_status,
        phone = s.phone, profile = s.profile
    """).result()
print(f"processed {len(out)} rows")

A 401, 402 or 429 ends the loop, so a bad key or spent records never spins. Rows left untouched are picked up on the next run.

Step 3: Add company enrichment

Companies use POST /v1/companies/enrich with a domain. Run it as a second pass in the same job, against a crm.companies table with a domain column.

python

resp = requests.post(f"{API}/v1/companies/enrich",
                     headers=HEADERS, json={"domain": "acme.com"})

A 404 means we hold nothing for that domain. Mark the row done and move on. It costs 0.

Step 4: Deploy as a Cloud Run job

Build and deploy the script as a job. Save it as main.py next to a requirements.txt and a Procfile, so the build knows what to run. Store the key in Secret Manager so it never sits in the job definition.

bash

printf 'google-cloud-bigquery\nrequests\n' > requirements.txt
echo 'web: python main.py' > Procfile

printf '%s' "$LEADOCEAN_API_KEY" | gcloud secrets create leadocean-key --data-file=-

gcloud run jobs deploy enrich-leads \
  --source . \
  --region us-central1 \
  --set-secrets LEADOCEAN_API_KEY=leadocean-key:latest

gcloud run jobs execute enrich-leads --region us-central1 --wait

The job's service account needs to read the secret and to run BigQuery jobs on the dataset. Insert two or three test rows first. The log line processed 3 rows confirms the run.

Step 5: Schedule it

Google documents scheduling a job from the Cloud Run console or with Cloud Scheduler, September 2026. Set an hourly trigger in the console, or create the scheduler entry yourself with the job's run URL and a service account that can invoke it.

Fifty rows an hour is 1,200 rows a day. Raise the LIMIT in the script for a larger backlog.

Step 6: Read the results

Query the table. Only some email_status values are safe to mail.

sql

SELECT id, work_email, email_status, phone, enrich_status
FROM crm.leads
WHERE enrich_status = 'matched'
  AND email_status IN ('verified', 'catch_all_valid', 'catch_all');

Drop invalid, spam_trap, abuse and disposable. LeadOcean gives no refund for bounced emails, so filter before you send.

What you get

The email_status values the query relies on come from leadocean_list_enum_values, a free call. We ran it on 2026-10-21.

json

{"name":"email_status","total":13,"matched":13,"values":["verified","catch_all_valid","catch_all","risky","unknown","untested","invalid","role","disposable","spam_trap","abuse","derived","none"],"truncated":false}

Each matched person returns this shape. It is the response shape from the API reference, with placeholder values.

json

{
  "success": true,
  "data": {
    "profile_data": { "...": "name, title, company" },
    "contact_data": {
      "has_email": true,
      "email_status": "verified",
      "contact_emails": [{ "email": "jane.doe@acme.com", "type": "work" }],
      "contact_phones": [{ "phone": "+14155550133", "type": "mobile" }]
    }
  }
}

Person enrich also returns career history in resume_data when held: past roles, education and certifications. The job above does not store it. Add it to profile if you want it.

What it costs

One record is one person or one company you receive. A people enrich that matches is 1 record, and so is a company enrich that matches. A 404, a count and an empty page cost nothing.

On Free, the 1,000 records are one-off, so the job stops with a 402 once they are spent. On a paid plan there are no credits. Solo is $97 a month and Agency is $497 a month, each with a fair-use pace instead of a balance. Reaching the pace slows the key until the window resets. See pricing.

Troubleshooting

Error	Cause	Fix
401	The `x-api-key` header is missing or the key is wrong.	Add a new secret version and redeploy the job.
402	Your records are spent. Free is 1,000, one-off.	Check `GET /v1/account`, or compare plans on pricing.
404	We hold nothing for that identifier.	Nothing to fix. It costs 0, and the job marks the row `not_found`.
429	Requests came faster than the plan's rate limit: 5 a second on Free, 200 on Agency.	Wait the `Retry-After` seconds. The one-at-a-time loop stays under it.
Rows never update	The load or MERGE failed, or `enriched_at` is already set.	Read the job logs and check `crm.leads_stage`.
Empty search results	A search filter used a value that is not an exact enum string.	Call `leadocean_list_enum_values` for the exact string.

FAQ

Can I enrich by name and company domain?

Not directly. Enrich takes exactly one of linkedin_url, email, phone or person_id. Search by domain first, then enrich by person_id.

Will the job re-spend records on rows it already enriched?

No. It only selects rows where enriched_at is null, and the MERGE sets that column on a match and on a 404.

How do I size a company list before I spend anything?

Use GET /v1/companies/count or the leadocean_count_companies MCP tool. Both are free and return an exact total.

Why load to a staging table and MERGE?

One MERGE replaces 50 single-row UPDATE statements. It is faster and easier to retry.

Do other warehouses work the same way?

The call is identical. See Snowflake, Postgres and Databricks. More recipes are in the blog hub.

Enrich your BigQuery rows on a schedule, free

Free to start. No credit card. 1,000 records to spend whenever you like.

Get your free API key →