# Registry and cost joins

## Parameter jobs

- `utm_source`: the sender. Platform (`linkedin`), publication (`acme_weekly`), partner (`northwind_reviews`) or placement (`expo_booth`). One slug per relationship you would report or pay separately.
- `utm_medium`: the distribution method, from the controlled list only.
- `utm_campaign`, non-paid channels: the readable initiative. Format `yyyy_mm_initiative`, then optional qualifiers in a fixed order (market, product, audience) only when they change a decision. Pattern `^20[0-9]{2}_(0[1-9]|1[0-2])_[a-z0-9_]+$`, 60 characters at most. Extra metadata (objective, budget owner, funnel stage) goes in registry columns, not in the name.
- `utm_campaign`, always-on programs (brand search, retargeting, lifecycle flows, evergreen affiliates): the month the program started, never changed (`2024_03_onboarding_flow`). Renaming monthly breaks the name history. A change of purpose or audience is a new initiative with a new name and `utm_id`.
- `utm_campaign`, paid platforms: the platform's campaign name, by the platform's name macro where one exists (`{{campaign.name}}`, `CAMPAIGN_NAME`, `{Campaign}`), or a Google Ads campaign custom parameter typed to equal the name. Google's campaign data import and campaign URL pages ask for names that match the platform. Name platform campaigns with the initiative's registry name plus a qualifier (`2026_11_forecasting_launch_prospecting`), so a "contains" filter on the registry name groups the initiative in GA4, or by campaign-naming-convention, in which case the initiative grouping comes from the campaign map.
- `utm_id`: the join key. See ID policy below.
- `utm_source_platform`: the buying or sending system when it differs from the source: ESP for a newsletter, affiliate network for a partner, DSP for programmatic. Lowercase slug. Leave empty when the source already is the platform (`linkedin`).
- `utm_content`: the variant. Paid platforms: ad or creative ID by macro. Owned channels: a slot from a short list kept in the vocabulary and in `allowed-pairs.csv`.
- `utm_term`: paid search keyword by macro. Meta: ad set ID by macro if ad set cost is joined. Otherwise empty.
- `utm_creative_format`: optional, from a fixed list such as `display`, `native`, `video`, `search`, `text`.
- `utm_marketing_tactic`: optional, from a fixed list such as `prospecting`, `remarketing`.

Google's campaign URL page (checked 2026-10-17) says to always set source, medium and campaign, strongly recommends `utm_id` and `utm_source_platform`, and says `utm_creative_format` and `utm_marketing_tactic` are not currently reported in GA4. The Data API lists `sessionManualCreativeFormat` and `sessionManualMarketingTactic`. In the BigQuery export they are `collected_traffic_source.manual_creative_format` and `collected_traffic_source.manual_marketing_tactic` on the event, and `session_traffic_source_last_click.manual_campaign.creative_format` and `session_traffic_source_last_click.manual_campaign.marketing_tactic` for the session. Use them only when a named warehouse model or API report reads them, after one test visit shows the property returns them.

## Contract with campaign-naming-convention

- campaign-naming-convention owns in-platform names for campaigns, ad groups or ad sets and ads. When the team uses it, its templates decide what `utm_campaign`, `utm_id` and `utm_content` carry on paid platforms.
- This skill owns source, medium, the vocabulary and allowed pairs, every non-paid value, the registry, the platform campaign map and the cost routes. It checks each paid template against the channel rules and the cost route before launch.
- One registry. If campaign-naming-convention already produced one, add this skill's columns to it. Reuse its initiative IDs as `initiative_id`, whatever their prefix, and never mint a `utm_id` equal to one of them.

## ID policy

- Two IDs, two jobs. `initiative_id` groups everything for one initiative across channels (`ini_2026_011`). `utm_id` identifies the thing that has its own cost line.
- Paid platform rows: `utm_id` is the ID of the platform object cost is reported on, inserted by macro (`{campaignid}`, `{{campaign.id}}`, LinkedIn `CAMPAIGN_ID`, Microsoft `{CampaignId}`). The registry stores the macro text; the platform campaign map stores the resolved IDs.
- Rows without a platform ID (email, partners, affiliates, QR, organic): mint `cmp_yyyy_nnn` from a sequence in the registry. The number has no meaning; never encode channel or date logic in it.
- One registry `utm_id` per initiative per publication, partner or placement. A newsletter issue that promotes two initiatives uses two IDs, one per initiative.
- Uniqueness: a registry `utm_id` maps to one `utm_campaign` and one `initiative_id`. A platform campaign ID maps to one `utm_campaign` on any date (a logged rename adds a dated map row). A `utm_id` is never reused after a row ends.
- Do not force one ID onto every platform. One initiative has several platform campaigns; the map ties each platform campaign ID to the initiative.
- External IDs that are not campaign keys (an affiliate network's program ID, a partner's offer code) go in `notes`, not in `utm_id`.

