Derived Field Function Reference

Almost any calculation available in a spreadsheet can be performed here. Functions are organised into five groups. The table below describes the purpose of each group, with an example from the retail scenario:

Function reference

Group	What it is for	Example
Conditional	“If this, then that” rules that assign customers to groups.	Purchase Recency Bucket — Recent, Warm, or Lapsed from days since the last purchase.
Date/Time	Working with dates and the time elapsed between them.	Days Since Last Purchase — days since the most recent purchase.
Math	Arithmetic, percentages, and rounding.	Average Purchase Value — total spend divided by number of purchases.
Text	Joining, tidying, or trimming text.	Reactivation Email Name — a correctly capitalised name.
Logical	Functions that work across a contact’s linked activity records.	Count Purchase activity — count how many purchases a customer has made.

Written in full, those five examples are:

Field	Formula
Total Spend	`SUM({Purchase History.Amount})`
Purchase Recency Bucket	`IF(DATEDIFF(TODAY(), MAX({Purchase History.Purchase Date}), "days") <= 30, "Recent", IF(DATEDIFF(TODAY(), MAX({Purchase History.Purchase Date}), "days") <= 90, "Warm", "Lapsed"))`
Days Since Last Purchase	`DATEDIFF(TODAY(), MAX({Purchase History.Purchase Date}), "days")`
Average Purchase Value	`ROUND(SUM({Purchase History.Amount}) / COUNT({Purchase History}), 2)`
Reactivation Email Name	`PROPER(TRIM({Name}))`

Text

Text functions join, tidy, or trim text values.

Function	What it does	Retail use case
`UPPER(text)`	Converts text to capitals.	Standardise Store Location names to capitals.
`LOWER(text)`	Converts text to lower case.	Normalise Email Address values for matching.
`TRIM(text)`	Removes spaces from the start and end of a value.	Remove stray spaces from an imported Name.
`PROPER(text)`	Capitalises the first letter of each word.	Produce the Reactivation Email Name from a customer’s Name.
`CONCAT(a, b, ...)`	Joins several values into a single piece of text.	Combine a customer’s Name and Email Address into one label.
`LEFT(text, n)`	Returns the first n characters.	Take the area code from a Phone Number.
`RIGHT(text, n)`	Returns the last n characters.	Show the last four digits of a Phone Number.
`SUBSTRING(text, start, len)`	Returns a portion of a value, starting where you specify.	Extract the local part of an Email Address.
`CONTAINS(text, search)`	Indicates whether the value contains the text you specify.	Check whether an Email Address is a Gmail account.
`REPLACE(text, old, new)`	Replaces one piece of text with another.	Remove dashes from a Phone Number before messaging.
`LENGTH(text)`	Counts the characters in a value.	Check a Phone Number has the expected number of digits.
`PADLEFT(text, len, char)`	Pads the start of a value to a set length with a chosen character.	Give a short reference code a fixed length with leading zeros.

Math

Math functions perform arithmetic on numbers — rounding, truncating, powers, roots, logarithms, remainders, and combining several numbers into one. Use them to tidy up a calculated figure, such as rounding an average order value to two decimal places, or to turn a customer’s spend and activity into a numeric score.

Function	What it does	Retail use case
`ROUND(num, decimals)`	Rounds to the number of decimal places you specify.	Round average order value to two decimal places.
`CEILING(num)`	Rounds up to the next whole number.	Round a spend figure up to a whole number.
`FLOOR(num)`	Rounds down to the previous whole number.	Round a spend figure down to a whole number.
`ABS(num)`	Returns the value without its minus sign.	Show the size of a spend difference, ignoring its direction.
`POWER(base, exp)`	Raises a number to a power.	Raise a spend figure to a power in a custom score.
`TRUNC(number, digits)`	Cuts off to the given number of decimals, without rounding.	Drop the pennies from a spend figure to show whole units.
`SQRT(num)`	Returns the square root of a number.	Take the square root of a spend figure in a custom score.
`MOD(num, divisor)`	Returns the remainder after division.	Split customers into groups by the remainder of a number.
`LOG(num)`	Returns the natural logarithm of a number.	Take the logarithm of a purchase count in a custom score.
`SUM(a, b, ...)`	Adds two or more numbers together.	Combine a customer’s spend across in-store, online, and app into one total.
`AVG(a, b, ...)`	Returns the average of two or more numbers.	Average a customer’s last three order values.
`MAX(a, b, ...)`	Returns the largest of the numbers given.	Take the highest of a customer’s four quarterly spends.
`MIN(a, b, ...)`	Returns the smallest of the numbers given.	Take the lowest price quoted for an item across channels.
`MEDIAN(a, b, ...)`	Returns the middle value of the numbers given.	Find the typical value among several quoted prices.
`EXP(number)`	Returns e (≈ 2.718) raised to the given number.	Apply exponential time-decay so recent purchases weigh more in a loyalty score.
`LOG10(number)`	Returns the base-10 logarithm of a number.	Compress a wide range of total spend into a smaller, comparable score.

