I need to clean up duplicate companies in a HubSpot CRM. Some have '(Corporate)' in the name to indicate parent companies, and others are regional offices with similar or identical names, domains, and locations. Some have the same name and domain but distinct locations, others have a distinct name but the same location and domain. I want to use Clay to normalize names with city/country, and create consistent parent-child relationships. How can I do this?
For HubSpot company cleanup, use our "Normalize Company Names" function to standardize names by removing suffixes like "(Corporate)" and legal designators. Steps: 1. Navigate to enrichment panel → "Tools" section 2. Select "Normalize Company Names" 3. Choose your company name column 4. Optionally normalize case For parent-child relationships, use the HubSpot "Lookup Object" action to map existing parent company connections in your CRM. Before updating, check for existing records using "Lookup Contact" with email as identifier to avoid duplicates. The normalize function will clean names like "Panamax Inc." to "Panamax" for consistency.
Gabriel A. Import CRM data into Clay (company name, domain, location) Add column "Normalize company name with location" Use GPT prompt: Combine company name with city and country to create a unique normalized name Add column: “Parent or child?” Use GPT to infer based on keywords like "Corporate" domain match, or city mismatch. Use Clay filters/grouping to identify and tag duplicates Export cleaned data back to HubSpot via Zapier or HubSpot integration Done clean, normalized, and structured.
Your approach is solid! Here's how to execute it: Step 1: Import & Normalize ** Import HubSpot data using our CRM source action ** Use "Normalize Company Names" function: enrichment panel → Tools section → select your company name column ** Add GPT column to combine normalized name with location for unique identifiers Step 2: Parent/Child Classification ** Create GPT column analyzing keywords like "Corporate", domain matches, and location mismatches ** Use our "Lookup Object" action to map existing parent-child relationships from HubSpot Step 3: Deduplicate ** Enable "Auto-dedupe" function at bottom-right of table, select your unique identifier column as the dedupe criteria Step 4: Export * Use HubSpot integration to update records directly, or export via our available integrations This workflow will give you clean, normalized company data with proper hierarchical relationships ready for your CRM.
Hey Gabriel A. , Without knowing too much about your current setup, I'm thinking this can be done based on what you mentioned.
First, import all company names that have (Corporate) in their name into Clay.
Use a formula to separate the '(Corporate)'out from the company name, and push all the '(Corporate)' into a different table.
In the new table, use Claygent to search for the domain of the company
In the new table, now you have Corporate Name + Domain. I would believe now, it will be easier for you to do some kind of association.
Might be hard to explain in words since this project might have many nuances. But feel free to DM and happy to have a chat about it if it's something you want to look deeper into.
Your approach is excellent! Here's how to execute it efficiently: Step 1: Import & Separate ** Import HubSpot companies into Clay ** Use our "Normalize Company Names" function: enrichment panel → Tools section → select company name column Step 2: Clean & Organize ** The normalize function removes suffixes like "(Corporate)" automatically - "Panamax Inc." becomes "Panamax" ** Use formulas to separate corporate entities into different tables as needed Step 3: Deduplicate ** Enable "Auto-dedupe" function at bottom-right of table ** Select your unique identifier column (like normalized name + domain) as dedupe criteria Step 4: Domain Enrichment ** Use Claygent to find domains for corporate entities ** Create associations between parent companies and regional offices This workflow will give you clean, normalized company hierarchies ready for HubSpot integration.
Welcome dude!