GuideApril 23, 2026·By Crew

How We Track Visit Frequency — The Technical Breakdown

Technical companion to Visit Frequency in The Numbers That Matter series.

Visit frequency — visits per member per month — becomes actionable when you can compute per-member baselines, detect anomalies, and identify the habit threshold empirically.

Per-member rolling baseline

SELECT
  m.id,
  COUNT(v.id) AS visits_last_90_days,
  COUNT(v.id) / 3.0 AS avg_monthly_frequency
FROM members m
LEFT JOIN visits v ON v.memberId = m.id
  AND v.checkInAt >= NOW() - INTERVAL '90 days'
WHERE m.membershipExpiresAt >= NOW()
GROUP BY m.id;

The 90-day window smooths out vacations and holidays, giving a baseline that represents typical behavior.

Detecting frequency changes

Compare the last 30 days against the prior 60:

WITH member_frequency AS (
  SELECT
    m.id,
    COUNT(v.id) FILTER (WHERE v.checkInAt >= NOW() - INTERVAL '30 days') AS recent_visits,
    COUNT(v.id) FILTER (
      WHERE v.checkInAt >= NOW() - INTERVAL '90 days'
        AND v.checkInAt < NOW() - INTERVAL '30 days'
    ) / 2.0 AS baseline_monthly_visits
  FROM members m
  LEFT JOIN visits v ON v.memberId = m.id
    AND v.checkInAt >= NOW() - INTERVAL '90 days'
  WHERE m.membershipExpiresAt >= NOW()
  GROUP BY m.id
)
SELECT *,
  CASE
    WHEN baseline_monthly_visits > 0
    THEN recent_visits / baseline_monthly_visits
    ELSE NULL
  END AS frequency_ratio
FROM member_frequency
WHERE baseline_monthly_visits >= 2
ORDER BY frequency_ratio ASC;

A frequency_ratio below 0.5 means the member's recent frequency dropped to less than half their baseline. That's your at-risk list.

Finding the habit threshold

Compare the median frequency of churned vs. retained members:

WITH churned AS (
  SELECT m.id,
    COUNT(v.id) / GREATEST(
      EXTRACT(EPOCH FROM (m.membershipEndDate - m.membershipStartDate)) / 2592000, 1
    ) AS avg_monthly_frequency
  FROM members m
  JOIN visits v ON v.memberId = m.id
  WHERE m.membershipEndDate IS NOT NULL
  GROUP BY m.id, m.membershipEndDate, m.membershipStartDate
),
retained AS (
  SELECT m.id,
    COUNT(v.id) / GREATEST(
      EXTRACT(EPOCH FROM (NOW() - m.membershipStreakStart)) / 2592000, 1
    ) AS avg_monthly_frequency
  FROM members m
  JOIN visits v ON v.memberId = m.id AND v.checkInAt >= m.membershipStreakStart
  WHERE m.membershipStreakStart IS NOT NULL
  GROUP BY m.id, m.membershipStreakStart
)
SELECT 'churned' AS segment, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_monthly_frequency) AS median_freq FROM churned
UNION ALL
SELECT 'retained', PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_monthly_frequency) FROM retained;

The gap between these medians reveals your danger zone.

Traffic heatmap for staffing

SELECT
  EXTRACT(DOW FROM checkInAt) AS day_of_week,
  EXTRACT(HOUR FROM checkInAt) AS hour,
  COUNT(*) AS visit_count
FROM visits
WHERE checkInAt >= NOW() - INTERVAL '90 days'
GROUP BY EXTRACT(DOW FROM checkInAt), EXTRACT(HOUR FROM checkInAt)
ORDER BY day_of_week, hour;

Storage approach

We don't precompute frequency. A composite index on (memberId, checkInAt) makes the queries above fast enough for real-time computation. Dashboard aggregates are cached with a short TTL.

Back to the operator post: Visit Frequency — what to do with this metric →

← PreviousVisit Frequency — The Metric That Predicts Whether They'll Stay or Leave Next →Room Utilization Rate — The Metric That Shows What You're Actually Selling

the-numbers-that-matter technical engineering