Formula

Formulas are how you turn raw platform fields into the metrics you actually report on - blended spend, total conversion value across platforms, a cleaned-up campaign name. You write the logic once as a custom field, and it's reusable everywhere in the Data Model.

Find it: Data Sources -> Data Model -> + (Create custom field), or Edit on an existing custom field.

Key terms

• Custom field - A metric or dimension you define yourself with a formula, saved into the Data Model.
• Formula - The expression itself - functions and field references, e.g. SUM(googleAds.spend, facebookAds.spend).
• Function - A named operation inside a formula - SUM, IF, COALESCE, CONCATENATE, etc.
• Operand - Whatever goes inside a function's parentheses - a field reference, a number, quoted text, or a nested function.
• Kind - Metric (a number) or Dimension (text/category). You pick this on the field; it has to match the functions you used.
• Field-level aggregation - The Sum/Average/Minimum/Maximum/Count/Count distinct setting on the custom field itself, controlling how it rolls up when charted. Separate from any SUM/AVG written inside the formula - see below.

How a formula becomes a working field

1. You write an expression using field names and functions - or describe what you want and let AI draft it.
2. On save, Jepto checks the formula: balanced parentheses, a shape it can parse, functions that match the Kind you chose, and field references that exist and are the right type. Anything broken is rejected before it reaches the catalog.
3. The formula is stored as part of the field's definition - not run once and cached as a fixed number, but re-evaluated fresh every time the field is used in a chart, against whatever date range and breakdown that chart is using.
4. Because it's stored once and referenced everywhere, fixing the formula later updates every report using that field automatically.

Two ways to build a formula

• Describe it in plain language, let AI draft it - "Total spend across all my ad platforms" - fast first draft
• Write it manually - Fine control, or tweaking an AI draft

Both produce the same thing: a formula string you can keep editing by hand. AI drafts are a starting point - always read the result before saving. AI may use any supported function below (including ones not listed in autocomplete).

Anatomy of a formula

Fields are referenced as source.field - for example googleAds.spend or facebookAds.spend. Start typing and the editor autocompletes from the fields available to this client. You can also reference another custom field; the editor matches on its label but inserts its custom_... id.

Functions wrap around one or more operands:

SUM(googleAds.spend, facebookAds.spend, tiktokAds.spend)

Text goes in quotes, numbers are bare:

CONCATENATE(googleAds.campaignName, " - ", googleAds.adGroupName)

The shape a formula has to take

A formula is one outer function call wrapping its operands, or a plain arithmetic / comparison expression. So all of these are fine:

SUM(googleAds.spend, facebookAds.spend)
googleAds.spend / googleAds.clicks
IF(googleAds.clicks > 0, googleAds.spend / googleAds.clicks, 0)


Operands can be field references, numbers, quoted text, arithmetic, comparisons, or a nested function - and arithmetic can combine function calls:

SUM(googleAds.spend, facebookAds.spend) / SUM(googleAds.clicks, facebookAds.clicks)

Metrics vs. dimensions

The functions you use determine the field's Kind:

Picking the wrong Kind is the most common save error. Jepto names it explicitly, e.g.:

This field is set to Metric, but CONCATENATE only works on Dimension fields. Switch the type to Dimension below, or use a metric function such as SUM or AVG.

It also checks the fields you referenced, not just the functions - passing a text dimension into SUM, or a numeric metric into CONCATENATE, is rejected with the field's own name in the message.

Function reference

These are the spreadsheet-style functions Jepto's formula engine supports (case-insensitive). The Formula Builder autocomplete lists the most common ones (SUM, AVG, IF, CONCATENATE, TITLECASE, UPPERCASE, LOWERCASE, JSON_STRUCT); the rest can be typed manually or generated by AI.

Aggregation

Control flow (CASE / IF / COALESCE)

There is no separate CASE(...) function name in expressions. Conditional branching is written with IF (which compiles to case/when logic under the hood). Use nested IF for multi-branch CASE-style grouping. Use COALESCE when you want the first non-null value.

Nested IF (CASE-style buckets):

IF(REGEXP_MATCH(googleAds.campaignName, "summer_.*"), "Summer", IF(REGEXP_MATCH(googleAds.campaignName, "brand_.*"), "Brand", "Other"))

Safe math and numeric helpers

Text

Regex (REGEXP_*)

Patterns use RE2-style regex. The pattern (and replacement, where used) must be quoted string literals.

Examples:

REGEXP_CONTAINS(googleAds.landingPage, "/blog/")
REGEXP_MATCH(googleAds.campaignName, "summer_.*")
REGEXP_EXTRACT(googleAds.url, "utm_campaign=([^&]+)")
REGEXP_REPLACE(googleAds.url, "[?&].*", "")


REGEXP_CONTAINS vs REGEXP_MATCH: contains looks for a substring match; match requires the entire value to match the pattern.

REPLACE vs REGEXP_REPLACE: REPLACE does a literal substring swap; REGEXP_REPLACE uses a regex pattern.

JSON

