Spot cohort analysis — the aggTrades walk

Spot cohort analysis — the aggTrades walk

The endpoint: GET https://api.binance.com/api/v3/aggTrades?symbol=ZECUSDT

This returns aggregated trades — each row is one taker order that may have filled against multiple maker orders, merged into a single entry. Every row has:

  • p — price
  • q — quantity in coins
  • a — the aggregated trade ID (sequential, used for paging)
  • T — timestamp in milliseconds
  • m — the buyer/seller flag. If m=true, the maker was the buyer, which means the taker was SELLING. If m=false, the taker was BUYING. This is counter-intuitive — m stands for "is the buyer the maker", not "is this a buy."

Dollar value: multiply p × q for each trade. This is what you sort into buckets.

The size buckets:

  • Retail: $0 – $1,000
  • Small: $1,000 – $10,000
  • Mid: $10,000 – $50,000
  • Large: $50,000 – $250,000
  • Whale: $250,000+

How to page through time:

  1. Get a seed trade at your start time: ?symbol=ZECUSDT&startTime=<unix_ms>&endTime=<unix_ms+60000>&limit=1
  2. Read the trade ID (a field) from the seed
  3. Page forward: ?symbol=ZECUSDT&fromId=<id>&limit=1000
  4. After each batch, set fromId to the last trade's a + 1
  5. Stop when len(rows) < 1000 (end of data) or you hit your request cap
  6. Add a small sleep between calls (0.04–0.05s) to stay well under rate limits

What to calculate per bucket:

  • Total buy dollars and count of buy orders
  • Total sell dollars and count of sell orders
  • Net = buy - sell (positive = that tier is buying)
  • Tilt = net / gross × 100 (how one-sided, as a percentage)

The gotchas:

  • High-volume coins (BTC, ZEC) can produce 10,000+ trades per minute. A 24-hour walk on BTC may need 2,500+ requests and take 5+ minutes. Cap your requests and accept partial coverage rather than timing out.
  • A 30-minute recent window (walking backward from the latest trade ID) gives a usable snapshot in under 30 seconds for any coin.
  • For multi-day analysis on high-volume coins, run in the background.
  • Prints over $50k or $100k are worth logging individually with timestamp and direction — they show when large orders hit.

Leads here