Additional Notes
Sign-ins are not users
A sign-in count measures events. One person can sign in multiple times.
Use sign-ins when the question is about volume or system usage.
Use unique users when the question is about reach.
Daily unique users are not the same as period unique users
Summing daily unique users gives a measure of user-days, not distinct people over the full period.
For distinct people over a longer period, use a cumulative unique user field such as mtd_unique_users or ytd_unique_users.
For distinct people over any other window, count the hashed user IDs in the raw IBM Verify event stream. The raw tables are preliminary, so check a raw count against the transformed figure for the same window.
Metrics can be reported over different timespans
Most metrics are meaningful over more than one window. The three standard reporting windows are month-to-date, year-to-date, and since the April 22, 2026 public launch. How you select a window depends on the kind of metric.
Additive metrics (sign-ins, MFA attempts, success rates, averages) are computed by aggregating daily rows, so the window is just the date bounds in the WHERE CAST(from_date AS DATE) BETWEEN DATE '<start>' AND DATE '<end>' clause. The same query covers any window; only the start date changes:
- Month-to-date: first of the current month (for example,
2026-06-01) - Year-to-date:
2026-01-01 - Since launch:
2026-04-22
Set the end date to the reporting date.
Cumulative unique-user metrics cannot be derived by filtering a date range, because summing daily unique_users counts user-days, not distinct people. Instead, pick the pre-computed column that matches the window and take its latest value:
- Month-to-date:
mtd_unique_users - Year-to-date:
ytd_unique_users
Never sum these cumulative columns across rows.
There is no since-launch unique-user column. Additive metrics, such as authentication events, SSO events, and MFA attempts, can be summed over any window back to launch.
Weighted averages should be used for call centre averages
When calculating average call length or average delay across multiple weeks, use a weighted average based on calls_answered.
A simple average of weekly averages may give too much influence to low-volume weeks.
Internal CanadaLogin components should usually be excluded from external adoption reporting
Which applications count as internal CanadaLogin components is recorded in rp.service.is_internal, not in this document. Resolve it through rp.alias, which maps the application IDs IBM Verify uses (and the names Google Analytics uses) back to a single service:
SELECT al.alias
FROM rp.alias al
JOIN rp.service s ON al.rp_id = s.rp_id
WHERE s.is_internalQuery the list rather than pasting it into a filter. A service is reclassified by editing the source spreadsheet, and a query holding its own copy of the list will not notice. Match it against application_id. The same subquery works as a NOT IN in SQL and as an anti_join() in dbplyr; both forms appear in SSO Events by Application.
Whether to exclude internal CanadaLogin components at all depends on the reporting question.
External services with no recent traffic can be treated as inactive
An external service with no SSO events in the last three months can be treated as inactive and left out of reporting, such as a count of integrated services or a table with one row per service. It is still a relying party; it has nothing to report. Say so wherever a count of services appears, because a reader cannot tell a dropped service from one that never existed.
The user ID “UNKNOWN” counts as one user
Some IBM Verify events carry the literal user ID "UNKNOWN" in place of a pseudonymized one. The summary tables count it as one user in unique_users, mtd_unique_users, and ytd_unique_users. Recipes that read the raw stream do the same, so the two agree. It moves any count by at most one.