HubSpot Contacts → Salesforce Lead¶

What

Convert a HubSpot Contacts CSV export into a Salesforce Lead Import CSV.

Synthetic / teaching example

Unlike examples/real-world/, the data here is constructed, not sourced — sample.csv is hand-written rows (fictional companies) engineered to plant one of each failure mode below. CRM exports are private customer data with no public dataset, so this lives in the teaching tier rather than real-world. The failure modes it exercises are real and documented; the rows are not.

Why interesting¶

Real CRM migrations take 2–8 weeks because picklist mismatches, mixed date formats and trailing whitespace fail silently — the import succeeds row by row, then Salesforce rejects half of them after the fact.

Failure modes documented in. (sources for the problem class — not for the data)

https://splitforge.app/blog/crm-import-failures-complete-guide/ — picklist enforcement, timestamp overwrites, read-only silent drops
https://clonepartner.com/blog/pipedrive-to-hubspot-migration-data-mapping-apis-rate-limits — multi-object association loss, type coercion

The tricks¶

See inline comments in sample.json:

Long picklist (Industry) → REMAP() over a named map — a reusable whole-value string lookup table.
Short picklists (Status, LeadSource) → CASE() over a TRIMmed subject. TRIM absorbs trailing whitespace (row 2 has Open) and, unlike an IF chain, it is written once instead of once per branch. Run it: CASE(TRIM([Lead Status]), 'New', 'Open - Not Contacted', 'Open', 'Working - Contacted', 'In Progress', 'Working - Contacted', 'Connected', 'Working - Contacted', 'Open Deal', 'Working - Contacted', 'closed-won', 'Closed - Converted', 'Bad Timing', 'Closed - Not Converted', 'Unqualified', 'Closed - Not Converted', 'Open - Not Contacted')
Required-but-empty field (Company) → COALESCE(..., '<missing>') sentinel so the failure is visible, not silent. Run it: COALESCE(TRIM([Company Name]), '<missing>')
Mixed date formats → IF(CONTAINS('/'), DATE_CONVERT US, DATE_CONVERT ISO) sniffs the separator per row. Run it: IF(CONTAINS([Create Date], '/'), DATE_CONVERT([Create Date], 'M/D/YYYY hh:mm:ss', 'YYYY-MM-DD[T]hh:mm:ss[Z]'), DATE_CONVERT([Create Date], 'YYYY-MM-DD hh:mm:ss', 'YYYY-MM-DD[T]hh:mm:ss[Z]')) — on show all both branches fire, on different rows.

Final result¶

Every HubSpot vocabulary lands on a Salesforce one, and the two date shapes converge:

raw HubSpot                          →  Salesforce Lead
"Software"                           →  Technology
"Pharma & Biotech"                   →  Biotechnology
"Open " (trailing space)             →  Working - Contacted
"closed-won"                         →  Closed - Converted
"2024-01-15 09:23:01"                →  2024-01-15T09:23:01Z
"1/12/2024 10:30:00"                 →  2024-01-12T10:30:00Z
"" (company missing)                 →  <missing>

One row deliberately does not convert. Row 7 (Cyberdyne) has Industry = "Foo Bar Industry", which is not a key in the map — and REMAP passes an unknown value through unchanged rather than blanking it. The run reports errors:0, the CSV looks fine, and Salesforce rejects the row on import. Input equals output is the only signal, which is exactly why the <missing> sentinel in trick 3 exists for the other failure mode: a gap you can see beats a gap you cannot.

Trace it in the GUI

Click that Industry cell: the trace pane shows REMAP("Foo Bar Industry") → "Foo Bar Industry" — input equal to output. Add the missing key to the map and the cell updates live.

Sample data¶

Run it with bxp-cli --config ./sample.json --template hubspot_to_sfdc_lead:

sample.json (config)sample.csvsample.csvx (result)

