# AI search traffic and conversion worksheet

Editable manual worksheet and complete fictional reconciliation

Pack 1.0 · 15 September 2026. Original worksheet 1.0 · 11 September 2026.

Guide: https://rankecho.io/resources/prove/citation-to-session-to-conversion

## Blank worksheet

# AI search traffic and conversion reconciliation

Manual worksheet — version 1.0. Use approved pseudonymous IDs in the working file. Keep raw email, payment credentials, and other unnecessary personal data out of analytics exports and this worksheet.

## 1. Freeze the cohort before calculating
- Site / analytics property:
- Time zone:
- Qualifying landing pages:
- Qualifying source rule (for example, reviewed AI Assistant session sources):
- Entry window, inclusive:
- Per-person follow-up window:
- Report close / refund snapshot:
- Required history for first-touch classification:
- Distinct-visitor metric and reporting identity:
- Known tracked person key:
- New-account definition:
- Trial-start definition:
- Confirmed first-payment definition:
- Paid-at-close definition:
- Currency and collected-amount definition:
- First-touch rule:
- Assisted-only rule:

## 2. Preserve visibility as separate context
| Visibility source | Planned checks | Eligible observed checks | Unavailable / failed | Owned-link citation observations | Evidence pointer |
| --- | ---: | ---: | ---: | ---: | --- |
| [Fixed prompt and surface panel] | | | | | |

Do not divide sampled citations by sessions, visitors, signups, accounts, payments, or revenue. The sampled-check population is not the visitor cohort.

## 3. Reconcile source control totals
| Unit | Raw count | Deduped count | Source/export | Definition or query | Excluded from joined rates | Reason |
| --- | ---: | ---: | --- | --- | ---: | --- |
| Identifiable AI referral sessions | | | | | | |
| Distinct analytics visitors | | | | | | |
| Direct / unattributed sessions | | | | | All | Source unknown; do not assign to AI |
| Signup events / people / new accounts | | | | | | |
| Trial-start events / accounts | | | | | | |
| Payment attempts / confirmed first-payment accounts | | | | | | |
| Gross collected first-payment amount | | | | | | |
| Succeeded refunds against eligible payments | | | | | | |
| Net collected first-payment amount at report close | | | | | | |

## 4. Build the identity and eligibility waterfall
| Disposition | Distinct people or declared person proxies | Rule | Evidence pointer |
| --- | ---: | --- | --- |
| Distinct visitors in qualifying sessions | | Starting population | |
| Less internal / test identities | | Mutually exclusive exclusion | |
| Less accounts created before the qualifying entrance | | Mutually exclusive exclusion | |
| Less unresolved analytics-to-person joins | | Eligibility unknown; never label as non-converters | |
| Less entrants without the full follow-up window | | Hold for a later mature cohort | |
| Known tracked eligible people | | Rate denominator | |

Known tracked eligible people = distinct visitors − internal/test − pre-existing accounts − unresolved joins − immature entrants.

## 5. Keep one joined row per eligible person
| person_key | qualifying_session_key | qualifying_time | landing_page | first_known_source | attribution_cut | eligible_reason | first_account_id | signup_time | trial_start_time | first_successful_payment_time | amount_received | currency | refund_amount_at_close | paid_at_close | evidence_pointers |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | ---: | --- | ---: | --- | --- |
| [pseudonymous ID] | | | | | AI first-touch / AI assisted-only | | | | | | | | | | |

If several people join one account, choose an account-level denominator before calculating an account rate. If one person creates several accounts, predeclare which first eligible account is retained. Keep duplicate events as a raw control total, not extra people or accounts.

## 6. Calculate only compatible, mature rates
- Matched-signup rate = eligible people with one matched first signup ÷ known tracked eligible people.
- Trial-start rate = unique eligible trial accounts ÷ unique eligible new accounts.
- Confirmed first-payment rate = unique eligible accounts with a successful first payment inside the follow-up window ÷ unique eligible new accounts.
- Paid-at-close rate = unique eligible accounts still paid at report close ÷ unique eligible new accounts.
- Net collected first-payment amount = eligible amount received − succeeded refunds against those payments as of report close.

