# RetroPrice (Film Price Guide) — Case Study

**Standalone build narrative · production snapshot March 2026**

From zero to **901,713** sold-listing records: an automated pipeline for physical media collectors that ingests eBay comps, reads Reddit for early demand, scores market “heat,” and serves a full PHP/Python web product—with honest ML where short-term price prediction is noisy.

---

## Executive summary

Collectors face opaque markets: the same title can swing by format, condition, grading, season, and social hype. **RetroPrice** (Film Price Guide) is a data product we built to answer three questions:

1. **What did this actually sell for?** (medians from sold listings, not ask prices)
2. **Is attention building?** (Reddit mention velocity across collector communities)
3. **Buy, hold, or sell?** (0–100 wave scores blending scarcity, social signal, and seasonality)

The system ran in production for roughly seven months with twice-daily ingestion, ~7,500 new listings per day, and **38,075** wave analyses. A weekly scikit-learn model predicts 7-day price change; we blend it with rule-based wave output so weak models do not override stable heuristics.

This document is self-contained. It does not depend on the live app being online.

---

## The problem

Physical media pricing is fragmented:

- **Format splits the market** — DVD volume dominates; LaserDisc and graded VHS carry premiums on tiny supply.
- **“Streaming-first” releases** can create accidental scarcity on disc (limited boutique runs).
- **Seasonality and liquidation** — winter volume vs. summer nostalgia/convention cycles show up in the data.
- **No single source of truth** — collectors compare thrift shelves, eBay solds, and forum chatter by hand.

We set out to unify **sold comps**, **community signal**, and **repeatable scoring** in one pipeline and product.

---

## What we shipped

| Surface | What it does |
|--------|----------------|
| **Price guide** | Sold medians and format breakdowns per catalog title |
| **Search & title pages** | TMDB-backed catalog with comp history |
| **Thrift check** | In-store deal check vs. recent sold medians |
| **Goodwill deals** | Polled listings scored against internal thresholds |
| **Amazon new releases** | Hot physical media drops cross-referenced with interest |
| **Collector guides** | SEO guides from structured templates (grading, hunting, formats) |
| **Wave analysis** | Dashboard of heat scores, scarcity, Reddit metrics |
| **Public collections** | Community shelves and watchlists (authenticated) |
| **Public read API (v1)** | JSON endpoints for prices, heating-up titles, integrators |

**Stack:** PHP 8.x (web + crawlers), Python 3.12 (analysis + ML), PostgreSQL (JSONB `price_history`), Redis (cache + cron locks), scikit-learn, optional Ollama for market notes, Alpine.js on the frontend. Deployed with **Vercel** for HTTP and **Railway** for scheduled cron workers sharing Postgres and Upstash Redis.

---

## Architecture

```
┌─────────────────────────────────────────────────────────┐
│              Daily pipeline (cron, 2× per day)           │
│                                                         │
│  eBay crawl ──→ Reddit scan ──→ AI pricing ──→ Wave     │
│  (PHP)          (PHP)           (Python)        analyzer│
│                                                (Python) │
│                       │                           │     │
│                       ▼                           ▼     │
│              ┌─────────────────┐        ┌──────────────┐│
│              │   PostgreSQL    │        │ ML predict   ││
│              │   price_history │        │ (sklearn)    ││
│              └────────┬────────┘        └──────────────┘│
│                       ▼                                 │
│              ┌─────────────────┐                        │
│              │  PHP web app    │                        │
│              │  guides · API   │                        │
│              └─────────────────┘                        │
└─────────────────────────────────────────────────────────┘
```

**Design choices worth calling out:**

- **JSONB for listings** — eBay payloads vary; store full documents, index sold price fields for aggregates.
- **Two runtimes, one orchestration** — PHP for Guzzle/eBay OAuth; Python for pandas/sklearn; shell dispatch ties cron steps together.
- **Signal-first wave batch** — analyze Reddit-active titles before random catalog fill (see *Pipeline evolution* below).

---

## Scale (March 2026)

