Skip to main content

Calls by Topic Category

Calls about each topic or category, as a share of calls, not of topic records.

How many calls were about each topic, and what share of all calls that is. Grouped into categories, this answers “what share of callers asked about X”.

It is a different measure from Call Topic Share, which answers “what share of the conversation was about X”. An agent can log several topics on one call. Call Topic Share counts topic records, so a call counts once per topic and the shares add up to 100%. This metric counts calls, so a call counts once per topic it touched, and the shares add up to more than 100%. Say which one a figure is: the two can differ by more than a factor of two for the same topic.

Calculation

Pick whole weeks of the weekly activity report (Sunday to Saturday). Take the topic records whose call_date falls inside those weeks, clean each topic, and count distinct call_no per topic. Divide by the weeks’ calls, excluding test calls (see Calls Accepted).

The denominator is the weekly report rather than the calls in the topic dump, because some calls have no topic logged. Every call is a caller; a call with no topic is a caller whose reason was not recorded.

WITH weeks AS (
    SELECT
        CAST(date_range_start AS DATE) AS week_start,
        CAST(date_range_end AS DATE) AS week_end,
        -- calls excluding test calls; a missing test_calls means none
        calls_accepted - COALESCE(test_calls, 0) AS calls
    FROM call_centre.weekly_activity_report
    -- set the reporting window in whole weeks
    WHERE CAST(date_range_start AS DATE) BETWEEN DATE '2026-09-06' AND DATE '2026-09-27'
),
topics AS (
    -- one row per call and topic, so a topic logged twice on a call counts once
    SELECT DISTINCT
        call_no,
        REGEXP_REPLACE(TRIM(statobject), '\s+', ' ') AS call_topic
    FROM call_centre.weekly_topic_dump
    WHERE call_date BETWEEN (SELECT MIN(week_start) FROM weeks)
                        AND (SELECT MAX(week_end) FROM weeks)
)

SELECT
    call_topic,
    COUNT(*) AS calls,
    CAST(COUNT(*) AS DOUBLE) / (SELECT SUM(calls) FROM weeks) AS share_of_calls
FROM topics
GROUP BY call_topic
ORDER BY calls DESC, call_topic;
weeks <- tbl(con, in_schema("call_centre", "weekly_activity_report")) |>
  # set the reporting window in whole weeks
  filter(
    as.Date(date_range_start) >= as.Date("2026-09-06"),
    as.Date(date_range_start) <= as.Date("2026-09-27")
  ) |>
  transmute(
    week_start = as.Date(date_range_start),
    week_end = as.Date(date_range_end),
    # calls excluding test calls; a missing test_calls means none
    calls = calls_accepted - coalesce(test_calls, 0)
  ) |>
  collect()

tbl(con, in_schema("call_centre", "weekly_topic_dump")) |>
  filter(
    call_date >= !!min(weeks$week_start),
    call_date <= !!max(weeks$week_end)
  ) |>
  # one row per call and topic, so a topic logged twice on a call counts once
  distinct(call_no, call_topic = str_squish(statobject)) |>
  count(call_topic, name = "calls") |>
  mutate(share_of_calls = as.double(calls) / !!sum(weeks$calls)) |>
  arrange(desc(calls), call_topic)

Grouping topics into categories

Topic text is free-form and grows over time, so the Support dashboard groups it into a handful of categories with a hand-kept map: the topic-categories.csv lookup first, then the patterns in topic_patterns in that dashboard’s R/metrics.R, then “Other”. Use that map rather than writing another one.

Count a call once per category, not once per topic: a call that touched two topics in the same category counts once in it. So apply the map to the call and topic rows before counting, not to the topic counts above:

categories <- readr::read_csv(
  "https://raw.githubusercontent.com/cds-snc/canadalogin-reporting-dashboards/main/dashboards/support/topic-categories.csv",
  show_col_types = FALSE
)

call_topics |>                       # one row per call_no and call_topic
  left_join(categories, by = c("call_topic" = "topic")) |>
  mutate(category = coalesce(category, "Other")) |>
  distinct(call_no, category) |>
  count(category, name = "calls")

This uses the lookup only; topics it does not list fall to “Other” here, where the dashboard would first try its patterns.

Interpretation notes

Shares add up to more than 100%. Do not stack them or draw them as a pie. Each one stands alone: “most callers asked what CanadaLogin is” is right; reading the remainder as “everything else” is wrong, because those same callers can appear under other categories too.

The windows have to match. The weekly report runs Sunday to Saturday. Cut the topic records to the same days, or a topic’s calls and the calls under it cover different spans.

Alternative names

  • why users called
  • call reasons
  • share of callers by topic
  • calls by category