Joining data

Often reports map cleanly to a single API endpoint. However, sometimes the information you want is spread across several endpoints – for example, one endpoint returns a list of campaigns and another returns the performance metrics for each campaign. To deliver this in a single report, you can join data from one or more other report types into it. The parent report supplies the base rows, and each joined report contributes additional fields to those rows.

Data joining happens in the background. The joined reports are usually marked as hidden so the user doesn't see them in the connector – the joined reports add fields to the parent's result and aren't intended to be selected on their own. The end user only ever sees the parent report, with the joined fields appearing alongside its own on each row. A connector can have several visible parent reports, each with its own set of hidden joined reports.

Example use cases for data joining:

• The data you need for your report type is returned by more than one endpoint.
  • A summary endpoint returns list of IDs that you want to look fetch details (such as performance stats) for from another endpoint.
• You want to chain related data across multiple endpoints (for example, campaign → line item → daily stats).

Configuration overview

Joining is defined inside a report type's decoration object as an array of relations. Each relation describes one join: which report type to fetch from, how to match rows between the two, and how the child request should behave.

Linking rows

The link object defines how a parent row is matched to a child row. Two patterns are possible, depending on whether the child report can be fetched independently or has to be queried per parent:

• Match on a shared field – the parent and child reports both return the same field (typically an ID). Set parent to the field ID on the parent report and child to the field ID on the child report. The connector fetches the child report's full result and joins rows where the values match.
• Fetch per parent ID – the child endpoint requires the parent's ID in its URL or parameters. Set only parent (no child); the connector fires a child request for each parent row, or for each batch of rows, passing the value in through placeholder {{query.parent_id}} or {{query.batch.parent_ids}}.

Note: The parent field must be defined in the parent report type's dimensions, even if it is hidden ("isVisible": false). It is what carries the join value through to the child request.

Join types

One-to-one ("type": "normal")

A one-to-one join is a lookup: for each row in the parent, the matching row from the child is added to the result. The child report typically returns all candidate rows in a single request, and rows are matched by field value (parent to child, or by fetching child report with parent included in the request).

Use the normal join type when you want to join data in one-to-one relation, so for example when your parent report returns an ID, and you want to enrich each row with the details for that ID – for example, a campaign row that needs the performance statistics attached.

Below you can find a template for a normal join:

"decoration": {
  "relations": [
    {
      "reportTypeId": "[child report type ID]",
      "type": "normal",
      "link": {
        "parent": "[parent field ID]",
        "child": "[child field ID]"
      },
      "useDates": true,
      "includeMetrics": true,
      "inheritDecorations": true
    }
  ]
}

Below you can find an annotated example from the Basis connector, where the campaign report joins each campaign row with its brand details:

"decoration": {
  "relations": [
    {
      "reportTypeId": "brand",
      "type": "normal",
      "link": {
        "parent": "campaign_brand_id",
        "child": "brand_id"
      },
      "useDates": true,
      "includeMetrics": true,
      "inheritDecorations": true
    }
  ]
}

In this example:

