Marketing attribution pipeline in dbt and BigQuery

# Marketing attribution pipeline

## Purpose

Daily attribution tables for a small ecommerce brand. The founders read a Monday spreadsheet export. There is no BI tool.

## Stack

- dbt Core with the dbt-bigquery adapter
- BigQuery datasets: `raw`, `staging`, `marts`, `exports`
- Dev datasets are named `dbt_YOURNAME`
- Project ID comes from the env var `DBT_BQ_PROJECT` (placeholder: `YOUR_PROJECT_ID`)

## Layout

- `models/staging`: one `stg_` model per source. Rename and cast only.
- `models/intermediate`: `int_` models for touchpoints and orders joins.
- `models/marts`: `fct_` and `dim_` tables.
- `models/exports`: spreadsheet-ready views.

## Sources

Three ad platforms, shop orders, and email clicks. Use placeholder names until the user fills in the real ones:

- `PLATFORM_A`, `PLATFORM_B`, `PLATFORM_C`: spend by day, campaign, channel
- Shop orders: order id, customer key, created_at, revenue, refunds
- Email clicks: click time, customer key, campaign

Column lists and open questions live in `SPEC.md`. If a column is unknown, add it to Open questions. Do not guess.

## Commands

```
dbt deps
dbt build --select state:modified+ --defer --state prod-artifacts
dbt build --select +fct_cost_per_order_daily
dbt source freshness
dbt docs generate
```

## Targets

- Always run against the `dev` target.
- Never run against `prod` unless the user asks.
- Prod runs happen only from the scheduler.

## Definitions

- Cost per order = total spend / distinct non-cancelled orders, per day, per channel.
- First touch and last touch use the lookback window var `attribution_window_days` (default 30).
- Each order gets one row per attribution model.

## Conventions

- All dates are UTC.
- A single `reporting_timezone` var is applied only in exports.
- Money is `NUMERIC` in the shop currency.
- Names are `snake_case`.
- Every model has a unique and not_null test on its grain.

## Gotchas

- Ad platforms restate spend for 1 to 3 days. Use incremental models with `insert_overwrite` and a 3 day lookback.
- Orders with no touchpoint go to channel `direct_or_unknown`. Never drop them.
- Divide with `safe_divide`.
- Email clicks are not proof of attribution without a bot filter.

## Cost rules

- On large marts, use `partition_by` date and `cluster_by` channel.
- Before full refreshes, run `dbt compile` and a BigQuery dry run to check bytes.
- Never use `--full-refresh` without asking.

## Secrets

- Never commit service account keys or `profiles.yml`.
- Use env vars and placeholders such as `YOUR_PROJECT_ID`.

## Workflow

1. Explore the relevant models and sources.
2. Plan the change.
3. Make the change.
4. Run the `verify` skill before saying done.

Use `/clear` between unrelated tasks.

## Review and specs

@REVIEW.md
@SPEC.md

---
name: dbt-test-runner
description: Use to run dbt build, dbt test or dbt source freshness and get back only the failures. Keeps noisy logs out of the main context.
model: haiku
tools: Bash, Read, Grep
maxTurns: 12
---

You run one dbt command for this project and report only what failed.

Rules:
- Run the exact command you are given. If it has no --target flag, add `--target dev`.
- Refuse any command that targets prod or uses --full-refresh. Say so in one line and stop.
- Never edit, create or delete files. Use Read and Grep only.
- Never print profiles.yml, keyfiles or environment secrets.

Steps:
1. Run the command with output sent to a log, for example `dbt build --select staging --target dev > /tmp/dbt-run.log 2>&1; echo $?`.
2. If the exit code is 0, reply `PASS: <command>` with the totals from the final summary line.
3. If it failed, use Grep on the log for `Failure in test`, `Error in`, `Compilation Error` and `Database Error`. Read only the few lines around each match (`-A 3 -B 1`).
4. For each failure, find the compiled SQL path in the log (target/run/ or target/compiled/) and confirm it exists with a small Read.