For basic math — addition, subtraction, multiplication, and division — use the operators. For example, 90 - DATEDIFF(TODAY(), MAX({Purchase History.Purchase Date}), "days") gives the days remaining before a customer becomes Lapsed.

Conditional

Conditional functions apply “if this, then that” rules and test the state of a value.

Function	What it does	Retail use case
`IF(condition, true, false)`	Returns one value when the condition is met, and another when it is not. IF statements can be nested to test several conditions in turn.	Assign a customer to a group based on a rule, such as their total spend.
`ISEMPTY(value)`	Returns true when the value is empty.	Find customers with no Email Address on file.
`ISBLANK(value)`	Returns true when the value is empty — an alias for ISEMPTY.	Find customers whose Phone Number is blank.
`ISNOTEMPTY(value)`	Returns true when the value holds content.	Find customers with a Phone Number for SMS.
`ISNULL(value)`	Returns true only when the value is a literal null (never set) — distinct from empty.	Flag records where a field was never populated, rather than cleared.
`ISNUMBER(value)`	Returns true when the value is a number.	Confirm an imported Amount is a number.
`ISTEXT(value)`	Returns true when the value is text.	Confirm a Store Location value is text.
`ISDATE(value)`	Returns true when the value parses as a date.	Confirm an imported Purchase Date is a valid date before using it.
`ISBOOLEAN(value)`	Returns true when the value is a boolean (true/false).	Confirm a yes/no field holds a true/false value.
`BETWEEN(val, min, max)`	Returns true when the value falls within a range.	Check whether a spend value falls within a band.

Note on the overlapping checks: ISBLANK is just another name for ISEMPTY (both true when there’s nothing there), whereas ISNULL is stricter — it’s true only for a real null (a value that was never set), not for an empty string or a cleared field.

Combine conditions with AND, OR, and NOT. For example, DATEDIFF(TODAY(), MAX({Purchase History.Purchase Date}), "days") > 90 AND SUM({Purchase History.Amount}) >= 1000 identifies high-value customers who have lapsed.

Date/Time

Date/Time functions work with dates and the time between them. They return the current date or time, extract part of a date (year, month, weekday, quarter), reformat a date for display, and add, subtract, or measure durations. Use them to work out how long ago something happened — such as the days since a customer’s last purchase — or to build follow-up dates and date-based labels.

