# Chia affiliate CSV reporting contract

**Version 1.0 · Daily schema `chia-affiliate-daily-v1` · Monthly schema
`chia-affiliate-monthly-v1`**

This is the machine-readable contract for Chia affiliate analytics exports.
These files are reporting documents, not invoices, statements, balances, or
commission calculations. All identifiers are opaque. No customer identity or
clinical data appears in an export.

## Cadence and report windows

For each report date Chia produces two daily files:

- `UTC`, covering `[00:00:00Z, 00:00:00Z next day)`;
- `America/New_York`, covering the two local midnights converted to UTC. This
  interval is DST-safe and can contain 23, 24, or 25 hours.

Automated sending opens at 06:00 UTC, after both preceding local days have
closed. The email subject is `Chia affiliate daily analytics — YYYY-MM-DD —
<timezone>`, so the two variants cannot be confused. Monthly exports use UTC
and name both the report month and timezone in the subject.

The scheduler sends at most one oldest-due artifact per one-minute tick.
Durable period/timezone/affiliate markers suppress duplicates. It automatically
reconsiders the most recent seven daily dates and three report months after a
short outage. An older missing period must be regenerated and sent with the
explicit dated CLI command; it is not relabelled as a current report. A delivery
whose outcome is uncertain is not retried automatically and requires operator
confirmation before a forced resend.

## Common envelope

Every CSV starts with these key/value rows, followed by a blank row:

| Key | Contract |
|---|---|
| `report_type` | `daily` or `monthly_ltv`. |
| `schema_version` | Exact schema identifier named at the top of this document. |
| `affiliate_id` | Chia-issued opaque affiliate code. |
| `period` | `YYYY-MM-DD` for daily; `YYYY-MM` for monthly. |
| `timezone` | `UTC` or `America/New_York`. Scheduled monthly files use `UTC`. |
| `window_start_utc` | Inclusive ISO-8601 UTC boundary. |
| `window_end_utc` | Exclusive ISO-8601 UTC boundary. |
| `period_closed` | `1` when the labelled period had finished at render time, `0` when it was still running. |
| `snapshot_utc` | Exclusive ISO-8601 UTC instant the facts in this file were read as of. Equals `window_end_utc` whenever `period_closed` is `1`. |
| `opaque_text_encoding` | `apostrophe-prefix-v1`; decoding is defined below. |

CSV files use UTF-8, RFC 4180 quoting, and `\n` line endings. Currency is USD
and monetary values are decimal dollars with exactly two fractional digits.
Timestamps are ISO-8601 UTC ending in `Z`. Boolean fields are `1` or `0`.

Every emailed file has `period_closed,1`. A `0` can only be seen by fetching a
running period from the read API, or by exporting one with the dated CLI
command. When it is `0` the figures are a preview: they will keep moving until
the period closes, and re-fetching is how you obtain the settled view. Two
renderings of the same closed period are byte-identical; two renderings of a
running period differ at least in `snapshot_utc`, by construction.

## Opaque text and spreadsheet safety

Database and postback identifiers remain byte-for-byte exact. In CSV only,
identifier and dimension cells use `apostrophe-prefix-v1`:

1. If a stored value begins with `=`, `+`, `-`, `@`, tab, carriage return, or an
   apostrophe, the exporter prefixes one apostrophe.
2. Otherwise it emits the value unchanged.
3. To recover the stored value, remove exactly one leading apostrophe only when
   the next character is one of the characters in step 1.

For example, stored click ID `-partner_7` is exported as `'-partner_7`. A stored
value already beginning with `'-` is exported as `''-` and still decodes without
ambiguity. This prevents spreadsheet formula execution without silently
changing the system-of-record value. Numeric metric cells—including negative
adjustments—remain numeric and do not use this encoding.

`applied_coupon_ids` is a compact JSON array inside one CSV cell. The array
preserves every captured opaque discount, promotion, or coupon ID in stored
order; `[]` means none. Consumers must parse the JSON rather than split on a
delimiter.

## Daily schema

### Section A — `section_a_daily_by_sub_id`

| Column | Meaning |
|---|---|
| `date` | Local report date under the file's timezone boundary. |
| `sub_id`, `sub_id2` | Opaque reporting dimensions. |
| `raw_clicks` | All retained non-test clicks. |
| `qualified_clicks` | Clicks eligible for attribution. |
| `deduction_reason` | Sorted `reason:count` tokens with vertical-bar separators. |
| `registrations` | Pre-payment email-capture facts in the window. |
| `purchases` | First attributed purchase facts in the window. |
| `new_purchase_value` | Gross order value for those purchases. |
| `renewals`, `renewal_value` | Renewal count and gross value in the window. |
| `total_value` | `new_purchase_value + renewal_value`. |
| `refunds` | Count of refund, chargeback, and reversal ledger deltas in the window. |
| `refunded_value` | Refund/chargeback deduction less reversals in the window. |
| `net_value` | `total_value - refunded_value`. |
| `click_to_purchase_cvr` | `purchases / qualified_clicks`, four decimals. |
| `epc_on_order_value` | New gross purchase value per qualified click. |