Report format:

FAIL: <command>
- <model or test name>: <one-line error>. Compiled SQL: <path or none>

List at most 10 failures. If there are more, end with "plus N more". Do not paste the full log.

{
  "permissions": {
    "allow": [
      "Bash(dbt compile:*)",
      "Bash(dbt build:*)",
      "Bash(dbt test:*)",
      "Bash(dbt run:*)",
      "Bash(dbt source freshness:*)",
      "Bash(dbt docs generate:*)",
      "Bash(dbt deps:*)",
      "Bash(git status)",
      "Bash(git diff:*)",
      "Bash(sqlfluff lint:*)"
    ],
    "ask": [
      "Bash(dbt run-operation:*)",
      "Bash(bq query:*)",
      "Bash(git commit:*)",
      "Bash(git push:*)"
    ],
    "deny": [
      "Read(./.env)",
      "Read(./profiles.yml)",
      "Read(./**/*keyfile*)",
      "Bash(rm -rf:*)",
      "Bash(dbt build --target prod:*)",
      "Bash(dbt run --target prod:*)",
      "Bash(dbt run --full-refresh:*)",
      "Bash(dbt build --full-refresh:*)"
    ],
    "blockWritesOutsideWorkingDirectories": true
  },
  "env": {
    "DBT_TARGET": "dev",
    "CLAUDE_CODE_SUBAGENT_MODEL": "haiku"
  },
  "hooks": {
    "PostToolUse": [
      {
        "matcher": "Edit|Write",
        "hooks": [
          {
            "type": "command",
            "command": "f=$(jq -r '.tool_input.file_path // empty'); case \"$f\" in *.sql) command -v sqlfluff >/dev/null 2>&1 && sqlfluff fix --force \"$f\" >/dev/null 2>&1 || true;; esac; exit 0"
          }
        ]
      }
    ]
  }
}

---
name: add-source
description: Repeatable steps to onboard a new ad platform or data source so every source in this dbt project follows the same pattern. Use when a new source is added to SPEC.md.
argument-hint: <source name>
disable-model-invocation: true
---

# Add source

Source name: $ARGUMENTS

If no name was given, stop and ask for one. Use lowercase snake_case, for example `platform_b_spend` or `shop_orders`.

## Before you start

- Read SPEC.md and find the row for this source.
- If any expected column is unknown, add it under Open questions in SPEC.md. Do not guess column names or types.
- Confirm you are on the dev target. Never edit prod settings.

## Step 1: Source YAML

Add the source to the staging sources YAML file, using the same layout as the other sources:

```yaml
sources:
  - name: <source_name>
    database: "{{ env_var('DBT_BQ_PROJECT') }}"
    schema: raw
    loaded_at_field: _loaded_at
    freshness:
      warn_after: {count: 26, period: hour}
      error_after: {count: 36, period: hour}
    tables:
      - name: <table_name>
        columns:
          - name: <key_column>
            tests:
              - unique
              - not_null
```

Use the real loaded-at timestamp column from SPEC.md. If none exists, stop and record it as an open question.

## Step 2: Staging model

Create `models/staging/stg_<source_name>.sql`:

- Only rename, cast and deduplicate. No business logic and no joins.
- Cast money to `NUMERIC`. Keep timestamps in UTC.
- Use `safe_cast` where the source type is not reliable.
- Add a unique and not_null test on the key in the model YAML.

Add `accepted_values` for any status or channel column.

## Step 3: Map into touchpoints or orders

- Ad platform sources: add the rows to `int_touchpoints` with a `channel` value. The value must be in the `accepted_values` list for `channel` in the model YAML. Add the new value to that list if it is new, and note it in SPEC.md.
- Order sources: confirm `int_orders` picks up the columns. Do not add a second orders source without asking.
- Email click sources: apply the bot filter before the row reaches `int_touchpoints`.

## Step 4: Reconciliation test