• reportTypeId points at the brand report type, which fetches the full brand list from /v1/brands.
• link.parent (campaign_brand_id) is the brand ID carried on each campaign row.
• link.child (brand_id) is the matching field on each brand row.
  • It is advised to mark the child field as hidden (with "isVisible": false to avoid displaying duplicate fields.

Each campaign row in the result is enriched with the brand whose brand_id matches that row's campaign_brand_id.

One-to-many ("type": "inverse")

An inverse join makes a separate child request for each parent ID, then attaches the results back to the parent. Use this when the child endpoint requires an ID in its URL or parameters and returns its results scoped to that single ID – typical for per-resource performance endpoints, daily breakdowns, or sub-entity lists.

The child report's request can insert the current parent ID using the {{query.parent_id}} placeholder.

Below you can find a template for an inverse join:

"decoration": {
  "relations": [
    {
      "reportTypeId": "[child report type ID]",
      "type": "inverse",
      "link": {
        "parent": "[parent field ID]"
      },
      "useDates": true,
      "includeMetrics": true,
      "inheritDecorations": false
    }
  ]
}

The child report references the parent ID in its request:

{
  "id": "[child report type ID]",
  "config": {
    "isVisible": false
  },
  "request": {
    "url": "https://api.example.com/resources/{{query.parent_id}}/performance"
  }
}

Below you can find an annotated example from the Ignite connector, where each campaign is joined with its performance time series:

"decoration": {
  "relations": [
    {
      "reportTypeId": "campaign_performance",
      "type": "inverse",
      "useDates": true,
      "includeMetrics": true,
      "inheritDecorations": false,
      "link": {
        "parent": "campaign_id"
      }
    }
  ]
}

The decorated campaign_performance report type injects the parent campaign ID into its URL:

{
  "id": "campaign_performance",
  "config": {
    "isVisible": false
  },
  "request": {
    "url": "https://api.realestate.com.au/campaign/v1/display-performance/{{query.parent_id}}"
  }
}

For each campaign returned by the parent report, the connector calls /display-performance/<campaign_id> and merges the returned rows back into that campaign.

Note: Inverse joins fire one child request per parent row, which can produce a large number of API calls when the parent has many rows. If the child endpoint accepts multiple IDs per request, configure batching to send them together.

Batching child requests

Batching sends a group of parent IDs to the child endpoint in a single request, instead of one request per parent row. Use it whenever the child endpoint accepts a list of IDs as a parameter or request body field – it cuts down on API calls and helps avoid rate limits.

Inside the child request, refer to the batch of IDs through the {{query.batch.parent_ids}} placeholder. You can format the array using helpers such as {{implode(',', query.batch.parent_ids)}} when the endpoint expects a comma-separated list, or use it directly in request body templates.

The batching object accepts:

Below you can find a template for an inverse join with batching:

"decoration": {
  "relations": [
    {
      "reportTypeId": "[child report type ID]",
      "type": "inverse",
      "link": {
        "parent": "[parent field ID]",
        "child": "[child field ID]"
      },
      "useDates": true,
      "includeMetrics": true,
      "batching": {
        "isEnabled": true,
        "batchSize": 20
      }
    }
  ]
}

Below you can find an annotated example from the X (Twitter) connector, where tweet rows are joined with engagement details fetched in batches of 20:

"decoration": {
  "relations": [
    {
      "reportTypeId": "details",
      "type": "inverse",
      "includeMetrics": true,
      "useDates": true,
      "link": {
        "parent": "tweet_id",
        "child": "tweet_id"
      },
      "batching": {
        "isEnabled": true,
        "batchSize": 20
      }
    }
  ]
}

The decorated details report passes the batched IDs as a request parameter:

{
  "name": "entity_ids",
  "value": "{{implode(',', query.batch.parent_ids)}}"
}

Each call to details requests stats for up to 20 tweet IDs at a time, and the returned rows are mapped back to the originating tweets using the tweet_id link.

Note: With batching decorations, you always need to define child link as well – otherwise data can't be joined to parent rows.

Joining multiple child reports

The relations array can hold more than one entry, so the same parent report can be enriched with data from several different child reports in a single query. All child reports stay hidden (as long as "isVisible": false has been set) – the user still picks only the parent report in the destination, and the merged fields appear on each of its rows.

Below you can find an example from the Basis connector, where the campaign report joins brand details (matched on brand_id), line items (fetched per campaign), and daily stats (also fetched per campaign):

"decoration": {
  "relations": [
    {
      "reportTypeId": "brand",
      "type": "normal",
      "link": { 
        "parent": "campaign_brand_id", 
        "child": "brand_id" 
      },
      "useDates": true,
      "includeMetrics": true,
      "inheritDecorations": true
    },
    {
      "reportTypeId": "line_item",
      "type": "normal",
      "link": { 
        "parent": "campaign_id" 
      },
      "useDates": true,
      "includeMetrics": true,
      "inheritDecorations": true
    },
    {
      "reportTypeId": "stats",
      "type": "inverse",
      "link": { 
        "parent": "campaign_id" 
      },
      "useDates": true,
      "includeMetrics": true,
      "inheritDecorations": true
    }
  ]
}

Chaining joins across report types

A child report type can itself define joins of its own, which lets you chain several report types together. To make those nested joins apply automatically to the parent's result, set inheritDecorations to true on the parent's relation.

For example, the Basis connector chains campaign → line item → stats → creative, with each step adding the next level via its own join. Because every relation enables inheritDecorations, requesting the top-level campaign report returns rows enriched with line item, stats, and creative information in a single query.

Note: Chaining joins can significantly increase the amount of API calls which might cause problems with API rate limits – avoid long chains when possible.