# Parsing names in the warehouse

The platform object ID is the join key. The name is a changeable attribute. Every join below uses `platform`, `account_id` and the object ID, stored as text, digits only. Nothing joins on a name, or on a name plus a date.

## Normalise IDs first

The same account reaches the warehouse in several forms: the Google Ads UI shows `123-456-7890` while the API and the BigQuery transfer give `1234567890`; Meta's ad account `id` is `act_1234567890` while every `account_id` field is `1234567890`; LinkedIn references are URNs such as `urn:li:sponsoredAccount:500000001`. A registry typed from the UI then matches nothing, every object falls back to the name parse or `unknown`, and exempt legacy objects lose their registry attributes. The coverage check can still pass on clean parses and hide it.

Apply one expression to every ID column, in staging and in the registry load:

```sql
-- BigQuery
REGEXP_REPLACE(
  REGEXP_REPLACE(TRIM(CAST(account_id AS STRING)), r'^(act_|urn:li:[A-Za-z]+:)', ''),
  r'-', ''
) AS account_id

-- Snowflake
REGEXP_REPLACE(
  REGEXP_REPLACE(TRIM(account_id::STRING), '^(act_|urn:li:[A-Za-z]+:)', ''),
  '-', ''
) AS account_id
```

It deliberately leaves anything else alone, so an ID damaged by a spreadsheet (`1.20212E+17`) fails the format test below instead of being turned into a wrong number. Owned-channel registry rows keep their `cmp_` IDs and are excluded from the test by platform.

## Models

1. `stg_ads_objects`: the current row per platform object at every level (platform, account ID, level, object ID, parent ID, name, status), IDs normalised. The directory the other models and the session join use.
2. `stg_ads_campaign_snapshots`: one row per platform, account, initiative-level object ID and snapshot date, with the name and status on that date.
   - Google Ads through BigQuery Data Transfer: read the date-partitioned `p_ads_Campaign_<customer_id>` table, filtering on `_DATA_DATE`. Each partition holds that day's match-table snapshot.
   - Snapshots are taken once a day and are not written for backfills, refresh-window days or manual runs. Name history starts on the transfer's first scheduled run, cannot be rebuilt for earlier dates, and has a gap for each failed run. Record the start date in `data-dictionary.md`.
   - Connectors: use their history tables if they keep them. Otherwise snapshot the current campaign table with a dbt snapshot using the `check` strategy on the name column. dbt recommends running snapshots hourly to daily, and a snapshot only sees the names present when it runs.
   - LinkedIn: the initiative level is the campaign group, and `utm_id` carries the group ID. Export LinkedIn cost by campaign group, or export it by campaign and map each campaign to its group through `stg_ads_objects`. Either way the `campaign_id` column below holds the campaign group ID for LinkedIn.
3. `int_campaign_name_history`: one row per object and name period.
4. `naming_vocab`: a dbt seed loaded from the same `naming-vocab.csv` the linter reads (`token`, `value`, `status`, `alias_of`, `retired_on`). Unique on token and value.
5. `campaign_registry`: loaded from the registry sheet, IDs normalised, every column as STRING (including `launch`, which CSV autodetect would type as an integer), blank cells as NULL (`NULLIF(TRIM(col), '')`), effective-dated, one current row per platform object.
6. `int_campaign_parsed`: one row per initiative-level object with its current name split into tokens and a parse status.
7. `dim_paid_campaign`: one row per initiative-level object with its current name, parse status, attributes and where they came from.

## Name history (BigQuery)

```sql
-- One row per platform object and name period. valid_to is exclusive.
WITH ordered AS (
  SELECT
    platform, account_id, campaign_id, snapshot_date, campaign_name,
    LAG(campaign_name) OVER (
      PARTITION BY platform, account_id, campaign_id ORDER BY snapshot_date
    ) AS previous_name
  FROM stg_ads_campaign_snapshots
),
starts AS (
  SELECT platform, account_id, campaign_id, campaign_name, snapshot_date AS valid_from
  FROM ordered
  WHERE previous_name IS NULL OR previous_name != campaign_name
)
SELECT
  platform, account_id, campaign_id, campaign_name, valid_from,
  LEAD(valid_from) OVER (
    PARTITION BY platform, account_id, campaign_id ORDER BY valid_from
  ) AS valid_to
FROM starts
```

