Accounts are not users

Among user accounts (non-solver) that swapped in a month, the share that transacted on at least two distinct days. Since April, 99.96% of 4.2M 64-hex implicit accounts are active on exactly one day and 80% have a single entry, consistent with per-swap deposit accounts. Account counts and account-level retention therefore cannot measure adoption.

Accounts are not users

Table

99.96% of 4.2M implicit accounts are active on exactly one day. Below: the share of accounts active on 2 or more days within a month.

Source · pipeline/sql/76_user_stickiness.sql and 77_account_quality.sql. Months under 28 cached days are gaps. · 2026-01-01 → 2026-10-01

0.22%

Latest (2026-10-01)

0.25%

Peak (2026-01-01)

0.04%

Low (2026-08-01)

0.10%

Mean of shown points

Points

How it is computed

Classify accounts as solver or user (>= 100 distinct counterparties = solver).
For each user account count distinct active days within the month.
Report the share with 2+ days, 5+ days, and the top-100 volume share.

Read this before quoting it

Mechanism (single-use deposit accounts) is inferred from behaviour and not yet confirmed against the Intents documentation.
Repeat EVM and named accounts are the only retention signal available.

The query

Download user_stickiness.csv

Runs in DuckDB over the local cache of BigQuery logs. Re-run with make metrics or python -m pipeline.metrics user_stickiness. Metric queries read the base tables built by 00_setup.sql.

pipeline/sql/76_user_stickiness.sql

1-- Per month: how many distinct users, what share transacted on >=2 / >=5 distinct days, and share of
2-- user-received volume from the top 100 users. (Counts days active within the month only.)
3WITH d AS (
4  SELECT date_trunc('month', l.date) AS month, l.account_id, count(DISTINCT l.date) AS n_days,
5         sum(l.usd_best) FILTER (WHERE l.amount_raw > 0) AS vol
6  FROM legs_usd l JOIN pair_receipts USING (receipt_id) JOIN account_roles r ON r.account_id = l.account_id
7  WHERE NOT l.failed AND r.role = 'user' GROUP BY ALL),
8k AS (SELECT *, row_number() OVER (PARTITION BY month ORDER BY coalesce(vol, 0) DESC) AS rk FROM d)
9SELECT CAST(month AS DATE) AS month, (SELECT count(DISTINCT date) FROM legs WHERE date_trunc('month', date) = k.month) AS days_covered, count(*) AS users,
10       avg((n_days >= 2)::INT) AS share_2plus_days, avg((n_days >= 5)::INT) AS share_5plus_days,
11       sum(vol) FILTER (WHERE rk <= 100) / sum(vol) AS top100_volume_share, sum(vol) AS user_volume_usd
12FROM k GROUP BY k.month ORDER BY month;

pipeline/sql/77_account_quality.sql

1-- Account quality since the contiguous cache start (2026-04-01): how many accounts are single-use, by address format.
2-- Basis for "active accounts are not users" in THESIS.md.
3WITH s AS (SELECT account_id, date, entry_id FROM legs WHERE NOT failed AND date >= DATE '2026-04-01' GROUP BY ALL),
4a AS (SELECT account_id, count(*) AS n_entries, count(DISTINCT date) AS n_days,
5        CASE WHEN regexp_matches(account_id, '^[0-9a-f]{64}$') THEN 'implicit_hex'
6             WHEN regexp_matches(account_id, '^0x[0-9a-fA-F]{40}$') THEN 'evm_address' ELSE 'named_account' END AS fmt
7      FROM s GROUP BY 1)
8SELECT fmt, count(*) AS accounts, avg((n_days = 1)::INT) AS share_one_day,
9       avg((n_entries = 1)::INT) AS share_single_entry, avg(n_entries) AS mean_entries
10FROM a GROUP BY 1 ORDER BY 2 DESC;

pipeline/sql/00_setup.sql (base tables: legs, prices, roles)

pipeline/sql/00_setup.sql

