Skip to main content

Cumulative Unique Users by Application

Distinct users per application this month or this calendar year.

The number of distinct users observed for each application over a chosen reporting window: this month or this calendar year.

The cumulative unique-user columns increase or remain stable over time. This metric is closer to “how many distinct people have used the application in this window” than the daily unique user metrics.

Calculation

Take the latest value of the cumulative unique-user column over the selected window, grouped by application. The window is chosen by which column you read (see the note on reporting timespans):

  • mtd_unique_users for month-to-date
  • ytd_unique_users for year-to-date

The WHERE CAST(from_date AS DATE) BETWEEN ... bounds set the “as of” reporting date; the most recent row in the window holds the current cumulative total.

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.

Month-to-date:

-- 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
    )
),

ranked AS (
    SELECT
        application_id,
        from_date,
        mtd_unique_users,
        ROW_NUMBER() OVER (
            PARTITION BY application_id
            ORDER BY from_date DESC
        ) AS rn
    FROM ibm_verify_events_transformed.events_app_login_counts
    WHERE CAST(from_date AS DATE) BETWEEN DATE '2026-06-01' AND DATE '2026-06-08'
    AND application_id <> 'UNKNOWN'
    -- to exclude internal CanadaLogin components from adoption reporting, uncomment:
    -- AND 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
    -- )
)

SELECT
    r.application_id,
    n.application_name,
    r.mtd_unique_users
FROM ranked r
LEFT JOIN latest_names n ON r.application_id = n.application_id
WHERE r.rn = 1
ORDER BY mtd_unique_users DESC;

Year-to-date:

-- 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
    )
),

ranked AS (
    SELECT
        application_id,
        from_date,
        ytd_unique_users,
        ROW_NUMBER() OVER (
            PARTITION BY application_id
            ORDER BY from_date DESC
        ) AS rn
    FROM ibm_verify_events_transformed.events_app_login_counts
    WHERE CAST(from_date AS DATE) BETWEEN DATE '2026-01-01' AND DATE '2026-06-08'
    AND application_id <> 'UNKNOWN'
    -- to exclude internal CanadaLogin components from adoption reporting, uncomment:
    -- AND 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
    -- )
)

SELECT
    r.application_id,
    n.application_name,
    r.ytd_unique_users
FROM ranked r
LEFT JOIN latest_names n ON r.application_id = n.application_id
WHERE r.rn = 1
ORDER BY ytd_unique_users DESC;

Month-to-date:

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")) |>
  filter(
    as.Date(from_date) >= as.Date("2026-06-01"),
    as.Date(from_date) <= as.Date("2026-06-08"),
    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"
  # ) |>
  group_by(application_id) |>
  slice_max(
    from_date,
    n = 1,
    with_ties = FALSE
  ) |>
  ungroup() |>
  # the table also holds the name seen that day; keep only the latest
  select(-application_name) |>
  left_join(latest_names, by = "application_id") |>
  select(
    application_id,
    application_name,
    mtd_unique_users
  ) |>
  arrange(desc(mtd_unique_users))

Year-to-date:

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")) |>
  filter(
    as.Date(from_date) >= as.Date("2026-01-01"),
    as.Date(from_date) <= as.Date("2026-06-08"),
    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"
  # ) |>
  group_by(application_id) |>
  slice_max(
    from_date,
    n = 1,
    with_ties = FALSE
  ) |>
  ungroup() |>
  # the table also holds the name seen that day; keep only the latest
  select(-application_name) |>
  left_join(latest_names, by = "application_id") |>
  select(
    application_id,
    application_name,
    ytd_unique_users
  ) |>
  arrange(desc(ytd_unique_users))

Interpretation notes

Each column resets on its own cadence: mtd_unique_users resets at the start of each month and ytd_unique_users at the start of each calendar year. Do not sum these columns across rows; they are already cumulative.

Alternative names

  • month-to-date unique users by application
  • year-to-date unique users by application
  • distinct user reach per application
  • total unique people reached per application