app-order-prod-key-stats_skill

Overview

This skill provides key-order metrics for the Jiusport APP aggregated by adjustable product dimensions. It computes order count, unpaid orders, refunded orders, paid amount, refunded amount, and unique users, with optional keyword and business-section filters. The output is generated from a fixed read-only SQL template and presented as a Markdown table with a short summary.

How this skill works

I ask you to confirm time range, optional keywords, desired dimension (title or detail), and optional business section. I then build the fixed SQL template with the selected dimension and filters, run a read-only query against the analytics table, and return results as a Markdown table plus a brief interpretation. The SQL template and aggregation logic are immutable except for the selectable grouping dimension and the injected filters for keywords and business section.

When to use it

• You want order metrics for a specific period filtered by keywords
• You need paid/refund amounts and counts broken down by product title or title+description
• You want unique user counts alongside order-level KPIs
• You want to filter results to a specific business section (e.g., ticketing)
• You need a quick read-only summary for product-level sales share and refund analysis

Best practices

• Provide exact start and end timestamps in 'YYYY-MM-DD' or 'YYYY-MM-DD HH:00:00' format
• Use comma or space separated keywords; matching is LIKE against title and description
• Choose TITLE (default) or DETAIL when you need order description in grouping
• Specify business section using the supported internal code (e.g., TICKET_ORDER) to avoid mapping errors
• Do not request additional SQL changes or write operations—only the fixed read-only template is allowed

Example use cases

• Monthly paid and refund summary for orders containing keyword 'VIP', grouped by order title
• Compare paid amount and refund amount for a ticketing section over a campaign period
• List product titles with highest paid amount and unique user counts for a promotional window
• Drill into orders mentioning 'basketball' using title+description grouping to identify common order variants
• Quick sanity check of unpaid order counts for a recent date range