For spend sources, add a singular test under `tests/` that compares the daily total in the staging model with the platform total in SPEC.md, within the tolerance written there. Do not loosen the tolerance to make it pass.

## Step 5: Update SPEC.md

- Fill in the source table row: columns, load cadence, freshness thresholds.
- Move any answered open questions out of the list.
- Keep the new source in the acceptance checklist.

## Step 6: Verify

Run the verify skill. Report:

- files created or changed
- the new source's freshness state
- any open questions still in SPEC.md

Do not say the source is done until the verify skill passes.

---
name: verify
description: Run after any model, test, source or macro change in this dbt project and before saying work is done. Checks lint, build, freshness, reconciliation and export output.
---

# Verify

Run these steps in order against the dev target. Stop at the first failing step, report it, and fix it before continuing.

## Step 1: Lint or parse

- If `.sqlfluff` exists, run `sqlfluff lint` on the changed `.sql` files.
- If it does not exist, run `dbt parse` instead.

## Step 2: Build changed models

- Confirm `DBT_TARGET` is `dev` (`echo $DBT_TARGET`). Stop if it is anything else.
- If the `prod-artifacts` state folder exists, run:
  `dbt build --select state:modified+ --defer --state prod-artifacts --target dev`
- If there is no state folder, list the changed models from `git diff --name-only` and run:
  `dbt build --select <model1>+ <model2>+ --target dev`
- Never add `--full-refresh`. Never add `--target prod`.

## Step 3: Source freshness

Run `dbt source freshness` only when a source YAML or an export model changed. Warnings on a source with no new data are expected only if the source is known to be quiet; report them, do not hide them.

## Step 4: Grain and reconciliation spot checks

Use an inline query with the same date range for both sides. Replace the date range with the one you are checking.

Orders per model. Each model must match the distinct orders in the shop orders staging model:

```
dbt show --target dev --inline "
select
  (select count(distinct order_id) from {{ ref('stg_shop_orders') }}
    where order_date between '2026-10-01' and '2026-10-07') as source_orders,
  (select count(distinct order_id) from {{ ref('fct_attribution_orders') }}
    where order_date between '2026-10-01' and '2026-10-07' and model = 'first_touch') as first_touch_orders,
  (select count(distinct order_id) from {{ ref('fct_attribution_orders') }}
    where order_date between '2026-10-01' and '2026-10-07' and model = 'last_touch') as last_touch_orders
"
```

Spend. Sum of spend in `fct_cost_per_order_daily` must equal the sum across the three platform staging spend models for the same dates. Use the staging model names from SPEC.md. If a platform has no spend in the range, say so.

Record the numbers. A mismatch is a failure unless the difference is inside the tolerance in SPEC.md.

## Step 5: Export smoke check

For each view in `models/exports`:

```
dbt show --target dev --select <export_model> --limit 10
```

Confirm:
- no null channel values
- no negative cost values
- friendly column names match SPEC.md

## Report format

Return a short report:

- Commands run, in order
- Pass or fail for each step
- Names of failing tests only (no full logs)
- Reconciliation numbers from Step 4
- What remains unverified and why

## Rules

- Never claim done while any test fails.
- Never weaken, skip or delete a test to make it pass. If a test looks wrong, report it and ask.
- If the dbt-test-runner subagent is available, use it for Step 2 and Step 3 output so the logs stay out of the main context.

# Setup guide

This guide takes a daily user from zero to a first dev run, then to the Monday routine.

## Prerequisites
- Python, with dbt-bigquery and sqlfluff installed: `pip install dbt-bigquery sqlfluff`
- The gcloud CLI
- A BigQuery project (YOUR_PROJECT_ID) and your dev dataset (dbt_YOURNAME)

## Authentication
- Dev: `gcloud auth application-default login` with your own Google account. No keys.
- Scheduler only: a service account. Keep its key in the secret manager, never in the repo or on a laptop.

## Profile
Create `~/.dbt/profiles.yml` outside the repo. Never commit it.