Repeat the account calculations separately for AI first-touch and AI assisted-only. Do not add overlapping attribution cuts. Do not annualize one payment, substitute checkout or trial events for payment, or call collected cash accounting revenue unless finance uses that definition.

## 7. Exceptions and interpretation
- Unmatched identities:
- Duplicate people, accounts, events, invoices, or payments:
- Failed, pending, zero-value, test, refunded, disputed, or out-of-window payments:
- Currency conversions and exchange-rate date:
- Consent, tag, attribution, or billing changes:
- Material concurrent campaigns or product changes:
- Result label: observed after an identifiable AI referral / AI first-touch / AI assisted-only / unknown
- What the evidence cannot establish, including causal lift or ROI:
- Owner and next reconciliation date:

## Worked fictional reconciliation

### What does a complete SaaS reconciliation look like?

This is original fictional data for Morrow Metrics, a fictional B2B SaaS company. It is not RankEcho customer data, a benchmark, or a claim about typical AI-search performance.

Scope: qualifying identifiable AI-referral entrances occurred from 1–30 June 2026 UTC on three declared resource pages. The company used a 90-day known-session history for its mutually exclusive attribution cuts and followed each entrant for 60 full days. The report closed on 31 August 2026 after every June entrant matured. Five July entrants visible in a later export belong to a different immature cohort and are absent from every June total below.

The fictional visibility panel had 48 planned prompt-by-surface checks: 42 eligible observations, six unavailable checks, and 14 eligible observations containing an owned link. The analytics export independently contained 52 identifiable AI-referral sessions from 41 distinct visitors. These populations were not joined, so no quotient between the 14 sampled observations and 52 sessions is computed or interpretable.

| Measure | Fictional result | Unit | Treatment |
| --- | --- | --- | --- |
| Sampled citation observations | 48 planned fixed checks; 42 eligible observations; 6 unavailable; 14 contained an owned link | Prompt-by-surface observations | Visibility context only; no citation-to-session or citation-to-revenue calculation |
| Identifiable AI referral sessions | 52 | Sessions | Reviewed analytics source export; repeated sessions remain sessions |
| Distinct analytics visitors | 41 | Distinct visitors under the declared reporting identity | A reporting unit, not 41 verified humans or accounts |
| Direct / unattributed traffic | 73 | Sessions | Shown separately and excluded; none is reassigned to AI |
| Known tracked eligible people | 24 | People in this synthetic one-person-per-identity cohort | Full 60-day follow-up and approved join available |
| Matched signup people and new accounts | 11 raw signup events → 9 people → 9 unique new accounts | People, events, and accounts | Two repeated events removed; every signup person creates one account in this example |
| Trial starts | 8 raw trial-start events → 6 unique accounts | Events and accounts | Two repeated events removed |
| Confirmed first payments | 4 attempts → 3 unique accounts with a successful amount received | Attempts and paid accounts | One unsuccessful attempt excluded |
| Paid accounts at report close | 2 | Unique accounts | One of the three first payments was fully refunded before close |
| First-payment amounts | USD 347 gross − USD 49 succeeded refund = USD 298 net collected | US dollars | Cash collected as defined for this worksheet; no ARR, LTV, accounting-revenue, or ROI claim |

### How do the eligible people and exclusions reconcile?

The 41 distinct visitors split into four mutually exclusive dispositions: 24 known tracked eligible people, two internal or test identities, eight identities joined to accounts created before the qualifying entrance, and seven identities without an approved analytics-to-person join. The arithmetic is 41 − 2 − 8 − 7 − 0 immature = 24.

The seven unresolved identities remain an identity gap. They are excluded from joined rates because eligibility and outcomes cannot be established; they are not counted as seven non-converters. No June entrant is excluded for immaturity because the report waited until all 60-day windows closed.

