How We Track Waitlist Conversion Rate — The Technical Breakdown
Technical companion to Waitlist Conversion Rate in The Numbers That Matter series.
Waitlist conversion rate requires tracking every waitlist entry from join to outcome. The key is capturing what happened — did they get a room, or did they leave? — and how long it took.
The waitlist entry model
Every time a member joins the waitlist, a record is created:
CREATE TABLE waitlist_entries (
id UUID PRIMARY KEY,
club_id UUID REFERENCES clubs(id),
member_id UUID REFERENCES members(id),
joined_at TIMESTAMPTZ NOT NULL,
resolved_at TIMESTAMPTZ, -- when the outcome occurred
outcome TEXT, -- 'converted', 'abandoned', 'expired'
room_type_id UUID REFERENCES room_types(id), -- null = next available
specific_room_id UUID REFERENCES rooms(id), -- null = any of type
rental_id UUID REFERENCES rentals(id), -- set if converted
position_at_join INTEGER, -- queue position when they joined
created_at TIMESTAMPTZ DEFAULT NOW()
);
Three possible outcomes:
- converted — member got a room (rental_id is set)
- abandoned — member left before being served (voluntary)
- expired — club closed or waitlist was cleared (involuntary)
The basic conversion rate
SELECT
COUNT(*) AS total_entries,
COUNT(*) FILTER (WHERE outcome = 'converted') AS converted,
COUNT(*) FILTER (WHERE outcome = 'abandoned') AS abandoned,
COUNT(*) FILTER (WHERE outcome = 'expired') AS expired,
ROUND(
COUNT(*) FILTER (WHERE outcome = 'converted')::decimal / COUNT(*) * 100, 1
) AS conversion_rate_pct
FROM waitlist_entries
WHERE joined_at >= NOW() - INTERVAL '30 days';
Conversion rate by wait time
This reveals the breaking point — how long members will actually wait:
SELECT
wait_bucket,
COUNT(*) AS entries,
COUNT(*) FILTER (WHERE outcome = 'converted') AS converted,
ROUND(
COUNT(*) FILTER (WHERE outcome = 'converted')::decimal / COUNT(*) * 100, 1
) AS conversion_rate_pct
FROM (
SELECT *,
CASE
WHEN wait_minutes < 10 THEN '0-10 min'
WHEN wait_minutes < 20 THEN '10-20 min'
WHEN wait_minutes < 30 THEN '20-30 min'
WHEN wait_minutes < 45 THEN '30-45 min'
WHEN wait_minutes < 60 THEN '45-60 min'
ELSE '60+ min'
END AS wait_bucket
FROM (
SELECT *,
EXTRACT(EPOCH FROM (resolved_at - joined_at)) / 60 AS wait_minutes
FROM waitlist_entries
WHERE resolved_at IS NOT NULL
AND joined_at >= NOW() - INTERVAL '30 days'
) timed
) bucketed
GROUP BY wait_bucket
ORDER BY MIN(wait_minutes);
Output:
wait_bucket | entries | converted | rate
------------|---------|-----------|------
0-10 min | 89 | 82 | 92.1%
10-20 min | 67 | 54 | 80.6%
20-30 min | 43 | 24 | 55.8%
30-45 min | 31 | 9 | 29.0%
45-60 min | 18 | 3 | 16.7%
60+ min | 12 | 1 | 8.3%
The cliff between 20-30 minutes and 30-45 minutes tells you: if the wait exceeds 30 minutes, most members bail. That's your operational target.
Conversion by room preference type
SELECT
CASE
WHEN specific_room_id IS NOT NULL THEN 'Specific room'
WHEN room_type_id IS NOT NULL THEN 'Room type'
ELSE 'Next available'
END AS preference,
COUNT(*) AS entries,
ROUND(
COUNT(*) FILTER (WHERE outcome = 'converted')::decimal / COUNT(*) * 100, 1
) AS conversion_rate_pct,
ROUND(AVG(
EXTRACT(EPOCH FROM (resolved_at - joined_at)) / 60
) FILTER (WHERE outcome = 'converted'), 1) AS avg_wait_converted
FROM waitlist_entries
WHERE joined_at >= NOW() - INTERVAL '30 days'
GROUP BY preference;
"Next available" should convert highest and fastest. If "Specific room" converts poorly, your Hosts might need to suggest alternatives more proactively.
Conversion by membership tier
To measure whether tier-based priority is actually working:
SELECT
mt.name AS tier,
COUNT(*) AS entries,
ROUND(
COUNT(*) FILTER (WHERE we.outcome = 'converted')::decimal /
COUNT(*) * 100, 1
) AS conversion_rate_pct,
ROUND(AVG(
EXTRACT(EPOCH FROM (we.resolved_at - we.joined_at)) / 60
) FILTER (WHERE we.outcome = 'converted'), 1) AS avg_wait_min
FROM waitlist_entries we
JOIN members m ON we.member_id = m.id
JOIN membership_tiers mt ON m.tier_id = mt.id
WHERE we.joined_at >= NOW() - INTERVAL '30 days'
GROUP BY mt.name
ORDER BY conversion_rate_pct DESC;
If VIP and Default tiers have similar conversion rates, your priority system isn't aggressive enough — or it's a selling point that isn't delivering.
Quantifying lost revenue
Every abandoned entry is potential revenue that walked out the door:
SELECT
COUNT(*) FILTER (WHERE outcome = 'abandoned') AS abandonments,
ROUND(
COUNT(*) FILTER (WHERE outcome = 'abandoned')
* (SELECT AVG(total_revenue) FROM visits
WHERE status = 'completed'
AND checked_in_at >= NOW() - INTERVAL '30 days')
, 2) AS estimated_lost_revenue
FROM waitlist_entries
WHERE joined_at >= NOW() - INTERVAL '30 days';
This multiplies abandonments by average RPV — a rough but useful estimate. If your waitlist had 50 abandonments last month and your RPV is $55, that's ~$2,750 in revenue that existed as demand but wasn't captured.
Post-waitlist churn detection
The main article mentions that a bad waitlist experience can predict future disengagement. To measure this, cross-reference waitlist failures with subsequent visit patterns:
SELECT
CASE
WHEN we.outcome = 'abandoned' THEN 'waitlist_failed'
WHEN we.outcome = 'converted' THEN 'waitlist_succeeded'
END AS experience,
AVG(subsequent_visits) AS avg_visits_next_30_days
FROM waitlist_entries we
JOIN LATERAL (
SELECT COUNT(*) AS subsequent_visits
FROM visits v
WHERE v.member_id = we.member_id
AND v.checked_in_at BETWEEN we.joined_at + INTERVAL '1 day'
AND we.joined_at + INTERVAL '31 days'
AND v.status = 'completed'
) sv ON true
WHERE we.joined_at BETWEEN NOW() - INTERVAL '90 days' AND NOW() - INTERVAL '30 days'
AND we.outcome IN ('abandoned', 'converted')
GROUP BY experience;
If members who failed the waitlist visit significantly less in the following month than those who succeeded, your waitlist experience is actively contributing to churn.
Back to the main article: Waitlist Conversion Rate — Is Your Waitlist a Queue or a Leaky Bucket?