```yaml
attribution:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: YOUR_PROJECT_ID
      dataset: dbt_YOURNAME
      location: YOUR_BQ_LOCATION
      threads: 4
```

The profile name must match the one in dbt_project.yml. The prod target is set up on the scheduler host by whoever owns the pipeline.

## First run
1. Copy the setup files into the repo root: CLAUDE.md, SPEC.md, PLAN.md, REVIEW.md, INSTALL.md, and the .claude/ folder.
2. Run `dbt deps`.
3. Run `dbt debug`. It should end with "All checks passed!"
4. Replace PLATFORM_A, PLATFORM_B and PLATFORM_C in SPEC.md with the real ad platform names, and fill in the column lists.

## Plugins
```
claude plugin marketplace add anthropics/knowledge-work-plugins
claude plugin install feature-dev@claude-plugins-official
claude plugin install code-review@claude-plugins-official
claude plugin install commit-commands@claude-plugins-official
claude plugin install data@knowledge-work-plugins
```

The knowledge-work-plugins marketplace has auto-update off by default. Update it yourself.

## Start the build
1. `cd` into the repo and run `claude`.
2. Paste the Phase 1 goal from PLAN.md as the kickoff prompt.
3. Run /clear between phases and commit after each phase passes.

## Monday routine
- The scheduler runs the export models against prod. The spreadsheet gets the last 8 weeks, as set up in SPEC.md.
- The scheduler runs `dbt source freshness`. A failure posts to YOUR_WEBHOOK_URL.
- The founders get the Monday export. The alert goes to the email or Slack channel set in SPEC.md, usually the founders plus the pipeline owner.

## When an alert fires
1. Check whether the source stopped sending or the load is late.
2. Run `dbt source freshness` locally against dev to confirm.
3. Fix the load, then run the verify skill before saying the issue is closed.

## Token habits
- Use /clear between unrelated tasks and between phases.
- Use /compact at natural breaks.
- Use /usage to see where tokens went.

# Attribution pipeline build plan

Five phases, each run as one `/goal`. Run `/clear` between phases. Commit after each phase passes.

Before each phase, Claude reads CLAUDE.md, SPEC.md and REVIEW.md. Every phase uses the dev target only (`DBT_TARGET=dev`).

## Phase 1: Sources and staging

**End state:** sources defined for PLATFORM_A, PLATFORM_B, PLATFORM_C, shop orders and email clicks, with `loaded_at_field` and freshness (warn 26h, error 36h). One `stg_` model per source, with unique and not_null tests on its key.

**Check:** `dbt build --select staging` finishes with zero errors and zero failing tests. `dbt source freshness` runs without config errors.

```
/goal Phase 1: build the staging layer. End state: dbt sources are defined for PLATFORM_A, PLATFORM_B, PLATFORM_C, shop orders and email clicks, with loaded_at_field and freshness set to warn after 26h and error after 36h. Each source has one stg_ model in models/staging that only renames, casts and deduplicates, with a unique and not_null test on its key and accepted_values where a channel or status column exists. Read SPEC.md and CLAUDE.md first and use the column lists in SPEC.md. If a column is unknown, add it under Open questions in SPEC.md instead of guessing. Check: run the verify skill and show `dbt build --select staging` finishing with zero errors and zero failing tests, plus `dbt source freshness` running without config errors. Constraints: dev target only; no --full-refresh; no prod runs; edit only models/staging, sources YAML, tests and SPEC.md; no secrets or keys in any file; do not weaken a test to make it pass; use the dbt-test-runner subagent for build output. Stop after 30 turns and summarize blockers, open questions and row counts per staging model.
```

## Phase 2: Touchpoints and orders intermediate layer

**End state:** `int_touchpoints` unifies ad clicks (UTM or click ids) and bot-filtered email clicks. `int_orders` deduplicates orders, flags cancelled orders and computes net revenue. Both have grain tests and a dedup check. A row count comparison against the orders source is recorded.

