Average Daily SSO Events
The average number of SSO events per day for each application over a selected reporting period.
This metric summarizes daily application usage into a single per-application average value. It is useful for understanding typical daily usage during a launch period, reporting window, or monitoring period.
Calculation
For each application, calculate the average of total_logins across all daily records in the selected reporting period.
Group on application_id, not application_name: a partner can rename an application, and the ID does not change. Each ID is labelled with its latest name from events_application_mapping. The ID UNKNOWN marks events with no application and is left out.
-- the latest name for each application ID
WITH latest_names AS (
SELECT application_id, application_name
FROM ibm_verify_events_transformed.events_application_mapping
WHERE date = (
SELECT MAX(date)
FROM ibm_verify_events_transformed.events_application_mapping
)
)
SELECT
a.application_id,
n.application_name,
AVG(a.total_logins) AS average_daily_sso_events
FROM ibm_verify_events_transformed.events_app_login_counts a
LEFT JOIN latest_names n ON a.application_id = n.application_id
-- set the reporting window; see the timespans note in cookbook/notes.qmd
WHERE CAST(a.from_date AS DATE) BETWEEN DATE '2026-04-22' AND DATE '2026-05-31'
AND a.application_id <> 'UNKNOWN'
-- to exclude internal CanadaLogin components from adoption reporting, uncomment:
-- AND a.application_id 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 a.application_id, n.application_name
ORDER BY average_daily_sso_events DESC;latest_names <- tbl(con, in_schema("ibm_verify_events_transformed", "events_application_mapping")) |>
filter(date == max(date, na.rm = TRUE)) |>
select(application_id, application_name)
tbl(con, in_schema("ibm_verify_events_transformed", "events_app_login_counts")) |>
# set the reporting window; see the timespans note in cookbook/notes.qmd
filter(
as.Date(from_date) >= as.Date("2026-04-22"),
as.Date(from_date) <= as.Date("2026-05-31"),
application_id != "UNKNOWN"
) |>
# to exclude internal CanadaLogin components from adoption reporting, uncomment:
# anti_join(
# tbl(con, in_schema("rp", "alias")) |>
# inner_join(tbl(con, in_schema("rp", "service")), by = "rp_id") |>
# filter(is_internal) |>
# select(application_id = alias),
# by = "application_id"
# ) |>
# the table also holds the name seen that day; keep only the latest
select(-application_name) |>
left_join(latest_names, by = "application_id") |>
group_by(application_id, application_name) |>
summarise(
average_daily_sso_events = mean(total_logins, na.rm = TRUE),
.groups = "drop"
) |>
arrange(desc(average_daily_sso_events))Interpretation notes
The average is taken over the daily rows present for each application in the window, not over every calendar day. If an application has no row on a day with zero activity, that day does not pull the average down, so the figure reflects typical activity on active days.
Alternative names
- average SSO events per day
- typical daily usage volume
- mean daily SSO event volume
- average daily application usage