Common applications
Putting the pieces together, we've aggregated a few common formula examples and how to write them here to get you started.

Waterfall Returned Data

    Aggregate multiple columns into one column, i.e. Pick A if possible, Pick B if not
    e.g. To waterfall different domains from Clearbit and Google into one column {{ "Domain - Clearbit" }} || {{ "Domain - Google" }}

Splitting a column into two

    Split a name or a term into two columns, i.e. Split Full Name into First Name & Last Name
    e.g. Split full name into first and last names First name: {{ "Full Name" }}.split(' ')[0] Last name: {{ "Full Name" }}.split(' ')[1]

Conditional runs

    Run actions based on specific conditions, e.g. run only if another action has run, run only if a prior action has returned a specific output, run only if box is checked
    The formula is applied via the action input and reads **if( {{condition}} , {{input}} )**
    In this example we want to run Clearbit only if BuiltWith has returned a match for Hubspot. We will apply **if( {{ "Uses Hubspot" }} , {{Domain}} )**

Labeling sources

    Keep track of the sources your data comes from, i.e. if A is true, label "TRUE", otherwise label "MAYBE"
    e.g. label personal & work emails returned and aggregate them into one column {{ "Email - Personal" }} ? "email-personal" : {{ "Email - Work" }} ? "email-work" : ""

Sanitize URLs

    Clean urls if they include unwanted characters like https:// or unwanted paths like /index.html, i.e. Strip down to just
    {{ "Domain" }}.replace("https://", "").replace("www.", "").split("/")[0]

Automatic checkbox

    Write a formula to show a checkbox in your table, checked or unchecked based on conditions of your choice, i.e. If A is true, show checkbox in this row
    {{ "Email" }}.includes("gmail")

Tier or filter your data by grouping

    Create buckets that records fall into using ternary operators that can help tiering or filtering, i.e. If A is between B and C, label D
    E.g. Segment companies according to employee count ranges of 1000+, 500-1000, 100-500, 100-500, 50-100, 25-50, 5-25 {{ "Employee Count" }} >= 1000 ? "1000+" : {{ "Employee Count" }} >= 500 ? "500-1000" : {{ "Employee Count" }} >= 100 ? "100-500": {{ "Employee Count" }} >= 50 ? "50-100" : {{ "Employee Count" }} >= 25 ? "25-50" : {{ "Employee Count" }} >= 5 ? "5-25":"1-5"

Consolidate an array into a comma-separated list

    If a single record returns multiple items in an array, extract all items into a column as a comma-separated list rather than requiring a column for each field, i.e. Push this list of emails into a single column as a comma-separated list
    {{ "Enrich Person" }}>{email.address}).join(", ") where:
      .map() — For each item in the {{ "Enrich Person" }}.emails, only show the value of email.address
      .join() — Add a comma between each item and return a single flat list.

Find tools used by keyword

    To find tools by keyword, set up Clay's Enrich Company action and create a formula to return a comma-separated list of found tools. _.values(.allTechnologies).map((tech)=>{}).filter((o)=>{["magento","shopify", "woocommerce","squarespace"].map((tech)=>{tech.toLowerCase()}).includes(o)}).join(", ")
    Where you can replace the tools in this part of the formula to get your expected results ["magento","shopify", "woocommerce","squarespace"]
Last modified 1mo ago