**Check:** `dbt build --select intermediate` passes. Row counts for `int_orders` and the orders source are compared and recorded in the final summary.

```
/goal Phase 2: build the intermediate layer. End state: int_touchpoints unifies ad clicks from PLATFORM_A, PLATFORM_B and PLATFORM_C (UTM or click ids) with bot-filtered email clicks, with one channel value per row from an accepted_values list. int_orders deduplicates orders, flags cancelled orders and computes revenue net of refunds. Both models have grain tests and a dedup check that fails on duplicates. Record in the summary the row count of int_orders against the shop orders source, and any difference with the reason. Read SPEC.md, CLAUDE.md and REVIEW.md first. Check: run the verify skill and show `dbt build --select intermediate` with zero errors. Constraints: dev target only; no --full-refresh; no prod runs; edit only models/intermediate, tests, macros and SPEC.md; no secrets or keys; do not weaken a test; no fan-out from touchpoint joins without a row count check. Stop after 30 turns and summarize blockers and open questions.
```

## Phase 3: Marts

**End state:** `fct_cost_per_order_daily` is incremental with a 3 day restate window (`insert_overwrite`, partitioned by date, clustered by channel). `fct_attribution_orders` gives one first touch and one last touch per order, honoring `attribution_window_days`. The channel comparison view is built. Unit tests on a small fixture cover tie-breaking and the lookback window.

**Check:** `dbt build --select marts` passes, including unit tests.

```
/goal Phase 3: build the marts. End state: fct_cost_per_order_daily is incremental with insert_overwrite and a 3 day restate window, partitioned by date and clustered by channel, with columns spend, orders, revenue, cost_per_order and roas (using safe_divide). fct_attribution_orders has grain order_id x model (first_touch, last_touch), exactly one row per order per model, honors the attribution_window_days var (default 30), and breaks ties by timestamp then source priority. A channel comparison view shows orders and revenue by channel under each model and the difference. Add unit tests on a small fixture for tie-breaking and the lookback window. Check: run the verify skill and show `dbt build --select marts` passing, including unit tests. Constraints: dev target only; no --full-refresh; no prod runs; check bytes with dbt compile and a BigQuery dry run before any large run; edit only models/marts, tests, macros and SPEC.md; no secrets or keys; do not weaken a test. Stop after 30 turns and summarize blockers and open questions.
```

## Phase 4: Exports and freshness alerts

**End state:** export views in `models/exports` with friendly names, rounded values, last 8 weeks and week-over-week change. Freshness thresholds confirmed. Row count anomaly and spend reconciliation tests in place. The scheduler step posts failures to `YOUR_WEBHOOK_URL`. A simulated stale source triggers a freshness error.

**Check:** `dbt source freshness` and `dbt build --select exports` pass. The simulated stale source produces an error, and the override is reverted.

```
/goal Phase 4: exports and freshness alerts. End state: views in models/exports with friendly column names, values rounded to 2 decimals, the last 8 weeks, and week-over-week change for spend, orders, revenue and cost per order, with dates shown in the reporting_timezone var. Source freshness is set to warn at 26h and error at 36h for every source. A row count anomaly test compares daily rows with the trailing 7 day average. A spend reconciliation test compares staging spend with platform totals within the tolerance in SPEC.md. Add a scheduler config under scheduler/ that runs dbt source freshness and the build, and posts any failure to YOUR_WEBHOOK_URL (placeholder, never a real URL). Simulate a stale source with a dev-only override (for example a var that filters a source to data older than 36 hours), confirm the freshness error, then revert the override and confirm it is reverted. Check: run the verify skill, show `dbt source freshness` and `dbt build --select exports` passing, and show the simulated error. Constraints: dev target only; no --full-refresh; no prod runs; edit only models, tests, macros, scheduler/ and SPEC.md; no secrets or keys; do not weaken an anomaly or reconciliation test. Stop after 30 turns and summarize blockers and open questions.
```

## Phase 5: Documentation and handoff