1-- Base tables. Inputs: raw parquet cache ({RAW}) plus the `assets` and `prices` tables registered by metrics.py
2-- from data/reference/. Nothing here is aggregated; every metric file below builds on these.
3
4-- forward-filled daily price per key (calendar x key), so a missing day reuses the last known price
5CREATE OR REPLACE TABLE prices_filled AS
6WITH cal AS (SELECT CAST(d AS DATE) AS date FROM generate_series(DATE '2024-12-31', DATE '2026-10-30', INTERVAL 1 DAY) t(d)),
7     keys AS (SELECT DISTINCT price_key FROM prices)
8SELECT k.price_key, c.date,
9       last_value(p.price IGNORE NULLS) OVER (PARTITION BY k.price_key ORDER BY c.date) AS price
10FROM keys k CROSS JOIN cal c LEFT JOIN prices p ON p.price_key = k.price_key AND p.date = c.date;
11
12-- one row per (token_diff entry, asset); entry_id identifies one token_diff entry (one account's side of a swap)
13CREATE OR REPLACE TABLE legs AS
14SELECT entry_id, CAST(ts AS DATE) AS date, ts, failed, intent_hash, account_id, receipt_id,
15       e.key AS asset, TRY_CAST(e.value AS DOUBLE) AS amount_raw
16FROM (SELECT row_number() OVER () AS entry_id, *,
17             unnest(map_entries(CAST(diff::JSON AS MAP(VARCHAR, VARCHAR)))) AS e
18      FROM read_parquet('{RAW}/swaps/*.parquet'));
19
20-- usd: priced from the price table. usd_imputed: for an entry with exactly ONE unpriced asset, the value implied
21-- by value conservation (minus the sum of the priced legs of the same entry), only if its sign matches the leg.
22-- usd_best = usd, else usd_imputed. Imputed values are always reported separately, never silently mixed in.
23CREATE OR REPLACE TABLE legs_usd AS
24WITH base AS (
25  SELECT l.*, a.symbol, a.origin_chain,
26         l.amount_raw / pow(10, a.decimals) AS qty,
27         CASE WHEN a.price_key = 'USD' THEN 1.0 ELSE p.price END AS price,
28         l.amount_raw / pow(10, a.decimals) * CASE WHEN a.price_key = 'USD' THEN 1.0 ELSE p.price END AS usd
29  FROM legs l
30  LEFT JOIN assets a ON a.asset = l.asset
31  LEFT JOIN prices_filled p ON p.price_key = a.price_key AND p.date = l.date),
32e AS (
33  SELECT *, sum(usd) OVER (PARTITION BY entry_id) AS priced_sum,
34         count(*) FILTER (WHERE usd IS NULL) OVER (PARTITION BY entry_id) AS n_unpriced
35  FROM base)
36SELECT * EXCLUDE (priced_sum, n_unpriced),
37       CASE WHEN usd IS NULL AND n_unpriced = 1 AND priced_sum IS NOT NULL
38                 AND sign(-priced_sum) = sign(amount_raw) THEN -priced_sum END AS usd_imputed,
39       coalesce(usd, CASE WHEN usd IS NULL AND n_unpriced = 1 AND priced_sum IS NOT NULL
40                               AND sign(-priced_sum) = sign(amount_raw) THEN -priced_sum END) AS usd_best
41FROM e;
42
43-- fees_collected per entry, priced the same way
44CREATE OR REPLACE TABLE fees_usd AS
45SELECT CAST(f.ts AS DATE) AS date, f.failed, f.e.key AS asset,
46       TRY_CAST(f.e.value AS DOUBLE) / pow(10, a.decimals) *
47         CASE WHEN a.price_key = 'USD' THEN 1.0 ELSE p.price END AS usd
48FROM (SELECT ts, failed, unnest(map_entries(CAST(fees::JSON AS MAP(VARCHAR, VARCHAR)))) AS e
49      FROM read_parquet('{RAW}/swaps/*.parquet') WHERE fees <> '{}') f
50LEFT JOIN assets a ON a.asset = f.e.key
51LEFT JOIN prices_filled p ON p.price_key = a.price_key AND p.date = CAST(f.ts AS DATE);
52
53-- NEP-245 mint/burn (deposits, withdrawals, refunds); token_ids and amounts are parallel arrays
54CREATE OR REPLACE TABLE mt_usd AS
55SELECT CAST(m.ts AS DATE) AS date, m.failed, m.event, m.memo, m.owner_id, m.asset,
56       TRY_CAST(m.amt AS DOUBLE) / pow(10, a.decimals) *
57         CASE WHEN a.price_key = 'USD' THEN 1.0 ELSE p.price END AS usd,
58       a.origin_chain, a.symbol, a.asset IS NULL AS unknown_asset
59FROM (SELECT ts, failed, event, memo, owner_id,
60             unnest(from_json(token_ids::JSON, '["VARCHAR"]')) AS asset,
61             unnest(from_json(amounts::JSON, '["VARCHAR"]')) AS amt
62      FROM read_parquet('{RAW}/mt/*.parquet')) m
63LEFT JOIN assets a ON a.asset = m.asset
64LEFT JOIN prices_filled p ON p.price_key = a.price_key AND p.date = CAST(m.ts AS DATE)
65WHERE m.asset LIKE 'nep141:%' OR m.asset LIKE 'nep245:%';
66
67
68-- Swap pairing: a receipt whose successful entries belong to exactly two accounts is one user<->solver swap.
69CREATE OR REPLACE TABLE pair_receipts AS
70SELECT receipt_id, min(account_id) AS a, max(account_id) AS b
71FROM (SELECT DISTINCT receipt_id, account_id FROM legs WHERE NOT failed)
72GROUP BY receipt_id HAVING count(*) = 2;
73
74-- Solver heuristic (documented in METHODOLOGY): an account is a solver if it faced >= 100 distinct counterparties
75-- across the window. Users face a handful of solvers; solvers face thousands of users. Not an on-chain label.
76CREATE OR REPLACE TABLE account_roles AS
77WITH sides AS (SELECT a AS account_id, b AS cp FROM pair_receipts UNION ALL SELECT b, a FROM pair_receipts)
78SELECT account_id, count(DISTINCT cp) AS n_counterparties, count(*) AS n_swaps,
79       CASE WHEN count(DISTINCT cp) >= 100 THEN 'solver' ELSE 'user' END AS role
80FROM sides GROUP BY account_id;