Hotel Operation Report Generator

Overview

Generate a multi-dimensional hotel operation analysis report by querying business data through the Aliyun DMS MCP service. The report covers OTA sales, GMV trends, traffic & search, operation scores, reviews, growth attribution, and actionable improvement measures — presented with KPI cards, charts, radar diagrams, and structured tables.

Prerequisites

1. Aliyun DMS MCP Configuration (User-Provided)

The user must configure their own DMS MCP connection. The token is not bundled in this skill. Guide the user through these steps:

1. Log in to the Aliyun DMS Console.
2. Navigate to the MCP / integration settings to obtain:
   • SSE URL (e.g., https://dms-mcpr-xxx.fcapp.run/sse)
   • Authorization Bearer Token (e.g., DMS-xxxx-xxxx-xxxx)
3. Write the configuration into ~/.workbuddy/mcp.json:

{
  "mcpServers": {
    "aliyun-dms-mcp-server": {
      "url": "<SSE_URL>",
      "headers": {
        "Authorization": "Bearer <DMS_TOKEN>"
      }
    }
  }
}

4. The user must open the connector management page in WorkBuddy and click Trust on the new aliyun-dms-mcp-server entry to activate it.

Security: Never hardcode or log the DMS token. Always use placeholders like <DMS_TOKEN> in documentation and examples.

2. Tool Availability Check

After the MCP is trusted, use ToolSearch to load DMS tools:

ToolSearch(queries: ["dms database search execute", "aliyun dms mcp"])

Key tools that should become available:

• mcp__aliyun-dms-mcp-server__listInstances
• mcp__aliyun-dms-mcp-server__searchDatabase
• mcp__aliyun-dms-mcp-server__getDatabase
• mcp__aliyun-dms-mcp-server__executeScript
• mcp__aliyun-dms-mcp-server__getTableDetailInfo
• mcp__aliyun-dms-mcp-server__listTables

3. PDF Export Tooling

For PDF export, the html-to-pdf-playwright skill (or Playwright + Chromium) is needed. If not available, install:

pip install playwright
playwright install chromium

Workflow

Step 1: Discover the Database

1. Call listInstances to enumerate registered database instances.
2. Call searchDatabase with keywords (hotel name, "hotel", "data", etc.) to locate the target database.
3. If search returns nothing, call getDatabase with the instance host/port and database name directly.
4. Record the database_id — it is required for all subsequent queries.

Tip: If searchDatabase returns empty, try executeScript with SHOW DATABASES; on a known instance to enumerate all schemas.

Step 2: Identify the Target Hotel

Query v_hotel_current to find the hotel:

SELECT hotel_id, name, short_name, city, room_count, status, updated_at
FROM v_hotel_current
WHERE name LIKE '%<keyword>%' OR short_name LIKE '%<keyword>%'
ORDER BY hotel_id;

If the hotel name is ambiguous (multiple branches), present the list to the user and let them choose. Record the hotel_id.

Step 3: Query Multi-Dimensional Data

Query data across 6 dimensions. See references/sql_queries.md for ready-to-use SQL templates. Adjust date ranges based on the user's request.

| Dimension | Primary Tables | Key Metrics | |-----------|---------------|-------------| | OTA Sales | ota_order, ota_order_price_item | Orders, valid orders, cancellation rate, sale amount, commission, ADR | | Business Daily | fact_business_daily | GMV, booking amount, instore amount, verified amount, refund, new users | | Operation Score | fact_score_daily, fact_score_metric | Total score, level, 5-dimension breakdown (装修/商品/内容/体验/评价) | | Traffic & Search | fact_traffic_entry_source, fact_traffic_scene, fact_search_overview, fact_search_keyword | Exposure, visit users, search clicks, CTR | | Conversion Funnel | fact_conversion_funnel | Exposure → click → trade user counts, conversion rates | | Reviews | fact_review_daily, fact_review_items | Avg score, review count, good rate, bad reviews |

Data Gap Handling: Some tables may have empty values for recent dates (data not yet synced). Always cross-validate with ota_order (which is usually the most timely) and note any missing data in the report.

Step 4: Analyze & Synthesize

Perform these analyses on the collected data:

1. Trend Analysis: Compare early vs. late period metrics (e.g., first 10 days vs. last 10 days of the month) to identify growth or decline.
2. Channel Breakdown: Analyze OTA orders by source (CTRIP, MEITUAN, DOUYIN, etc.) to identify channel dependency.
3. Room Type Analysis: Break down sales by room_type_name to find best-sellers and high-cancellation types.
4. Score Attribution: If the operation score dropped, identify which dimension (内容/体验/评价) caused the decline using fact_score_metric.
5. Growth Attribution: Synthesize the drivers of growth (new store traffic bonus, solid base score, high search conversion) and causes of slowdown (traffic decline, review score drop, single-channel dependency).
6. Peer Comparison: Query the same metrics for 2-3 comparable hotels to benchmark performance.

Step 5: Generate Visual Report

Generate an HTML report following the structure in references/report_template.md. Key design principles:

• Cover page with hotel name, report period, generation date.
• KPI cards for headline numbers (GMV, valid orders, operation score, cancellation rate, avg rating, search exposure).
• Charts: Use inline SVG or CSS-based charts (bar, line, pie, radar). Do NOT rely on external JS libraries (they may not render in PDF).
• Tables: Styled with alternating row colors, bold headers.
• Color scheme: Red for growth/positive, green for decline/negative (Chinese market convention). Blue for neutral data.
• Insight boxes: Colored callout boxes (blue=info, yellow=warning, red=critical) for key findings.

A base HTML template is available in assets/report_template.html.

Step 6: Export to Word / PDF

Word (DOCX):

cd <tencent-docx>/skills/html-to-docx/scripts && \
  python -m html_to_docx convert <input.html> -o <output.docx>

PDF (via Playwright):

from playwright.sync_api import sync_playwright

with sync_playwright() as p:
    browser = p.chromium.launch()
    page = browser.new_page()
    page.goto(f'file://{html_path}')
    page.pdf(path=pdf_path, format='A4', print_background=True,
             margin={'top': '10mm', 'right': '10mm', 'bottom': '10mm', 'left': '10mm'})
    browser.close()

Always use present_files to deliver the final document to the user.

Report Structure (6 Sections)

1. 核心数据概览 — KPI cards + headline summary
2. 销售表现 — GMV trend, daily orders, channel breakdown, room type analysis
3. 流量与客户 — Search exposure, traffic scenes, conversion funnel, new users
4. 内容运营 — Operation score radar, 5-dimension breakdown, improvement gaps
5. 销售增长归因 — Growth drivers, decline causes, attribution diagram
6. 运营诊断与提升 — Priority action items, monitoring plan, benchmark comparison

Common Issues & Solutions

| Issue | Solution | |-------|----------| | searchDatabase returns empty | Use getDatabase with host/port + database name, or executeScript with SHOW DATABASES; | | fact_conversion_funnel data is empty for latest date | Data sync delay; cross-validate with ota_order and note the gap in the report | | fact_traffic_entry_source has records but all zeros | Traffic data not yet crawled; use fact_search_overview which is usually more timely | | listTables returns 502 error | Retry; if persistent, use executeScript with SHOW TABLES; instead | | Playwright PDF conversion fails | Ensure Chromium is installed: playwright install chromium | | Score dropped but cause unclear | Query fact_score_metric for the date before and after the drop, compare dimension scores |

Reference Files

• references/database_schema.md — Complete table structures and field descriptions
• references/sql_queries.md — Ready-to-use SQL query templates for all 6 dimensions
• references/report_template.md — Detailed report structure, visualization design guide, and CSS specifications
• assets/report_template.html — Base HTML template with CSS styling