Skip to main content

call_centre.weekly_topic_dump

Individual support calls with their topic classifications, one row per topic per call.

Individual call records from the CanadaLogin support queue, with topic classifications for each call.

Each row represents one topic record associated with a single call. A single call may have multiple rows if multiple topics were recorded.

Keywords: call centre, topics, call reasons, contact drivers, caller issues

Provenance

Sourced from a weekly Excel report that is typically sent to the EDCP Data and Research team via email. That spreadsheet is extracted and loaded into the data repository via the call_centre_etl.py Lambda. The extracted_at field records when each row was last extracted from the source file.

  • Updated: Weekly, typically on Tuesdays
  • Retention: 7 years
  • Steward: EDCP Data and Research Team
  • Contact: Colin Douglas

Fields

One row per topic record per call. A single call (call_no) may appear on multiple rows if more than one topic was recorded.

Field Name Type Description
call_no integer Identifier for the call. Not unique - multiple rows share the same call_no when multiple topics were recorded
call_datetime timestamp Date and time the call took place
call_date date Date the call took place
date string Call date as a text date. Mirrors call_date
clienttype string Type of caller, for example “Business”, “Public”, “Government of Canada”
language string Language of the call, for example “English”, “French”
origin string Province or region the caller is calling from
statobject string The call topic as recorded in the source system. May contain leading/trailing whitespace or inconsistent spacing
cstat string Full topic classification path, including category and topic (for example, “Main Document description::What is CanadaLogin?”)
transactiontype string Type of transaction recorded for the topic: “Search”, “Callback”, or “Comment”. Padded with trailing spaces, so trim before comparing. “Callback” marks every topic row of a call escalated to PSO
programcontact string Program contact information provided to the caller, if any. Often missing
source float Unused

Notes

Because a single call can generate multiple topic rows, do not count rows to get call volume. Use COUNT(DISTINCT call_no) or join to weekly_activity_report for call counts.

statobject frequently contains leading/trailing whitespace and repeated internal spaces. Always clean it before grouping:

REGEXP_REPLACE(TRIM(statobject), '\s+', ' ') AS call_topic

See the Call Centre metrics chapter for topic count and topic share example SQL.

call_datetime is a native timestamp and can be used directly. call_date is the same value truncated to a DATE.