{
  // TRICK 1 — long picklist via a named map + REMAP.
  // REMAP is a general-purpose whole-value string lookup: unmapped keys pass
  // through unchanged (visible in the GUI as input == output, the smoking gun
  // for row 7).
  maps: {
    industry_hs_to_sfdc: {
      "Software":                "Technology",
      "Software & Technology":   "Technology",
      "Pharma & Biotech":        "Biotechnology",
      "Manufacturing":           "Manufacturing",
      "Financial Services":      "Finance",
      "Automotive":              "Manufacturing",
      "Consulting":              "Consulting",
      "Education":               "Education"
    }
  },
  conversion_templates: {
    hubspot_to_sfdc_lead: {
      data_dir:           ".",
      file_pattern_in:    ".csv",
      file_pattern_out:   ".csvx",
      csv_text_quote_out: "double",
      input_schema: {
        // TRICK 4 — mixed date formats. HubSpot legacy/edited rows can leak
        // US format ("1/12/2024 10:30:00") into otherwise-ISO exports.
        // Sniff the separator and dispatch.
        $date:        "IF(CONTAINS([Create Date], '/'), DATE_CONVERT([Create Date], 'M/D/YYYY hh:mm:ss', 'YYYY-MM-DD[T]hh:mm:ss[Z]'), DATE_CONVERT([Create Date], 'YYYY-MM-DD hh:mm:ss', 'YYYY-MM-DD[T]hh:mm:ss[Z]'))",
        $firstName:   "TRIM([First Name])",
        $lastName:    "TRIM([Last Name])",
        $email:       "TRIM([Email])",
        $phone:       "TRIM([Phone Number])",
        $title:       "TRIM([Job Title])",

        // TRICK 3 — required-but-empty field. Salesforce Lead requires
        // Company. Sentinel makes the failure obvious post-import rather
        // than silently empty.
        $company:     "COALESCE(TRIM([Company Name]), '<missing>')",

        $industry:    "REMAP(TRIM([Industry]), 'industry_hs_to_sfdc')",

        // TRICK 2 — short picklist via CASE. The subject is TRIMmed once,
        // which is the point: row 2 has "Open " with trailing whitespace, and
        // an IF chain would need the same TRIM repeated at every leaf — one
        // missed leaf is a silent kill. CASE gives the trap a single place.
        $status:      "CASE(TRIM([Lead Status]), 'New', 'Open - Not Contacted', 'Open', 'Working - Contacted', 'In Progress', 'Working - Contacted', 'Connected', 'Working - Contacted', 'Open Deal', 'Working - Contacted', 'closed-won', 'Closed - Converted', 'Bad Timing', 'Closed - Not Converted', 'Unqualified', 'Closed - Not Converted', 'Open - Not Contacted')",
        $leadSource:  "CASE(TRIM([Original Source]), 'Organic Search', 'Web', 'Paid Search', 'Web', 'Direct Traffic', 'Web', 'Email Campaign', 'Email', 'Referral', 'Partner Referral', 'Trade Show', 'Trade Show', 'LinkedIn', 'Social', 'Other')",
        $numEmployees:"[Number of Employees]",
        $revenue:     "[Annual Revenue]",
        $city:        "TRIM([City])",
        $state:       "TRIM([State/Region])",
        $country:     "TRIM([Country])"
      },
      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],
      output_schema: {
        FirstName:         "$firstName",
        LastName:          "$lastName",
        Email:             "$email",
        Phone:             "$phone",
        Title:             "$title",
        Company:           "$company",
        Industry:          "$industry",
        Status:            "$status",
        LeadSource:        "$leadSource",
        NumberOfEmployees: "$numEmployees",
        AnnualRevenue:     "$revenue",
        City:              "$city",
        State:             "$state",
        Country:           "$country",
        CreatedDate:       "$date"
      }
    }
  }
}

First Name,Last Name,Email,Phone Number,Company Name,Job Title,Lead Status,Original Source,Industry,Number of Employees,Annual Revenue,City,State/Region,Country,Create Date
Anna,Novak,anna.novak@acmecorp.com,+1-555-0101,Acme Corp,VP Marketing,New,Organic Search,Software,250,15000000,Boston,MA,USA,2024-01-15 09:23:01
Tomas,Svoboda,tomas.svoboda@globex.com,(555) 234-5678,Globex Industries,Director of Sales,Open ,Direct Traffic,Manufacturing,1500,250000000,Chicago,IL,USA,2024-02-03 14:45:22
Petra,Dvorakova,petra@initech.io,555.345.6789,Initech,CTO,In Progress,Paid Search,Software & Technology,80,8000000,Austin,TX,USA,2024-02-20 11:12:34
Jan,Kovar,jan.kovar@umbrella.com,+420 776 123 456,Umbrella Holdings,CEO,New,Referral,Pharma & Biotech,5000,500000000,Prague,Praha,Czech Republic,2024-03-05 08:00:00
Sarah,Mitchell,sarah.m@stark.com,5559876543,,Senior Engineer,New,Organic Search,Software,1200,80000000,San Francisco,CA,USA,2024-03-18 16:34:11
Mike,Johnson,mjohnson@wayne.com,+1 (555) 111-2222,Wayne Enterprises,COO,closed-won,Trade Show,Financial Services,3500,200000000,Gotham,NJ,USA,1/12/2024 10:30:00
Lisa,Brown,lisa.brown@cyberdyne.com,,Cyberdyne Systems,Head of AI,Open,Email Campaign,Foo Bar Industry,450,45000000,Sunnyvale,CA,USA,2024-04-02 13:25:00
Karl,Mueller,karl.m@bavarian-motors.de,+49 89 1234 5678,Bavarian Motors GmbH,VP Engineering,Connected,Organic Search,Automotive,12000,3500000000,Munich,Bayern,Germany,2024-04-15 07:55:44
Yuki,Tanaka,yuki@sakura-tech.jp,+81-3-1234-5678,Sakura Tech,Product Manager,Bad Timing,LinkedIn,Software,90,12000000,Tokyo,Tokyo,Japan,2024-05-01 22:18:30
Carlos,Rodriguez,carlos.r@latam-trade.com,+52 55 1234 5678,Latam Trade,Regional Director,Unqualified,Cold Outreach,Consulting,25,2500000,Mexico City,CDMX,Mexico,2024-05-22 12:04:09
Emma,Watson,emma@oxford-edu.org.uk,+44 1865 270000,Oxford Educational,Dean of Studies,Open Deal,Webinar,Education,800,15000000,Oxford,Oxfordshire,United Kingdom,2024-06-10 09:11:00
Raj,Patel,raj.patel@mumbai-tech.in,+91 22 1234 5678,Mumbai Tech Solutions,Founder,New,Referral,Software & Technology,15,500000,Mumbai,MH,India,2024-07-08 18:42:15