JSON_STRUCT rules: the first operand must be a source.field reference to a structured field. The path must be a quoted JSONPath starting with $. - e.g. '$.placeId'. Property chaining is also supported in some forms, e.g. JSON_STRUCT(field).name or JSON_STRUCT(field).items[0].

Dates

DATE_DIFF units (string literal third argument): day / days, month / months, year / years.

Operators

Ratios don't need a dedicated function:

googleAds.conversionValue / googleAds.spend

For division that should return null when the divisor is zero, prefer SAFE_DIVIDE over plain /.

Worked examples

Blended spend across platforms

SUM(googleAds.spend, facebookAds.spend, tiktokAds.spend)

Kind: Metric. Format: Currency. Aggregation: Sum.

Blended ROAS across platforms

SUM(googleAds.conversionValue, microsoftAds.conversionValue) / SUM(googleAds.spend, microsoftAds.spend)

Total conversion value from both platforms divided by total spend from both. Kind: Metric. Format: Decimal. Aggregation: Average - a ratio shouldn't be summed across rows.

Cost per conversion, safe against zero conversions

SAFE_DIVIDE(googleAds.spend, googleAds.conversions)

Or with an explicit fallback:

IF(googleAds.conversions > 0, googleAds.spend / googleAds.conversions, 0)

Fill missing values with COALESCE

SUM(googleAds.clicks, COALESCE(googleAnalytics4.sessions, 0))

A combined campaign label

CONCATENATE(googleAds.campaignName, " | ", googleAds.adGroupName)

Kind: Dimension. Useful when you want campaign and ad group on one table axis instead of two columns.

Pull a value out of a Business Profile struct

JSON_STRUCT(googleBusinessProfile.metadata, '$.placeId')

Business Profile stores several fields as structured objects; JSON_STRUCT reaches in and pulls out a single scalar. Kind: Dimension.

CASE-style campaign buckets with nested IF

IF(REGEXP_MATCH(googleAds.campaignName, "summer_.*"), "Summer", IF(REGEXP_MATCH(googleAds.campaignName, "brand_.*"), "Brand", "Other"))

Kind: Dimension.

Settings that go with the formula

Saving a custom field takes more than the expression:

• Name - required. This is the label everyone sees in field pickers.
• Description - optional, but it becomes the tooltip in every picker. Write it for the next person.
• Kind - Metric or Dimension.
• Type / format - Metric fields only, and required: Number, Decimal, Currency, or Percentage. There are presets (Currency - $1,234.56, Percentage - 5.23%, Whole number - 1,234, Compact - 1.2M) plus precision and currency controls, with a live preview of how a value will render. Dimension fields are always plain text.
• Aggregation - Metric fields only, and required: Sum, Average, Minimum, Maximum, Count, or Count distinct. Defaults to Sum.
• Enable for All Clients - see Scope.

The one thing that trips people up: aggregation order

Formula functions run after the underlying metrics are already aggregated for the report period - not row by row. SUM(googleAds.spend, facebookAds.spend) doesn't add up every row in the raw data; it adds the two platforms' already-aggregated totals for whatever date range and breakdown that chart is using.

This matters most for rate metrics. Don't build "average CTR" by averaging per-day CTRs - build it as total clicks divided by total impressions:

SUM(googleAds.clicks, microsoftAds.clicks) / SUM(googleAds.impressions, microsoftAds.impressions)

An average of daily CTRs treats a low-traffic day and a high-traffic day as equally important; a true weighted rate doesn't.

Field-level aggregation vs. in-formula aggregation

These are two different settings and it's easy to conflate them:

• In-formula aggregation - the SUM/AVG/MIN/MAX you write inside the expression, combining several source fields (e.g. spend across three platforms).
• Field-level Aggregation - the dropdown on the custom field itself (Metric fields only), which controls how the saved field's result rolls up when it's grouped by a dimension on a chart (e.g. summed per campaign vs. averaged per day).

A formula can use SUM internally and still have its field-level Aggregation set to Average, if that's the correct way to roll the result up. Ratios almost always want Average; totals want Sum.

Avoid divide-by-zero

Plain / produces a broken or blank value when the denominator is zero - common with ROAS or CPA on a slow day with no conversions. Prefer SAFE_DIVIDE, or wrap the division in IF:

SAFE_DIVIDE(googleAds.spend, googleAds.conversions)
IF(googleAds.conversions > 0, googleAds.spend / googleAds.conversions, 0)


Referencing other custom fields

A formula can reference another custom field, so you can build a "blended spend" field once and reuse it inside a "blended CPA" field. Type the field's name and pick it from autocomplete - the editor inserts its custom_... id.

Circular references (A refers to B, B refers back to A) are detected and rejected on save.

Scope

Custom fields can be scoped to a single client, or switched to Enable for All Clients to make them available account-wide. Once a field is global it can't be moved back to a single client - Jepto blocks the toggle with:

You cannot change a global card field to client-scoped.

If you need a one-off variant later, create a new client-scoped field instead.

Common validation errors