## Rename protocol

1. Keep `utm_id`.
2. Add a `decisions.md` entry: old name, new name, effective date, reason, who approved.
3. Non-paid rows: update the registry row's `utm_campaign` and keep the old name in `notes`.
4. Paid rows: close the map row (`last_seen`) and add a row with the new name and `first_seen`. Update every place the name is typed (Google Ads custom parameter, ESP templates, redirects).
5. Meta name macros keep the first-published name, so a renamed Meta campaign keeps sending the old one. Avoid renaming on a connector route; if it happens, the map keeps the old `utm_campaign` for that ID and reports join on Session manual campaign ID.
6. Reports that span the change group by `utm_id`, or join the mapping, so the rename is not read as a channel shift.

## Registry columns

The CSV template is `assets/utm-registry-template.csv`. One row per distributed link.

- `link_id`: registry row key, `lnk_nnnn`.
- `initiative_id`: cross-channel initiative.
- `status`: `draft`, `approved`, `live`, `ended`. Only `approved` rows are built; only tested rows become `live`.
- `channel`: the team's channel label (`paid_social`, `newsletter`, `event`), for filtering the registry only.
- `utm_source`, `utm_medium`, `utm_campaign`, `utm_id`, `utm_source_platform`, `utm_content`, `utm_term`, `utm_creative_format`, `utm_marketing_tactic`: the values. On macro rows, the macro text as entered.
- `utm_id_origin`: `platform_macro` or `registry`.
- `landing_url`: the destination before tagging. https, on one of the team's landing hosts.
- `param_template`: macro rows only. The exact string entered in the platform (suffix, URL parameters field, tracking parameters), and whether it was entered in the UI or the API.
- `published_url`: what the audience sees or scans when it differs from the tagged URL (shortener, QR redirect, vanity path).
- `tagged_url`: static rows only. Output of the link builder.
- `placement`: where the link appears (ad account and level, newsletter issue, partner page, printed item).
- `publisher`: who publishes it: `in_house`, the agency, or the partner.
- `owner`: role responsible for the row, not a person's email.
- `expected_channel`: default channel group prediction from the rules, `test required` with the test result, plus the custom group channel when one applies. Google Ads rows with manual UTMs also record the fallback channel.
- `cost_route`: `ga4_google_ads_link`, `ga4_3p_connector`, `ga4_file_import`, `warehouse_only`, `none`.
- `cost_model`: `cpc`, `cpm`, `flat_fee`, `commission`, `none`.
- `flat_fee`, `currency`, `cost_allocation`: for flat fees only. Allocation is `on_send_date` or `even_over_run`.
- `start_date`, `end_date`: ISO dates. Always-on rows leave `end_date` empty.
- `test_evidence`: where the resolved URL, consent runs, observed channel and CRM check are recorded (ticket, file, date).
- `notes`: renames, exceptions, external IDs, reasons for an intended Paid Other or Unassigned.

## Allowed pairs columns

The CSV template is `assets/allowed-pairs.csv`. One row per approved source/medium pair. The link builder reads it.

