How We Track Time Between Visits — The Technical Breakdown

Technical companion to Time Between Visits in The Numbers That Matter series.

---

Time Between Visits (TBV) is the average gap in days between a member's consecutive visits. The engineering challenge is computing a per-member rolling baseline and detecting deviations from it efficiently.

Computing TBV for a single member

Using window functions to pair consecutive visits:

SELECT
  member_id,
  checked_in_at,
  LAG(checked_in_at) OVER (
    PARTITION BY member_id ORDER BY checked_in_at
  ) AS previous_visit,
  EXTRACT(DAY FROM
    checked_in_at - LAG(checked_in_at) OVER (
      PARTITION BY member_id ORDER BY checked_in_at
    )
  ) AS days_between
FROM visits
WHERE member_id = :member_id
  AND status = 'completed'
ORDER BY checked_in_at;

The average of the days_between column is that member's baseline TBV.

Computing baseline TBV for all members

WITH visit_gaps AS (
  SELECT
    member_id,
    EXTRACT(EPOCH FROM (
      checked_in_at - LAG(checked_in_at) OVER (
        PARTITION BY member_id ORDER BY checked_in_at
      )
    )) / 86400.0 AS days_between
  FROM visits
  WHERE status = 'completed'
    AND checked_in_at >= NOW() - INTERVAL '90 days'
)
SELECT
  member_id,
  ROUND(AVG(days_between), 1) AS avg_tbv_days,
  ROUND(STDDEV(days_between), 1) AS tbv_stddev,
  COUNT(*) AS visit_gaps_measured
FROM visit_gaps
WHERE days_between IS NOT NULL
GROUP BY member_id
HAVING COUNT(*) >= 3;  -- need enough data points for a meaningful baseline

The HAVING >= 3 filter ensures you're only computing baselines for members with enough visit history. Standard deviation is included because it tells you how consistent the member is — a low stddev means the baseline is reliable, a high stddev means the member is naturally erratic and anomaly detection should be less sensitive.

Denormalize for performance

Store the baseline on the member record and refresh nightly:

Member
  avg_time_between_visits:  Decimal?   -- rolling 90-day average in days
  tbv_stddev:               Decimal?   -- standard deviation
  last_visit_at:            DateTime?  -- denormalized from most recent visit
  tbv_updated_at:           DateTime

last_visit_at is updated on every check-in. The other fields are updated by a nightly job. This means the "current gap" — days since last visit — is always available as:

current_gap = today - last_visit_at

And the deviation ratio is:

deviation = current_gap / avg_time_between_visits

The early warning query

Find all members whose current gap exceeds their baseline by a configurable multiplier:

SELECT
  m.id,
  m.name,
  m.avg_time_between_visits,
  EXTRACT(DAY FROM NOW() - m.last_visit_at) AS days_since_last,
  ROUND(
    EXTRACT(DAY FROM NOW() - m.last_visit_at) /
    NULLIF(m.avg_time_between_visits, 0), 1
  ) AS deviation_ratio
FROM members m
WHERE m.membership_streak_start IS NOT NULL   -- active members only
  AND m.avg_time_between_visits IS NOT NULL    -- has a baseline
  AND m.last_visit_at IS NOT NULL
  AND EXTRACT(DAY FROM NOW() - m.last_visit_at) >
      m.avg_time_between_visits * 2.0          -- 2x threshold
ORDER BY deviation_ratio DESC;

Adjust the 2.0 multiplier to control sensitivity. For high-frequency members (baseline of 3 days), 2x triggers at 6 days — reasonable. For low-frequency members (baseline of 14 days), 2x triggers at 28 days — also reasonable. The multiplier scales naturally.

Tiered risk classification

SELECT
  m.id,
  m.name,
  CASE
    WHEN m.last_visit_at IS NULL THEN 'no_data'
    WHEN current_gap <= m.avg_time_between_visits * 1.2 THEN 'healthy'
    WHEN current_gap <= m.avg_time_between_visits * 2.0 THEN 'at_risk'
    WHEN current_gap <= m.avg_time_between_visits * 3.0 THEN 'critical'
    ELSE 'likely_churned'
  END AS risk_tier
FROM (
  SELECT *,
    EXTRACT(DAY FROM NOW() - last_visit_at) AS current_gap
  FROM members
  WHERE membership_streak_start IS NOT NULL
) m;

This gives you four buckets to drive different outreach strategies. The Manager dashboard can show counts per tier as a health summary.

Measuring event impact

To see whether an event or promotion actually pulled members back sooner:

WITH before_event AS (
  SELECT member_id, AVG(days_between) AS avg_tbv_before
  FROM visit_gaps
  WHERE visit_date BETWEEN event_date - 30 AND event_date
  GROUP BY member_id
),
after_event AS (
  SELECT member_id, AVG(days_between) AS avg_tbv_after
  FROM visit_gaps
  WHERE visit_date BETWEEN event_date AND event_date + 30
  GROUP BY member_id
)
SELECT
  ROUND(AVG(b.avg_tbv_before), 1) AS avg_tbv_before,
  ROUND(AVG(a.avg_tbv_after), 1) AS avg_tbv_after,
  ROUND(AVG(a.avg_tbv_after) - AVG(b.avg_tbv_before), 1) AS delta
FROM before_event b
JOIN after_event a ON b.member_id = a.member_id;

A negative delta means members visited more frequently after the event. A positive delta means the event had no lasting impact (or coincided with a natural downturn).

Deduplication note

If a member visits twice in one day, those are technically two visits with a 0-day gap. Depending on your use case, you may want to deduplicate by day:

SELECT DISTINCT member_id, DATE(checked_in_at) AS visit_date
FROM visits
WHERE status = 'completed'

Using visit_date instead of raw timestamps prevents same-day visits from artificially deflating the TBV baseline.

---

Back to the main article: Time Between Visits — Your Early Warning System for Churn