Skip to main content

Hi,

 

We’re battling getting contact duplicates from Meta lead campaigns. Basically people forget they’ve already signed up, and send in a new registration. Since these are Meta, we cannot control anything upstream.

 

A first step is to get our people, who only work in interfaces, alerted that there’s a duplicate to a contact. It would therefore be enough, for now, to achieve a field that tells the user the amount that the current user’s e-mail address can be found in the total base (~2.000 contacts now).

 

I’m trying an approach where I link all contacts to one record in another table, then lookup all e-mail addresses. Back in my main table I link to the second table and lookup the e-mail addresses.

 

I would the like to count the instances of the field “email” in the lookup field where all e-mail addresses are. FIND finds only the first and gives the position, SEARCH does the same. COUNT and COUNTALL do not work on text.

 

How could I get the count?

 

Rgds.

 

Björn

How are these contacts originally getting into your system?

Since your users only have interface access, they don’t yet have access to Airtable’s DeDupe extension, which allows you to easily cleanup duplicates that are already in the system. But you have access to that extension, so you can do a one-time cleanup.

If you want to do a DeDupe cleanup that doesn’t involve extensions, I demonstrate one cool technique on this Airtable podcast episode.

However, your best approach would probably be to prevent the duplicates from ever getting into your system to begin with.

If you’re receiving the contacts via a CSV file, then I would recommend using Make’s CSV automations to automatically merge the duplicates upon import.

I demonstrate how to prevent duplicates with CSV files by using Make on this episode of the BuiltOnAir podcast.

Alternatively, you can use the same exact technique by using Make’s Facebook Lead Ads integrations.

When a customer signs up through your Facebook form, you can prevent the duplicate from ever appearing in Airtable to begin with.

Hope this helps!

If you have a budget and you’d like to hire the best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld


Try this:

(
LEN({Email Rollup (from Table 1) Rollup (from Rollup)}) -
LEN(
SUBSTITUTE(
{Email Rollup (from Table 1) Rollup (from Rollup)},
Email,
''
)
)
) / LEN({Email})

And I’ve set it up here for you to check out


Thank you ​@TheTimeSavingCo!

This was exactly what I was looking for. Didn’t expect it to come in the shape of LEN-formulas, but I have an intuitive understanding of the logic there.

Thanks again!

Björn


Thanks ​@ScottWorld for you reply. The problem is we get the contacts in from Meta lead forms, so there’s no way to filter them there. Maybe Make’s FB integration could be useful, but for right now what I can deal with was the LEN-formula thing suggested by ​@TheTimeSavingCo.

 


@0800-grizzly

Yes, that’s what I was referring to above — as soon as a Meta lead form is submitted, Make can check to see if that record already exists in your Airtable base before adding the new lead into Airtable.

It can also merge a duplicate lead as well.

I demonstrate this process in my video that I linked to above (although that demonstration is done from the context of a CSV file).

- ScottWorld, Expert Airtable Consultant


Reply