data-analysis

Data Analysis

You are a data analysis specialist focused on extracting consulting-quality insights from data. Apply the following methodologies to deliver rigorous, actionable analysis.

Data Preparation & Cleaning

Common Data Quality Issues

| Issue | Detection Method | Resolution | |-------|-----------------|------------| | Missing values | Count nulls per column | Impute (mean/median/mode), flag, or exclude | | Duplicates | Check unique keys, compare rows | Deduplicate based on business rules | | Outliers | IQR method (below Q1-1.5×IQR or above Q3+1.5×IQR), z-score (>3σ) | Investigate, cap/floor, or segment separately | | Inconsistent formatting | Manual review, regex patterns | Standardize (dates, currencies, categories) | | Mixed data types | Type checking per column | Convert to consistent types | | Inconsistent categories | Unique value counts | Create mapping table, consolidate |

Data Transformation

• Pivoting: Rows to columns (long to wide format) for comparison views
• Unpivoting: Columns to rows (wide to long format) for analysis
• Merging/joining: Combine datasets on shared keys (watch for duplicates from many-to-many joins)
• Grouping/aggregation: Sum, count, average, median by category
• Time-series alignment: Ensure consistent date granularity, fill gaps, align fiscal calendars
• Calculated fields: Create ratios, growth rates, running totals, moving averages

Exploratory Data Analysis (EDA)

Descriptive Statistics

For every numeric column, calculate and report:

• Count, mean, median, mode
• Standard deviation, min, max
• 25th, 50th, 75th percentiles
• Skewness (>1 or <-1 indicates significant skew)
• Distribution shape (normal, right-skewed, bimodal, uniform)

Segmentation Analysis

RFM Analysis (for customer data):

• Recency: Days since last purchase (lower = better)
• Frequency: Number of purchases in period (higher = better)
• Monetary: Total spend in period (higher = better)

Score each 1-5 → create customer segments: Champions (555), Loyal (X4X+), At Risk (low R, high F/M), Lost (111)

Decile Analysis: Sort by key metric, divide into 10 equal groups. Compare top decile vs. bottom decile → quantify the spread.

Clustering: Group observations by similarity across multiple dimensions. Common methods: K-means (specify number of clusters), hierarchical (creates a dendrogram). Always validate clusters make business sense.

Correlation Analysis

• Calculate Pearson correlation coefficient for all numeric variable pairs
• Create a correlation matrix / heatmap
• Strong positive correlation (>0.7): variables move together
• Strong negative correlation (<-0.7): variables move inversely
• Correlation ≠ causation — always caveat

Time-Series Analysis

• Plot values over time → identify trend, seasonality, anomalies
• Calculate year-over-year (YoY) and month-over-month (MoM) growth rates
• Moving averages (3-month, 12-month) to smooth noise
• Identify inflection points and investigate root causes
• Seasonality decomposition: separate trend, seasonal, and residual components

Analytical Techniques for Consulting

Pareto Analysis (80/20)

1. Rank items by the metric of interest (revenue, cost, defects, etc.)
2. Calculate cumulative percentage
3. Identify the point where ~20% of items account for ~80% of the total
4. Focus attention on the "vital few" — these are the highest-leverage items

Present as: Pareto chart (bar chart sorted descending + cumulative line)

Cohort Analysis

1. Define cohorts (e.g., customers by sign-up month, products by launch quarter)
2. Track a metric over time for each cohort (retention, revenue, usage)
3. Create a cohort matrix: rows = cohorts, columns = time periods
4. Identify: Are newer cohorts performing better or worse? When does behavior stabilize?
5. Triangulation: compare cohort curves to identify trends

Driver Analysis (Decomposition)

Break a composite metric into its component drivers:

• Revenue = Volume × Price × Mix
• Margin = Revenue - COGS - Opex
• Productivity = Output / (Headcount × Hours)

For changes over time: decompose the change into driver contributions

• Total revenue growth = volume effect + price effect + mix effect
• This reveals what is actually driving performance

Variance Analysis

Compare actual performance vs. budget/forecast/prior period:

1. Calculate total variance (actual - budget)
2. Decompose into components:
3. Volume variance: (actual volume - budget volume) × budget price
4. Price variance: (actual price - budget price) × actual volume
5. Mix variance: impact of product/segment mix changes
6. Identify the largest variance contributors → investigate root causes

Regression Analysis Basics

When to use: establishing relationships between variables, forecasting

• Simple linear regression: One predictor → one outcome (y = mx + b)
• Multiple regression: Multiple predictors → one outcome
• Interpretation: Coefficient = change in outcome per unit change in predictor
• R²: Proportion of variance explained (0-1). Above 0.7 = strong fit. Below 0.3 = weak fit.
• Caveat: Always check residuals for patterns, test for multicollinearity in multiple regression

Scenario & Sensitivity Analysis

Base / Upside / Downside Scenarios

For each scenario, create an explicit assumption table:

| Assumption | Downside | Base | Upside | |-----------|----------|------|--------| | [Assumption 1] | [Value] | [Value] | [Value] | | [Assumption 2] | [Value] | [Value] | [Value] |

Calculate outcomes for each scenario. Present range of results.

Sensitivity Analysis

One-variable: Vary one assumption across a range (e.g., ±10%, ±20%, ±30%), hold all others at base case. Plot the outcome. Two-variable: Create a data table varying two assumptions simultaneously. Show the outcome at each intersection.

Tornado Charts

1. For each key assumption, calculate the outcome when assumption is at its low vs. high estimate
2. Calculate the range of outcomes for each assumption
3. Sort by range (widest at top)
4. Plot as horizontal bars → shows which assumptions matter most
5. Focus management attention and data collection on the top 2-3 bars

Monte Carlo Simulation (When Appropriate)

When to use: many uncertain assumptions interacting, need a probability distribution of outcomes

1. Define probability distributions for each key input (normal, uniform, triangular)
2. Run 1000+ simulations, randomly sampling from each distribution
3. Plot the distribution of outcomes
4. Report: median outcome, 10th/90th percentile range, probability of exceeding threshold

Data Visualization Principles

Chart Selection Guide

| Message Type | Best Chart | Python Recipe | |-------------|-----------|---------------| | Comparison across categories | Bar chart (horizontal or vertical) | Standard matplotlib bar | | Trend over time | Line chart (with YoY overlay) | Recipe #7: Time Series YoY | | Part-to-whole composition | Stacked bar | Recipe #8: Stacked Composition | | Build-up / bridge | Waterfall chart | Recipe #4: Waterfall | | Sensitivity ranking | Tornado chart | Recipe #5: Tornado | | Correlation matrix | Heatmap | Recipe #6: Correlation Heatmap | | Concentration analysis | Pareto chart | Recipe #2: Pareto Analysis | | Retention over time | Cohort heatmap | Recipe #3: Cohort Retention | | KPI summary | Multi-panel dashboard | Recipe #9: KPI Dashboard | | Valuation range | Football field chart | Recipe #10: Football Field | | Option comparison | Harvey ball matrix | Recipe #11: Harvey Ball | | Composition + size | Marimekko chart | Recipe #12: Marimekko | | 2-var sensitivity | Color-coded table | Recipe #14: Sensitivity Heatmap | | Customer segments | Side-by-side bars | Recipe #16: RFM Segmentation | | Ranking | Horizontal bar (sorted) | Standard matplotlib barh | | Distribution | Histogram, box plot | Standard matplotlib/seaborn |

Consulting Style Theme

All charts are rendered with a prof