**End state:** every mart and export column has a description. A README section covers the Monday routine and what to do when an alert fires. `dbt docs generate` succeeds.

**Check:** `dbt docs generate` exits zero. A check of `target/manifest.json` finds no column in `models/marts` or `models/exports` without a description.

```
/goal Phase 5: documentation and handoff. End state: every model in models/marts and models/exports has a description, and every column in those models has a description. A README.md section explains the Monday export routine (how to refresh, where the export lands, how to check it) and what to do when a freshness or test alert fires (which source, who to contact, when to rerun). Check: run the verify skill, run `dbt docs generate` with zero errors, and check target/manifest.json to show no column in models/marts or models/exports lacks a description. Constraints: dev target only; no --full-refresh; no prod runs; edit only model YAML, README.md, docs and SPEC.md; no secrets or keys; no personal data in descriptions. Stop after 30 turns and summarize blockers and open questions.
```

# Review checklist

Use this list for every model change. CLAUDE.md imports this file, so /code-review applies it.

## Grain and joins
- Every model states its grain and has unique and not_null tests on it.
- No fan-out from many-to-many touchpoint joins. Compare row counts before and after.

## Attribution
- Exactly one first touch and one last touch per order.
- The attribution_window_days lookback is honored.
- Ties break deterministically: timestamp, then source priority.

## Money and time
- Refunds and cancelled orders are handled explicitly.
- Money stays in the shop currency. No silent conversion.
- Every ratio uses safe_divide.
- Storage is UTC. Timezone conversion happens only in exports.
- The 3 day restate window covers late-arriving data.

## Incremental safety and cost
- insert_overwrite partitions match the days rewritten. Re-runs give the same result.
- Partition filters are present. No select * from large raw tables.
- Bytes scanned are reasonable. Dry run before any full refresh.

## Tests and freshness
- New columns have tests. Every source has freshness set.
- Anomaly and reconciliation tests are never silenced or deleted to pass.

## Security and privacy
- No keys, passwords or profiles.yml in the repo.
- No email addresses in marts or exports. Hash customer keys.

## Founder-visible numbers
- Flag any change to a number founders already saw in a prior export.
- State the old value, the new value and the cause.

# Attribution pipeline spec

## Goal and users

Daily attribution tables for a small ecommerce brand. Two founders read a spreadsheet export every Monday. No BI tool.

The pipeline answers three questions:

- What did each channel cost per order, and what was its return on ad spend?
- Which channel gets credit for each order under first touch and under last touch?
- Did any source stop sending data?

## Sources

Each source gets one `stg_` model. Column lists are expected shapes, not confirmed schemas. Anything unconfirmed is marked **[open question]** and listed under Open questions below.

| Source | Grain | Expected columns | Load cadence | Notes |
|---|---|---|---|---|
| PLATFORM_A spend | date x campaign | date, campaign_id, campaign_name, channel, spend, currency | Daily, restates 1 to 3 days back | [open question] confirm currency field |
| PLATFORM_B spend | date x campaign | date, campaign_id, campaign_name, channel, spend, currency | Daily, restates 1 to 3 days back | [open question] confirm currency field |
| PLATFORM_C spend | date x campaign | date, campaign_id, campaign_name, channel, spend, currency | Daily, restates 1 to 3 days back | [open question] confirm currency field |
| Shop orders | order | order_id, customer_key, created_at, revenue, refunds, status | Hourly or daily **[open question]** | status values for cancelled orders **[open question]** |
| Email clicks | click | click_at, customer_key, campaign, bot_flag | Hourly or daily **[open question]** | [open question] source of bot flag |

Platform click data for attribution: each ad platform's click rows, with UTM parameters or click ids. [open question] which click id fields each platform exposes.

## Outputs

### Output 1: fct_cost_per_order_daily

- Grain: date x channel
- Columns: spend, orders, revenue, cost_per_order, roas
- `cost_per_order` = spend / distinct non-cancelled orders, using `safe_divide`
- `roas` = revenue / spend, using `safe_divide`
- Incremental with `insert_overwrite` and a 3 day restate window

