Skip to content

Using regular expressions (regex) or formulas in Clay

Did you know that you can use regular expressions (regex) or formulas in Clay to extract text, do calculations, manipulate data, and more?

If you're relying on a single database for your lead lists, just wait until you see what's possible.

In this video, we show three different examples of manipulating data from multiple data sources to build unique lead lists that can be used in outreach campaigns to prospective customers.

First we show how to trim text after a character. Next, trimming next before a character. And finally, doing calculations and categorizing data in groups.


[00:00:00] Introduction

[00:00:00] Harris Kenny: You can use regular expressions or regex in Clay to manipulate data.

[00:00:05] Harris Kenny: When you think about the workflow. You're maybe getting the list from somewhere that's partial and you're doing enrichment, and then you want to prepare that list for whatever outreach it may be.

[00:00:14] Harris Kenny: Perhaps you're syncing that to your CRM. Perhaps you're running an email campaign. You can use regex to basically more efficiently do these data operations and clean up and formatting all in one place and then export.

[00:00:26] Harris Kenny: I'm going to show you three examples. How to pull before a character after a character, and then how to look at bins basically, or groups of numbers and then generate copy specific to what those numbers reflect. It's going to make more sense when I show you. So let's just jump in.

[00:00:44] Trimming text right after a character

[00:00:44] Harris Kenny: Here we have a list where we've got a set of domains. Which you can see in this column. And we're using Clay to find the LinkedIn profile for those companies. Now, in this case, it's school districts.

[00:00:57] Harris Kenny: LinkedIn includes in the mapped data locality. So this is coming from the LinkedIn page. You can see where, for example, Autauga county schools based in Prattville, Alabama.

[00:01:09] Harris Kenny: What we want to do is we want to get the city name and we want to include that in our copy.

[00:01:14] Harris Kenny: We've written a formula, which is using JavaScript regex here in Clay.

[00:01:18] Harris Kenny: We've got these brackets and we're referencing the locality, which is from LinkedIn, which is just from the domain.

[00:01:26] Harris Kenny: We're splitting off everything that comes after the comma and we're removing it.

[00:01:30] Harris Kenny: Now you can see that we've got only the city names.

[00:01:33] Harris Kenny: If you're using clay as this workspace, you can just get this done and not have to go to Google sheets or another place to manipulate the data.

[00:01:40] Trimming text left before a character

[00:01:40] Harris Kenny: Next, I want to show you an example of using regex to get everything that comes after a symbol.

[00:01:47] Harris Kenny: We've got full domains, now these are coming from email addresses, and I'm actually not going to show you the email addresses here, but you can see that we're using a formula.

[00:01:56] Harris Kenny: In this case, we're using the pop operator here to get everything that comes after the http://www. -- right?

[00:02:07] Harris Kenny: We just want to get a really clean domain that we can use to then do a site search, which I can explain this in another video we're using Google to do site searches for specific phrases.

[00:02:18] Doing calculations and categorizing data

[00:02:18] Harris Kenny: This third example is a little more complicated. We're doing a couple things here.

[00:02:22] Harris Kenny: First, we have a list of domains that we got through web scraping. Kind of a long story, not worth getting into here, but the point is we're doing some math and we are calculating first the number of employees, which we're getting from LinkedIn.

[00:02:37] Harris Kenny: Then we're doing a little bit of division and turning that into a ratio. We're taking the number of employees that are matched, and the total number of employees, dividing and then multiplying by a hundred in order to get a ratio, basically a clean number.

[00:02:51] Harris Kenny: And that ratio is then being used to determine the size.

[00:02:57] Conclusion

[00:02:57] Harris Kenny: There's a lot going on here and I'm happy to make more videos explaining these more complex operations. If people find this interesting, just let me know.

[00:03:03] Harris Kenny: Thanks.