| Metric | Value |
|--------|------:|
| Price records | 901,713 |
| Catalog titles (TMDB-tracked) | 3,770 |
| Titles with comp data | 3,753 |
| Wave analyses | 38,075 |
| Formats tracked | 5 (DVD, Blu-ray, 4K, VHS, LaserDisc) |
| Subreddits monitored | 16 |
| Movies with Reddit signal (30 days) | 120 |
| ML labeled samples (7-day outcome) | 7,868 |
| Collection start | 26 Aug 2025 |
| Typical daily ingest | ~7,500 listings |

### Format shape of the market

| Format | Listings | Titles | Avg sold |
|--------|----------|--------|----------|
| DVD | 376,539 | 3,251 | $12.14 |
| Blu-ray | 293,176 | 2,177 | $18.21 |
| 4K UHD | 154,007 | 852 | $32.37 |
| LaserDisc | 73,453 | 747 | $42.05 |
| VHS | 4,538 | 939 | $22.03 |

LaserDisc averages highest per listing—low supply, dedicated base. VHS averages exceed DVD despite far fewer listings, driven by sealed/graded outliers.

### Surprise valuations (from production aggregates)

| Title | Format | Avg sold | Listings |
|-------|--------|----------|----------|
| *Akira* (1988) | VHS | $1,543.99 | 10 |
| *The Ballad of Buster Scruggs* (2018) | Blu-ray | $1,293.44 | 19 |
| *The Matrix* (1999) | LaserDisc | $1,098.44 | 75 |
| *U-571* (2000) | LaserDisc | $804.15 | 12 |
| *The Iron Giant* (1999) | LaserDisc | $446.10 | 11 |

*Buster Scruggs* illustrates streaming-first limited physical runs creating comp spikes unrelated to “classic” demand.

---

## Phase 1 — Data collection

**eBay Browse API crawler (PHP), cron at 02:00 and 14:00 UTC:**

1. Select ~100 catalog movies per run (rotation across 3,770 titles).
2. Query all target formats per title.
3. Persist listing/sold metadata into `price_history` as JSONB.

Additional feeds: **Heritage Auctions** for high-end graded results, **Goodwill** poller, **Amazon** release tracking, **Reddit** OAuth scans.

---

## Phase 2 — Wave analysis

**Question:** when should a collector buy or sell?

**Wave score (0–100)** combines:

| Component | Weight | Inputs |
|-----------|--------|--------|
| eBay scarcity | 40% | Listing count, sell-through, scarcity band |
| Reddit signal | 40% | Mentions, engagement, cross-subreddit spread, price language |
| Seasonality | 20% | Historical seasonal multipliers |

**Trending helper (Reddit):**

```text
trending = mentions×10 + avg_upvotes×2 + avg_comments×3 + subreddit_count×5
```

**Subreddits (16):** r/VHS, r/dvdcollection, r/criterion, r/boutiquebluray, r/Steelbooks, r/movies, r/TrueFilm, r/4kbluray, r/LaserDisc, r/horror, r/scifi, r/animation, r/flipping, r/MovieDeals, r/vintagemovies, r/bluraycollectors.

**Example high-signal rows (snapshot):**

| Movie | Wave score | Reddit trending | eBay listings |
|-------|------------|-----------------|---------------|
| *Vertigo* (1958) | 78.9 | 200 | 2 |
| *The Insider* (1999) | 78.6 | 180 | 22 |
| *Spirited Away* (2001) | 66.3 | 250 | 44 |
| *Pulp Fiction* (1994) | 64.0 | 150 | 100 |

**Score → action**

| Score | Status | Typical read |
|-------|--------|--------------|
| 75+ | Peak | Momentum high; consider selling before cool-off |
| 60–74 | Heating up | Hold / watch |
| 40–60 | Stable | Sideways |
| 25–40 | Cooling | Wait for entry |
| <25 | Bottom | Default for most titles |

**Distribution after 38,075 analyses:** ~82% bottom (<25), ~13% cooling, ~5% stable, <1% heating, <0.1% peak—most of the catalog is quiet; value is catching the small heating set early.

