Bi Analyst

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.

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

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

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

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

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

Core Workflows

1. Dashboard Design

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

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

2. Analytical SQL

Common analysis patterns:

3. Reporting & Metrics

Metric definition template:

## [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]

4. Stakeholder Management

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

Dashboard Design

Layout Patterns

Executive Summary Dashboard

Purpose: High-level health check for leadership Layout:

• Top row: 3-5 KPI cards (current value, vs target, vs prior period)
• Middle: Trend line for primary metric (revenue, users, etc.)
• Bottom: Breakdown by key dimension (region, product, channel)

Refresh: Daily or weekly

Operational Dashboard

Purpose: Monitor daily operations and catch issues Layout:

• Left: Real-time metrics with alerts (queue depth, error rates)
• Center: Hourly/daily trends
• Right: Detailed table for drill-down

Refresh: Hourly or near real-time Interaction: Filtering by date, team, status

Analytical/Exploratory Dashboard

Purpose: Enable self-service analysis Layout:

• Filters on top (date range, dimensions)
• Multiple charts with cross-filtering
• Summary table with export option

Refresh: Daily or on-demand

Chart Selection Guide

Avoid These Common Mistakes

Color & Accessibility

Rules:

• Use color intentionally: red/green only for good/bad status
• Ensure 4.5:1 contrast ratio for text
• Don't rely on color alone; add patterns or labels
• Colorblind-friendly palettes: Tableau 10, ColorBrewer

Dashboard color roles:

Tool-Specific Tips

Tableau

• Use extracts for performance; live connections for real-time
• Set up publishing permissions by project
• Use parameters for user-driven filtering
• Avoid excessive calculated fields; pre-compute in database when possible

Looker

• Define once in LookML; reuse across dashboards
• Use explores to prevent user confusion
• Set up content validation for production
• Use sql_always_where for row-level security

Power BI

• Use DAX carefully; complex measures can be slow
• Import mode for performance; DirectQuery for large datasets
• Set up row-level security (RLS) by role
• Use bookmarks and drill-through for interactivity

Looker Studio (Data Studio)

• Blend data sparingly; prefer single source
• Use community visualizations cautiously
• Set up data credentials for sharing

Dashboard Performance

Documentation Template

# [Dashboard Name]

## Purpose
What question does this answer?

## Audience
Who uses it and how often?

## Data Sources
- [Source 1] — refresh frequency
- [Source 2] — refresh frequency

## Key Metrics
| Metric | Definition | Owner |
|---|---|---|
| Revenue | Sum of order_value | Finance |

## Filters
- Date range: defaults to last 30 days
- Region: all by default

## Known Issues / Limitations
- Data lags by 24 hours
- Excludes refunds (see separate dashboard)

## Changelog
| Date | Change | Author |
|---|---|---|
| 2024-01-15 | Added region filter | @analyst |

Reporting & Metrics

KPI Framework

Metric Hierarchy

Business Objective
  └── Strategic KPIs (lagging indicators)
        └── Operational KPIs (leading indicators)
              └── Tactical Metrics (diagnostics)

Example:

Increase revenue by 20%
  └── Monthly Recurring Revenue (MRR)
        └── Customer Acquisition Rate
              └── Marketing Qualified Leads (MQLs)
              └── Lead-to-Customer Conversion Rate
        └── Customer Retention Rate
              └── 30-Day Activation Rate
              └── Support Ticket Volume
        └── Average Revenue Per User (ARPU)
              └── Upsell/Cross-sell Rate
              └── Product Usage Frequency

Metric Definition Standards

Required Fields

Metric Categories

Report Templates

Weekly Business Review

# Week of [Date]

## Executive Summary
- Key takeaway 1
- Key takeaway 2
- Action required from leadership

## KPIs vs Targets
| KPI | Current | Target | Status | Trend |
|---|---|---|---|---|
| Revenue | $1.2M | $1.1M | ✅ Ahead | ↑ 5% |

## Deep Dives
### [Topic 1]
- Observation
- Root cause (if known)
- Recommended action

## Actions & Owners
| Action | Owner | Due |
|---|---|---|
| Investigate churn spike | @analyst | Friday |

Monthly Performance Report

# [Month] Performance Report

## Highlights
Top 3 wins and top 3 concerns

## Trends (6-month view)
[Insert key trend charts]