A name changed from A to B and back to A produces three periods, which is what the weekly rename check needs.

## Parse (BigQuery): int_campaign_parsed

```sql
WITH current_name AS (
  SELECT platform, account_id, campaign_id, campaign_name
  FROM int_campaign_name_history
  WHERE valid_to IS NULL
),
parsed AS (
  SELECT
    platform, account_id, campaign_id, campaign_name,
    ARRAY_LENGTH(SPLIT(campaign_name, '_')) AS token_count,
    SPLIT(campaign_name, '_')[SAFE_OFFSET(0)] AS market,
    SPLIT(campaign_name, '_')[SAFE_OFFSET(1)] AS objective,
    SPLIT(campaign_name, '_')[SAFE_OFFSET(2)] AS funnel,
    SPLIT(campaign_name, '_')[SAFE_OFFSET(3)] AS audience,
    SPLIT(campaign_name, '_')[SAFE_OFFSET(4)] AS offer,
    SPLIT(campaign_name, '_')[SAFE_OFFSET(5)] AS launch_month
  FROM current_name
)
SELECT
  p.platform, p.account_id, p.campaign_id, p.campaign_name, p.launch_month,
  CASE
    WHEN p.campaign_name IS NULL THEN 'missing_name'
    WHEN NOT REGEXP_CONTAINS(p.campaign_name, r'^[a-z0-9_-]+$') THEN 'bad_characters'
    WHEN p.token_count != 6 THEN 'wrong_token_count'
    WHEN NOT REGEXP_CONTAINS(p.launch_month, r'^20[2-9][0-9](0[1-9]|1[0-2])$') THEN 'bad_launch'
    WHEN vm.value IS NULL OR vo.value IS NULL OR vf.value IS NULL
      OR va.value IS NULL OR vr.value IS NULL THEN 'unknown_value'
    ELSE 'ok'
  END AS parse_status,
  COALESCE(NULLIF(vm.alias_of, ''), vm.value) AS market,
  COALESCE(NULLIF(vo.alias_of, ''), vo.value) AS objective,
  COALESCE(NULLIF(vf.alias_of, ''), vf.value) AS funnel,
  COALESCE(NULLIF(va.alias_of, ''), va.value) AS audience,
  COALESCE(NULLIF(vr.alias_of, ''), vr.value) AS offer
FROM parsed AS p
LEFT JOIN naming_vocab AS vm ON vm.token = 'market' AND vm.value = p.market
LEFT JOIN naming_vocab AS vo ON vo.token = 'objective' AND vo.value = p.objective
LEFT JOIN naming_vocab AS vf ON vf.token = 'funnel' AND vf.value = p.funnel
LEFT JOIN naming_vocab AS va ON va.token = 'audience' AND va.value = p.audience
LEFT JOIN naming_vocab AS vr ON vr.token = 'offer' AND vr.value = p.offer
```

Retired values resolve through `alias_of`, so old names keep reporting under the new value.

## Attribute precedence (BigQuery): dim_paid_campaign

```sql
SELECT
  c.platform, c.account_id, c.campaign_id, c.campaign_name, c.parse_status,
  r.object_id IS NOT NULL AS in_registry,
  CASE
    WHEN r.object_id IS NOT NULL
      AND r.market IS NOT NULL AND r.objective IS NOT NULL AND r.funnel IS NOT NULL
      AND r.audience IS NOT NULL AND r.offer IS NOT NULL AND r.launch IS NOT NULL
      THEN 'registry'
    WHEN c.parse_status = 'ok' THEN 'name'
    ELSE 'unknown'
  END AS attribute_source,
  COALESCE(r.market, IF(c.parse_status = 'ok', c.market, NULL), 'unknown') AS market,
  COALESCE(r.objective, IF(c.parse_status = 'ok', c.objective, NULL), 'unknown') AS objective,
  COALESCE(r.funnel, IF(c.parse_status = 'ok', c.funnel, NULL), 'unknown') AS funnel,
  COALESCE(r.audience, IF(c.parse_status = 'ok', c.audience, NULL), 'unknown') AS audience,
  COALESCE(r.offer, IF(c.parse_status = 'ok', c.offer, NULL), 'unknown') AS offer,
  COALESCE(CAST(r.launch AS STRING), IF(c.parse_status = 'ok', c.launch_month, NULL), 'unknown') AS launch_month,
  r.initiative_id,
  r.utm_campaign,
  r.predecessor_id,
  r.naming_exempt
FROM int_campaign_parsed AS c
LEFT JOIN campaign_registry AS r
  ON r.platform = c.platform
  AND r.account_id = c.account_id
  AND r.object_id = c.campaign_id
  AND r.level IN ('campaign', 'campaign_group')
  AND r.effective_to IS NULL
```

