Clay Icon
Join the Community
03 Feedback & Requests

Request for City and State Split in Company Location Enrichment

Avatar of Collin R.Collin R.
·
·

Am I missing an easy option on "Location" company enrichment to split it between City & State (consistent / normalized)? I am hacking it together with Comma delimiter and a postcode normalization functions, but feels like I can't be the only one that wants that very standardized process to be prebuilt functionality (and Clay presumably can do it better than me)?

5 comments

  • Avatar of Channeled
    Channeled
    APP
    ·

    Yes, we have a prebuilt functionality for normalizing location data, including splitting it into consistent City and State fields. Our "Normalize Location" feature uses Mapbox's Geocoding API to standardize location information within Clay workflows. This feature can transform any location into a uniform data point based on city, eliminating the need for manual hacking or complex functions. The output includes normalized fields for City, State, Zip Code, and more, providing you with consistent and standardized location data.

  • Avatar of Collin R.
    Collin R.
    ·
    ·

    Yes, I understand there is paid enrichment here but that frankly feels silly to spend 1 credit on it when you're giving us "city, state" in basic enrichment. A much lower leverage credit than a Claygent prompt... Guess I'll keep hacking via formulas

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·
  • Avatar of Summiya B.
    Summiya B.
    ·
    ·

    You can use formula to separate city and state from the data lready you have

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·

    You can use our "write to table" feature to create a formula that separates city and state from the existing location data. Here's a general approach: 1. Use a text splitting function to separate the city and state based on a comma. 2. Trim any extra spaces from the resulting values. 3. If needed, use additional functions to handle edge cases or inconsistencies in the data format. For example, if your location column is named "Location", you might use a formula like: LBKSTRIM(SPLIT(Location, ",", 1)) AS City, TRIM(SPLIT(Location, ",", 2)) AS StateLBKSThis approach allows you to create separate columns for City and State without using additional credits for enrichment.