Channel groupings are analytical classifications applied to attribution results. They map raw source and medium values into human-readable channel names like "Paid Search", "Organic", or "Direct Mail".

Channel groupings are not facts — they are analysis. This distinction drives the architecture: they sit at the very end of the attribution pipeline so that changing channel logic never requires rebuilding upstream models.

---

Architecture

flowchart LR
    subgraph pipeline [Attribution Pipeline]
        TP[source_touchpoints] --> NT[nexus_touchpoints]
        NT --> NTP[nexus_touchpoint_paths]
        NTP --> NTPB[nexus_touchpoint_path_batches]
        NTPB --> LMT[attribution models]
        LMT --> NAMR[nexus_attribution_model_results]
    end
    subgraph channels [Channel Groupings]
        NAMR -->|"helper macro + client CASE"| CH[attribution_model_results_channels]
    end

attribution_model_results_channels is a client-level model (not in the package) that reads from nexus_attribution_model_results and applies client-defined CASE logic. The package provides a reusable helper macro for the boilerplate.

Rebuild cost: When channel logic changes, you rebuild only this one model. No touchpoints, paths, batches, or attribution models need to be rebuilt.

---

Data Model

attribution_model_results_channels produces one row per (attribution result, channel grouping):

Column	Type	Description
attribution_model_result_id	string	FK to nexus_attribution_model_results
attribution_model_name	string	e.g. last_marketing_touch
channel_grouping_name	string	e.g. marketing_channel
channel_group	string	e.g. Paid Search

This EAV-style schema is consistent with other Nexus patterns (nexus_event_dimensions, nexus_states). Adding a new channel grouping adds rows, not columns — no schema changes, no downstream breakage.

Multiple Channel Groupings

You can define multiple groupings in the same model. Each grouping is a separate analytical lens on the same source/medium data:

• marketing_channel — Paid Search, Organic, Direct, Email, etc.
• channel_type — Paid, Owned, Earned
• platform — Google, Meta, Bing, etc.

Per-Model Logic

Each grouping macro receives attribution_model_name as a parameter, so it can return different classifications per attribution model. For example, last_gclid results could always be classified as "Paid Search" while last_marketing_touch uses the full CASE logic.

---

Package Helper Macro

The nexus package provides channel_grouping_union in macros/attribution/channel_grouping.sql:

{% macro channel_grouping_union(groupings) %}
{% for grouping in groupings %}
{% if not loop.first %}UNION ALL{% endif %}
SELECT
    attribution_model_result_id,
    attribution_model_name,
    '{{ grouping.name }}' as channel_grouping_name,
    {{ grouping.expression }} as channel_group
FROM {{ ref('nexus_attribution_model_results') }}
{% endfor %}
{% endmacro %}

This macro handles the SELECT/UNION ALL boilerplate. The client provides:

• name — the grouping identifier (becomes channel_grouping_name)
• expression — a SQL expression (typically a CASE from a client macro)

---

Implementation

1. Define a Channel Grouping Macro

Create a macro that returns a SQL CASE expression. This is the single source of truth for how channels are classified:

-- macros/channel_groupings/marketing_channel_grouping.sql
{% macro marketing_channel_grouping(source_col, medium_col, model_name_col) %}
  CASE
    WHEN LOWER({{ source_col }}) IN ('google','bing','google.com')
      AND LOWER({{ medium_col }}) IN ('cpc','pmax','paid search','paidsearch')
      THEN 'Paid Search'
    WHEN LOWER({{ medium_col }}) = 'organic'
      THEN 'Organic'
    WHEN {{ source_col }} IS NULL AND {{ medium_col }} IS NULL
      THEN 'Direct'
    WHEN LOWER({{ source_col }}) = 'direct'
      THEN 'Direct'
    WHEN LOWER({{ medium_col }}) = 'email'
      THEN 'Email'
    WHEN LOWER({{ medium_col }}) = 'referral'
      THEN 'Referral'
    ELSE 'Other'
  END
{% endmacro %}

The macro takes column name parameters (source_col, medium_col, model_name_col) so it can be called with different column references. The model_name_col parameter allows branching per attribution model if needed:

{% if model_name_col %}
  WHEN {{ model_name_col }} = 'last_gclid' THEN 'Paid Search'
{% endif %}

2. Create the Client Model

-- models/attribution/attribution_model_results_channels.sql
{{ config(materialized='table', tags=['attribution']) }}

{{ nexus.channel_grouping_union([
    {
      'name': 'marketing_channel',
      'expression': marketing_channel_grouping('source', 'medium', 'attribution_model_name')
    }
]) }}

Adding a second grouping is just another entry in the list:

{{ nexus.channel_grouping_union([
    {
      'name': 'marketing_channel',
      'expression': marketing_channel_grouping('source', 'medium', 'attribution_model_name')
    },
    {
      'name': 'channel_type',
      'expression': channel_type_grouping('source', 'medium', 'attribution_model_name')
    }
]) }}

No dispatch, no project-name prefixes, no special configuration. Each grouping macro is a plain client-defined macro.

3. Query Channel Groupings

Join from any attribution result to get the channel:

SELECT
  ch.channel_group AS channel,
  COUNT(*) AS event_count
FROM nexus_attribution_model_results amr
LEFT JOIN attribution_model_results_channels ch
  ON amr.attribution_model_result_id = ch.attribution_model_result_id
  AND ch.channel_grouping_name = 'marketing_channel'
WHERE amr.attribution_model_name = 'last_marketing_touch'
GROUP BY ch.channel_group
ORDER BY event_count DESC

Use COALESCE(ch.channel_group, 'Other') to handle attribution results where the channel CASE didn't match any rule.

---

Design Decisions

Why a separate table (not a column on nexus_attribution_model_results)?

• Multiple groupings — each grouping adds rows, not columns. No schema changes.
• Consistent with Nexus patterns — dimensions are EAV, states are EAV, channel groupings are EAV.
• Clean separation — attribution results are facts; channel groupings are analysis.

Why a table (not a view)?

Channel grouping CASE statements often use REGEXP_LIKE and other string operations that are expensive to evaluate at query time. Building as a table means:

• Build once at dbt time (fast — just reads the already-built upstream table)
• Query fast — join on attribution_model_result_id + filter on channel_grouping_name is a simple indexed scan
• Change cheaply — update the macro, rebuild just this one model

Why at the end of the pipeline (not on touchpoints)?

Channel groupings are analytical classifications that change more frequently than the underlying data. Putting them on touchpoints would require rebuilding the entire attribution chain (touchpoints → paths → batches → attribution models) on every channel logic change — which can take 30+ minutes. At the end of the pipeline, rebuilds take under 2 minutes.

Why client-level (not in the package)?

Channel grouping logic is inherently client-specific. Each client has different marketing channels, naming conventions, and classification rules. The package provides the boilerplate macro; the client owns the logic.