Working with Custom Fields in Windsor.ai

Below are a few of the most common marketing metrics you might want to create as custom fields:

1. CPA (Cost per Acquisition):
   
   Formula:

   float(totalcost) / float(conversions)

   This divides your total cost by the number of conversions, telling you how much each acquisition costs on average.

2. ROAS (Return on Ad Spend):
   
   Formula:

   float(transactionrevenue) / float(totalcost)

   This shows how much revenue you earn for each unit of currency you spend on ads.

3. CTR (Click-Through Rate):
   
   Formula:

   (float(clicks) / float(impressions)) * 100

   This converts your click-to-impression ratio into a percentage.

4. CPC (Cost per Click):
   
   Formula:

   float(totalcost) / float(clicks)

   This lets you see how much each click costs on average.

Expert tips and tricks for creating custom fields in Windsor.ai

• Check field names: Always verify the exact field names from the data fields reference pages. Small differences in field names can cause the formula to fail.Link: Data Fields Reference Pages
• Validate with a simple test: Start by creating a simple formula and make sure it produces a reasonable value. For example, check your CPA calculation for a given day against your known metrics.
• Blending data from different sources:
  If, for example, you want to calculate ROI from Facebook Ads spend and Shopify revenues, you can use the all connector and apply this formula, based on the relevant fields for each channel:

  float(spend)/float(order_net_sales)

Functions

• lookup()

  lookup(value, json_map, default, operator)

  Parameters

  • value – Field or literal to evaluate.
  • json_map – JSON array of [match, return] pairs, evaluated top-down.
    Example: '[[\"facebook\", \"Paid Social\"], [\"email\", \"Lifecycle\"]]'
  • default – Returned when nothing matches (optional; defaults to null).
  • operator – Comparison method:
    • eq – Equals (case-sensitive for text)
    • contains – Substring match
    • gt/gte – Numeric greater-than / ≥
    • lt/lte – Numeric less-than / ≤

  Returns

  • The mapped value from json_map when a pair matches.
  • default if no match is found.
  • null if json_map is malformed or operator unsupported.

  Examples

  # Exact text match
  lookup(channel,
         '[ ["facebook", "Paid Social"], ["email", "Lifecycle"] ]',
         "Other",
         "eq")
  

  # Substring match
  lookup(campaign,
         '[ ["promo", "Promo"], ["brand", "Brand"] ]',
         "Other",
         "contains")
  

  # Numeric tiering
  lookup(spend,
         '[ [5000, "High"], [1000, "Medium"], [0, "Low"] ]',
         "Low",
         "gt")
  

Converts a date (as text) into a Unix timestamp — the number of seconds since 1 January 1970.

• regexp_extract()

Extracts part of a text value using a regular-expression pattern.

Syntax:

regexp_extract(text, pattern)

Parameters:

• text: The field or text you want to search.
• pattern: The regular-expression pattern. Wrap the part you need in ( ).

Returns:

• The first piece of text that matches what’s inside the ( ).
• If nothing matches or the pattern is unsafe/invalid, the original value is returned unchanged.

Examples:

regexp_extract('Product-1234-US', 'Product-(\\d{4})') → '1234'

regexp_extract('[email protected]', '@(.*)$') → 'example.com'

Tips:

• Keep patterns simple—avoid overly nested wildcards like (.*)*.
• Only the text inside the first pair of ( ) is returned.

• coalesce()

Returns the first value that is not empty; otherwise, uses a fallback.

Syntax:

coalesce(value, fallback)

Parameters:

• value: The main value.
• fallback (optional): Value to use when the first one is empty. Default is 0.

Example:

coalesce('', 'N/A') → 'N/A'

• to_float()

Converts text or whole numbers to a decimal number.

Syntax:

to_float(value)

Returns:

• A decimal number, or the original value if it can’t be converted.

Example:

to_float('12.34') → 12.34

• float()

Alias of to_float.

• to_int()

Converts text or decimals to a whole number.

to_int(value)

• Returns 0 when the input is empty.

to_int('42') → 42

• replace()

Find-and-replace text inside a string.

replace(text, find, replace_with)

replace('US$ 20', 'US$ ', '') → '20'

• to_datetime()

Turns many date formats into YYYY-MM-DD.

to_datetime(date_string, format)

to_datetime('31/12/2025', '%d/%m/%Y') → '2025-12-31'

• to_timestamp()

Converts a date (as text) into a Unix timestamp — the number of seconds since 1 January 1970.

Syntax:

to_timestamp(date_string, format)

Parameters:

• date_string: The date you want to convert (e.g., '2025-04-18').
• format (optional): The format your date is in. The default is '%Y-%m-%d' (year-month-day).

Returns:

• A number representing the date in timestamp format.
• If the input is empty or invalid, the original value is returned.

Examples:

to_timestamp('2025-04-18') → 1744934400

to_timestamp('04/18/2025', '%m/%d/%Y') → 1744934400

• fromtimestamp()

Converts a Unix timestamp to YYYY-MM-DD.

fromtimestamp(1744934400) → '2025-04-18'

• split_string()

Splits text and returns one part.

split_string(text, separator, index)

split_string(A/B/C', '/', 1) → 'B'

• list_join()

Combines many values into one text list, separated by commas.

list_join('red', 'green', 'blue') → "red,green,blue"

• sum()

Adds all provided numbers. Empty values are ignored.

sum(1, 2, '', 3) → 6

• max()

Returns the largest value, ignoring empty ones.

max(10, '', 7) → 10

• avg()

Calculates the average of the values (empty items are skipped).

avg(4, 6, 8) → 6

• length()

Counts how many inputs are provided (excluding empty values).

length('a', '', 'b') → 2

• nullif()

Returns empty when two values are the same; otherwise, returns the first value.

nullif('paid', 'paid') → (empty)

• not()

Reverses a true/false value.

not(true) → false

Conclusion

Creating custom metrics and dimensions with Windsor.ai is straightforward and flexible. By combining existing fields and using familiar spreadsheet-like formulas, you can tailor your analytics to your exact needs.

Give it a try with CPA, ROAS, or any other custom metric you’ve been wanting to track!