> ## Documentation Index
> Fetch the complete documentation index at: https://docs.extole.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Custom Data Queries using Extole Reports

> A complete reference for Extole's report mapping expression language — mappings, filters, sort order, and every available function.

# Overview

Extole's configurable reporting framework lets you define exactly what data a report returns, how it is shaped, and which rows are included — all through a mapping expression language passed as report parameters. This document is a complete reference for that language.

You set these expressions in the Advanced section of a report's configuration screen — see [Advanced Report Configuration](/guides/dashboards-and-reporting/configuring-reports/advanced-report-configuration). To pick which report type to run, see [Configurable Report Types and Their Entities](/guides/dashboards-and-reporting/configuring-reports/configurable-report-types-and-their-entities); for the fields each entity exposes, see [Entities and Context Available in Extole's Configurable Reporting System](/guides/dashboards-and-reporting/configuring-reports/entities-and-context-available-in-extole-s-configurable-reporting-system). If a pre-built report already answers your question, check [Report Types](/guides/dashboards-and-reporting/report-types/index) first.

# Glossary

| Term                 | Description                                                                            |
| -------------------- | -------------------------------------------------------------------------------------- |
| **Data source**      | The primary entity loaded per row (step record, input record, reward event, etc.)      |
| **Mappings**         | Semicolon-separated column definitions that specify column name and value expression   |
| **Filters**          | Boolean expressions that exclude rows from the result                                  |
| **Sort order**       | Comma-separated `ascending()`/`descending()` expressions                               |
| **Dimension column** | A column whose raw value is preserved per row (not aggregated)                         |
| **Grouping column**  | A column that performs an aggregation; only valid in metrics report types              |
| **Records report**   | Lists individual entities; one row per record (Events, Input Records, Person, Rewards) |
| **Metrics report**   | Groups and counts records; one row per unique combination of dimension values          |

# Mapping Syntax

## Column Definition

`ascending()`

Mappings are a semicolon-separated list of `name=expression` pairs. The column name appears as the header in the report output.

`descending()`

## Hidden Columns

Prefix a column name with `hidden(...)` to compute an intermediate value without including it in the output. Hidden columns can be referenced by name in subsequent expressions.

`name=expression`

## Accessing Event Properties

Direct property access on the primary record:

`hidden(...)`

For deeply nested keys that contain dots in their name, wrap the key in double quotes:

`==`

# Filter Syntax

Filters are semicolon-separated boolean expressions. All entries must be satisfied (implicit AND between statements).

`!=`

**Operators:** `==`, `!=`, `>=`, `<=`, `>`, `<`, `like`, `not like`

**Values** are quoted strings or column references:

`>=`

Multiple filters:

`<=`

Null checks:

`>`

Within a `collection(...)` filter parameter, filters use `and`/`or`:

`<`

# Sort Order

Comma-separated sort expressions applied in order:

`like`

# Functions Reference

## Attribute Access

### ATTRIBUTE / Direct Access

Returns a raw property from the event record. The `ATTRIBUTE()` wrapper is optional.

`not like`

### PROPERTY

Extracts a named field from a JSON string value.

**Parameters:**

* `(anonymous)` — expression evaluating to a JSON string

**Returns:** String

`collection(...)`

### TO\_JSON

Converts a column value to its JSON string representation. Useful for text-based searches on complex objects.

**Parameters:**

* `(anonymous)` — column to serialize

**Returns:** String

**Limitation:** requires a sub-field; `to_json(event)` is not supported.

`and`

## Scalar / Formatting Functions

### BOOLEAN\_FORMAT

Evaluates a condition and returns one of two values.

**Parameters:**

* `(anonymous, 1st)` — condition expression
* `(anonymous, 2nd)` — value when condition is true
* `(anonymous, 3rd)` — value when condition is false

`or`

### NUMBER\_FORMAT

Formats a numeric value to decimal with scale 2.

**Parameters:**

* `(anonymous)` — column with numeric value

`ATTRIBUTE()`

### DATE\_FORMAT

Formats a timestamp value.

**Parameters:**

* `(anonymous)` — column with timestamp
* `format` *(optional)* — date/time pattern string; defaults to `ISO_OFFSET_DATE_TIME`; special value `epoch` returns milliseconds since Unix epoch
* `timezone` *(optional)* — timezone to apply

`(anonymous)`

### DURATION\_FORMAT

Formats a duration value. Parameters align with `DATE_FORMAT`.

### DEFAULT

Returns the base expression if non-null; otherwise returns the fallback.

**Parameters:**

* `(anonymous)` — primary expression
* `value` — fallback if primary is null