## Segment Performance
| Segment | Revenue | Growth | Notes |
|---|---|---|---|
| Enterprise | $500K | +12% | Strong upsell |
| SMB | $300K | -3% | Churn in Q2 cohort |

## Forecast vs Actual
| Metric | Forecast | Actual | Variance |
|---|---|---|---|

## Next Month Focus
1. Priority 1
2. Priority 2

Root Cause Analysis Template

When a metric moves unexpectedly:

## Metric: [Name]
## Observation: [What changed and by how much]
## Timeframe: [When did it start]

### Hypotheses
1. [Hypothesis 1] — [How to test]
2. [Hypothesis 2] — [How to test]
3. [Hypothesis 3] — [How to test]

### Analysis
[Data and charts supporting or refuting each hypothesis]

### Conclusion
[Most likely cause with confidence level]

### Recommended Action
[What should be done, by whom, by when]

Data Quality Disclaimers

Always include when relevant:

• Data latency: "Data refreshes daily at 6 AM UTC"
• Known gaps: "Refund data lags by 48 hours"
• Scope limitations: "Excludes test accounts and internal users"
• Methodology notes: "Cohort defined by first purchase date"

Report Automation

Analytical SQL

Date & Time Analysis

Calendar Dimensions

-- Generate date spine
WITH date_spine AS (
  SELECT date_trunc('day', d) AS date_day
  FROM generate_series('2023-01-01'::date, CURRENT_DATE, '1 day'::interval) AS d
)

Period-over-Period

WITH daily AS (
  SELECT date_trunc('day', order_date) AS day, SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT
  day,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY day) AS prev_day,
  LAG(revenue, 7) OVER (ORDER BY day) AS prev_week,
  LAG(revenue, 28) OVER (ORDER BY day) AS prev_28_day,
  revenue / NULLIF(LAG(revenue, 28) OVER (ORDER BY day), 0) - 1 AS yoy_28d
FROM daily;

Year-to-Date / Rolling Windows

SELECT
  date,
  SUM(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d,
  SUM(revenue) OVER (PARTITION BY year ORDER BY date) AS ytd_revenue,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_30d_avg
FROM daily_metrics;

Cohort Analysis

Retention Cohort

WITH user_first_order AS (
  SELECT user_id, MIN(order_date) AS first_order_date
  FROM orders GROUP BY 1
),
cohort_activity AS (
  SELECT
    u.user_id,
    u.first_order_date,
    o.order_date,
    DATEDIFF('month', u.first_order_date, o.order_date) AS periods_since_first
  FROM user_first_order u
  JOIN orders o ON u.user_id = o.user_id
)
SELECT
  DATE_TRUNC('month', first_order_date) AS cohort_month,
  periods_since_first,
  COUNT(DISTINCT user_id) AS active_users,
  COUNT(DISTINCT user_id) * 1.0 / FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
    PARTITION BY DATE_TRUNC('month', first_order_date) ORDER BY periods_since_first
  ) AS retention_rate
FROM cohort_activity
GROUP BY 1, 2
ORDER BY 1, 2;

Funnel Analysis

Step-by-Step Conversion

WITH events AS (
  SELECT user_id, event_name, event_timestamp,
    MIN(CASE WHEN event_name = 'signup' THEN event_timestamp END) OVER (PARTITION BY user_id) AS signup_time
  FROM event_log
)
SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'signup' THEN user_id END) AS signups,
  COUNT(DISTINCT CASE WHEN event_name = 'product_view' THEN user_id END) AS viewed,
  COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS added_cart,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchased,
  -- Conversion rates
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) * 1.0 /
    NULLIF(COUNT(DISTINCT CASE WHEN event_name = 'signup' THEN user_id END), 0) AS overall_cvr
FROM events
WHERE event_timestamp >= DATEADD('day', -30, CURRENT_DATE);

Time-to-Convert

WITH first_events AS (
  SELECT user_id, MIN(CASE WHEN event_name = 'signup' THEN event_timestamp END) AS signup,
    MIN(CASE WHEN event_name = 'purchase' THEN event_timestamp END) AS purchase
  FROM event_log
  GROUP BY 1
)
SELECT
  AVG(DATEDIFF('hour', signup, purchase)) AS avg_hours_to_convert,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF('hour', signup, purchase)) AS median_hours
FROM first_events
WHERE purchase IS NOT NULL;

