Is there any option to auto-run a column in clay?

saw the q about hubspot, how many records are in that table?

I'd suggest creating a n8n automation with a cron job to webhook them in daily. only way you can auto run a column. will be a pain tho

Jeff T. Right now, about 25000 records haha. Updating them once daily would be enough though. I just need a (relatively) accurate table that reflects our hubspot entries that I use for a duplicate check within clay

Yeah, you'll have to do a little n8n cron job to import daily via webhook, only way you'll be able to trigger a column run

Do you have any tutorial for that or any idea how to ask AI how to set it up haha?

go to claude and just tell it the workflow you want

and tell tit you're doing it in n8n. it will be able to get you ther

Alright thanks for helping out 🙂 !

Hey there Maximilian thanks for reaching out and appreciate you jumping in Jeff! Let us know if you have any other questions.

Yea my overall issue is to conduct a duplicate check that normalizes the company name and domain enough in order to capture like 95%+ of the duplicates even in very fuzzy spellings. For this normalization I need to use clay formulas and one table that reflects all Hubspot companies and their normalized name and domain. I have to check whether the companies are already in hubspot before running complex sequences.

Hey there Maximilian, Do you mind sending the link to the table so we can take a look? https://downloads.intercomcdn.com/i/o/1171717996/ac7729f84b426e1d0358af02/image.png?expires=1736280000&signature=40966230dc57e59de7ce70f9fca506006a0d73b70bfaf694421fbdfdb31957c0&req=dSEgF85%2FmohWX%2FMW1HO4zYTywS3%2BDpcW2BKsam%2FhVPvxeg%3D%3D%0A

Thanks 🙂 ! https://app.clay.com/workspaces/424789/tables/t_oN56i3criekB/views/gv_hAegApVmew9C

Hey Maximilian - The duplicate checking approach you described would actually be less reliable than using HubSpot's built-in fuzzy search capabilities. HubSpot's API includes a fuzzy matching that will handle company name variations better than manual string normalization. Here's how to implement this with Clay: 1. Create a HTTP API to call HubSpot's search endpoint with each company name you want to check. Include both name and domain fuzzy matching: POST https://api.hubapi.com/crm/v3/objects/companies/search headers: Authorization: Bearer + HUBSPOT_TOKEN, Content-Type: application/json body: { filterGroups: [{ filters: [ { propertyName: "name", operator: "SIMILAR_TO", value: INPUT.company_name }, { propertyName: "domain", operator: "SIMILAR_TO", value: INPUT.domain } ] }], properties: ["name", "domain"], limit: 10 } }) You'd replace the INPUT. values with your own in Clay using forward slash. 2. Process the response to check for matches with a Formula This will catch spelling variations, abbreviations, and formatting differences automatically through HubSpot's fuzzy matching algorithm. Let me know if you need extra help

Hey Bo (. 🙂 thanks for your response! The issue is, Hubspots built-in "manage duplicates" only resulted in about 200 duplicates whereas the manual duplicate check returned 3700+ duplicates (which actually almost all were duplicates). Is the algorithm you are referring to different to the function I may have used in Hubspot? I have used the function which allows you to review and merge found duplicates.

This should work differently since it's using fuzzy match, but we can also try to go even deeper IF this previous method isn't working. This should also find more than via the UI with the HTTP API. I'd try the first one to see the results, and then you can also try this one Key differences from HubSpot's UI tool: 1. Uses both FUZZY_MATCH and SIMILAR_TO operators 2. Customizable threshold (0.7 catches more variants) 3. Considers multiple properties 4. Returns confidence scores 5. Can process in bulk via API POST https://api.hubapi.com/crm/v3/objects/companies/search Headers: Authorization: Bearer {hubspot_token} Content-Type: application/json Body: { "filterGroups": [ { "filters": [ { "propertyName": "domain", "operator": "FUZZY_MATCH", "value": "{INPUT.domain}", "threshold": 0.7 }, { "propertyName": "domain", "operator": "SIMILAR_TO", "value": "{INPUT.domain}" }, { "propertyName": "website", "operator": "FUZZY_MATCH", "value": "{INPUT.domain}", "threshold": 0.7 } ] }, { "filters": [ { "propertyName": "name", "operator": "FUZZY_MATCH", "value": "{INPUT.company_name}", "threshold": 0.7 }, { "propertyName": "name", "operator": "SIMILAR_TO", "value": "{INPUT.company_name}" } ] } ], "properties": [ "name", "domain", "website", "industry", "createdate", "hs_lastmodifieddate" ], "limit": 100, "sorts": [ { "propertyName": "score", "direction": "DESCENDING" } ] }

Okay wow this sounds amazing!! However, I haven't experimented with Clay API's yet and struggle a little bit on the setup, I have just created an Hubspot AUTH token

I'd highly recommend checking out our university HTTP API video here: https://www.clay.com/university/lesson/http-api-clay-101 That should help you, and also, LLM is very good at this. I'd suggest using the one you prefer to go into more depth as well! :)

Bo (. hey man I've managed to set it up, however the operators fuzzy match and similar to do not seem to exist as i get this error

Clay received a 400 error from the API with Content: {"status":"error","message":"Invalid input JSON on line 1, column 66: Enum type must be one of: [IN, NOT_HAS_PROPERTY, LT, EQ, GT, NOT_IN, GTE, CONTAINS_TOKEN, HAS_PROPERTY, LTE, NOT_CONTAINS_TOKEN, BETWEEN, NEQ]","correlationId":"6e88e111-d90b-45f1-956f-5a8dc034689d","category":"VALIDATION_ERROR"}

Maximilian - Do you mind sending the link to the table so we can take a look? https://downloads.intercomcdn.com/i/o/1171717996/ac7729f84b426e1d0358af02/image.png?expires=1736298900&signature=57c47bdf7b1894b8deebe51685cc8556573d1a60848d084c38e1bf77d14610b5&req=dSEgF85%2FmohWX%2FMW1HO4zYTywS3%2BD58f2BKsam%2FhVPuq4A%3D%3D%0A

yes this is my new table to test the API call https://app.clay.com/workspaces/424789/tables/t_AXnhq6aUARg8/views/gv_BdSraPTJV9mA

Also I have just searched the hubspot documentation and it seems that there is only a "Contains token" operator

Maybe a workaround would be some manual normalization and then using the api call?

Yup you're right - The issue is with the operator "FUZZY_MATCH" and "SIMILAR_TO" which are not valid (anymore) operators according to the API error message. Here's the solution: Replace the invalid operators with valid ones from this list: [IN, NOT_HAS_PROPERTY, LT, EQ, GT, NOT_IN, GTE, CONTAINS_TOKEN, HAS_PROPERTY, LTE, NOT_CONTAINS_TOKEN, BETWEEN, NEQ] Modified JSON body should use CONTAINS_TOKEN instead:

I updated your table. Does it cover all variations?

Yea i have now also implemented the manual normalization and it seems to work but ill need to see tomorrow

Thanks!

Thank you so much for sharing your feedback Kenneth T.!

Hey Maximilian, everything okay? Did you get it to work as planned?