Custom expressions

Custom expressions are like formulas in spreadsheet software like Excel or Google Sheets. They are the power tools in the notebook editor of the query builder that allow you to ask more complicated questions.

Custom expressions to create filters, metrics, and custom columns

To create a custom expression, create a Custom Column (where the custom expression is used as a Field Formula to calculate values for the new column), or click on Filter or Summarize and select Custom Expression.

When using the query builder, you can use expressions to create new:

• Filters. The expression = contains([comment], "Xtremepush") would filter for rows where the comment field contained the word "Xtremepush".
• Metrics. Also known as summaries or aggregations. = share([Bet Amount] > 50) would return the percentage of bets with stake amount greater than 50 dollars.
• Custom columns. You could use = [Stake Amount] - [Win Amount] to create a new column, which you could name "GGR".

This page covers the basics of expressions. You can check out a full list of expressions.

Types of expressions

There are two basic types of expressions, Aggregations and Functions. Check out a full list of expressions.

Aggregations

Aggregations take values from multiple rows to perform a calculation, such as finding the average value from all values in a column. Aggregations functions can only be used in the Summarize section of the notebook editor, since aggregations use values from all rows for that column.

Functions

Functions, by contrast, do something to each value in a column, like searching for a word in each value (contains function), rounding each value up to the nearest integer (ceil function), and so on.

Basic mathematical operators

Use + (add), - (subtract), * (multiply), / (divide) on numeric columns with numeric values, like integers, floats, and doubles. You can use parentheses, ( and ), to group parts of your expression.

For example, you could create a new column that calculates the difference between the Stake Amount and Win Amount of a bet: = [Stake Amount] - [Win Amount].

Currently, it's not possible to do math on timestamp columns.

Conditional operators

These areAND, OR, NOT, >(greater than), >= (greater than or equal to), < (less than), <= (less than or equal to), = (equal), != (not equal to).

These operators allow, for example, to create a filter for customers from California or Vermont: = [State] = "CA" OR [State] = "VT".

Referencing other columns

You can refer to columns in the current table, or to columns that are linked via a foreign key relationship. Column names should be included within square brackets, like this: [Name of Column]. Columns in connected tables can be referred to using the following syntax: [ConnectedTableName.Column].

📘

Filter expressions and conditionals

Filter expressions must return a Boolean value (something that's either true or false). For example, [Stake Amount] - [Win Amount] > 100.

Working with dates in filter expressions

Dates in filter expressions must be used in the following format: "YYYY-MM-DD" (ISO-8601) enclosed by quotes.

For example:

between([Created At], "2020-01-01", "2020-03-31") OR [Received At] > "2019-12-25"

This expression would return rows where Created At is between January 1, 2020 and March 31, 2020, or where Received At is after December 25, 2019.

List of expressions

See a full list of expressions.