### Section B — `section_b_purchase_reconciliation`

| Column | Meaning |
|---|---|
| `timestamp_utc` | Original purchase time. |
| `click_id`, `sub_id`, `sub_id2` | Partner click and reporting dimensions. |
| `txid` | Stable opaque Chia purchase UUID used in postbacks. |
| `term_months` | Purchased term length. |
| `order_value` | Gross original order value. |
| `net_collected` | Cash collected on the original purchase. |
| `is_returning_customer`, `is_zero_value` | Purchase classification flags. |
| `offer`, `link_code` | Chia-minted opaque references unique to the issued link; they encode no treatment or destination semantics. |
| `applied_coupon_ids` | Compact JSON array of every captured opaque coupon-related ID. |
| `refund_state` | `none`, `partial`, or `full` as of the report-window end. |
| `refunded_amount` | Cumulative refund/active-chargeback amount through the window end. |
| `renewals_to_date` | Non-test renewals through the window end. |
| `revenue_to_date` | Net collected purchase and renewal cash less refunds/active chargebacks. |

Section B includes a purchase when that purchase, one of its renewals, or one of
its adjustments had activity in the daily window. Its cumulative columns are
always evaluated at the exact exclusive window end.

### Section C — `section_c_exclusions`

| Column | Meaning |
|---|---|
| `reason` | `b2b_partner_rail`, `admin_added`, or `deferred_sibling`. |
| `count` | Durable append-only exclusion count in the report window. |

### Section D — `section_d_refund_chargeback_adjustments`

Section D reads the immutable `AffiliateRefund` ledger. Despite its historical
model name, each row is an append-only refund, chargeback, or reversal delta.

| Column | Meaning |
|---|---|
| `timestamp_utc` | Adjustment occurrence time. |
| `adjustment_id` | Stable opaque Chia adjustment UUID. |
| `click_id`, `purchase_txid`, `sub_id`, `sub_id2` | Reconciliation references. |
| `applies_to` | `purchase` or `renewal`. |
| `renewal_cycle` | Renewal cycle number, blank for an original purchase. |
| `adjustment_type` | `refund`, `chargeback`, or `chargeback_reversal`. |
| `net_value_adjustment` | Negative when value leaves Chia; positive when a chargeback is restored. |
| `currency` | `USD`. |
| `offer`, `link_code` | Chia-minted opaque references unique to the issued link; they encode no treatment or destination semantics. |

The ledger stores only a one-way digest of Stripe's source reference, so the CSV
does not invent or claim to expose a raw Stripe refund/dispute ID.

### Representative daily CSV

All values below are synthetic and non-identifying.

```csv
Chia affiliate analytics
This is a reporting document. It is not an invoice or a statement.
report_type,daily
schema_version,chia-affiliate-daily-v1
affiliate_id,aff_demo
period,2026-08-04
timezone,UTC
window_start_utc,2026-08-04T00:00:00Z
window_end_utc,2026-08-05T00:00:00Z
period_closed,1
snapshot_utc,2026-08-05T00:00:00Z
opaque_text_encoding,apostrophe-prefix-v1

section_a_daily_by_sub_id
date,sub_id,sub_id2,raw_clicks,qualified_clicks,deduction_reason,registrations,purchases,new_purchase_value,renewals,renewal_value,total_value,refunds,refunded_value,net_value,click_to_purchase_cvr,epc_on_order_value
2026-08-04,camp_a,creative_7,125,118,no_user_agent:2|scanner_ua:5,6,3,4482.00,1,100.00,4582.00,3,200.00,4382.00,0.0254,37.98

section_b_purchase_reconciliation
timestamp_utc,click_id,sub_id,sub_id2,txid,term_months,order_value,net_collected,is_returning_customer,is_zero_value,offer,link_code,applied_coupon_ids,refund_state,refunded_amount,renewals_to_date,revenue_to_date
2026-08-04T12:00:00Z,partner_click_7,camp_a,creative_7,6c0b843e-69e1-49fd-a69d-6f371dab7c0f,6,1494.00,1400.00,0,0,off_0123456789abcdef01234567,link_demo,"[""promo_aff_launch"",""coupon_partner_20""]",partial,200.00,1,1300.00

section_c_exclusions
reason,count
b2b_partner_rail,1
admin_added,0
deferred_sibling,1

section_d_refund_chargeback_adjustments
timestamp_utc,adjustment_id,click_id,purchase_txid,sub_id,sub_id2,applies_to,renewal_cycle,adjustment_type,net_value_adjustment,currency,offer,link_code
2026-08-04T18:00:00Z,10421a12-5517-45d1-8d36-4febce4ccf62,partner_click_7,6c0b843e-69e1-49fd-a69d-6f371dab7c0f,camp_a,creative_7,purchase,,refund,-200.00,USD,off_0123456789abcdef01234567,link_demo
2026-08-04T19:00:00Z,880b4fef-e0c1-4c26-8fbb-2fd3ae42c815,partner_click_7,6c0b843e-69e1-49fd-a69d-6f371dab7c0f,camp_a,creative_7,purchase,,chargeback,-500.00,USD,off_0123456789abcdef01234567,link_demo
2026-08-04T20:00:00Z,91d51326-b913-45b3-b47f-62b598a472cc,partner_click_7,6c0b843e-69e1-49fd-a69d-6f371dab7c0f,camp_a,creative_7,purchase,,chargeback_reversal,500.00,USD,off_0123456789abcdef01234567,link_demo
```