**Seasonal snapshot (aggregated):**

| Season | Avg price | Volume note |
|--------|-----------|-------------|
| Summer | $57.68 | Lower volume in early window |
| Fall | $21.44 | 83,817 listings |
| Winter | $20.14 | 671,843 listings |
| Spring | $19.61 | 140,243 listings |

Applied multipliers in scoring: summer 1.3×, spring 1.1×, fall 1.0×, winter 0.85× (tuned as features, not hard-coded-only rules).

---

## Phase 3 — Machine learning

**Goal:** predict **7-day forward % price change** from wave features + listing stats.

- **7,868** labeled samples (features at time *t*, outcome at *t+7*).
- **12 features:** Reddit metrics, eBay counts/prices, scarcity, season/format encodings, graded count.
- **Models:** RandomForest vs GradientBoosting; weekly retrain (Sunday 01:00).

**First production training (reported honestly):**

| Metric | Value |
|--------|------:|
| Model | RandomForest (200 trees, max_depth 10) |
| Train R² | 0.278 |
| Test R² | 0.010 |
| Test MAE | 19.15% |

Short-horizon single-title prediction is noisy; the model still informs **feature importance** (eBay price and listing counts dominate; Reddit features sparse when mentions = 0).

**Blend policy:**

- ML agrees with rules → boost confidence.
- ML disagrees and R² > 0.3 → weight ML ~60%.
- Weak model → keep rules, lower confidence.

---

## Phase 4 — Pipeline evolution (Reddit-first)

Early runs analyzed **300 random** movies per wave batch—most with zero Reddit signal.

**Redesign:**

1. **Phase A:** all catalog titles with Reddit activity in the last 7 days.
2. **Phase B:** random fill for coverage.

Scraper upgrades: 16 subreddits (was 9), **hot + top + new** sorts, catalog title matching for quoted/bold titles without year tokens.

This was the largest quality jump: wave output on *Spirited Away* during a hype cycle is signal; wave output on a silent catalog title is noise.

---

## Daily pipeline (five steps)

1. **TMDB batch crawler (PHP)** — eBay across formats → JSONB rows.
2. **Reddit trending analyzer (PHP)** — subreddit scan + catalog match.
3. **AI price analysis (Python)** — weighted pricing; optional Ollama notes.
4. **Wave analyzer (Python)** — signal-first selection, 0–100 scores.
5. **ML predictions (Python)** — load weekly model, blend with rules.

---

## What we learned

1. **LaserDisc is the sleeper format** in averages—supply-constrained niche with high willingness to pay.
2. **Scarcity beats fame** — limited disc runs beat household-name titles without supply shocks.
3. **Most titles are “bottom”** — edge is detecting the ~5% heating before peak.
4. **Reddit leads when it fires** — boutique/criterion chatter can precede comps by 1–2 weeks, but only ~120 titles had 30-day signal.
5. **Winter volume ≠ summer price** — high listing volume in winter aligns with lower average solds in the snapshot.

---

## Limitations & next steps (from production roadmap)

- Reddit still sparse for most catalog rows; comment-level NLP not yet in the loop.
- Heritage auction rows in DB but not fully fused into ML features.
- Price alerts planned on existing eBay notification webhooks.
- Seasonal hypothesis needs a full 12-month window for rigorous validation.
- Graded-item tracking (CGC, VGA, WATA) scoped in product backlog.

---

## Source material in the repo

This case study consolidates:

- `docs/how-we-built-film-price-guide.md` — primary narrative and metrics
- `docs/wave-analysis-system.md` — methodology and schema detail
- `docs/wave-rotation-strategies.md` — catalog rotation and signal-first batching
- `docs/RAILWAY_AND_UPSTASH.md` — split Vercel web / Railway cron topology
- `docs/retroprice-api-reference.md` — public API surface

---

*RetroPrice / Film Price Guide — built with PHP, Python, PostgreSQL, scikit-learn, and a lot of sold-listing data.*