Data Analyst

Purpose

When to Use

• Creating or optimizing dashboards (Tableau, Power BI, Looker, Superset)
• Writing complex SQL queries for data extraction and analysis
• Defining and standardizing business KPIs (Churn, ARR, MAU, Conversion)
• Performing ad-hoc analysis to answer specific business questions
• Analyzing user behavior (Cohorts, Funnels, Retention)
• Automating reporting workflows

Core Capabilities

Business Intelligence

• Designing and building interactive dashboards in BI tools
• Creating automated reporting pipelines and data refresh schedules
• Implementing self-service analytics capabilities for business users
• Developing KPI frameworks and metric definitions

SQL and Data Extraction

• Writing complex queries with window functions, CTEs, and advanced joins
• Optimizing query performance for large datasets
• Creating reusable views and materialized tables
• Implementing data extraction from multiple data sources

Data Visualization

• Selecting appropriate chart types for different data stories
• Designing clear, intuitive dashboard layouts
• Implementing color schemes and visual hierarchies
• Creating interactive visualizations for exploration

Business Insights

• Translating data findings into actionable business recommendations
• Conducting cohort analysis, funnel analysis, and retention analysis
• Performing trend analysis and forecasting
• Communicating findings to non-technical stakeholders

3. Core Workflows

Workflow 1: Dashboard Design & Implementation

1. Requirements Gathering
   • Audience: VP of Sales, Regional Managers.
   • Questions to Answer: "Are we hitting target?", "Which region is lagging?", "Who are top reps?"
   • Key Metrics: Total Revenue, % to Quota, YoY Growth, Pipeline Coverage.
2. Data Preparation (SQL)
   sql

   WITH sales_data AS (
       SELECT 
           r.region_name,
           s.sales_rep_name,
           DATE_TRUNC('month', o.order_date) as sales_month,
           SUM(o.amount) as revenue,
           COUNT(DISTINCT o.order_id) as deal_count
       FROM orders o
       JOIN sales_reps s ON o.rep_id = s.id
       JOIN regions r ON s.region_id = r.id
       WHERE o.status = 'closed_won'
         AND o.order_date >= DATE_TRUNC('year', CURRENT_DATE)
       GROUP BY 1, 2, 3
   ),
   quotas AS (
       SELECT 
           sales_rep_name,
           month,
           quota_amount
       FROM sales_quotas
       WHERE year = EXTRACT(YEAR FROM CURRENT_DATE)
   )
   SELECT 
       s.*,
       q.quota_amount,
       (s.revenue / NULLIF(q.quota_amount, 0)) as attainment_pct
   FROM sales_data s
   LEFT JOIN quotas q ON s.sales_rep_name = q.sales_rep_name 
                      AND s.sales_month = q.month;

3. Visualization Design (Conceptual)
   • Top Level (KPI Cards): Total Revenue vs Target, YoY Growth %.
   • Trend (Line Chart): Monthly Revenue vs Quota trend line.
   • Breakdown (Bar Chart): Attainment % by Region (Sorted desc).
   • Detail (Table): Top 10 Sales Reps (Revenue, Deal Count, Win Rate).
4. Implementation & Interactivity
   • Add "Region" and "Date Range" filters.
   • Set up drill-through from Region bar chart to Rep detail list.
   • Add tooltips showing MoM change.
5. Quality Check
   • Validate numbers against source system (CRM).
   • Check performance (load time < 5s).
   • Verify filter interactions.

Workflow 3: Funnel Analysis (Conversion)

1. Define Steps
   1. Landing Page View
   2. Signup Button Click
   3. Form Submit
   4. Email Confirmation
2. SQL Analysis
   sql

   SELECT
       COUNT(DISTINCT CASE WHEN step = 'landing_view' THEN user_session_id END) as step_1_landing,
       COUNT(DISTINCT CASE WHEN step = 'signup_click' THEN user_session_id END) as step_2_click,
       COUNT(DISTINCT CASE WHEN step = 'form_submit' THEN user_session_id END) as step_3_submit,
       COUNT(DISTINCT CASE WHEN step = 'email_confirm' THEN user_session_id END) as step_4_confirm
   FROM web_events
   WHERE event_date >= DATEADD('day', -30, CURRENT_DATE);

