Relating events without a sessionId

Group events that have no sessionId into approximate visits, using the request values the Hub records: the X-Forwarded-For header, user agent and language.

A record has a null sessionId when:

• the storage policy stored no identifier for the visitor, for example under optIn before they accept, or always under disabled;
• the event came from your server outside a visitor's request, such as a webhook or a cron job.

This guide covers the first case. Events from a webhook carry the sender's address and user agent, not a visitor's, so they form visits of their own. Leave them out by name when you count visitors.

Relating events this way still recognises a visitor, only without a cookie. For what that means for consent, see Consent.

Group events into visits

Events with the same IP address, user agent and language belong to one visitor. That visitor's events form one visit until 30 minutes pass without an event.

The Hub stores the X-Forwarded-For header as it arrived, in forwardedFor, and leaves finding the visitor's address in it to you. Every proxy in front of your app appends the address it received the request from, so count from the right: with one proxy, the visitor's address is the last entry; with two, the one before it. Entries further left are whatever the client sent, and can be made up.

For example, for a visitor at 203.0.113.7 who sends X-Forwarded-For: 10.0.0.1 themselves:

Behind the CDN, the last entry is the CDN's own address, appended by the load balancer.

This query is for the Postgres destination. Other databases need their own JSON and window syntax, but the steps are the same.

WITH keyed AS (
  SELECT
    *,
    md5(concat_ws('|',
      trim(split_part(hub_properties->>'forwardedFor', ',', -1)),The last entry: one proxy in front of your app. With two, use -2.
      hub_properties->>'userAgent',
      hub_properties->>'acceptLanguage'
    )) AS visitor_key
  FROM eventrail_events_v1
  WHERE session_id IS NULL
    AND hub_properties->>'forwardedFor' IS NOT NULL
),
marked AS (
  SELECT
    *,
    CASE
      WHEN received_at - LAG(received_at) OVER w < INTERVAL '30 minutes' THEN 0A gap of 30 minutes starts a new visit.
      ELSE 1
    END AS starts_visit
  FROM keyed
  WINDOW w AS (PARTITION BY visitor_key ORDER BY received_at)
)
SELECT
  *,
  visitor_key || '-' || SUM(starts_visit) OVER (
    PARTITION BY visitor_key ORDER BY received_at
  ) AS visit_id
FROM marked;

For example, four page_view events from browsers with the same user agent and language:

The third event comes from another address, so it belongs to another visitor. The fourth comes 44 minutes after the first visitor's last event, so it starts their second visit.

Use visit_id where you would otherwise use session_id, for example in COUNT(DISTINCT visit_id).

How accurate it is

A visit found this way is an estimate:

• Visitors merge when they share an IP address, as they do behind an office network, a mobile carrier or a VPN. Two people with the same phone and browser version behind one address become one visit.
• Visits split when a visitor's address changes mid-visit, for example when their phone moves from Wi-Fi to mobile data.
• Everyone merges when you read an entry too far to the right: every record then has your own proxy's address instead of the visitor's.

Use the result for counts and funnels, never for decisions about one person.