Customer Service Records (Kefu Xiaoxin) Skill
Query, export, and process customer service conversation records from Kefu Xiaoxin, converting them into a manual review sheet or a knowledge hit details table. Three workflows can be executed independently or in sequence: Query & Export (data source) -> Manual Review Sheet / Knowledge Hit Details Table (based on exported records).
Trigger Scenarios
- "Query/export Kefu Xiaoxin conversation records from X to Y"
- "Generate a manual review sheet for Kefu Xiaoxin from X to Y" (automatically queries and exports records first)
- "Generate knowledge hit details for Kefu Xiaoxin from X to Y" (automatically queries and exports records first)
Date format: YYYY-MM-DD. --end includes the full day.
Prerequisites
python3+pandas+openpyxl+pymysql. Install missing packages viapip3 install pandas openpyxl pymysql(prefer domestic mirrors if available).- Environment variables:
DMS_HOST/DMS_PORT(default 3306) /DMS_DB/DMS_ACCESS_ID/DMS_ACCESS_SECRET(configured in~/.zshenv, effective in new terminal sessions). - DMS is an internal network address and requires a company VPN connection.
Execution Flow
All three scripts output to the current working directory (cwd). {SKILL_DIR} = ~/.codex/skills/customer-service-records/scripts.
1. Query & Export Conversation Records
python3 {SKILL_DIR}/dms_query.py --start 2026-07-03 --end 2026-07-04
# Equivalent for a single day: --date 2026-07-03
- Connects to DMS, executes a fixed SQL query, and exports to xlsx
- Output:
customer-service-conversations_<start>~<end>.xlsx - The SQL is a built-in template; only the time range can be modified via
--start/--end/--date. No changes to SELECT/JOIN/CASE or other parts are allowed. - Automatic Batching: DMS secure access proxy limits single queries to 3000 rows. The script first COUNTs total rows; if the count exceeds 2900 rows, it automatically splits the query by day (or further into 6-hour/1-hour intervals if needed) and merges results, followed by a row count integrity check. The caller does not need to worry about batching logic—the output is always a single complete xlsx file.
2. Generate Manual Review Sheet
First execute step 1 to obtain the conversation records xlsx, then:
python3 {SKILL_DIR}/custom_service_data_transfer.py "<path_to_conversation_xlsx>" --start 2026-07-03 --end 2026-07-04
- Splits the "relevant knowledge" field into multiple rows, adding columns: Needs Manual Review, Knowledge Content, Knowledge Score, Knowledge Source
- Retains only rows that require manual review (thumbs-down, no relevant knowledge, insufficient knowledge to summarize, or normal answers with knowledge relevance > 0.25)
- Output:
customer-service-manual-review_<start>~<end>.xlsx
3. Generate Knowledge Hit Details Table
First execute step 1 to obtain the conversation records xlsx, then:
python3 {SKILL_DIR}/process_to_knowledge_detail.py "<path_to_conversation_xlsx>" --start 2026-07-03 --end 2026-07-04
- Splits the "relevant knowledge" field into multiple rows, adding columns: Knowledge, Knowledge ID, Knowledge Source, Knowledge Score, Knowledge Question, Knowledge Answer
- Retains all rows (no filtering)
- Output:
customer-service-knowledge-hit-details_<start>~<end>.xlsx
File Naming Convention
customer-service-{records|manual-review|knowledge-hit-details}_<start>~<end>.xlsx, dates in YYYY-MM-DD format, separated by ~.
The forward slash
/is a path separator and cannot appear in filenames, so dates use-as separators and~to connect start and end dates.
About the SQL
dms_query.py contains a fixed SQL template that LEFT JOINs aim.custom_service_chat with company.company to query Kefu Xiaoxin Q&A details. Only the time range may be modified (via command-line arguments); all other parts must remain unchanged. See the SQL_TEMPLATE within the script for details.
Additionally, the script includes a COUNT_SQL (using the same WHERE conditions as SQL_TEMPLATE) to count total rows before export, determining whether batching is required. This COUNT query does not involve JOINs and only counts rows from the custom_service_chat table.
DMS 3000-Row Limit
DMS secure access proxy has a 3000-row limit on single query result sets. dms_query.py ensures data integrity through the following strategy:
- First, use a COUNT query to get the total number of rows in the target time range.
- If total rows <= 2900 (100-row buffer), execute a single query to export.
- If total rows > 2900, split by day; if a single day still exceeds the limit, progressively split into 6-hour -> 1-hour intervals.
- After merging all batch results, perform a row count consistency check against the COUNT result.
Upon Completion
Report the generated file name and its absolute path.