- The level filter stops a targeting-level or ad-level registry row from matching an initiative-level ID.
- `attribute_source` is `registry` only when the registry row fills every attribute. A row with blanks still wins column by column, but reports it as `name` or `unknown`, so incomplete registry rows stay visible.
- `in_registry` and `parse_status` are separate on purpose: report the registry match rate apart from the parse rate. A registry with the wrong ID format shows as a low match rate even when names parse.
- When the registry and the parsed name disagree, the weekly exception report lists the object (query in `governance.md`).

## Snowflake differences

```sql
parsed AS (
  SELECT
    platform, account_id, campaign_id, campaign_name,
    ARRAY_SIZE(SPLIT(campaign_name, '_')) AS token_count,
    NULLIF(SPLIT_PART(campaign_name, '_', 1), '') AS market,
    NULLIF(SPLIT_PART(campaign_name, '_', 2), '') AS objective,
    NULLIF(SPLIT_PART(campaign_name, '_', 3), '') AS funnel,
    NULLIF(SPLIT_PART(campaign_name, '_', 4), '') AS audience,
    NULLIF(SPLIT_PART(campaign_name, '_', 5), '') AS offer,
    NULLIF(SPLIT_PART(campaign_name, '_', 6), '') AS launch_month
  FROM current_name
)
-- in the parse:
--   WHEN NOT REGEXP_LIKE(p.campaign_name, '[a-z0-9_-]+') THEN 'bad_characters'
--   WHEN p.launch_month IS NULL
--     OR NOT REGEXP_LIKE(p.launch_month, '20[2-9][0-9](0[1-9]|1[0-2])') THEN 'bad_launch'
-- in dim_paid_campaign: IFF() in place of IF(), r.launch::STRING in place of CAST.
```

- `SPLIT_PART` is 1-based and returns an empty string, not NULL, when the part is out of range. Wrap it in `NULLIF(..., '')` or test for `''`.
- `REGEXP_LIKE` implicitly anchors the pattern at both ends, so the patterns above need no `^` and `$`. BigQuery's `REGEXP_CONTAINS` does not anchor, so its patterns keep them.
- `SPLIT` returns NULL when the input is NULL, so keep the `missing_name` branch first.

## Child levels

Ad groups, asset groups, ad sets, LinkedIn campaigns and ads are parsed the same way at their own grain, never joined onto the campaign spend table. Token positions come from the rules file. Example for Meta ad sets:

```sql
SELECT
  o.platform, o.account_id, o.object_id AS ad_set_id, o.parent_id AS campaign_id, o.name,
  SPLIT(o.name, '_')[SAFE_OFFSET(0)] AS targeting,
  SPLIT(o.name, '_')[SAFE_OFFSET(1)] AS theme,
  SPLIT(o.name, '_')[SAFE_OFFSET(2)] AS placement
FROM stg_ads_objects AS o
WHERE o.platform = 'meta' AND o.level = 'ad_set'
```

- Check `targeting`, `placement` and `format` against `naming_vocab`, and `theme`, `concept` and `variant` against their patterns, exactly as for campaigns.
- Join to the initiative through `parent_id`, and to ad-level spend on the ad ID. Responsive search ads take concept and variant from their registry rows.

## Spend and sessions

Spend joins on IDs and must not change row count or cost:

