Field calculations and value formatting

A metric or dimension field doesn't have to return the raw value it reads from the API. You can process a field's value in two ways:

• Calculations compute a field's value from other fields — for example dividing clicks by impressions to get a rate. Defined with subfields and calculation.
• Value formatting transforms the value into the shape you want — for example turning a timestamp into a plain date, or rounding a number. Defined with valueFormat.

Both are set on the field itself, and both can be used together. When a field has both, the calculation runs first, then the value formatting is applied to the result.

Field calculations

Calculations let you compute a field's value from other fields instead of reading it directly from the API response. This is useful when the data source doesn't return a value you need — for example a rate the API doesn't provide, but which you can derive from two fields it does return.

A calculation is defined on a metric or dimension field with two parts:

• subfields — the list of fields whose values feed the calculation, in order.
• calculation — an array of operations applied to those subfields, each with an operation and, for some operations, parameters.

How subfields work

The subfields array lists the IDs of the fields used in the calculation. Operations refer to them by position — "subfield 1" is the first entry, "subfield 2" the second, and so on.

For example, a click-through rate metric that divides clicks by impressions lists both as subfields and applies the divide operation:

{
  "id": "ctr",
  "label": "CTR",
  "description": "Click-through rate",
  "dataType": "float.number.ratio",
  "subfields": [
    "clicks",
    "impressions"
  ],
  "calculation": [
    {
      "operation": "divide"
    }
  ]
}

Here divide takes the value of subfield 1 (clicks) and divides it by subfield 2 (impressions).

calculation is an array, so you can chain several operations — each runs in order on the result of the previous one.

Metric operations

Example: divide

{
  "id": "ctr",
  "subfields": [
    "clicks",
    "impressions"
  ],
  "calculation": [
    {
      "operation": "divide"
    }
  ]
}

With clicks = 100 and impressions = 1000, the result is 0.1.

Example: currency_conversion

{
  "id": "cost_usd",
  "subfields": [
    "cost",
    "advertiser_currency",
    "date"
  ],
  "calculation": [
    {
      "operation": "currency_conversion",
      "parameters": [
        "",
        "USD"
      ]
    }
  ]
}

With cost = 100, advertiser_currency = EUR, and date = 2020-01-01, the value is converted from EUR to USD using that date's rate.

Example: math

The math operation runs a sequence of steps. Each step is an object with a type and the parameters that type needs (most commonly sfnum, the subfield number to act on). Available step types include plus, minus, mult, div, get (take the value from a subfield), div_target (divide the running value into the given subfield's value), div_sum_by_sum (sum two groups of subfields and divide one by the other), and replace.

{
  "id": "engagement_rate",
  "subfields": [
    "likes",
    "profile_views",
    "shares"
  ],
  "calculation": [
    {
      "operation": "math",
      "parameters": [
        { "type": "plus", "sfnum": 2 },
        { "type": "plus", "sfnum": 3 },
        { "type": "div_target", "sfnum": 1 }
      ]
    }
  ]
}

With likes = 10, profile_views = 2, and shares = 3, this computes likes / (profile_views + shares) = 10 / 5 = 2.

Dimension operations

Example: concat

{
  "id": "full_name",
  "subfields": [
    "first_name",
    "last_name"
  ],
  "calculation": [
    {
      "operation": "concat",
      "parameters": [
        " "
      ]
    }
  ]
}

With first_name = Jane and last_name = Doe, the result is Jane Doe.

Example: replace_with_subfield_values

{
  "id": "campaign_url",
  "subfields": [
    "campaign_id"
  ],
  "calculation": [
    {
      "operation": "replace_with_subfield_values",
      "parameters": [
        "https://www.example.com/campaign/{sf1}/"
      ]
    }
  ]
}

With campaign_id = 123456, the result is https://www.example.com/campaign/123456/.

Value formatting

Value formatting transforms a field's value before it's returned, using the valueFormat property. This is useful when the raw value isn't in the shape you want — for example converting a timestamp into a plain date, rounding a number, or cleaning up a string.

valueFormat takes an items array, where each item has an operation and, for most operations, parameters. Items run in order, so you can chain several transformations.

{
  "id": "article_creation_date",
  "label": "Ad creation date",
  "dataType": "string.time.date",
  "value": {
    "source": "jsonPath",
    "value": "$.article_creation_date"
  },
  "valueFormat": {
    "items": [
      {
        "operation": "convert_date",
        "parameters": ["U", "Y-m-d"]
      }
    ]
  }
}

When a field also has a calculation, the calculation runs first and the value formatting is applied to its result — so you can, for example, round a derived rate.

Dimension formatting operations

Metric formatting operations

Note: A single operation/parameters pair can also be set directly on valueFormat (without items) — but the items array is the current form and should be used for new configurations.