- `utm_source`, `utm_medium`: the pair.
- `expected_default_channel`: the prediction from [ga4-channel-rules.md](ga4-channel-rules.md#how-to-predict-a-pair), or `test required`.
- `expected_custom_channel`: the custom group channel, when one applies.
- `rule_matched`: the rule or rules the pair satisfies, in words.
- `ambiguous`: `yes` when the satisfied rules point to different channels. The builder refuses an ambiguous pair until `test_result` starts with `pass`.
- `content_slots`: allowed `utm_content` values for static links, separated by `|`. Empty means any slug.
- `test_result`: `not_tested`, or `pass` or `fail` followed by the observed channel and the link ID tested.
- `checked_on`, `checked_url`: when and against which live page the prediction was made.

## Platform campaign map columns

The CSV template is `assets/platform-campaign-map-template.csv`. One row per platform campaign per `utm_campaign` value it has sent, filled from a read-only export at launch and after every rename.

- `platform`: `google_ads`, `microsoft_ads`, `meta`, `linkedin`, `tiktok` and so on.
- `ad_account_id`: the account or customer ID, as text.
- `platform_campaign_id`: the resolved ID that `utm_id` carries, as text. Long IDs lose digits when a spreadsheet stores them as numbers.
- `platform_campaign_name`: the name in the platform export on `exported_on`.
- `link_id`: the registry row whose template the campaign uses.
- `initiative_id`: the initiative the campaign serves.
- `utm_source`, `utm_medium`: the fixed pair for that platform.
- `utm_campaign`: the value the links send for this campaign in this date range, confirmed by the launch test (for Meta, the name at first publish).
- `first_seen`, `last_seen`: the date range for this `utm_campaign` value. Empty `last_seen` means current.
- `exported_on`, `export_method`: when and how the row was read (MCP server tool, UI export).

An enabled paid campaign with spend and no map row is a tagging gap. List it; do not guess its values.

## Cost joins

### GA4 campaign data import

Checked on 2026-10-17 (https://support.google.com/analytics/answer/10071305):

- Required: Campaign source (`utm_source`), Campaign medium (`utm_medium`), Date (`YYYY-MM-DD`), and at least one of clicks, cost and impressions. Currency (ISO 4217) is required when cost is included; the changelog dates this to 28 July 2026.
- Optional but highly recommended: Campaign ID (`utm_id`) and Campaign name (`utm_campaign`). Values in the file must match what users logged exactly: `Facebook` does not match `facebook`. The page also asks for dynamic name values such as `{{campaign.name}}` where the platform supports them, and says the import screen does not accept `{{...}}` placeholders.
- Data joins to Analytics data on the required keys: source, medium and date.
- `utm_term` and `utm_content` cannot be included. Ad-level and keyword-level cost stays in the warehouse.
- Values are daily. Re-uploading the same keys overwrites them. No duplicate keys in one file. Deleting an import source removes its data from reports. Data can take up to 24 hours to appear.
- Import sources include CSV, Google Sheets, SFTP, HTTPS, BigQuery, Cloud Storage, Snowflake, Redshift, S3, MySQL and PostgreSQL, plus automated connectors for Meta, Pinterest, Reddit, Snap and TikTok.
- Health: "% imported" (rows imported over rows in the file) and "Match rate" (imported rows joined to Analytics data, last two years only). The match rate uses the required keys, so it can look healthy while campaign names differ. Compare names and IDs in the Non Google campaign report as well.
- Where it shows: Reports, Acquisition, Non Google campaign; Advertising, Planning, All channels; and Reports, Data import, Campaign data import validation (the validation report, added 10 August 2026). Spend from linked integrations and imports feeds `advertiserAdCost` in the Data API.
- Do not import Google Ads cost; the Google Ads link already supplies it to the same cost metric.

Building the file for a platform on a file import (LinkedIn, Microsoft, X):

1. Export daily spend, clicks and impressions by campaign ID from the platform.
2. Join the export to the platform campaign map on `platform` and `platform_campaign_id`, choosing the map row whose `first_seen` to `last_seen` range covers the date. Take `utm_source`, `utm_medium` and `utm_campaign` from the map and `utm_id` from the campaign ID. An ID missing from the map is a tagging gap: list it, do not guess its values.
3. Add the currency, write the columns in `assets/cost-import-template.csv`, and check the import preview for the number format it expects (Google's page shows cost as `0,000.00`).
4. After the first upload, open the validation report and compare totals with the platform.

Flat fees (sponsored newsletter, event booth): one row per allocation date from the registry's `flat_fee` and `cost_allocation`, keyed on the row's source, medium, `utm_id` and campaign.

Affiliate commission: import to GA4, if at all, as one row per partner per date with the partner's source and medium. The order-level join belongs in the warehouse.

### Automated connectors

- One fixed `utm_medium` per platform. Meta allows one `utm_source` or one per publisher platform; read each other connector's setup page live for its exact rule. Google does not support several source/medium pairs per platform in these connectors (for example `fb / cpc` and `meta / cpc`); move to a file import if history mixes pairs.
- Meta's connector cannot change its ad account, source or medium after creation; it has to be deleted and recreated. Treat other connectors the same way until their live page says otherwise. Decide the values in the registry, then create the connector.
- On a connector route, `utm_campaign` must be the platform's campaign name (name macro, campaign named before first publish, no renames while the connector runs). If the links must carry a different name, use a file import built from the map instead.

### Warehouse joins

- Cost side: platform, campaign ID, date, spend, clicks, impressions, currency; ad ID for ad-level cost.
- Map: platform cost joins to the platform campaign map on `platform` and `platform_campaign_id`, which adds `initiative_id`, source, medium and the `utm_campaign` live on that date.
- Session side, GA4 BigQuery export: `collected_traffic_source.manual_campaign_id`, `manual_source`, `manual_medium`, `manual_campaign_name`, `manual_content`, `manual_term`, `manual_source_platform` on the event that carried the parameters, or `session_traffic_source_last_click.manual_campaign.campaign_id` for the session's last-click manual campaign. Google Ads clicks sit in `session_traffic_source_last_click.google_ads_campaign` with `campaign_id`.
- CRM side: the UTM fields captured on the form or checkout, first-touch and latest-touch stored separately (first-party-click-id-capture, planned, covers capture).
- Join keys: map `platform_campaign_id` equals the session or CRM `utm_id`, plus date. Registry IDs (`cmp_...`) join to the registry's own flat-fee rows. Affiliate commission joins to orders on the network's transaction or order ID.
- Keep history explainable: a mapping table of old values to new, with effective dates, applied in the model rather than by editing raw data.