生成式数据分析 Skill 的完整范例解读 knowledge-work-plugins 中 Data Context Extractor 的 ShopCo 示例输出【免费下载链接】knowledge-work-pluginsOpen source repository of plugins primarily intended for knowledge workers to use in Claude Cowork项目地址: https://gitcode.com/GitHub_Trending/kn/knowledge-work-plugins本文围绕>-- BigQuery: List datasets SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA -- BigQuery: List tables in a dataset SELECT table_name FROM project.dataset.INFORMATION_SCHEMA.TABLES -- Snowflake: List schemas SHOW SCHEMAS IN DATABASE my_database -- Snowflake: List tables SHOW TABLES IN SCHEMA my_schema示例中SHOPCO_DW.CORE.FCT_ORDERS、SHOPCO_DW.CORE.DIM_CUSTOMERS等三部分限定名正是 Snowflake 方言下 schema 发现结果的直接体现。此阶段还会向用户确认“分析师最常查询的 3–5 张表”保证文档化工作优先覆盖高频使用面。Phase 2五类核心提问示例章节的源头SKILL.md 要求以对话方式而非一次性抛完询问五类问题示例产出的每个章节几乎都能一一映射回去提问主题典型提问在示例中对应的章节实体消歧最关键“大家说 user/customer 时到底指什么”Entity Disambiguation主标识符“某实体的主键是什么是否有多套 ID”实体章节中的user_id / customer_id / account_id关键指标“最常被问的 2–3 个指标怎么算”Key MetricsGMV、Net Revenue数据卫生“哪些数据必须永远过滤掉”Standard Filters常见坑“新分析师常犯什么错”参考文件中的 Gotchas 与 CaveatsPhase 3目录结构生成SKILL.md 规定了产物目录骨架[company]-data-analyst/ ├── SKILL.md └── references/ ├── entities.md # Entity definitions and relationships ├── metrics.md # KPI calculations ├── tables/ # One file per domain │ ├── [domain1].md │ └── [domain2].md └── dashboards.json # Optional: existing dashboards catalogShopCo 示例采用了一种更扁平的等价布局references/orders.md、customers.md、products.md直接置于 references 下并通过 SKILL.md 的Knowledge Base Navigation表格建立导航——两者都满足“每个领域一个独立参考文件”的原则。Phase 4打包与交付交付环节由 package_data_skill.py 支撑用法为python package_data_skill.py path/to/skill-folder [output-directory]该脚本在打包前会做硬性校验其校验规则恰好回答了“什么样才算一份合格的技能”目录下必须存在SKILL.md文件必须以---开头具备 YAML frontmatterfrontmatter 前 500 字符内必须含name:、前 1000 字符内必须含description:内容中不允许残留[PLACEHOLDER]或[COMPANY]占位符——这正是“示例必须写满、不得留空”的机器化约束。通过校验后脚本将所有文件以相对路径压缩为{技能目录名}.zip并跳过隐藏文件、__pycache__、.DS_Store、Thumbs.db等杂物。三、产物一解剖生成的 ShopCo SKILL.md这是 example-output.md 的核心也是整份技能的门面。完整内容如下注意其中内嵌了多个可运行的 SQL 块--- name: shopco-data-analyst description: ShopCo data analysis skill for Snowflake. Provides context for querying e-commerce data including customer, order, and product analytics. Use when analyzing ShopCo data for: (1) Revenue and order metrics, (2) Customer behavior and retention, (3) Product performance, or any data questions requiring ShopCo-specific context. --- # ShopCo Data Analysis ## SQL Dialect: Snowflake - **Table references**: SHOPCO_DW.SCHEMA.TABLE or with quotes for case-sensitive: Column_Name - **Safe division**: DIV0(a, b) returns 0, DIV0NULL(a, b) returns NULL - **Date functions**: - DATE_TRUNC(MONTH, date_col) - DATEADD(DAY, -1, date_col) - DATEDIFF(DAY, start_date, end_date) - **Column exclusion**: SELECT * EXCLUDE (column_to_exclude) --- ## Entity Disambiguation **Customer can mean:** - **User**: A login account that can browse and save items (CORE.DIM_USERS: user_id) - **Customer**: A user who has made at least one purchase (CORE.DIM_CUSTOMERS: customer_id) - **Account**: A billing entity, can have multiple users in B2B (CORE.DIM_ACCOUNTS: account_id) **Relationships:** - User → Customer: 1:1 (customer_id user_id for purchasers) - Account → User: 1:many (join on account_id) --- ## Business Terminology | Term | Definition | Notes | |------|------------|-------| | GMV | Gross Merchandise Value - total order value before returns/discounts | Use for top-line reporting | | NMV | Net Merchandise Value - GMV minus returns and discounts | Use for actual revenue | | AOV | Average Order Value - NMV / order count | Exclude $0 orders | | LTV | Lifetime Value - total NMV per customer since first order | Rolling calc, updates daily | | CAC | Customer Acquisition Cost - marketing spend / new customers | By cohort month | --- ## Standard Filters Always apply these filters unless explicitly told otherwise: sql -- Exclude test and internal orders WHERE order_status ! TEST AND customer_type ! INTERNAL AND is_employee_order FALSE -- Exclude cancelled orders for revenue metrics AND order_status NOT IN (CANCELLED, FRAUDULENT) --- ## Key Metrics ### Gross Merchandise Value (GMV) - **Definition**: Total value of all orders placed - **Formula**: SUM(order_total_gross) - **Source**: CORE.FCT_ORDERS.order_total_gross - **Time grain**: Daily, aggregated to weekly/monthly - **Caveats**: Includes orders that may later be cancelled or returned ### Net Revenue - **Definition**: Actual revenue after returns and discounts - **Formula**: SUM(order_total_gross - return_amount - discount_amount) - **Source**: CORE.FCT_ORDERS - **Caveats**: Returns can occur up to 90 days post-order; use settled_revenue for finalized numbers --- ## Knowledge Base Navigation | Domain | Reference File | Use For | |--------|----------------|---------| | Orders | references/orders.md | Order tables, GMV/NMV calculations | | Customers | references/customers.md | User/customer entities, LTV, cohorts | | Products | references/products.md | Catalog, inventory, categories | --- ## Common Query Patterns ### Daily GMV by Channel sql SELECT DATE_TRUNC(DAY, order_timestamp) AS order_date, channel, SUM(order_total_gross) AS gmv, COUNT(DISTINCT order_id) AS order_count FROM SHOPCO_DW.CORE.FCT_ORDERS WHERE order_status NOT IN (TEST, CANCELLED, FRAUDULENT) AND order_timestamp DATEADD(DAY, -30, CURRENT_DATE()) GROUP BY 1, 2 ORDER BY 1 DESC, 3 DESC ### Customer Cohort Retention sql WITH cohorts AS ( SELECT customer_id, DATE_TRUNC(MONTH, first_order_date) AS cohort_month FROM SHOPCO_DW.CORE.DIM_CUSTOMERS ) SELECT c.cohort_month, DATEDIFF(MONTH, c.cohort_month, DATE_TRUNC(MONTH, o.order_timestamp)) AS months_since_first, COUNT(DISTINCT c.customer_id) AS active_customers FROM cohorts c JOIN SHOPCO_DW.CORE.FCT_ORDERS o ON c.customer_id o.customer_id WHERE o.order_status NOT IN (TEST, CANCELLED) GROUP BY 1, 2 ORDER BY 1, 2 3.1 frontmatter触发式 descriptionname: shopco-data-analyst与description遵循 skill-template.md 的规范。值得注意两点设计description 是“触发式”的它列举了三个高频使用场景营收与订单指标、客户行为与留存、商品表现并加上“或任何需要 ShopCo 特定上下文的数据问题”兜底句。这样当用户在会话中提出相关问题、Skill 被检索评估时描述与用户意图的匹配率更高这是让 LLM 正确“想起”这份技能的关键。name 采用[company]-data-analyst约定既体现了归属公司又明确了角色定位方便在多个公司技能间区分。3.2 SQL Dialect 段放在最前的方言备忘该段完全取自 sql-dialects.md 中 Snowflake 一节的对应内容涵盖表引用DATABASE.SCHEMA.TABLE三段式大小写敏感列需加引号安全除法DIV0返回 0、DIV0NULL返回 NULL避免除零报错日期函数DATE_TRUNC、DATEADD、DATEDIFF列排除Snowflake 特有的SELECT * EXCLUDE (...)。把方言要点放在正文最前是有意的设计错误的方言语法是跨数仓技能最常见的失败点先声明“我在说哪种 SQL”后续所有示例才有正确的语法上下文。3.3 Entity Disambiguation 段一词多义的实体消歧“Customer”在 ShopCo 里至少有三个含义User可登录浏览/收藏的账号CORE.DIM_USERS.user_idCustomer至少完成一次购买的用户CORE.DIM_CUSTOMERS.customer_idAccountB2B 计费实体可挂多个用户CORE.DIM_ACCOUNTS.account_id。随后用两行明确了关系User → Customer为 1:1Account → User为 1:many。这种写法把“同一个词在不同语境下的精确指代”固化下来配合[TABLE: ID_FIELD]的标注方式让 Agent 在写 JOIN 时能直接找到正确的键。这正是 SKILL.md 中“实体消歧是 Critical 环节”的原因——语义歧义不解决后续所有查询都可能错位。3.4 Business Terminology 段术语表治标更治本GMV、NMV、AOV、LTV、CAC 五个电商高频术语各占一行格式为术语 | 定义 | 使用注意。注意 Notes 列不只是解释而是给出使用边界GMV 用于“top-line reporting”口径偏毛AOV 计算要“Exclude $0 orders”口径修正LTV 是“rolling calc, updates daily”时效性提示。这比单纯查字典式定义更有实战价值——它告诉 Agent 什么时候该用、什么时候不该用某个口径。3.5 Standard Filters 段可直接复制的过滤 SQL数据卫生提问的答案被组织为“除非用户明确要求否则总是应用”的过滤子句排除测试订单、内部用户、员工订单营收指标再排除取消与欺诈订单。将其做成可整段复制的 SQL 片段而非文字描述是示例刻意强调的要点之一——减少了 Agent 每次手动拼 WHERE 的出错率。3.6 Key Metrics 段公式即真理每个指标都按固定五元组描述Definition一句话定义→ Formula精确公式→ Source数据来源表.列→ Time grain时间粒度→ Caveats口径陷阱。以 GMV 为例公式精确到SUM(order_total_gross)来源精确到列Caveats 明确指出“包含未来可能取消/退货的订单”。而 Net Revenue 的 Caveats 更关键——“退货可能发生在下单后 90 天内正式口径请用 settled_revenue”——这种隐性业务规则若不固化任何 Agent 都无从得知。3.7 Knowledge Base Navigation 与 Common Query Patterns导航表把references/orders.md、customers.md、products.md三个参考文件与用途一一对应形成“SKILL.md 是索引、references 是正文”的双层结构。最后给出两个完整可运行的查询模式Daily GMV by Channel按日 × 渠道聚合 GMV 与订单数体现了标准过滤 DATEADD时间窗的典型组合Customer Cohort Retention用 CTE 先算首购月再按DATEDIFF(MONTH, ...)计算留存月数——是一个跨DIM_CUSTOMERS与FCT_ORDERS的多表复杂查询范式。这两个示例直接演示了“标准过滤器如何落到真实查询中”属于可运行、可模仿的黄金样板。四、产物二解剖references/orders.md 领域参考文件SKILL.md提供索引真正承载细节的是领域参考文件。示例给出了references/orders.md的完整内容# Orders Tables Order and transaction data for ShopCo. --- ## Key Tables ### FCT_ORDERS **Location**: SHOPCO_DW.CORE.FCT_ORDERS **Description**: Fact table of all orders. One row per order. **Primary Key**: order_id **Update Frequency**: Hourly (15 min lag) **Partitioned By**: order_date | Column | Type | Description | Notes | |--------|------|-------------|-------| | **order_id** | VARCHAR | Unique order identifier | | | **customer_id** | VARCHAR | FK to DIM_CUSTOMERS | NULL for guest checkout | | **order_timestamp** | TIMESTAMP_NTZ | When order was placed | UTC | | **order_date** | DATE | Date portion of order_timestamp | Partition column | | **order_status** | VARCHAR | Current status | PENDING, SHIPPED, DELIVERED, CANCELLED, RETURNED | | **channel** | VARCHAR | Acquisition channel | WEB, APP, MARKETPLACE | | **order_total_gross** | DECIMAL(12,2) | Pre-discount total | | | **discount_amount** | DECIMAL(12,2) | Total discounts applied | | | **return_amount** | DECIMAL(12,2) | Value of returned items | Updates async | **Relationships**: - Joins to DIM_CUSTOMERS on customer_id - Parent of FCT_ORDER_ITEMS via order_id --- ## Sample Queries ### Orders with Returns Rate sql SELECT DATE_TRUNC(WEEK, order_date) AS week, COUNT(*) AS total_orders, SUM(CASE WHEN return_amount 0 THEN 1 ELSE 0 END) AS orders_with_returns, DIV0(SUM(CASE WHEN return_amount 0 THEN 1 ELSE 0 END), COUNT(*)) AS return_rate FROM SHOPCO_DW.CORE.FCT_ORDERS WHERE order_status NOT IN (TEST, CANCELLED) AND order_date DATEADD(MONTH, -3, CURRENT_DATE()) GROUP BY 1 ORDER BY 1 4.1 表头元数据五要素齐备对照 domain-template.md 的要求FCT_ORDERS的表头包含了完整的五要素Location完整三段式表路径Description表内容 使用场景“one row per order”还隐含粒度信息Primary Keyorder_idUpdate FrequencyHourly15 分钟延迟——决定数据新鲜度判断Partitioned Byorder_date——提示查询应先按分区列过滤以减少扫描。4.2 字段表Notes 列是精华9 个字段的表格里Notes 列记录了最容易踩坑的信息customer_id在游客下单时为 NULL外键可空order_timestamp是UTC时区陷阱order_date是分区列return_amount异步更新口径时效。正如 domain-template.md 的 Tips 所说“只对重要列做列级细节跳过created_at这类显而易见字段”“把 gotchas 放在显眼位置”。示例正是如此执行的。4.3 RelationshipsJOIN 拓扑两行关系描述补全了表间拓扑向上JOIN DIM_CUSTOMERS ON customer_id向下FCT_ORDER_ITEMS以order_id为外键挂靠。配合 SKILL.md 中实体消歧的键定义Agent 即可推断出完整的星型结构。4.4 Sample Queries带标准过滤的退货率查询示例查询展示了CASE WHEN计数 DIV0安全除法 近 3 个月时间窗的标准组合。注意它自动携带了 Standard Filters排除 TEST/CANCELLED——这是“参考文件中的示例必须可运行且遵循标准口径”的直接体现。五、从示例反推八项质量标准example-output.md 结尾明确列出“本示例演示了”的 8 项要素这也是评估任何生成技能质量的标准Complete frontmatter with triggering description—— frontmatter 完整、描述具备触发能力Dialect-specific SQL notes—— 方言专属 SQL 备忘Clear entity disambiguation—— 清晰的实体消歧Terminology glossary—— 术语表Standard filters as copy-paste SQL—— 可复制粘贴的标准过滤器Metric definitions with formulas—— 带公式的指标定义Navigation to reference files—— 参考文件导航Real, runnable query examples—— 真实可运行的查询示例。这与 SKILL.md 末尾的Quality Checklist遥相呼应交付前须逐项确认 frontmatter 完整、实体消歧清晰、关键术语已定义、标准过滤已记录、每个领域至少 2–3 条示例查询、SQL 使用正确的方言语法、参考文件已从 SKILL.md 导航链接。示例文档相当于把这份 checklist 变成了“已打勾”的参考答案。六、把示例移植到自己的数仓落地步骤替换主体将ShopCo换成你的公司名将 Snowflake 换成你的实际数仓类型BigQuery / PostgreSQL / Redshift / Databricks / MySQL 等并从 sql-dialects.md 中摘录对应方言段。按模板填坑以 skill-template.md 生成SKILL.md、以 domain-template.md 生成各领域参考文件把真实表名、列名、公式、过滤条件替换进占位符模板要求“不得残留任何[PLACEHOLDER]文本”这同时也是打包脚本的硬校验。执行五类提问按 SKILL.md 的 Phase 2 逐项收集实体消歧、主标识符、关键指标、数据卫生与常见坑确保每个章节都有真实业务依据。保持查询可运行示例中的 SQL 全部以真实示例内的表名列名写成你的版本也应如此——用可执行查询代替抽象描述。打包交付运行python package_data_skill.py 技能目录 [输出目录]脚本会先做结构校验存在 SKILL.md、含 frontmatter、无残留占位符再产出.zip分发文件。持续迭代后续需要补充营销、财务等新领域时切换至 Iteration 模式加载现有技能 → 定位缺口 → 针对性 schema 探索与提问 → 用领域模板生成新参考文件 → 更新Knowledge Base Navigation表格并重新打包。七、小结example-output.md 的价值在于它把“抽象的方法论”落地成了“具体的成品范本”一份合格的生成式数据分析 Skill应当同时具备触发式 frontmatter、方言备忘、实体消歧、术语表、标准过滤、带公式的指标定义、参考文件导航、可运行查询示例这 8 个要素并通过SKILL.md索引 references/细节的双层结构承载公司私有数据知识。对照 SKILL.md 的四阶段流程、两个占位符模板与 package_data_skill.py 的校验逻辑任何人都能把这套“分析师经验 → 结构化技能”的流水线复制到自己的数据仓库中——这正是 knowledge-work-plugins 生态中数据类技能的通用制造范式。【免费下载链接】knowledge-work-pluginsOpen source repository of plugins primarily intended for knowledge workers to use in Claude Cowork项目地址: https://gitcode.com/GitHub_Trending/kn/knowledge-work-plugins创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考