Skip to main content

Average Daily SSO Events

Average SSO events per day for each application.

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