Back to Details

bi-analyst

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Business Intelligence Analyst

商业智能分析师

Overview

概述

Design dashboards, write analytical SQL, define KPIs, and manage stakeholder analytics requirements. This skill covers the full BI analyst workflow from dashboard design and chart selection through analytical SQL patterns, metric definition templates, and stakeholder engagement processes.
设计仪表盘、编写分析型SQL、定义KPI，并管理利益相关方的分析需求。 本技能涵盖BI分析师的完整工作流程，从仪表盘设计、图表选择，到分析型SQL模式、指标定义模板，再到利益相关方沟通流程。

Features

功能特性

  • Chart selection guidance for different analytical questions
  • SQL pattern library for cohort, funnel, retention, and cumulative analysis
  • Metric definition templates with formula, numerator, denominator, and data source
  • Stakeholder interview and engagement workflow
  • BI tool patterns for Tableau, Looker, and Power BI
  • 针对不同分析场景的图表选择指导
  • 适用于群组、漏斗、留存和累计分析的SQL模式库
  • 包含公式、分子、分母和数据源的指标定义模板
  • 利益相关方访谈与沟通流程
  • 适用于Tableau、Looker和Power BI的BI工具模式

Usage

使用方法

  1. Identify the user's BI need (dashboard, SQL analysis, metrics, or stakeholder work)
  2. Follow the corresponding workflow below
  3. Produce structured outputs: dashboard wireframes, SQL queries, metric definitions, or stakeholder interview notes
  1. 识别用户的BI需求（仪表盘、SQL分析、指标或利益相关方协作）
  2. 遵循下方对应的工作流程
  3. 生成结构化输出：仪表盘线框图、SQL查询语句、指标定义或利益相关方访谈记录

Examples

示例

  • User: "Build a retention dashboard" Agent: Runs Dashboard Design workflow, selects line chart for retention curves, applies F-pattern hierarchy, adds benchmark context
  • User: "Write SQL for cohort analysis" Agent: Runs Analytical SQL workflow, uses self-join on first-event date pattern, returns cohort retention table
  • User: "Define our churn metric" Agent: Runs Reporting & Metrics workflow, fills metric definition template with formula, numerator, denominator, data source
  • 用户："Build a retention dashboard" Agent：执行仪表盘设计工作流程，选择折线图展示留存曲线，应用F型布局层级，添加基准对比信息
  • 用户："Write SQL for cohort analysis" Agent：执行分析型SQL工作流程，使用基于首次事件日期的自连接模式，返回群组留存表
  • 用户："Define our churn metric" Agent：执行报告与指标工作流程，填写指标定义模板，包含公式、分子、分母和数据源

When to Use

使用场景

  • Building or revising dashboards and self-serve BI reports
  • Writing analytical SQL for metrics, cohorts, funnels, or retention
  • Defining, documenting, or reconciling KPIs and business metrics
  • Presenting data insights or eliciting analytics requirements from stakeholders
  • 构建或修订仪表盘及自助式BI报告
  • 编写用于指标、群组、漏斗或留存分析的分析型SQL
  • 定义、文档化或协调KPI与业务指标
  • 展示数据洞察或向利益相关方收集分析需求

When NOT to Use

非适用场景

  • Enterprise data platform, mesh, or governance architecture → use 
    data-architect
  • Warehouse ETL design, incremental loads, or platform-specific tuning → use 
    data-warehouse-engineer
  • dbt marts, incremental models, data tests, and docs/lineage → use 
    analytics-data-engineer
  • Predictive modeling, experiment design, or ML productionization → use 
    data-scientist
  • Business process mapping or BRD/FRD requirements without analytics delivery → use 
    business-analyst
  • Business model research, market sizing, unit economics modeling → use 
    business-model-researcher
  • 企业数据平台、数据网格或治理架构 → 使用
    data-architect
  • 数据仓库ETL设计、增量加载或平台特定调优 → 使用
    data-warehouse-engineer
  • dbt数据集市、增量模型、数据测试及文档/血缘 → 使用
    analytics-data-engineer
  • 预测建模、实验设计或ML落地 → 使用
    data-scientist
  • 无分析交付的业务流程映射或BRD/FRD需求 → 使用
    business-analyst
  • 商业模式研究、市场规模测算、单位经济模型 → 使用
    business-model-researcher

Core Workflows

核心工作流程

1. Dashboard Design

1. 仪表盘设计