The fictional company also recorded 73 Direct sessions on the included pages. Those sessions sit outside the 52 identifiable AI-referral sessions and the 41-visitor starting population. Similar timing or landing pages do not establish their source, so none is assigned to AI.

| Disposition | Visitors | Rate treatment |
| --- | --- | --- |
| Known tracked, new-account eligible, and fully mature | 24 | Included as the person-level denominator |
| Internal or test | 2 | Excluded by declared identity rule |
| Account existed before qualifying entrance | 8 | Excluded from the new-account cohort |
| Identity join unresolved | 7 | Excluded and reported as unknown, never as a non-conversion |
| Immature June entrant | 0 | None; report waited for full follow-up |
| Total distinct analytics visitors | 41 | Control total |

### Which conversion rates are valid in the fictional cohort?

The person-level signup calculation is 9 matched signup people ÷ 24 known tracked eligible people = 37.5%. In this synthetic example each signup person created exactly one new account, so the next calculations have nine unique eligible new accounts as their denominator.

Six of nine new accounts started a trial, or 66.7%. Three of nine had a confirmed successful first payment inside the same 60-day follow-up, or 33.3%; equivalently, three of six trial accounts first paid, or 50.0%. At report close, two of nine new accounts remained paid, or 22.2%, after one fully refunded first payment.

The three successful first payments were USD 149, USD 149, and USD 49: USD 347 gross. The USD 49 payment received a succeeded full refund before close, leaving two paid accounts and USD 298 net collected first-payment amount. Paid-account counts and currency amounts stay in separate columns. The worksheet does not annualize the payments or claim accounting revenue, lifetime value, incremental lift, or ROI.

| Calculation | Compatible numerator and denominator | Result |
| --- | --- | --- |
| Matched-signup rate | 9 matched signup people ÷ 24 eligible people | 37.5% |
| Trial-start rate | 6 unique trial accounts ÷ 9 unique new accounts | 66.7% |
| New-account first-payment rate | 3 confirmed first-payment accounts ÷ 9 unique new accounts | 33.3% |
| Trial-to-first-payment rate | 3 confirmed first-payment accounts ÷ 6 unique trial accounts | 50.0% |
| Paid-at-close rate | 2 paid-at-close accounts ÷ 9 unique new accounts | 22.2% |
| Net collected first-payment amount | USD 347 received − USD 49 succeeded refund | USD 298 |

### How should first-touch and assisted conversions be reported?

Morrow Metrics defines AI first-touch as an identifiable AI referral on the earliest known site session in the 90-day history. AI assisted-only means an identifiable AI referral occurred before signup, while the earliest known session came from another source. These cuts are mutually exclusive in this worksheet; an overlapping assisted definition would require separate tables whose totals are never added.

The cuts describe recorded sequences under a chosen history window. Missing earlier visits, identity changes, consent, and unattributed sessions can change the classification. Neither cut estimates what would have happened without the AI visit.

| Attribution cut | Eligible people | Signup people / accounts | Trial accounts | First-payment accounts | Paid at close | Net collected amount |
| --- | --- | --- | --- | --- | --- | --- |
| AI first-touch | 15 | 5 | 4 | 2 | 2 | USD 298 |
| AI assisted-only | 9 | 4 | 2 | 1 | 0 | USD 0 after a USD 49 refund |
| Total mature joined cohort | 24 | 9 | 6 | 3 | 2 | USD 298 |

## Use with your own records

Complete the cohort definitions and preserve source controls. Reconcile identities and billing records in your approved environment, then enter reviewed summary counts in the workbook. Its formulas perform arithmetic, not identity joins, eligibility decisions or causal attribution. Keep sampled citations and Direct traffic separate. Repeat the account calculations for mutually exclusive attribution cuts.

[Preview the Proof Loop](https://rankecho.io/demo/proof) or [evaluate the Proof Loop](https://rankecho.io/product/proof-loop). RankEcho does not natively connect this worksheet to analytics or billing providers.