### Output 2: fct_attribution_orders

- Grain: order_id x model (`first_touch`, `last_touch`)
- One row per order per model
- Orders with no touchpoint in the lookback window get channel `direct_or_unknown`

Channel comparison view: orders and revenue by channel under each model, plus the difference between first touch and last touch for each channel.

## Touchpoint rules

- **Counts as a touchpoint:** ad clicks identified by UTM parameters or click ids, and email clicks that pass the bot filter.
- **Does not count:** email clicks flagged as bot traffic. Email clicks without the bot filter are not treated as proof of attribution.
- **Lookback window:** dbt var `attribution_window_days`, default 30. A touchpoint counts only if it falls within that many days before the order `created_at`.
- **Tie-breaking:** earliest timestamp first. If timestamps are equal, use source priority: ad platforms before email. [open question] confirm the order between PLATFORM_A, PLATFORM_B and PLATFORM_C.
- **Refunds:** revenue is net of refunds. The order still counts as an order.
- **Cancelled orders:** excluded from order counts and revenue in both outputs.

## Export

- Location: `models/exports`, views only.
- Friendly column names, rounded values (money to 2 decimals, ratios to 2 decimals).
- Last 8 weeks, with week-over-week change for spend, orders, revenue and cost per order.
- Dates shown in `reporting_timezone` (applied only in exports).
- Delivery: decision left to the user. Options:
  - A scheduled query that writes to Google Sheets.
  - A CSV dropped to a bucket, then imported to the spreadsheet.

Document the chosen option in INSTALL.md and README.md.

## Data quality

- **Freshness:** per source, warn after 26 hours, error after 36 hours, using `loaded_at_field`.
- **Row count anomaly:** daily row count in each staging and mart table compared with the trailing 7 day average. Flag a day that falls outside an agreed band. [open question] band width.
- **Spend reconciliation:** sum of spend in staging per platform and day compared with the platform total for the same day. Pass if within tolerance. [open question] tolerance, proposed 1%.
- Every mart and staging model has a unique and not_null test on its grain.

## Alerting

- The scheduler runs `dbt source freshness` and the build after each run.
- On any freshness failure or test failure, the scheduler posts a message to `YOUR_WEBHOOK_URL` (Slack incoming webhook) and sends the same message by email to both founders.
- [open question] founders' email addresses and whether Slack is used.

## Non-goals

- Multi-touch fractional attribution models.
- Incrementality testing.
- Real-time dashboards.
- Personal data in marts or exports. Customer keys are hashed before they reach marts.

## Acceptance checklist

- [ ] All five sources have a `stg_` model with a unique and not_null key test.
- [ ] Freshness is set per source with warn at 26h and error at 36h.
- [ ] `fct_cost_per_order_daily` has grain tests and matches spend reconciliation.
- [ ] `fct_attribution_orders` has exactly one first touch and one last touch per order.
- [ ] Unit tests cover tie-breaking and the lookback window.
- [ ] Export views have friendly names, rounding, 8 weeks of data and week-over-week change.
- [ ] A simulated stale source produces a freshness error.
- [ ] Spend reconciliation and row count anomaly tests run and pass on dev data.
- [ ] Scheduler posts failures to `YOUR_WEBHOOK_URL`.
- [ ] The verify skill passes.

## Open questions

- Confirm the real names for PLATFORM_A, PLATFORM_B and PLATFORM_C.
- Confirm the currency field on each ad platform, and whether all spend is in one shop currency.
- Confirm the click id fields each platform exposes.
- Confirm the shop orders status values for cancelled orders.
- Confirm the shop orders and email click load cadence.
- Confirm the source of the email bot flag.
- Confirm the source priority order for tie-breaking.
- Confirm the row count anomaly band and the spend reconciliation tolerance.
- Choose the export delivery method (Google Sheets query or CSV to bucket).
- Confirm the founders' email addresses and whether Slack is used for alerts.