`(anonymous)`

### CONSTANT

Defines a literal constant value.

`to_json(event)`

### CONCATENATE

Concatenates up to 256 values in sequence.

**Parameters:**

* `(anonymous, 0..255)` — values to join

`(anonymous, 1st)`

### CONCATENATE\_COLLECTION

Joins all elements of a collection into a single string.

**Parameters:**

* `(anonymous)` — collection expression
* `separator` *(optional, default: `,`)* — delimiter
* `unique` *(optional, default: `false`)* — `"true"` deduplicates values

`(anonymous, 2nd)`

### REPLACE

Replaces occurrences of a pattern within a string.

**Parameters:**

* `(anonymous)` — source string
* `search` — pattern to find
* `replacement` — replacement string

`(anonymous, 3rd)`

### SPLIT

Transforms a delimited string into an array.

**Parameters:**

* `(anonymous)` — source string
* `separator` *(optional, default: `,`)* — delimiter

`(anonymous)`

## Date / Period Functions

### START\_DATE

Maps a timestamp to the start of the period it falls in.

**Parameters:**

* `(anonymous)` — timestamp column
* `period` — one of: `NONE`, `DAY`, `WEEK`, `MONTH`, `QUARTER`, `YEAR`, `TRAILING_WEEK`, `TRAILING_TWO_WEEKS`, `TRAILING_THREE_WEEKS`, `TRAILING_MONTH`

`(anonymous)`

### END\_DATE

Maps a timestamp to the end of the period it falls in. Same parameters as `START_DATE`.

`format`

### NOW

Returns the current timestamp at report execution time.

`ISO_OFFSET_DATE_TIME`

## Entity Lookup Functions

These functions join to external entities by ID and expose their properties via dot notation.

### PERSON

Loads the full person profile.

**Parameters:**

* `(anonymous)` — column with person ID

**Returns:** `api.Person` properties

`epoch`

Key Person properties: `id`, `email`, `normalizedEmail`, `firstName`, `lastName`, `locale`, `data`, `steps`, `rewards`, `friends`, `advocates`, `shareables`, `journeys`, `audienceMemberships`, `recentRequestContexts`.

### CAMPAIGN

Loads the latest state of a campaign.

**Parameters:**

* `(anonymous)` — column with campaign ID

**Returns:** `api.BuiltCampaign` properties

`timezone`

Key Campaign properties: `id`, `name`, `campaignName`, `description`, `state`, `version`, `programLabel`, `tags`, `steps`, `rewardRules`.

### CAMPAIGN\_SUMMARY

Builds an aggregated campaign state from all known client change events.

**Parameters:**

* `(anonymous)` — column with campaign ID

**Returns:** `api.CampaignSummary` properties

`DATE_FORMAT`

### CLIENT

Loads the client model object. The join column is optional; defaults to the event's client.

**Parameters:**

* `(anonymous, optional)` — column with client ID

**Returns:** `api.Client` properties

`(anonymous)`

### CLIENT\_PROPERTIES

Loads the property map associated with the current client. No join column required.

**Returns:** name/value map; access specific properties by name

`value`

### CLIENT\_VERTICAL

Shortcut to extract a vertical attribute from a client.

`(anonymous, 0..255)`

### EVENT

Loads a specific consumer event by ID.

**Parameters:**

* `(anonymous)` — column with consumer event ID

**Returns:** `api.ConsumerEvent` (or subtype: `StepConsumerEvent`, `RewardConsumerEvent`, `InputConsumerEvent`)

`(anonymous)`

Key ConsumerEvent properties: `id`, `rootEventId`, `type`, `eventTime`, `person`, `data`, `sandbox`, `clientContext`.

Additional StepConsumerEvent properties: `name`, `stepName`, `campaignId`, `personId`, `visitType`, `quality`, `attribution`.

### REWARD

Combines all known reward events for a given reward ID into a single summary.

**Parameters:**

* `(anonymous)` — column with reward ID

**Returns:** `api.RewardSummary` properties

`separator`

Key RewardSummary properties: `id`, `currentState`, `faceValue`, `supplierId`, `rewardType`, `partnerRewardId`, `email`, `data`. States: `EARNED`, `FULFILLED`, `SENT`, `REDEEMED`, `FAILED`, `CANCELED`, `REVOKED`.

### REWARD\_SUPPLIER

Loads the reward supplier model.

**Parameters:**

* `(anonymous)` — column with reward supplier ID

**Returns:** `api.RewardSupplier` properties

`,`

### STEP\_RECORD

Finds the step record for a given step consumer event.

**Parameters:**