```sql
SELECT 'before' AS stage, COUNT(*) AS row_count, SUM(cost) AS cost
FROM fct_campaign_spend_daily
WHERE spend_date BETWEEN '2026-10-01' AND '2026-10-07'
UNION ALL
SELECT 'after', COUNT(*), SUM(s.cost)
FROM fct_campaign_spend_daily AS s
LEFT JOIN dim_paid_campaign AS d
  ON d.platform = s.platform
  AND d.account_id = s.account_id
  AND d.campaign_id = s.campaign_id
WHERE s.spend_date BETWEEN '2026-10-01' AND '2026-10-07'
```

Pass: both rows identical, and no spend row has a NULL `attribute_source` after the join (a NULL means an ID missing from the directory). `fct_campaign_spend_daily` carries `currency` at its grain; never sum cost across currencies.

Sessions carry IDs too. Use the session-scoped `session_traffic_source_last_click` record, the session's last-click attribution; `collected_traffic_source` holds only the values present on each collected event and gives different session counts:

```sql
SELECT
  CASE
    WHEN session_traffic_source_last_click.google_ads_campaign.campaign_id IS NOT NULL THEN 'google_ads'
    WHEN session_traffic_source_last_click.manual_campaign.source IN ('facebook', 'instagram') THEN 'meta'
    WHEN session_traffic_source_last_click.manual_campaign.source = 'linkedin' THEN 'linkedin'
  END AS platform,
  -- utm_id is the initiative-level ID: Meta campaign, LinkedIn campaign group
  COALESCE(
    CAST(session_traffic_source_last_click.google_ads_campaign.campaign_id AS STRING),
    session_traffic_source_last_click.manual_campaign.campaign_id
  ) AS campaign_id,
  COUNT(*) AS sessions
FROM `your_project.analytics_XXXXXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20261001' AND '20261007'
  AND event_name = 'session_start'
GROUP BY 1, 2
HAVING platform IS NOT NULL
```

- Match the source values to the UTM registry; the list above follows utm-convention-and-registry's defaults.
- Older LinkedIn links tagged with `CAMPAIGN_ID` carry a campaign, not a group: map those IDs to their group through `stg_ads_objects` (`level = 'campaign'`, `parent_id`) before the join.
- Meta and LinkedIn sessions carry no account ID, so they join on platform and ID. Test that each ID is unique within its platform in the directory before relying on that join.

## Renames in reports

- The registry row's `change_type` decides. `correction` (a token was wrong): report "as is", all history under the current attributes. `attribute_change` (what runs changed and relaunching was not possible): report "as was", joining spend to the effective-dated registry on spend date (`effective_from <= spend_date < effective_to`).
- Reports restate only after the registry row is updated, because the registry wins over the name.
- Show the current name in reports. Keep `name_at_spend` from the name history available for audits.
- When spend moved to a new ID, `predecessor_id` links the two for reports that follow an initiative across IDs.

## Tests

- `dim_paid_campaign`: unique on platform, account ID and campaign ID.
- `naming_vocab`: unique on token and value. A duplicate row multiplies every joined spend row.
- `campaign_registry`: one row per platform object where `effective_to` is NULL.
- ID format, as a singular test that returns no rows:

```sql
SELECT platform, account_id, object_id, parent_id
FROM campaign_registry
WHERE platform IN ('google_ads', 'meta', 'linkedin')
  AND (NOT REGEXP_CONTAINS(COALESCE(account_id, ''), r'^[0-9]+$')
    OR NOT REGEXP_CONTAINS(COALESCE(object_id, ''), r'^[0-9]+$')
    OR (parent_id IS NOT NULL AND NOT REGEXP_CONTAINS(parent_id, r'^[0-9]+$')))
```

- Registry match: every current registry `object_id` with spend in the last 90 days matches a `stg_ads_objects` row on platform, account ID and object ID.
- `int_campaign_name_history`: no overlapping periods per object.
- `parse_status` accepted values: `ok`, `missing_name`, `bad_characters`, `wrong_token_count`, `bad_launch`, `unknown_value`.
- Every object ID with spend in the last 7 days exists in `dim_paid_campaign`.
- Spend join: row count and total cost unchanged (query above).