FirstName,LastName,Email,Phone,Title,Company,Industry,Status,LeadSource,NumberOfEmployees,AnnualRevenue,City,State,Country,CreatedDate
Anna,Novak,anna.novak@acmecorp.com,+1-555-0101,VP Marketing,Acme Corp,Technology,Open - Not Contacted,Web,250,15000000,Boston,MA,USA,2024-01-15T09:23:01Z
Tomas,Svoboda,tomas.svoboda@globex.com,(555) 234-5678,Director of Sales,Globex Industries,Manufacturing,Working - Contacted,Web,1500,250000000,Chicago,IL,USA,2024-02-03T14:45:22Z
Petra,Dvorakova,petra@initech.io,555.345.6789,CTO,Initech,Technology,Working - Contacted,Web,80,8000000,Austin,TX,USA,2024-02-20T11:12:34Z
Jan,Kovar,jan.kovar@umbrella.com,+420 776 123 456,CEO,Umbrella Holdings,Biotechnology,Open - Not Contacted,Partner Referral,5000,500000000,Prague,Praha,Czech Republic,2024-03-05T08:00:00Z
Sarah,Mitchell,sarah.m@stark.com,5559876543,Senior Engineer,<missing>,Technology,Open - Not Contacted,Web,1200,80000000,San Francisco,CA,USA,2024-03-18T16:34:11Z
Mike,Johnson,mjohnson@wayne.com,+1 (555) 111-2222,COO,Wayne Enterprises,Finance,Closed - Converted,Trade Show,3500,200000000,Gotham,NJ,USA,2024-01-12T10:30:00Z
Lisa,Brown,lisa.brown@cyberdyne.com,,Head of AI,Cyberdyne Systems,Foo Bar Industry,Working - Contacted,Email,450,45000000,Sunnyvale,CA,USA,2024-04-02T13:25:00Z
Karl,Mueller,karl.m@bavarian-motors.de,+49 89 1234 5678,VP Engineering,Bavarian Motors GmbH,Manufacturing,Working - Contacted,Web,12000,3500000000,Munich,Bayern,Germany,2024-04-15T07:55:44Z
Yuki,Tanaka,yuki@sakura-tech.jp,+81-3-1234-5678,Product Manager,Sakura Tech,Technology,Closed - Not Converted,Social,90,12000000,Tokyo,Tokyo,Japan,2024-05-01T22:18:30Z
Carlos,Rodriguez,carlos.r@latam-trade.com,+52 55 1234 5678,Regional Director,Latam Trade,Consulting,Closed - Not Converted,Other,25,2500000,Mexico City,CDMX,Mexico,2024-05-22T12:04:09Z
Emma,Watson,emma@oxford-edu.org.uk,+44 1865 270000,Dean of Studies,Oxford Educational,Education,Working - Contacted,Other,800,15000000,Oxford,Oxfordshire,United Kingdom,2024-06-10T09:11:00Z
Raj,Patel,raj.patel@mumbai-tech.in,+91 22 1234 5678,Founder,Mumbai Tech Solutions,Technology,Open - Not Contacted,Partner Referral,15,500000,Mumbai,MH,India,2024-07-08T18:42:15Z