* `(anonymous)` — column with step event ID
* `step_name` *(required)* — column with the step name
* `event_time_name` *(required)* — column with the step event time

**Returns:** `api.StepRecord` properties

`unique`

Key StepRecord properties: `id`, `clientId`, `eventTime`, `requestTime`, `personId`, `name`, `deviceType`, `attribution`, `visitType`, `quality`, `data`.

### INPUT\_RECORD

Finds the input record corresponding to an input consumer event.

**Parameters:**

* `(anonymous)` — column with input consumer event ID
* `event_name` *(optional)* — column with event name; omit to match any name
* `event_time_name` *(required)* — column with event time

**Returns:** `api.InputRecord` properties

`false`

Key InputRecord properties: `id`, `clientId`, `eventTime`, `personId`, `name`, `locale`, `apiType`, `appType`, `deviceType`, `userAgent`, `data`.

### DEVICE\_TYPE

Parses a user agent and returns a device/browser/OS classification.

**Parameters:**

* `(anonymous, optional)` — consumer event ID (function joins to the full event to extract user agent)
* `user_agent` *(optional)* — explicit user agent string; use instead of the anonymous event ID
* `mode` *(optional)* — one of:
  * `DEVICE_TYPE` *(default)* — Mobile, Desktop, Other
  * `VERSIONED_DEVICE_TYPE` — e.g. Apple iPhone iOS 17.4.1, Desktop Mac OS X 10.15.7
  * `DETAILED_DEVICE_TYPE` — UNKNOWN, ROBOT, MOBILE, PHONE, DESKTOP, ROBOT\_MOBILE, TABLET, TV
  * `BROWSER_TYPE` — e.g. Safari, Chrome, Edge, DuckDuckGo
  * `OS_TYPE` — e.g. Android, iOS, Linux, Tizen

`"true"`

## Collection Functions

### COLLECTION

Filters and optionally extracts fields from a collection.

**Parameters:**

* `(anonymous)` — source collection expression
* `filter` *(optional, repeatable)* — filter predicate; use `and`/`or` within one `filter:` parameter, or supply multiple separate `filter:` parameters (all must match)
* `extracting` *(optional)* — extract a single field from each element

`(anonymous)`

Multiple separate `filter:` arguments (implicit AND between them):

`search`

### COUNT

Counts all elements in a collection. Used in records reports (not metrics); wraps a `COLLECTION` expression.

`replacement`

### COUNT\_DISTINCT

Counts distinct values of the extracted field across a collection. Used in records reports (not metrics).

`(anonymous)`

### PERSON\_COLLECTION

Paginated, memory-efficient collection extraction from a person profile. Preferred over `COLLECTION` for person data.

**Parameters:**

* `(anonymous)` — person ID (must come from `person(id).id` to ensure identity resolution)
* `collection` *(required)* — one of: `steps`, `rewards`, `friends`, `advocates`, `shareables`, `journeys`, `audience_memberships`, `request_contexts`, `shares`, `data`
* `filter` *(optional)* — filter predicate
* `extracting` *(optional)* — field to extract from each element
* `reduce` *(optional)* — aggregation to apply: `sum`, `count`, `min`, `max`

`separator`

### FIRST

Returns the first element of a collection, optionally after sorting.

**Parameters:**

* `(anonymous)` — source collection
* `sortBy` *(optional)* — field to sort by before selecting
* `(anonymous, 2nd)` — sub-property to extract from the selected element

`,`

### LAST

Returns the last element of a collection, optionally after sorting. Same parameters as `FIRST`.

`(anonymous)`

### INDEX

Returns the element at a specific position in a collection.

**Parameters:**

* `(anonymous)` — source collection
* `index` *(required)* — zero-based integer index
* `sortBy` *(optional)* — field to sort by before indexing

`period`

### EXPLODE

Expands an array into multiple rows — one row per element.

**Parameters:**

* `(anonymous)` — array expression

`NONE`

## Aggregation Functions (Metrics Reports Only)

These functions are only valid in metrics report types (e.g. `CONFIGURABLE_EVENT_METRICS`, Input Record Metrics, etc.).

### Advocate vs. Friend Funnel — event.personId Identity

`GROUP_*` functions load events matching the given `step_name`. The meaning of `event.personId` on those events depends on which side of the referral funnel the step belongs to. **Which steps belong to which funnel side is determined by the journey configuration in the campaign** — advocate-journey steps record the advocate as `event.personId`, friend-journey steps record the friend.