3. Calculate Conversion Rates
   • Step 1 to 2: (Step 2 / Step 1) * 100
   • Step 2 to 3: (Step 3 / Step 2) * 100
   • Step 3 to 4: (Step 4 / Step 3) * 100
   • Overall: (Step 4 / Step 1) * 100
4. Insight Generation
   • "Drop-off from Click to Submit is 60%. This is high. Potential form friction or validation errors."
   • Recommendation: "Simplify form fields or add social login."

Workflow 5: Embedded Analytics (Product Integration)

1. Dashboard Creation (Parameterized)
   • Create dashboard in BI tool (e.g., Looker/Superset).
   • Add a global parameter

     customer_id

     .
   • Filter all charts:

     WHERE organization_id = {{ customer_id }}

     .
2. Security (Row Level Security)
   • Ensure

     customer_id

     cannot be changed by the client.
   • Use Signed URLs (JWT) generated by backend.
3. Frontend Integration (React)
   javascript

   import { EmbedDashboard } from '@superset-ui/embedded-sdk';
   
   useEffect(() => {
       EmbedDashboard({
           id: "dashboard_uuid",
           supersetDomain: "https://superset.mycompany.com",
           mountPoint: document.getElementById("dashboard-container"),
           fetchGuestToken: () => fetchGuestTokenFromBackend(),
           dashboardUiConfig: { hideTitle: true, hideTab: true }
       });
   }, []);

4. Performance Tuning
   • Enable caching on the BI server (5-15 min TTL).
   • Use pre-aggregated tables for the underlying data.

5. Anti-Patterns & Gotchas

❌ Anti-Pattern 1: Pie Chart Overuse

• Using a pie chart for 15 different categories.
• Using a pie chart to compare similar values (e.g., 49% vs 51%).

• Human brain struggles to compare angles/areas accurately.
• Small slices become unreadable.
• Impossible to see trends.

• Use Bar Charts for comparison.
• Limit Pie/Donut charts to 2-4 distinct categories (e.g., Mobile vs Desktop) where "Part-to-Whole" is the only message.

❌ Anti-Pattern 2: Complex Logic in BI Tool

• Creating 50+ calculated fields in Tableau/Power BI with complex

  IF/ELSE

  and string manipulation logic.
• Doing joins and aggregations inside the BI tool layer instead of SQL.

• Performance: Dashboard loads slowly as it computes logic on the fly.
• Maintenance: Logic is hidden in the tool, hard to version control or debug.
• Reusability: Other tools/analysts can't reuse the logic.

• Push logic upstream to the database/SQL layer.
• Create a clean View or Table (

  mart_sales

  ) that has all calculated fields pre-computed.
• BI tool should just visualize the data, not transform it.

❌ Anti-Pattern 3: Inconsistent Metric Definitions

• Marketing defines "Lead" as "Email capture".
• Sales defines "Lead" as "Phone call qualification".
• Dashboard shows conflicting numbers.

• Loss of trust in data.
• Time wasted reconciling numbers.

• Data Dictionary: Document definitions explicitly.
• Certified Datasets: Use a governed layer (e.g., Looker Explores, dbt Models) where the metric is defined once in code.

7. Quality Checklist

• Title & Description: Every chart has a clear title and subtitle explaining what it shows.
• Context: Numbers include context (e.g., "% growth vs last month", "vs Target").
• Color: Color is used intentionally (e.g., Red/Green for sentiment, consistent brand colors) and is colorblind accessible.
• Clutter: unnecessary gridlines, borders, and backgrounds removed (Data-Ink Ratio).

• Validation: Dashboard totals match source system totals (spot check).
• Null Handling:

  NULL

  values handled explicitly (filtered or labeled "Unknown").
• Filters: Date filters work correctly across all charts.
• Duplicates: Join logic checked for fan-outs (duplicates).

• Load Time: Dashboard loads in < 5 seconds.
• Query Cost: SQL queries are optimized (partitions used, select * avoided).
• Extracts: Use extracts/imports instead of Live connections for static historical data.

• Tooltips: Hover tooltips provide useful additional info.
• Mobile: Dashboard is readable on mobile/tablet if required.
• Action: The dashboard answers "So What?" (leads to action).