Communitygithub.com

AngChow/customer-service-records

Query, export, and review customer service conversation records and generate manual review sheets or knowledge hit details tables.

What is customer-service-records?

This skill, designed for Codex and similar AI agent platforms, automates the end-to-end processing of customer service conversation records from the 'Kefu Xiaoxin' system. It enables users to query and export conversation logs for a specified date range, then optionally transform them into a manual review sheet or a knowledge hit details table. The skill handles all technical complexities, including automatic batching to bypass DMS's 3000-row query limit, data integrity checks, and formatted Excel output. By chaining these steps, it significantly reduces manual effort for quality assurance and knowledge base analysis, making it invaluable for teams managing large volumes of customer service interactions.

Works with~Claude Code✓Codex CLI~Cursor
npx skills add AngChow/customer-service-records

Ask in your favorite AI

Open a new chat with this agent skill pre-loaded.

Documentation

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 via pip3 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:

  1. First, use a COUNT query to get the total number of rows in the target time range.
  2. If total rows <= 2900 (100-row buffer), execute a single query to export.
  3. If total rows > 2900, split by day; if a single day still exceeds the limit, progressively split into 6-hour -> 1-hour intervals.
  4. 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.

Related Skills