| Funnel side      | Example steps                          | `event.personId` represents |
| ---------------- | -------------------------------------- | --------------------------- |
| Advocate journey | `shared`, `email_sent`                 | The advocate (Advocate ID)  |
| Friend journey   | `share_clicked`, `converted`, `signup` | The friend                  |

If you want to measure advocate activity using a **friend-funnel step** (e.g. how many share clicks each advocate generated), group by `event.data.related_person_id`, not `event.personId`:

`DAY`

If you mix advocate-side and friend-side steps in the same report, use `BOOLEAN_FORMAT` to pick the correct person ID conditionally:

`WEEK`

Pulling `GROUP_COUNT_DISTINCT(event.personId, step_name:"shared")` and expecting it to represent advocates is correct — `shared` is an advocate-funnel step, so `event.personId` is the advocate. But pulling `GROUP_COUNT_DISTINCT(event.personId, step_name:"share_clicked")` gives you a count of unique friends, not advocates. Mix the two step names without accounting for this and the person IDs represent different populations in each column.

All aggregation functions share a common set of optional filter parameters in addition to their required ones:

| Parameter     | Description                                                                                                                                                       |
| ------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `step_name`   | Step name to match (use `"ALL"` for any step; required for metrics on step records)                                                                               |
| `name`        | Input event name (required for input record metrics)                                                                                                              |
| `names`       | Comma-separated list of input event names                                                                                                                         |
| `audience_id` | Audience ID filter                                                                                                                                                |
| `attribution` | `ALL`, `ATTRIBUTED`, `UNATTRIBUTED`                                                                                                                               |
| `quality`     | `ALL`, `HIGH`, `LOW`, `NONE`                                                                                                                                      |
| `visit_type`  | `ALL`, `NEW_TO_CLIENT`, `NEW_TO_PROGRAM`, `NEW_TO_CAMPAIGN`, `NORMAL`                                                                                             |
| `channel`     | Channel filter string                                                                                                                                             |
| `source`      | Source filter string                                                                                                                                              |
| `time_range`  | `CURRENT`, `ALL_TIME`, `SAME_PERIOD_PRIOR_TIME_RANGE`, `SAME_PERIOD_PRIOR_YEAR`, `SAME_PERIOD_PRIOR_QUARTER`, `SAME_PERIOD_PRIOR_MONTH`, `SAME_PERIOD_PRIOR_WEEK` |

### GROUP\_COUNT

Counts the number of records matching the group.

`MONTH`

### GROUP\_COUNT\_DISTINCT

Counts distinct values of the given expression within the group.

`QUARTER`

### GROUP\_SUM

Sums the values of the expression within the group.

`YEAR`

### GROUP\_AVG

Averages the values of the expression within the group.

`TRAILING_WEEK`

### GROUP\_MIN

Returns the minimum value within the group.

`TRAILING_TWO_WEEKS`

### GROUP\_MAX

Returns the maximum value within the group.

`TRAILING_THREE_WEEKS`

### GROUP\_FIRST

Returns the first occurrence of the expression within the group, ordered by event time.

`TRAILING_MONTH`

### GROUP\_LAST

Returns the last occurrence of the expression within the group.

`START_DATE`

### GROUP\_CONCATENATE

Concatenates all values of the expression within the group.

`(anonymous)`

## Arithmetic & Rate Functions

### SUM

Adds two expressions.

`api.Person`

### SUBTRACT

Subtracts the second expression from the first.

`id`

### RATE

Calculates a rate: value / denominator.

`email`

### PERCENTAGE

Calculates a percentage: (value / total) \* 100.

`normalizedEmail`

### BENCHMARK

Compares a rate to a benchmark threshold.

`firstName`

## Utility Functions

### MAP\_DIMENSION

Maps a value to a dimension definition stored in the platform.

**Parameters:**

* `(anonymous)` — source value expression
* `dimension_name` — name of the dimension
* `program_label` — program label expression

`lastName`

### REPORT\_PARAMETERS

Provides access to dynamic parameters passed at report execution time. Useful for parameterized templates.

`locale`

### GEO\_IP

Extracts geo-location data from an IP address.

`data`

### SUPPORT

Provides access to support-related metadata for the event's client.

`steps`

### RISK\_VALUE

Extracts a risk signal value.

`rewards`

# Enums Reference

## Attribution

Controls which events are included based on attribution status.

| Value          | Description                        |
| -------------- | ---------------------------------- |
| `ALL`          | Attributed and unattributed events |
| `ATTRIBUTED`   | Only events with attribution       |
| `UNATTRIBUTED` | Only events without attribution    |

## Visit Type

Segments events by whether the person is new to a given scope.