## Monthly schema

The only section is `monthly_cohort_rollup`:

| Column | Meaning |
|---|---|
| `cohort_month`, `sub_id` | Purchase cohort and opaque dimension. |
| `purchases`, `acquired_customers` | Total and non-returning purchase counts. |
| `cumulative_renewal_count`, `cumulative_renewal_value` | Renewals through the report cutoff. |
| `cumulative_total_value` | Gross purchase plus renewal value. |
| `revenue_per_acquired_customer_day_30/60/90/180` | Mature net collected value per acquired customer; blank until mature. |
| `refund_rate`, `chargeback_rate`, `refunded_value` | Cumulative adjustment measures through the cutoff. |
| `median_click_to_purchase_lag_hours` | Median attribution lag in hours. |

Each later monthly artifact refreshes every older cohort row as horizons mature.
The report-month end is the maximum cutoff, so reproducing an older artifact
does not leak later facts into it.

### Representative monthly CSV

```csv
Chia affiliate analytics
This is a reporting document. It is not an invoice or a statement.
report_type,monthly_ltv
schema_version,chia-affiliate-monthly-v1
affiliate_id,aff_demo
period,2026-07
timezone,UTC
window_start_utc,2026-07-01T00:00:00Z
window_end_utc,2026-08-01T00:00:00Z
period_closed,1
snapshot_utc,2026-08-01T00:00:00Z
opaque_text_encoding,apostrophe-prefix-v1

monthly_cohort_rollup
cohort_month,sub_id,purchases,acquired_customers,cumulative_renewal_count,cumulative_renewal_value,cumulative_total_value,revenue_per_acquired_customer_day_30,revenue_per_acquired_customer_day_60,revenue_per_acquired_customer_day_90,revenue_per_acquired_customer_day_180,refund_rate,chargeback_rate,refunded_value,median_click_to_purchase_lag_hours
2026-06,camp_a,12,10,4,400.00,18328.00,1310.00,1298.00,,,0.1667,0.0833,300.00,5.25
```

## Trace evidence boundary

For support reconciliation Chia retains the exact frozen requested postback URL,
HTTP status or error kind, latency, and up to the first 512 decoded response-body
characters for each attempt. Response headers and the remainder of the body are
not retained. This bounded evidence is intentional: arbitrary response bodies
and headers may contain credentials, cookies, identity, or unrelated sensitive
data. The frozen URL contains the partner's own tracker credential, so trace
output is transferred only through an approved secure channel and never in an
ordinary support email.

## Changelog

### 2026-08-10 — `period_closed` and `snapshot_utc` added to the common envelope

Two key/value rows were added to the preamble of every daily and monthly file,
between `window_end_utc` and `opaque_text_encoding`.

**This changes the bytes of every export.** A consumer that reads the preamble
by key, or that skips to the blank row before the first section, is unaffected.
A consumer that reads the preamble by fixed row offset must be updated. Section
markers, headers, and data rows are unchanged in both schemas, so the schema
identifiers stay `chia-affiliate-daily-v1` and `chia-affiliate-monthly-v1`.

Why: the read API can serve a period that has not finished — a running month is
a supported request, because cohort horizons mature and "wait for the month to
close" is not a state worth waiting for. Until now such a document was
indistinguishable from a settled one: same shape, same period label, same
statement-shaped filename. `period_closed` states the difference and
`snapshot_utc` states the cutoff its figures were taken at. The same two facts
appear in the JSON representation of the report, so the two surfaces cannot
disagree about whether a number is final.

Alongside this, the read API now refuses a monthly period that has not
*started* with `400 invalid_request`. `GET /reports/monthly/2099-01` previously
returned `200` with a 2099 window and a fileable filename.