BigQuery → Cisco Webex Contact Center Mapping

Field-level mapping from the flattened ccaas_raw_lab BigQuery tables (output of the CCAI → BigQuery pipeline) to the Cisco Webex Contact Center schema. This page is a scaffold — structure and styling are in place; the mapping rows are placeholders to be filled in.

1. Overview

Purpose: define how each column in the query-ready BigQuery tables lands in Cisco Webex Contact Center (and/or Webex CC Analyzer / the Reporting & Analytics data model), so the CCAI/UJET POC data can be reconciled against or loaded into Webex CC.

• Source of truth: p-640-lab-itmsc.ccaas_raw_lab.metadata, ….session_data_feeds, ….ujet_session_summaries.
• Target: Cisco Webex Contact Center — TBD: exact object(s) / API / dataset.
• Direction: TBD (one-way BQ→Webex, or reconciliation only).
• Grain: TBD (per contact / per session / per activity leg).

2. Flow

flowchart LR
    BQ["ccaas_raw_lab (BigQuery)\nmetadata / session_data_feeds / ujet_session_summaries"]
    T["Transform / mapping layer\n(TBD: SQL view, dbt, script)"]
    WX["Cisco Webex Contact Center\n(TBD: API / import / Analyzer model)"]
    BQ --> T --> WX
      

3. Environments & references

4. Mapping conventions

• Source column — exact column name in the flat BigQuery table.
• Target field — Webex CC field / attribute name.
• Transform — direct, cast, lookup, concat, derive, or a short expression.
• Notes — nullability, cardinality, known gaps.
• Use TBD for anything not yet confirmed.

5. Cisco report fields — SummaryDaily Reports (Agent-Detail)

Target Excel/Sheets export: SummaryDaily Reports(Agent-Detail). 33 columns, one row per agent × time interval. Column letters are the spreadsheet positions.

5b. Actual BigQuery table — ccaas_raw_lab.metadata_current

The live per-call table in p-640-lab-itmsc.ccaas_raw_lab. Confirmed from SELECT * FROM metadata_current WHERE session_key = 'call-12175'. Same columns as the flat metadata table plus five view-added columns (channel, provider_channel_id, session_key, provider_conversation_id, row_hash).

6. metadata_current → Cisco report field

Left column = real column in ccaas_raw_lab.metadata_current. The report grain is agent × 15-min interval × precision queue, so most rows below need a GROUP BY rollup off the per-call table.

6c. Kevin's requirements — working session (Sep 10, 2026)

Source: "Schedule CCaaS Exports to CC Archive" working session, Kevin McCoy (reporting owner) & Rohit Godugu. Notes below are a summary; verify against the recording before building.

What Kevin wants — two report shapes

1. Queue report — nothing to do with individual agents.
   • Dimensions: Queue and time interval (date is split out but is the same axis).
   • The "big five" measures: Offered, Answered, Abandoned (roughly offered − answered), Service Level (% of answered calls answered within a configured threshold), ASA (average speed of answer — how long answered calls waited).
   • 6th: Longest wait time in the interval (for answered calls).
   • Nice-to-have: max calls in queue at each interval.
   • Collected but not critical: abandon-within-30s buckets, abandoned-by-agent vs abandoned-by-member.
2. Agent report.
   • Dimensions: time interval, queue, agent.
   • Core measures (the ones they actually need): Calls Answered, Talk Time, Work Time (after-call wrap-up), Hold Time. Talk + Work + Hold = Handle Time (AHT).
   • "Take it if we have it": Agent-Out calls, Transfer In / Transfer Out (historically unreliable).
   • Low value: Logged-In / Available / Not-Ready time — double-counts when an agent is logged into multiple queues (every logged-in second counts on every queue).

What Kevin actually wants — CCAI side vs Cisco side

One row per attribute/measure Kevin asked for. CCAI (BigQuery) = column in ccaas_raw_lab.metadata_current (or a derivation from it). Cisco = column in the SummaryDaily Reports export. italic grey = not available on that side.

A. Queue report — dimensions

"selectedMenu": {
  "id": 8,
  "name": "US_ECOMM_General_CB",
  "parentId": 6,
  "menuType": "ivr_menu"
}
"menuPath": {
  "name": "Routing Queues/ECOMM General/US_ECOMM_General_CB",
  "materializedPath": "2/6/8"
}

B. Queue report — measures ("the big five" + 2)

C. Agent report — dimensions

D. Agent report — measures