| Value             | Description                  |
| ----------------- | ---------------------------- |
| `ALL`             | All visit types              |
| `NEW_TO_CLIENT`   | First visit to this client   |
| `NEW_TO_PROGRAM`  | First visit to this program  |
| `NEW_TO_CAMPAIGN` | First visit to this campaign |
| `NORMAL`          | Returning visitor            |

## Quality

Filters events by conversion quality signal.

| Value  | Description              |
| ------ | ------------------------ |
| `ALL`  | All quality levels       |
| `HIGH` | High-quality conversions |
| `LOW`  | Low-quality conversions  |
| `NONE` | No quality signal        |

## Time Period (for START\_DATE / END\_DATE)

| Value                  | Description           |
| ---------------------- | --------------------- |
| `NONE`                 | No period bucketing   |
| `DAY`                  | Calendar day          |
| `WEEK`                 | Calendar week         |
| `MONTH`                | Calendar month        |
| `QUARTER`              | Calendar quarter      |
| `YEAR`                 | Calendar year         |
| `TRAILING_WEEK`        | Rolling 7-day window  |
| `TRAILING_TWO_WEEKS`   | Rolling 14-day window |
| `TRAILING_THREE_WEEKS` | Rolling 21-day window |
| `TRAILING_MONTH`       | Rolling 30-day window |

## Column Time Range (for Aggregation Functions)

| Value                          | Description                          |
| ------------------------------ | ------------------------------------ |
| `CURRENT`                      | Within the report's time range       |
| `ALL_TIME`                     | All available history                |
| `SAME_PERIOD_PRIOR_TIME_RANGE` | Same duration, immediately preceding |
| `SAME_PERIOD_PRIOR_YEAR`       | Same period one year ago             |
| `SAME_PERIOD_PRIOR_QUARTER`    | Same period one quarter ago          |
| `SAME_PERIOD_PRIOR_MONTH`      | Same period one month ago            |
| `SAME_PERIOD_PRIOR_WEEK`       | Same period one week ago             |

## Person Collection Names (for PERSON\_COLLECTION)

| Value                  | Description                        |
| ---------------------- | ---------------------------------- |
| `steps`                | Person's step records              |
| `rewards`              | Person's rewards                   |
| `friends`              | Friends referred by this person    |
| `advocates`            | Advocates who referred this person |
| `shareables`           | Shareable links/content            |
| `journeys`             | Journey memberships                |
| `audience_memberships` | Audience segment memberships       |
| `request_contexts`     | Recent request context records     |
| `shares`               | Share events                       |
| `data`                 | Person data entries                |

# Recipes

## Count Events by Step (Metrics Report)

`friends`

## Traffic Funnel With Attribution Filters

`advocates`

## Count Input Events by Name, App Type, and API Type

`shareables`

## Daily Bucketing

`journeys`

## Person Details With Lookup

`audienceMemberships`

## Conditional Column (Boolean Format)

`recentRequestContexts`

## Add N Days Offset to a Timestamp (e.g. +7 Days)

`(anonymous)`

## Extract Device Type From Input Record

`api.BuiltCampaign`

## Count Steps on a Person Profile

`id`

## Sum Total Rewards Issued

`name`

## Cross-Client API Type Breakdown (Run From Extole Account)

Report parameters:

* `target_client_ids`: `ALL_CLIENTS`
* `filters`: `client(event.clientId).clientType=="CUSTOMER"`

`mappings`:

`campaignName`

## Check if a Referral Exists on a Person (With Hidden Intermediate)

`description`

## Compare a Step Date Against a Rolling 45-Day Window

`state`

## Parameterized Period (Using REPORT\_PARAMETERS)

`version`

## Client Vertical

`programLabel`

## Null-Safe Data Extraction

`tags`

## Null Check Filter

Column `Has_Share_Click` must not be absent:

`steps`

## Salesforce Account ID

`rewardRules`

## Count Steps on a Person Profile (Records Report)

Use `COUNT_DISTINCT(COLLECTION(...))` in records reports where `PERSON_COLLECTION` is not available:

`(anonymous)`

## Count Targeted Campaigns Across Multiple Step Names

Counts distinct campaigns touched by steps matching any of several step names:

`api.CampaignSummary`

## Count All Events (Any Step)

`(anonymous, optional)`

## Rate as a Fraction (e.g. 30%)

Pass `"3.333333333333"` as the denominator to compute 30%:

`api.Client`

## Input Record Metrics — Count by Name, App Type, and API Type

`(anonymous)`

## Input Record Metrics — Count a Specific Input Event per Person per Day

`api.ConsumerEvent`

## Metrics — Shares and Conversions per Person

`StepConsumerEvent`