Segmentation

RFM Analysis

WITH customer_stats AS (
  SELECT
    user_id,
    DATEDIFF('day', MAX(order_date), CURRENT_DATE) AS recency,
    COUNT(*) AS frequency,
    SUM(amount) AS monetary
  FROM orders
  GROUP BY 1
),
scored AS (
  SELECT
    user_id,
    NTILE(5) OVER (ORDER BY recency DESC) AS r_score,  -- lower recency = higher score
    NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
    NTILE(5) OVER (ORDER BY monetary DESC) AS m_score
  FROM customer_stats
)
SELECT
  r_score, f_score, m_score,
  COUNT(*) AS customer_count,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 THEN 'Champions'
    WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
    WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
    WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
    ELSE 'Others'
  END AS segment
FROM scored
GROUP BY 1, 2, 3;

Ranking & Top N

-- Top 10 products by revenue per region
WITH ranked AS (
  SELECT
    region, product_name, SUM(revenue) AS total_revenue,
    RANK() OVER (PARTITION BY region ORDER BY SUM(revenue) DESC) AS rank
  FROM sales
  GROUP BY 1, 2
)
SELECT * FROM ranked WHERE rank <= 10;

Anomaly Detection in SQL

WITH stats AS (
  SELECT
    metric_name,
    AVG(value) AS mean,
    STDDEV(value) AS stddev
  FROM daily_metrics
  WHERE date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY 1
)
SELECT
  m.date, m.metric_name, m.value,
  ABS(m.value - s.mean) / NULLIF(s.stddev, 0) AS z_score
FROM daily_metrics m
JOIN stats s ON m.metric_name = s.metric_name
WHERE ABS(m.value - s.mean) / NULLIF(s.stddev, 0) > 3;

Performance Tips

Stakeholder Management

Requirements Gathering

Discovery Interview Template

## Stakeholder: [Name, Role]
## Date: [Date]

### Business Context
What is happening in the business that prompted this request?

### The Question
What decision are you trying to make?

### Current State
How do you answer this question today?
What data do you currently use?

### Success Criteria
What would make this analysis/dashboard a success?
What action would you take with the answer?

### Constraints
What is the deadline?
Who else needs to see this?
Any known data limitations?

### Follow-up
- [ ] Data access request
- [ ] Prototype by [date]
- [ ] Review meeting scheduled

Question Refinement

When stakeholders ask for data, dig deeper:

Data Storytelling

SCQA Framework

• Situation: What is the current state?
• Complication: What changed or is problematic?
• Question: What do we need to decide?
• Answer: What does the data say?

Presentation Structure

1. Start with the answer (BLUF — Bottom Line Up Front) 2. Show the evidence (1-2 key charts) 3. Explain the "so what" (business impact) 4. Recommend action (specific, owned, time-bound) 5. Add details as backup (appendix for questions)

Slide Guidelines

Managing Feedback

Feedback Triage

Iteration Management

Limit feedback rounds:

• Round 1: Structural (metrics, dimensions, layout)
• Round 2: Refinement (formatting, labels, colors)
• Round 3: Polish only (minor tweaks)

If a round 4 is needed, revisit scope or requirements.

Building Trust

Proactive communication:

• Share interesting findings without being asked
• Warn about data issues before stakeholders discover them
• Document assumptions and limitations transparently

Demonstrating rigor:

• Show sample sizes and confidence intervals
• Acknowledge uncertainty ("estimate with 90% CI")
• Distinguish correlation from causation explicitly

Building data literacy:

• Teach stakeholders to read dashboards independently
• Share metric definitions openly
• Explain methodology in accessible language

Difficult Conversations

When Data Contradicts Expectations

1. Verify your analysis (check for errors first) 2. Present with context ("Here's what we expected, here's what we found") 3. Propose hypotheses, not conclusions 4. Suggest next steps (deeper dive, experiment, data fix)

When Data Quality Is Poor

1. Be transparent about limitations 2. Quantify the impact ("5% of records have missing values") 3. Propose remediation ("We can exclude these or impute with X") 4. Set expectations for when quality will improve

When Requests Are Impossible

1. Explain the blocker clearly (data doesn't exist, privacy constraint) 2. Offer alternatives (proxy metric, partial answer, future data collection) 3. Suggest who can unblock (engineering, legal, vendor)