Sign-Ins per Active User (Estimated)
How often a typical user signs in to CanadaLogin over a calendar month, counting only the users who signed in at all that month.
This builds on Completed Sign-In Sessions and inherits its estimate and its assumptions. The denominator is mtd_unique_users read on the month’s last day, so the reporting window has to be whole calendar months.
Calculation
Per calendar month, estimate sessions as in Completed Sign-In Sessions: sum successful_logins and subtract the successes of the factors that can complete a sign-in (sms_otp, voice_otp, fido2). Divide by mtd_unique_users on the month’s last day, which is that month’s distinct signed-in users.
WITH first_factor AS (
SELECT
date_trunc('month', CAST(from_date AS DATE)) AS month,
SUM(successful_logins) AS successful_events
FROM ibm_verify_events_transformed.events_auth_total_logins
-- set the reporting window; whole calendar months only
WHERE CAST(from_date AS DATE) BETWEEN DATE '2026-05-01' AND DATE '2026-08-31'
GROUP BY 1
),
second_factor AS (
SELECT
date_trunc('month', CAST(from_date AS DATE)) AS month,
SUM(count) AS mfa_successes
FROM ibm_verify_events_transformed.events_mfa_activity
-- set the reporting window; whole calendar months only
WHERE CAST(from_date AS DATE) BETWEEN DATE '2026-05-01' AND DATE '2026-08-31'
-- 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')
GROUP BY 1
),
active_users AS (
SELECT month, mtd_unique_users AS monthly_active_users
FROM (
SELECT
date_trunc('month', CAST(from_date AS DATE)) AS month,
mtd_unique_users,
ROW_NUMBER() OVER (
PARTITION BY date_trunc('month', CAST(from_date AS DATE))
ORDER BY from_date DESC
) AS day_rank
FROM ibm_verify_events_transformed.events_auth_total_logins
-- set the reporting window; whole calendar months only
WHERE CAST(from_date AS DATE) BETWEEN DATE '2026-05-01' AND DATE '2026-08-31'
)
-- mtd_unique_users accumulates through the month, so the last day holds
-- the month's distinct signed-in users
WHERE day_rank = 1
)
SELECT
first_factor.month,
first_factor.successful_events - second_factor.mfa_successes
AS estimated_sessions,
active_users.monthly_active_users,
CAST(
first_factor.successful_events - second_factor.mfa_successes AS DOUBLE
) / NULLIF(active_users.monthly_active_users, 0)
AS sign_ins_per_active_user
FROM first_factor
JOIN second_factor ON first_factor.month = second_factor.month
JOIN active_users ON first_factor.month = active_users.month
ORDER BY first_factor.month;sign_in_types <- c("sms_otp", "voice_otp", "fido2")
first_factor <- tbl(con, in_schema("ibm_verify_events_transformed", "events_auth_total_logins")) |>
# set the reporting window; whole calendar months only
filter(
as.Date(from_date) >= as.Date("2026-05-01"),
as.Date(from_date) <= as.Date("2026-08-31")
) |>
group_by(month = floor_date(as.Date(from_date), "month")) |>
summarise(successful_events = sum(as.double(successful_logins), na.rm = TRUE))
second_factor <- tbl(con, in_schema("ibm_verify_events_transformed", "events_mfa_activity")) |>
# set the reporting window; whole calendar months only
filter(
as.Date(from_date) >= as.Date("2026-05-01"),
as.Date(from_date) <= as.Date("2026-08-31"),
# 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
) |>
group_by(month = floor_date(as.Date(from_date), "month")) |>
summarise(mfa_successes = sum(as.double(count), na.rm = TRUE))
active_users <- tbl(con, in_schema("ibm_verify_events_transformed", "events_auth_total_logins")) |>
# set the reporting window; whole calendar months only
filter(
as.Date(from_date) >= as.Date("2026-05-01"),
as.Date(from_date) <= as.Date("2026-08-31")
) |>
group_by(month = floor_date(as.Date(from_date), "month")) |>
# mtd_unique_users accumulates through the month, so the last day holds
# the month's distinct signed-in users
filter(from_date == max(from_date, na.rm = TRUE)) |>
ungroup() |>
select(month, monthly_active_users = mtd_unique_users)
first_factor |>
inner_join(second_factor, by = "month") |>
inner_join(active_users, by = "month") |>
mutate(
estimated_sessions = successful_events - mfa_successes,
sign_ins_per_active_user =
as.double(estimated_sessions) / as.double(monthly_active_users)
) |>
select(month, estimated_sessions, monthly_active_users, sign_ins_per_active_user) |>
arrange(month)Interpretation notes
This is per active user, not per registered user. A user who does not sign in at all that month is not counted in the denominator.
Sessions carry the undercount described in Completed Sign-In Sessions.
Alternative names
- sign-in frequency
- sessions per active user
- monthly sign-in rate