Skip to main content

Completed Sign-In Sessions (Estimated)

Estimated number of sign-ins that got all the way through the flow.

An estimate of the number of completed sign-in sessions to CanadaLogin over a selected reporting period, where a session is one user getting all the way through the sign-in flow regardless of how many authentication methods it took.

Calculation

A completed session has exactly one successful first-factor event (the password) plus zero or more successful second factors. Both kinds of success are lumped together in successful_logins on events_auth_total_logins, while the second-factor successes are counted separately on events_mfa_activity. Subtracting the second from the first leaves one event per session: the first-factor success. So sum successful_logins over the window and subtract the sum of mfa_activity.count where result = 'success', restricted to the factors that can complete a sign-in.

WITH first_factor AS (
    SELECT SUM(successful_logins) AS successful_events
    FROM ibm_verify_events_transformed.events_auth_total_logins
    -- set the reporting window; see the timespans note in cookbook/notes.qmd
    WHERE CAST(from_date AS DATE) BETWEEN DATE '2026-01-01' AND DATE '2026-05-28'
),

second_factor AS (
    SELECT SUM(count) AS mfa_successes
    FROM ibm_verify_events_transformed.events_mfa_activity
    -- set the reporting window; see the timespans note in cookbook/notes.qmd
    WHERE CAST(from_date AS DATE) BETWEEN DATE '2026-01-01' AND DATE '2026-05-28'
      -- outcome events only; a "sent" OTP is an issuance, not a success
      AND result = 'success'
      -- email_otp validates an email address at sign-up and never enters
      -- successful_logins, so subtracting it would over-correct
      AND mfa_type IN ('sms_otp', 'voice_otp', 'fido2')
)

SELECT
    first_factor.successful_events - second_factor.mfa_successes
        AS estimated_completed_sessions
FROM first_factor
CROSS JOIN second_factor;
sign_in_types <- c("sms_otp", "voice_otp", "fido2")

successful_events <- tbl(con, in_schema("ibm_verify_events_transformed", "events_auth_total_logins")) |>
  # set the reporting window; see the timespans note in cookbook/notes.qmd
  filter(
    as.Date(from_date) >= as.Date("2026-01-01"),
    as.Date(from_date) <= as.Date("2026-05-28")
  ) |>
  summarise(successful_events = sum(as.double(successful_logins), na.rm = TRUE)) |>
  pull(successful_events)

mfa_successes <- tbl(con, in_schema("ibm_verify_events_transformed", "events_mfa_activity")) |>
  # set the reporting window; see the timespans note in cookbook/notes.qmd
  filter(
    as.Date(from_date) >= as.Date("2026-01-01"),
    as.Date(from_date) <= as.Date("2026-05-28"),
    # outcome events only; a "sent" OTP is an issuance, not a success
    result == "success",
    # email_otp validates an email address at sign-up and never enters
    # successful_logins, so subtracting it would over-correct
    mfa_type %in% sign_in_types
  ) |>
  summarise(mfa_successes = sum(as.double(count), na.rm = TRUE)) |>
  pull(mfa_successes)

successful_events - mfa_successes

Interpretation notes

The estimate works because each session contributes exactly one first-factor success no matter how many second factors it used:

Session type successful_logins Subtracted MFA successes Net (= sessions)
Password only 1 0 1
Password + SMS OTP 2 1 1
Password + step-up (2 factors) 3 2 1
Mistyped password, then OTP 2 (the failure lands in failed_logins) 1 1
Sign-up email validation 0 0 0

email_otp is excluded because a successful email_otp does not increment successful_logins. It validates an email address at sign-up rather than completing a sign-in.

The estimate rests on two assumptions:

  1. Passwordless and FIDO-first logins are undercounted. A session whose only factor is FIDO2 is one event in successful_logins and also a fido2 success in events_mfa_activity, so it nets to zero. The figure is therefore a slight underestimate.
  2. The two tables are assumed to count sign-in second-factor successes the same way. Both derive from the same IBM Verify event stream, which makes this plausible, but it is not guaranteed by anything in the data catalog.

Because of these assumptions, label the result as estimated wherever it is reported, and never present it as an exact session count. When the question is about distinct people rather than sessions, prefer unique_users (daily active users), which needs no assumptions: a user cannot be a unique user without completing a sign-in.

Alternative names

  • estimated sign-in sessions
  • estimated login count
  • sign-in sessions