Design checklist:
  1. Define the audience and action
    • Who uses this dashboard? How often?
    • What decision does it support?
    • What action should they take after viewing?
  2. Choose the right charts
    QuestionChart Type
    How much/many?KPI cards, bar charts
    How does it change over time?Line charts, area charts
    How is it distributed?Histograms, box plots
    How do parts relate to the whole?Pie charts (limited), treemaps, stacked bars
    How do variables relate?Scatter plots, heatmaps
    Where is it happening?Maps, geo charts
  3. Apply visual hierarchy
    • Most important metrics at top left (F-pattern reading)
    • Use size and color for emphasis, not decoration
    • Limit to 3-5 colors per dashboard
    • Consistent formatting across all dashboards
  4. Add context
    • Benchmarks, targets, or prior period comparisons
    • Annotations for significant events
    • Last refresh timestamp
设计检查清单：
  1. 定义受众与行动目标
    • 谁使用该仪表盘？使用频率如何？
    • 它支持什么决策？
    • 用户查看后应采取什么行动？
  2. 选择合适的图表
    问题类型图表类型
    数量/规模如何？KPI卡片、柱状图
    随时间如何变化？折线图、面积图
    分布情况如何？直方图、箱线图
    各部分与整体的关系？饼图（限用）、树状图、堆叠柱状图
    变量间的关系？散点图、热力图
    发生在何处？地图、地理图表
  3. 应用视觉层级
    • 最重要的指标放在左上角（符合F型阅读模式）
    • 使用大小和颜色强调重点，而非装饰
    • 每个仪表盘限制使用3-5种颜色
    • 所有仪表盘保持格式一致
  4. 添加上下文信息
    • 基准值、目标值或同期对比
    • 重大事件标注
    • 最后刷新时间戳

2. Analytical SQL

2. 分析型SQL

Common analysis patterns:
AnalysisSQL Pattern
Month-over-month growth
LAG()
window function
Running total
SUM() OVER (ORDER BY date)
Top N per group
ROW_NUMBER() OVER (PARTITION BY group ORDER BY metric DESC)
Cohort retentionSelf-join on first-event date
Funnel conversion
COUNT(DISTINCT CASE WHEN step = N THEN user_id END)
Cumulative distinct
COUNT(DISTINCT user_id) OVER (ORDER BY date)
常见分析模式：
分析类型SQL模式
同比/环比增长
LAG()
窗口函数
累计总和
SUM() OVER (ORDER BY date)
分组Top N
ROW_NUMBER() OVER (PARTITION BY group ORDER BY metric DESC)
群组留存基于首次事件日期的自连接
漏斗转化
COUNT(DISTINCT CASE WHEN step = N THEN user_id END)
累计独立用户数
COUNT(DISTINCT user_id) OVER (ORDER BY date)

3. Reporting & Metrics

3. 报告与指标

Metric definition template:
markdown
undefined
指标定义模板：
markdown
undefined

[Metric Name]

[Metric Name]

Definition: [Clear, unambiguous description] Formula: [Mathematical formula or SQL pseudocode] Numerator: [What is counted] Denominator: [The population, if a rate/ratio] Data source: [Table(s) used] Dimensions: [How it can be sliced: date, region, product] Owner: [Who maintains this definition] Last updated: [Date]
undefined
Definition: [Clear, unambiguous description] Formula: [Mathematical formula or SQL pseudocode] Numerator: [What is counted] Denominator: [The population, if a rate/ratio] Data source: [Table(s) used] Dimensions: [How it can be sliced: date, region, product] Owner: [Who maintains this definition] Last updated: [Date]
undefined

4. Stakeholder Management

4. 利益相关方管理

Engagement workflow:
  1. Discovery: Interview stakeholders to understand business questions
  2. Prototype: Build a quick draft with sample data
  3. Review: Walk through with stakeholders; capture feedback
  4. Refine: Iterate based on feedback (limit to 2-3 rounds)
  5. Deliver: Deploy with documentation and training
  6. Maintain: Schedule quarterly reviews for relevance
沟通工作流程：
  1. 发现需求：访谈利益相关方，了解业务问题
  2. 制作原型：使用样本数据快速构建草稿
  3. 评审反馈：与利益相关方共同审阅，收集反馈意见
  4. 优化迭代：根据反馈进行调整（限制2-3轮迭代）
  5. 交付成果：部署成品并提供文档与培训
  6. 维护更新：每季度安排评审，确保内容相关性