> ## Documentation Index
> Fetch the complete documentation index at: https://docs.staging.metronome.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Trial data reference

> Metric definitions, queries, and reports for trial starts, conversion, attrition, and retention.

Measure your trials with [Data Export](/guides/reporting-insights/data-export/overview) SQL or, without SQL, with [in-app reports](/guides/reporting-insights/in-app-reporting). Both assume the [tagging and conventions](/guides/pricing-packaging/billing-model-guides/free-trials/overview#tagging-and-conventions) from the overview.

## Definitions

| Metric | Definition |
| - | - |
| Trial starts | Contracts whose `contract_type` custom field is `trial`, counted by `starting_at` |
| Conversions | `contracts_transitions` rows whose `from_contract_id` is a trial contract, where **`date`** is the effective conversion time |
| Conversion rate (cohort) | Conversions within N days ÷ trial starts, by trial-start week or month |
| Time to convert | `transition.date − trial.starting_at` |
| Exhausted | Trial credit with `balance = 0` and no expiration entry with a negative amount |
| Expired unused (attrition) | Trial credit whose ledger has a `credit_segment_expiration` entry with a negative amount, **and** no transition from that contract |
| Active trials on day D | Trial contracts with `starting_at ≤ D < coalesce(ending_before, ∞)` and no transition before D |
| Post-conversion retention | Converted customers with a finalized invoice where `total > 0` in month M after the conversion month |

## Data Export SQL

<Warning>
  **Filter `environment_type` in every query.** Sandbox and production data share a single export destination, so scope each query to `environment_type = 'PRODUCTION'`.
</Warning>

Notes that apply to every query:

* Contract, balance, and transition tables are **daily snapshots** (`snapshot_id`, `updated_at` = export time), so keep only the most recently exported row per id. Customer, alert, and invoice tables are incremental. See [data availability](/guides/reporting-insights/data-export/overview#data-availability) for the transfer frequency and freshness of each table.
* JSON columns export as **strings**: use `json_extract_scalar` (Trino/Athena), `JSON_VALUE` (BigQuery), or `PARSE_JSON(...):path::string` (Snowflake).
* Custom fields live under **`metadata.custom_fields`**, not a `custom_fields` column (except `customer.custom_fields`, which is a real column).
* Table and column names follow the [database reference](/guides/reporting-insights/data-export/database-reference); a modelled warehouse layer on top may rename or split schemas. More query patterns are in the [SQL cookbook](/guides/reporting-insights/data-export/cookbook).

```sql theme={null}
-- One current row per contract / balance / transition (snapshot tables): latest export wins
WITH contracts AS (
  SELECT id, customer_id, name, starting_at, ending_before, archived_at, created_at,
         json_extract_scalar(metadata, '$.custom_fields.contract_type') AS contract_type
  FROM (SELECT c.*, row_number() OVER (PARTITION BY c.id ORDER BY c.updated_at DESC) AS rn
        FROM contracts_contracts c WHERE c.environment_type = 'PRODUCTION')
  WHERE rn = 1
),
trials AS (SELECT * FROM contracts WHERE contract_type = 'trial'),
conversions AS (
  SELECT t.from_contract_id AS trial_contract_id, t.to_contract_id AS paid_contract_id, t.date AS converted_at
  FROM (SELECT t.*, row_number() OVER (PARTITION BY t.id ORDER BY t.updated_at DESC) AS rn
        FROM contracts_transitions t WHERE t.environment_type = 'PRODUCTION') t
  JOIN trials tr ON tr.id = t.from_contract_id
  WHERE t.rn = 1 AND upper(t.type) = 'RENEWAL'
),
-- Trial credits with their expiration entries (ledger is a JSON array string)
trial_credits AS (
  SELECT b.id AS credit_id, b.contract_id, b.customer_id, b.balance,
         json_extract_scalar(b.access_schedule, '$.schedule_items[0].date')     AS segment_start,
         json_extract_scalar(b.access_schedule, '$.schedule_items[0].end_date') AS segment_end,
         b.ledger
  FROM (SELECT b.*, row_number() OVER (PARTITION BY b.id ORDER BY b.updated_at DESC) AS rn
        FROM contracts_balances b WHERE b.environment_type = 'PRODUCTION') b
  WHERE b.rn = 1 AND b.type = 'credit'
    AND json_extract_scalar(b.metadata, '$.custom_fields.trial') = 'true'
),
expirations AS (
  SELECT tc.credit_id, tc.contract_id,
         CAST(json_extract_scalar(e, '$.amount') AS double)  AS expired_amount,    -- negative in the export
         from_iso8601_timestamp(json_extract_scalar(e, '$.timestamp')) AS expired_at
  FROM trial_credits tc
  CROSS JOIN UNNEST(CAST(json_parse(tc.ledger) AS ARRAY(JSON))) AS u(e)
  WHERE json_extract_scalar(e, '$.type') = 'credit_segment_expiration'
),
-- Daily trial funnel: starts, conversions, expired-unused (attrition).
-- Aggregate each metric on its own date, then join. Conversions and expirations land days
-- after the start, so a day spine built only from starting_at would drop most of them.
starts_by_day AS (
  SELECT date(starting_at) AS day, count(DISTINCT id) AS n FROM trials GROUP BY 1
),
conversions_by_day AS (
  SELECT date(converted_at) AS day, count(DISTINCT trial_contract_id) AS n FROM conversions GROUP BY 1
),
expired_unused_by_day AS (
  SELECT date(ex.expired_at) AS day, count(DISTINCT ex.contract_id) AS n
  FROM expirations ex
  LEFT JOIN conversions cv ON cv.trial_contract_id = ex.contract_id
  WHERE cv.trial_contract_id IS NULL AND ex.expired_amount < 0
  GROUP BY 1
),
days AS (
  SELECT day FROM starts_by_day
  UNION SELECT day FROM conversions_by_day
  UNION SELECT day FROM expired_unused_by_day
)
SELECT d.day,
       coalesce(s.n, 0) AS trial_starts,
       coalesce(c.n, 0) AS conversions,
       coalesce(e.n, 0) AS expired_unused
FROM days d
LEFT JOIN starts_by_day         s ON s.day = d.day
LEFT JOIN conversions_by_day    c ON c.day = d.day
LEFT JOIN expired_unused_by_day e ON e.day = d.day
ORDER BY d.day;
```

```sql theme={null}
-- Cohort conversion rate and time-to-convert (trial start month)
SELECT date_trunc('month', tr.starting_at) AS cohort,
       count(*)                                                        AS trials,
       count(cv.trial_contract_id)                                     AS converted,
       round(100.0 * count(cv.trial_contract_id) / count(*), 1)        AS conversion_pct,
       approx_percentile(date_diff('hour', tr.starting_at, cv.converted_at) / 24.0, 0.5) AS median_days_to_convert
FROM trials tr LEFT JOIN conversions cv ON cv.trial_contract_id = tr.id
GROUP BY 1 ORDER BY 1;
```

```sql theme={null}
-- Post-conversion retention: months with a paid invoice, by conversion cohort
SELECT date_trunc('month', cv.converted_at) AS cohort,
       date_diff('month', date_trunc('month', cv.converted_at), date_trunc('month', i.start_timestamp)) AS month_n,
       count(DISTINCT i.customer_id) AS paying_customers
FROM conversions cv
JOIN contracts pc ON pc.id = cv.paid_contract_id
JOIN invoice i ON i.customer_id = pc.customer_id AND i.status = 'FINALIZED' AND i.total > 0
              AND i.environment_type = 'PRODUCTION' AND i.start_timestamp >= cv.converted_at
GROUP BY 1, 2 ORDER BY 1, 2;
```

(Retention here means "billed for usage in month N"; for usage-based retention independent of billing, use `line_item.quantity` by `product_id` instead.)

## Without Data Export

Data Export is an add-on. These [in-app reports](/guides/reporting-insights/in-app-reporting) cover the essentials:

| Need | Report | What to read |
| - | - | - |
| Trial expirations with all tags | **Commit expiration ledger entries by month** | `revenue_category = credit_segment_expiration`; `ledger_entry_amount`, which is **positive** here and negative in the API and export; `customer_custom_fields`, `contract_metadata`, `balances_metadata` |
| Conversions | **Contracts by created date** | `transitioned_from_contract_id` / `transitioned_to_contract_id`, flattened onto each contract row; `metadata` holds `contract_type` |
| Trial grants | **Commits and credits by created date** | One row per credit, plus `metadata`; archived credits are excluded |
| Paid activity | **Invoices by status and effective date** | Finalized invoices only, with `finalized_at`; `metadata` carries contract and customer custom fields |

For real-time per-customer state, `POST /v1/contracts/customerBalances/list` returns the credit with `custom_fields`, `access_schedule`, `ledger`, and `balance`.