Documentation

To use any function in Wizard, you can leverage the Custom Formula functionality. In this mode, any custom SQL functions are supported, allowing you to create unique transformations by applying the formulas you need, even if they aren’t included in the standard UI dropdowns.

CustomField leverages AI within the custom filters, making the product highly flexible and allowing you to incorporate formulas that aren’t natively supported in the UI. This means you can easily extend the standard functionality, adapting it to fit your specific needs and cases.

Below we will cover the most common use cases together.

Use Cases

SplitByChar

The SplitByChar function enables you to divide a string into segments based on a specified delimiter (character or symbol). This function is particularly useful when you want to separate information stored in a single text field.

Formula to apply for campaign name:

```splitByChar('-', campaign_name[1])```

The only thing you need to change is the delimiter and position to get it to work on your data.

toFloat64 / toString

Sometimes, data can contain different types. For example, a campaign ID can be either a string or a number, depending on the data source. This creates a supertype problem because a table cannot have multiple data types within a single column. To resolve this issue, we can apply a custom formula to the problematic fields.

Formula to apply for a string that should be a numeric value:

```toFloat64(clicks)```

Formula to apply for a numeric value that should be a string:

```toString(campaign_id)```

replaceRegexpAll

The replaceRegexpAll function can be used to clean up numeric values stored as strings by removing unwanted characters, such as currency symbols or text, leaving only the numeric part. This is particularly useful for fields like costs, revenues, or other numeric values that might include symbols or text.

Let’s take a look at usage based on the example:

Let’s say you have a field that contains costs with a currency symbol (e.g., $123.45, €678,90, or USD 1000) and you want to remove the currency symbol or text and extract only the numeric value.

Formula to apply:

```toFloat64(replaceRegexpAll(costs, '[^0-9.]', ''))```

The formula will return the number without currency and make field as a numeric.

regexpExtract

The regexpExtract function is used to extract a specific part of a string that matches a regular expression pattern. It searches for text within a field that matches the provided regular expression and extracts a specific capturing group.

Let’s take a look on usage based on example:

Campaign name equal product-id-12345

Formula to apply for ID extraction:

```regexpExtract(campaign_name, "product-id-(\\d+)", 1)```

The formula will return just id ‘12345’.

lower / upper

The Lower and Upper functions adjust the case of text to lowercase or uppercase, respectively. This standardization is helpful when dealing with fields that may have inconsistent casing.

Formula to apply on the field to make entities in lower case:

```lower(adset_name)```

Formula to apply on the field to make entities in upper case:

```upper(campaign_name)```

Conclusion

Custom functions within a Custom Formula offer the flexibility to tailor transformations precisely to your needs. The examples provided demonstrate the range of possibilities, you can implement any custom formula as long as it’s a valid SQL function. This means there are essentially no limitations beyond what SQL itself supports, giving you the freedom to apply the transformations you need without restriction.