Function	What it does	Retail use case
`NOW()`	Returns the current date and time.	Timestamp the moment a value was last calculated.
`TODAY()`	Returns today’s date.	Measure days since a customer’s last purchase (with DATEDIFF).
`CURRENTTIME()`	Returns the current time of day.	Record the time of day a calculation ran.
`TIMESTAMP()`	Returns Unix-epoch seconds for “now”.	Store a machine-readable timestamp for syncing with another system.
`FORMAT(value, pattern)`	Formats a date or number using a pattern.	Show a Purchase Date as a “Mar 2024” label.
`YEAR(date)`	Returns the calendar year.	Find the year of a customer’s first purchase.
`MONTH(date)`	Returns the month number (1–12).	Find the month of a customer’s most recent purchase for seasonality.
`DAY(date)`	Returns the day of the month (1–31).	Find the day of the month a customer last purchased.
`DAYNAME(date)`	Returns the day-of-week name (Monday, …).	Find the weekday of a customer’s last purchase.
`MONTHNAME(date)`	Returns the month name (January, …).	Show the month name of a customer’s first purchase.
`WEEKNUM(date)`	Returns the week of the year (1–53).	Group purchases by week of the year for a weekly report.
`QUARTER(date)`	Returns the calendar quarter (Q1–Q4).	Find the quarter of a customer’s most recent purchase.
`DATEADD(date, amount, unit)`	Adds a duration (days/months/years/hours/minutes/seconds) to a date.	Work out a follow-up date 30 days after the last purchase.
`DATESUBTRACT(date, amount, unit)`	Subtracts a duration from a date.	Find the date 90 days before today to set a “lapsed” cut-off.
`DATEDIFF(end, start, unit)`	Returns the difference between two dates in the chosen unit.	Count the days since a customer’s last purchase.
`YEARDIFF(end, start)`	Returns completed years between two dates (birthday-aware).	Calculate a customer’s age from their date of birth.
`BUSINESSDAYS(start, end)`	Counts the weekdays between two dates.	Count working days since the last order for an SLA.
`DATEISBETWEEN(date, low, high)`	Returns true when low ≤ date ≤ high.	Check whether a purchase fell within a promotion period.

Logical

Logical functions work across a contact’s linked activity records — the individual rows in an activity like Purchase History. They count those records, test whether any of them match a condition, and pull specific values out of them. Use them to answer questions such as “has this customer ever bought X?”, “how many times?”, or “what was the value on their most recent purchase?” — and, with LOOKUP, to fetch a value from another contact in the same Directory.

Function	What it does	Retail use case
`COUNT_ACTIVITY(activity)`	Number of activity records for the contact.	Count how many purchases a customer has made.
`HAS_ACTIVITY(activity)`	True when the contact has at least one activity record.	Flag customers who have ever made a purchase.
`COUNT_ACTIVITY_WHERE(activity, predicate)`	Count activity records matching a predicate.	Count a customer’s purchases over 1,000.
`HAS_ACTIVITY_WHERE(activity, predicate)`	True when any activity record matches a predicate.	Flag customers who have ever bought from the Downtown store.
`ANY_ACTIVITY_MATCH(activity, predicate)`	Alias for HAS_ACTIVITY_WHERE.	Flag customers with any single purchase over 1,000.
`FIRST_ACTIVITY_DATE(activity, field)`	Earliest value of a date field across activity records.	Find the date of a customer’s first purchase.
`LATEST_ACTIVITY_DATE(activity, field)`	Latest value of a date field across activity records.	Find the date of a customer’s most recent purchase.
`ACTIVITY_FIELD_VALUE(activity, field, "LATEST")`	Latest (or first) value of an activity field for the contact.	Get the store of a customer’s most recent purchase.
`ACTIVITY_FIELD_LIST(activity, field, separator)`	Joins every value of an activity field across a contact’s records, in chronological order.	List every product a customer has bought, in order.
`ACTIVITY_FIELD_LIST(activity, field, separator, "DISTINCT")`	As above, but removes duplicate values (case-insensitive) before joining.	List the distinct stores a customer has shopped at, no repeats.
`ACTIVITY_FIELD_AT(activity, field, N)`	The Nth value from a contact’s activity records (1-indexed; negative N counts from most recent).	Get the amount of a customer’s most recent purchase (N = −1).
`ACTIVITY_FIELD_AT(activity, field, N, sortByField, "ASC"\|"DESC")`	The Nth value when sorted by another activity field — enables top-N, largest, or fastest-style extractions.	Get the product from a customer’s single largest purchase (sort by Amount, DESC, N = 1).
`LOOKUP(matchField, matchValue, returnField)`	Looks up a value from another row in this Directory where matchField equals matchValue.	Fetch a referrer’s name using the referrer’s customer ID.

The same functions apply regardless of industry: a hospital might count appointments, a charity might total donations, and a college might measure the days since a student last logged in.

Sogolytics Study: Southwest Leads U.S. Airline CX; Frontier Customers Show Highest Switching Risk

Function Reference

In this article