TransferIn: 0
TransferOut: 0
AgentID: 31 (no per-transfer name field exists in
  Cisco's Agent-Detail export -- only aggregate counters;
  no comm_transfers-style from/to record is available for
  this call since Cisco doesn't track call-12175 at all)

participants_json[2]:
  id: 23804
  type: "agent"
  entry_type: "queue_or_transfer"
  user_id: 31
  first_name: "Chandrika"
  last_name: "Veerkar"
  status: "finished"

handle_durations[0].transfer: false
handle_durations[1].transfer: false

"transfers": [],
"escalations": [{
  "id": 5849,
  "status": "escalated",
  "reason": "by_virtual_agent",
  "createdAt": "2026-09-04T16:08:10.000Z",
  "escalatedAt": "2026-09-04T16:08:41.000Z",
  "fromVirtualAgent": {"id": 12, "name": "QAT-Voice-Member-Self-Service"},
  "toAgent": {"id": 31, "name": "Chandrika Veerkar"}
}]
"participants": [
  {"id": 23804, "type": "agent", "entryType": "queue_or_transfer",
   "userId": 31, "firstName": "Chandrika", "lastName": "Veerkar"}
]
"handleDurations": [
  {"id": 3580, "transfer": false, "transferId": null},
  {"id": 3581, "transfer": false, "transferId": null}
]

Cisco: TransferIn=0, TransferOut=0 (no name)
CCAI:  escalations[0].fromVirtualAgent.name = "QAT-Voice-Member-Self-Service"
       escalations[0].toAgent.name          = "Chandrika Veerkar"

E. Virtual-agent performance (Kevin wants this included, voice + chat)

{
  "id": 23803,
  "type": "virtual_agent",
  "status": "finished",
  "callDuration": 137,
  "connectedAt": "2026-09-04T16:05:53.000Z",
  "endedAt": "2026-09-04T16:08:10.000Z",
  "virtualAgent": {"conversationId": "119dw_UDr9XTGyfXuNujcf2Fw"}
}
"virtualAgent": {"id": 12, "name": "QAT-Voice-Member-Self-Service", "vaAlias": null}

{
  "id": 7908,
  "virtualAgent": {"id": 12, "name": "QAT-Voice-Member-Self-Service"},
  "callDuration": 137,
  "escalationReason": "by_virtual_agent",
  "finishReason": "escalated",
  "responseCount": 23,
  "fallbackResponseCount": 0,
  "initiatedBy": "end_user",
  "startedAt": "2026-09-04T16:05:53.000Z",
  "endedAt": "2026-09-04T16:08:10.000Z"
}

F. Grain, keys & PII

"participants": [
  {"id": 23802, "type": "end_user", "callId": 12175},
  {"id": 23803, "type": "virtual_agent", "callId": 12175},
  {"id": 23804, "type": "agent", "userId": 31, "callId": 12175}
]

{"id": 23802, "type": "end_user", "phoneNumber": "+918867813818"}
{"id": 23803, "type": "virtual_agent", "virtualAgent": {"conversationId": "119dw_UDr9XTGyfXuNujcf2Fw"}}
{"id": 23804, "type": "agent", "userId": 31, "firstName": "Chandrika", "lastName": "Veerkar",
 "teams": [{"id":10,"name":"US Ecomm Spanish Agents"}, {"id":13,"name":"PR Logistics English + Spanish"}]}

Cross-cutting requirements

• Virtual agents included (voice and chat) — both the self-service "member service agent" (does real work via GMP: reward status, membership upgrade) and the FAQ / knowledge bot that escalates. Want: how many interactions enter, how many answered, how many resolved / contained. Kevin will separate VA stats in his own reports but wants them in the export.
• Grain = one row per interaction, keyed by call_id (calls) / chat_id (chats). On a transfer, one row is not enough — each agent who handled the call must get a row so both get credit. So: a stable interaction ID for distinct counting, plus per-agent rows for agent credit. Queue "answered" counts distinct IDs; agent "answered" counts rows.
• PII: redact member name and member phone number from transcripts. Kevin does not want to collect member PII (compliance burden). The support-line number is fine.
• Where wait time occurs in CCAI: member → steering/root agent (= IVR) → sub-agent (order mgmt, membership, …; these sub-agents are deployed to queues / PQs) → escalation agent → human agent. No wait time until escalation to a human agent — ASA / wait time only accrues at that last step.

Coverage vs our columns

Queue report

Agent report

Virtual agent / containment

Grain & keys

PII

6d. One record, fully expanded — call-12175

Every field of a single BigQuery row (session_key = 'call-12175'), flattened section → item → field, exactly as it comes back from the nested-repeated query. This shows what one real interaction record actually contains, field by field.

7. metadata → Webex CC

Source: p-640-lab-itmsc.ccaas_raw_lab.metadata (~7,278 rows, one per call).

8. session_data_feeds → Webex CC

Source: p-640-lab-itmsc.ccaas_raw_lab.session_data_feeds (one row per event block).

9. ujet_session_summaries → Webex CC

Source: p-640-lab-itmsc.ccaas_raw_lab.ujet_session_summaries (~30 rows in POC).

Reference: value / enum maps

metadata.status → Webex CC

session_data_feeds.event_type → Webex CC activity / event

Reference: transform rules

Open questions

1. Which Webex CC surface is the target — Provisioning API, a data import, or the Analyzer / Reporting model?
2. Is this a one-way load, or reconciliation between CCAI data and Webex CC records?
3. What is the join key between CCAI call_uuid / id and a Webex CC contact / session ID?
4. Target grain: one row per contact, per session, or per activity leg?
5. Agent ID cross-walk: how do UJET agent_info_id values map to Webex CC agent IDs?
6. Queue model: does menu_path_* map to a Webex CC queue, entry point, or something else?
7. Required vs optional Webex CC fields with no CCAI source — defaults?
8. Timestamp format, timezone, and duration units expected by the target.

Log: Queue name verification notes

Cross-system queue-name verification trail — matching the same interaction across Cisco, CCAI, BigQuery, and Salesforce, plus a false-positive dead end that came up while doing it.

The verification chain

Confirming Cisco’s “US Membership” queue is the same queue as CCAI’s US_MS_General_Chat, by tracing one real interaction across all four systems:

Dead end: US_EN_CTWS_PCS

Conclusion: US_EN_CTWS_PCS is an unrelated CTWS-LOB queue and is not part of the Membership/Chat mapping — the agent mismatch (Ans_Butt vs. Bibiam Martinez) confirms it’s a false positive from a loose keyword search, not a real cross-system match. Search should filter on the PQ Name column specifically, not scan every column for the queue-name text.

Confirmed Cisco ↔ CCAI queue-name mapping (reference table)

Source: user’s personal “Queue Name verification 2” notes, cross-referencing Google Cloud/BigQuery (QAT env), CXinsights, CCAI, Salesforce, and a Cisco Excel export dated 07/14/2026. “NEW” rows have no confirmed Cisco-side name yet.