Skip to main content

Attribution

The Attribution table contains attribution calculations for each enabled attribution model as defined in the Dreamdata Data Hub UI.

Attribution is calculated for each stage journey (dd_stage_id) and assigned to individual sessions (dd_session_id).

Each session in a stage journey has two key values for every attribution model:

  • Attribution Value: The monetary value assigned to the session.
  • Attribution Weight: A percentage (0–1) representing the share of credit the session receives. A session can receive up to 100% attribution.

Attribution is performed at the session level, as it primarily serves to analyze acquisition channels. Each attribution model is applied to the same journey but may include or exclude specific sessions based on the attribution model settings defined in the Dreamdata Data Hub UI.

Because a session can be part of multiple stage journeys, it is repeated in this table for each relevant dd_stage_id. This makes dd_session_id + dd_stage_id the primary key of the table.

Querying Attribution Data​

The most standard use of the Attribution table needs to apply a filter or group by on both a stage name and an attribution model. For example, to analyze sessions attributed to the Data-Driven model for Closed-Won deals, you can use:

-- BigQuery SQL example
SELECT
    *
FROM
    attribution_table AS r,
    UNNEST(attribution) AS a
WHERE
    a.model = 'Data-Driven'
    AND r.stage.name = 'Closed-Won'

Relationship to events table​

The Attribution table contains session parameters similar to the events.session field in the Events table.

The sessions included in the attribution analysis can be obtained from the Events table by unnesting the stage array (to obtain events in each stage journey) and picking the first event of the session.

-- BigQuery SQL example
SELECT
    e.session,
    s.dd_stage_id,
    s.name
FROM
    events_table AS e,
    UNNEST(stages) AS s
WHERE
    e.dd_event_session_order = 1

SQL Examples for the Attribution Table​

Example 1: Attribution for a Single Stage Model​

This example demonstrates how to query the Attribution table to analyze the Data-Driven attribution for the MQL stage model in a given period (2024).

By running the query below, you can assess three key metrics:

  • total_deals: The number of unique MQL deals in the period.
  • total_value: The total value (attributed by the Data-Driven model) of those MQL deals.
  • influenced_value: The sum of MQL deal values, only counting each deal’s value once (to avoid duplication if a deal appears multiple times in the attribution array).

Note: The timestamp field represents the session date, not the stage date. This analysis helps you understand how activities/sessions within the selected period influence your revenue funnel, in the example below, at the MQL stage.

-- BigQuery SQL example: Summarized Data-Driven attribution for MQL-stage deals in 2024
SELECT
  COUNT(*) AS total_deals,                       -- Number of deals in the period
  ROUND(SUM(attr_total), 0) AS total_value,      -- Total attributed value
  ROUND(SUM(stage_value), 0) AS influenced_value -- Total 'stage' value
FROM (
  SELECT
    dd_stage_id,                                 -- Unique deal identifier
    MAX(stage.value) AS stage_value,             -- 'stage.value' for the deal (same per dd_stage_id, we use MAX to select just one)
    SUM(attr.value) AS attr_total                -- Total attributed value for this deal (for 'Data-Driven' model)
  FROM
    attribution_table AS a,
    UNNEST(attribution) AS attr
  WHERE
    a.stage.name = 'MQL'                         -- Stage model filter
    AND a.timestamp >= '2024-01-01'
    AND a.timestamp < '2025-01-01'
    AND attr.model = 'Data-Driven'               -- Attribution model filter
  GROUP BY
    dd_stage_id                                  -- Aggregate per deal
) AS deals