New Accounts and Idle Account Rate
New CanadaLogin accounts per day, and how many of them never used a partner service. A jump in the idle rate is a sign of bot sign-ups.
It reads the raw event stream, because no summary table records when an account first appears.
Calculation
An account is new on the ET day of its first event in authentication_activity. It is idle if it has no successful event in application_usage for a public application. Public means not flagged internal in rp.service; an application with no row in rp.alias counts as public, because internal CanadaLogin components are the ones registered on purpose and a new partner can go live before it is added.
There are three idle windows:
- Ever: no public use at all yet. Keeps falling as accounts age.
- 14 days: no public use within 14 days of the first event. Final once 14 days have passed.
- Same day: no public use by the end of the ET day the account was made. Final the next morning, so it is the one to use for spotting bot days.
Leave out a blank user ID. The user ID "UNKNOWN" counts as one account, as it does in the summary tables (see notes).
By day:
WITH first_seen AS (
SELECT
subject,
min(CAST(substr(time, 1, 19) AS timestamp)) AS first_ts
FROM ibm_verify_events_raw.authentication_activity
WHERE subject <> ''
GROUP BY subject
),
new_accounts AS (
SELECT
subject,
first_ts,
-- time is a UTC string; report on ET days
date(with_timezone(first_ts, 'UTC') AT TIME ZONE 'America/Toronto') AS created_on
FROM first_seen
),
public_use AS (
SELECT
userid,
min(CAST(substr(time, 1, 19) AS timestamp)) AS first_public_ts
FROM ibm_verify_events_raw.application_usage
WHERE result = 'success'
-- only internal CanadaLogin components are left out; an unmapped one is public
AND applicationid NOT IN (
SELECT al.alias
FROM rp.alias al
JOIN rp.service s ON al.rp_id = s.rp_id
WHERE s.is_internal
)
GROUP BY userid
)
SELECT
n.created_on,
count(*) AS new_accounts,
count_if(p.userid IS NULL) AS idle_accounts,
count_if(p.userid IS NULL OR p.first_public_ts > n.first_ts + INTERVAL '14' DAY) AS idle_accounts_14d,
count_if(p.userid IS NULL OR date(with_timezone(p.first_public_ts, 'UTC') AT TIME ZONE 'America/Toronto') > n.created_on) AS idle_accounts_same_day,
CAST(count_if(p.userid IS NULL) AS DOUBLE) / count(*) AS idle_rate,
CAST(count_if(p.userid IS NULL OR p.first_public_ts > n.first_ts + INTERVAL '14' DAY) AS DOUBLE) / count(*) AS idle_rate_14d,
CAST(count_if(p.userid IS NULL OR date(with_timezone(p.first_public_ts, 'UTC') AT TIME ZONE 'America/Toronto') > n.created_on) AS DOUBLE) / count(*) AS idle_rate_same_day
FROM new_accounts n
LEFT JOIN public_use p ON n.subject = p.userid
-- set the reporting window; see the timespans note in cookbook/notes.qmd
WHERE n.created_on BETWEEN DATE '2026-09-01' AND DATE '2026-09-30'
GROUP BY n.created_on
ORDER BY n.created_on;Since launch, point in time:
WITH first_seen AS (
SELECT
subject,
min(CAST(substr(time, 1, 19) AS timestamp)) AS first_ts
FROM ibm_verify_events_raw.authentication_activity
WHERE subject <> ''
GROUP BY subject
),
new_accounts AS (
SELECT
subject,
date(with_timezone(first_ts, 'UTC') AT TIME ZONE 'America/Toronto') AS created_on
FROM first_seen
),
public_use AS (
SELECT
userid,
min(CAST(substr(time, 1, 19) AS timestamp)) AS first_public_ts
FROM ibm_verify_events_raw.application_usage
WHERE result = 'success'
AND applicationid NOT IN (
SELECT al.alias
FROM rp.alias al
JOIN rp.service s ON al.rp_id = s.rp_id
WHERE s.is_internal
)
GROUP BY userid
),
-- accounts made each day, and accounts that first used a public service each day
daily AS (
SELECT created_on AS day, count(*) AS created, 0 AS activated
FROM new_accounts
GROUP BY created_on
UNION ALL
SELECT
date(with_timezone(p.first_public_ts, 'UTC') AT TIME ZONE 'America/Toronto') AS day,
0 AS created,
count(*) AS activated
FROM new_accounts n
JOIN public_use p ON n.subject = p.userid
GROUP BY date(with_timezone(p.first_public_ts, 'UTC') AT TIME ZONE 'America/Toronto')
)
SELECT
day,
sum(sum(created)) OVER (ORDER BY day) AS accounts,
sum(sum(created) - sum(activated)) OVER (ORDER BY day) AS idle_accounts
FROM daily
GROUP BY day
ORDER BY day;By day:
first_seen <- tbl(con, in_schema("ibm_verify_events_raw", "authentication_activity")) |>
filter(!is.na(subject), subject != "") |>
mutate(ts = sql("CAST(substr(time, 1, 19) AS timestamp)")) |>
group_by(subject) |>
summarise(first_ts = min(ts, na.rm = TRUE), .groups = "drop") |>
# time is a UTC string; report on ET days
mutate(created_on = sql("date(with_timezone(first_ts, 'UTC') AT TIME ZONE 'America/Toronto')"))
# only internal CanadaLogin components are left out; an unmapped one is public
internal_apps <- tbl(con, in_schema("rp", "alias")) |>
inner_join(tbl(con, in_schema("rp", "service")), by = "rp_id") |>
filter(is_internal) |>
select(applicationid = alias)
public_use <- tbl(con, in_schema("ibm_verify_events_raw", "application_usage")) |>
filter(result == "success") |>
anti_join(internal_apps, by = "applicationid") |>
mutate(ts = sql("CAST(substr(time, 1, 19) AS timestamp)")) |>
group_by(userid) |>
summarise(first_public_ts = min(ts, na.rm = TRUE), .groups = "drop") |>
mutate(first_public_day = sql("date(with_timezone(first_public_ts, 'UTC') AT TIME ZONE 'America/Toronto')"))
first_seen |>
# set the reporting window; see the timespans note in cookbook/notes.qmd
filter(
created_on >= as.Date("2026-09-01"),
created_on <= as.Date("2026-09-30")
) |>
left_join(public_use, by = c("subject" = "userid")) |>
mutate(
idle = is.na(first_public_ts),
idle_14d = is.na(first_public_ts) | first_public_ts > sql("first_ts + INTERVAL '14' DAY"),
idle_same_day = is.na(first_public_ts) | first_public_day > created_on
) |>
group_by(created_on) |>
summarise(
new_accounts = n(),
idle_accounts = sum(as.integer(idle), na.rm = TRUE),
idle_accounts_14d = sum(as.integer(idle_14d), na.rm = TRUE),
idle_accounts_same_day = sum(as.integer(idle_same_day), na.rm = TRUE),
.groups = "drop"
) |>
mutate(
idle_rate = as.numeric(idle_accounts) / new_accounts,
idle_rate_14d = as.numeric(idle_accounts_14d) / new_accounts,
idle_rate_same_day = as.numeric(idle_accounts_same_day) / new_accounts
) |>
arrange(created_on)Since launch, point in time:
first_seen <- tbl(con, in_schema("ibm_verify_events_raw", "authentication_activity")) |>
filter(!is.na(subject), subject != "") |>
mutate(ts = sql("CAST(substr(time, 1, 19) AS timestamp)")) |>
group_by(subject) |>
summarise(first_ts = min(ts, na.rm = TRUE), .groups = "drop") |>
mutate(created_on = sql("date(with_timezone(first_ts, 'UTC') AT TIME ZONE 'America/Toronto')"))
internal_apps <- tbl(con, in_schema("rp", "alias")) |>
inner_join(tbl(con, in_schema("rp", "service")), by = "rp_id") |>
filter(is_internal) |>
select(applicationid = alias)
public_use <- tbl(con, in_schema("ibm_verify_events_raw", "application_usage")) |>
filter(result == "success") |>
anti_join(internal_apps, by = "applicationid") |>
mutate(ts = sql("CAST(substr(time, 1, 19) AS timestamp)")) |>
group_by(userid) |>
summarise(first_public_ts = min(ts, na.rm = TRUE), .groups = "drop") |>
mutate(first_public_day = sql("date(with_timezone(first_public_ts, 'UTC') AT TIME ZONE 'America/Toronto')"))
# accounts made each day, and accounts that first used a public service each day
created <- first_seen |>
count(day = created_on, name = "created") |>
mutate(activated = 0L)
activated <- first_seen |>
inner_join(public_use, by = c("subject" = "userid")) |>
count(day = first_public_day, name = "activated") |>
mutate(created = 0L)
union_all(created, activated) |>
group_by(day) |>
summarise(
created = sum(created, na.rm = TRUE),
activated = sum(activated, na.rm = TRUE),
.groups = "drop"
) |>
window_order(day) |>
mutate(
accounts = cumsum(created),
idle_accounts = cumsum(created - activated)
) |>
select(day, accounts, idle_accounts) |>
arrange(day)Interpretation notes
Idle accounts are a signal, not a bot count. Some real people sign up and never use a service. Normal days run about 10% to 15% idle. On bot-wave days, nearly all new accounts are idle.
Pick the window for the question. To spot a bot day, use the same-day rate: it is final the next morning. To compare months, use the 14-day rate: the “ever” rate keeps falling as accounts have more time to reach a service, so recent days look worse than old ones, and the last 14 days of any window are still provisional on the 14-day rate.
New accounts add up across days, rates do not. Each account has one creation day, so summing new_accounts or idle_accounts over days is correct. Recompute a rate over a longer window from the summed counts.
Since launch is point in time. An account leaves the idle count on the day it first uses a public service, and earlier days are never restated. So the idle count on a past day is what it was on that day, not how many of those accounts are still idle now. The other choice, restating history, would shrink every past day’s idle count as accounts later reach a service.
When an account appears. Most new accounts first appear with a successful password, which suggests the sign-up flow signs an account in once the password is set, before any MFA. A few first appear through a token exchange instead, possibly migrated accounts. Both readings are observed, not confirmed with the sign-up flow’s owners.
It is expensive. The query reads both raw tables from launch, and the cost grows every month. Run it for a reporting window, not on a schedule.
Alternative names
- new accounts
- sign-ups
- account creations
- registrations
- idle accounts
- dormant accounts
- accounts with no service use
- accounts since launch
- total accounts