Completed Sign-In Sessions (Estimated)
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_successesInterpretation 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:
- Passwordless and FIDO-first logins are undercounted. A session whose only factor is FIDO2 is one event in
successful_loginsand also afido2success inevents_mfa_activity, so it nets to zero. The figure is therefore a slight underestimate. - 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