comparative-analysis

name	comparative-analysis
description	Systematic comparison of segments, cohorts, or time periods - ensure fair apples-to-apples comparisons, identify meaningful differences, explain WHY differences exist

Comparative Analysis Process

Overview

This skill guides you through systematic comparison of two or more groups, segments, cohorts, or time periods. Unlike exploratory-analysis (where you discover patterns) or guided-investigation (where you answer broad questions), comparative analysis helps you rigorously compare specific groups and explain why they differ.

Comparative analysis is appropriate when:

• You need to compare performance between specific segments (regions, products, customer cohorts)
• You want to understand how groups differ across multiple dimensions
• You're evaluating changes before/after an intervention or between time periods
• The user asks "How does X compare to Y?" or "What's different about segment A vs B?"
• You need to make fair, apples-to-apples comparisons with controls for confounding factors

Prerequisites

Before using this skill, you MUST:

1. Have data imported into SQLite database using the importing-data skill
2. Have data quality validated and cleaned using the cleaning-data skill (MANDATORY - never skip)
3. Have created an analysis workspace (just start-analysis comparative-analysis <name>)
4. Have clearly defined what you're comparing (user must specify comparison groups)
5. Be familiar with the component skills:
   • understanding-data - for data profiling
   • writing-queries - for SQL query construction
   • interpreting-results - for result analysis
   • creating-visualizations - for text-based visualizations

Mandatory Process Structure

You MUST use TodoWrite to track progress through all 5 phases. Create todos at the start:

- Phase 1: Define Comparison - pending
- Phase 2: Segment Definition - pending
- Phase 3: Metric Comparison - pending
- Phase 4: Difference Explanation - pending
- Phase 5: Conclusions and Recommendations - pending

Update status as you progress. Mark phases complete ONLY after checkpoint verification.

---

Phase 1: Define Comparison

CHECKPOINT: Before proceeding, you MUST have:

• Clarified what groups/segments/periods are being compared
• Identified the comparison objective (what question does this answer?)
• Determined comparison type (segments, cohorts, time periods, before/after)
• Established what metrics will be compared
• Documented the comparison framework
• Saved to 01 - comparison-definition.md

Instructions

1. Clarify the comparison goal with the user

Ask clarifying questions:

• What specific groups/segments do you want to compare?
• What's the purpose of this comparison? What decision will it inform?
• What differences would be meaningful or actionable?
• Are there specific metrics you care about most?
• What time period should the comparison cover?

2. Define the comparison framework

Create analysis/[session-name]/01-comparison-definition.md with: ./templates/phase-1.md

3. Get user confirmation before proceeding
   • Review comparison definition with user
   • Confirm groups are defined correctly
   • Verify metrics align with user's goals
   • Adjust framework if needed

Common Rationalization: "The comparison is obvious, I'll just start querying" Reality: Without explicit definition, you'll make unstated assumptions about what "fair" means.

Common Rationalization: "I'll compare everything and see what's different" Reality: Unfocused comparison produces noise. Define specific metrics and materiality thresholds.

---

Phase 2: Segment Definition

CHECKPOINT: Before proceeding, you MUST have:

• Verified that comparison groups exist in the data
• Validated group definitions with actual queries
• Checked for data quality issues within each group
• Documented group characteristics and sample sizes
• Confirmed groups are comparable (similar data quality, coverage)
• Saved to 02 - segment-definition.md

Instructions

1. Validate that groups exist and are well-defined

Create analysis/[session-name]/02-segment-definition.md with: ./templates/phase-2.md

2. Handle segment definition issues

If groups are not comparable:

• Sample size too small: Consider combining groups, expanding time window, or adjusting comparison
• Different time periods: Either align periods or explicitly control for temporal effects
• Data quality differs: Document the difference and consider if it invalidates comparison
• Overlapping groups: Redefine to ensure groups are mutually exclusive

3. Document any exclusions or adjustments

If you exclude outliers, filter dates, or adjust definitions, document clearly:

## Adjustments Made for Fair Comparison

1. **Outlier handling:** Excluded 8 transactions >$50,000 as data entry errors (confirmed with field validation)
2. **Date alignment:** Limited both groups to Feb 1 - Apr 30 to match shorter group's coverage
3. **Null handling:** Excluded 127 transactions with NULL customer_id from both groups

Common Rationalization: "The groups look fine, I'll skip validation" Reality: Unstated data quality issues or sample size problems will invalidate your comparison.

Common Rationalization: "Sample sizes are different but that's okay" Reality: Large sample size differences require per-capita normalization. Raw totals are misleading.

---

Phase 3: Metric Comparison

CHECKPOINT: Before proceeding, you MUST have:

• Calculated all primary metrics for each group
• Calculated all secondary metrics for each group
• Computed differences (absolute and percentage) between groups
• Created comparison visualizations (tables, charts)
• Identified which metrics differ meaningfully (per materiality threshold)
• Documented all comparisons with queries and results
• Saved to 03 - metric-comparison.md

Instructions

1. Compare groups systematically across all metrics

Create analysis/[session-name]/03-metric-comparison.md with: ./templates/phase-3.md

2. Be rigorous about normalization

Compare apples-to-apples:

• Use per-customer or per-day metrics, not raw totals (unless groups are identical size)
• Calculate percentages within group (e.g., % of group's revenue by category)
• Use rate metrics (transactions per customer, revenue per transaction)

Wrong: "Northeast has $458K revenue vs Southeast's $392K" Right: "Northeast has $161/customer vs Southeast's $150/customer (7.5% higher)"

3. Compute statistical and practical significance

Statistical significance: Is difference larger than random variation would explain?

• With large samples (>1000), small differences can be statistically significant
• Document sample sizes to provide context

Practical significance: Is difference large enough to matter for decisions?

• Use materiality threshold from Phase 1
• 5% difference in revenue might be huge for a billion-dollar company, trivial for a startup

4. Use visualization to make differences clear

• Side-by-side tables with difference columns
• ASCII bar charts showing relative magnitudes
• Percentage difference callouts

Common Rationalization: "I'll just show the raw numbers and let the user interpret" Reality: Your job is interpretation. Show differences clearly and explain what they mean.

Common Rationalization: "Northeast has more revenue, that's the answer" Reality: Explain WHY - more customers? Higher spend per customer? Different product mix? Dig deeper.

Common Rationalization: "These differences are statistically significant, so they matter" Reality: Statistical significance ≠ practical significance. A 1% difference might be "significant" but not meaningful.

---

Phase 4: Difference Explanation

CHECKPOINT: Before proceeding, you MUST have:

• Identified the 2-3 most important differences from Phase 3
• For each difference, investigated potential causes
• Analyzed confounding factors
• Ruled out alternative explanations where possible
• Quantified relative contribution of different factors
• Documented explanation analysis with supporting queries
• Saved to 04 - difference-explanation.md

Instructions

1. Focus on the most meaningful differences

Don't try to explain every small difference. Focus on:

• Differences that exceed materiality threshold
• Differences that are surprising or counter-intuitive
• Differences that inform the comparison objective

2. Investigate WHY differences exist

Create analysis/[session-name]/04-difference-explanation.md with: ./templates/phase-4.md

3. Be intellectually honest about causation

Comparative analysis shows WHAT differs, not always WHY:

• With observational data, you usually can't prove causation
• Multiple explanations may fit the data
• Acknowledge uncertainty and alternative explanations

4. Decompose complex differences

When metrics differ, break them into components:

• Revenue = Customers × Revenue per Customer
• Revenue per Customer = Transactions per Customer × Revenue per Transaction
• Identify which component drives the overall difference

Common Rationalization: "I found the difference, that's enough" Reality: Finding the difference is half the job. Explaining WHY is equally important.

Common Rationalization: "This factor correlates with the difference, so it's the cause" Reality: Correlation ≠ causation. Multiple factors may correlate. Be cautious about causal claims.

Common Rationalization: "I'll ignore confounds since I can't measure them" Reality: Acknowledge unmeasured confounds explicitly. They limit your conclusions but shouldn't be ignored.

---

Phase 5: Conclusions and Recommendations

CHECKPOINT: Before proceeding, you MUST have:

• Summarized key differences and their magnitudes
• Explained most likely drivers of differences
• Assessed confidence in findings
• Identified actionable insights
• Made specific recommendations based on comparison
• Documented limitations and caveats
• Saved to 05 - conclusions-and-recommendations.md
• Updated 00 - overview.md with comparison summary

Instructions

1. Synthesize findings into clear conclusions

Create analysis/[session-name]/05-conclusions-and-recommendations.md with: ./templates/phase-5.md

2. Update overview document

Update: 00 - overview.md

Add at the end:

## Comparison Summary

**Groups Compared:** [Groups]

**Time Period:** [Date range]

**Comparison Completed:** [Date]

---

## Key Differences Identified

1. **[Difference 1]:** [Brief description with magnitude]
   - Driver: [Primary explanation]
   - Confidence: [High/Medium/Low]

2. **[Difference 2]:** [Brief description with magnitude]
   - Driver: [Primary explanation]
   - Confidence: [High/Medium/Low]

3. **[Difference 3]:** [Brief description with magnitude]
   - Driver: [Primary explanation]
   - Confidence: [High/Medium/Low]

---

## Top Recommendations

1. **[Recommendation 1]:** [One sentence]
   - Expected impact: [Magnitude]

2. **[Recommendation 2]:** [One sentence]
   - Expected impact: [Magnitude]

---

## File Index

- 01 - Comparison Definition
- 02 - Segment Definition
- 03 - Metric Comparison
- 04 - Difference Explanation
- 05 - Conclusions and Recommendations

3. Communicate findings to user

Present conclusions clearly:

• Lead with directional summary (which group "wins" and why)
• Quantify key differences with specific numbers
• Explain drivers of differences
• Acknowledge uncertainty and limitations
• Provide actionable recommendations
• Suggest follow-up questions

Common Rationalization: "I'll just present all the numbers and let the user draw conclusions" Reality: Your job is to interpret and synthesize. Provide clear conclusions, not just data dumps.

Common Rationalization: "I'm 100% confident in these conclusions" Reality: Be honest about confidence levels and limitations. Overconfidence undermines credibility.

Common Rationalization: "Comparison complete, no follow-up needed" Reality: Comparisons often raise more questions than they answer. Identify high-value follow-up investigations.

---

Common Rationalizations

"The groups are obviously different, I'll skip formal definition"

Why this is wrong: "Obvious" differences often have unstated assumptions. Explicit definition prevents misunderstandings and ensures you're answering the right question.

Do instead: Complete Phase 1 fully. Define groups, metrics, and materiality thresholds explicitly.

"Sample sizes look fine, I'll skip validation"

Why this is wrong: Sample size is only one aspect. Data quality, temporal alignment, and outliers can invalidate comparisons even with large samples.

Do instead: Validate groups thoroughly in Phase 2. Check quality, coverage, and comparability.

"I'll just compare raw totals"

Why this is wrong: Raw totals are misleading when groups have different sizes. $500K vs $400K tells you nothing if one group is 2x the size of the other.

Do instead: Normalize by customers, days, or transactions. Compare per-capita or rate metrics.

"Northeast has higher revenue, that's the finding"

Why this is wrong: Stating WHAT differs without explaining WHY provides limited value. The explanation is where actionable insights live.

Do instead: Decompose differences. Explain whether higher revenue comes from more customers, higher per-customer value, different mix, etc.

"These groups are different, so one must be better"

Why this is wrong: Different doesn't mean better. Context matters. Lower-revenue group might serve a different market, have different goals, or optimize for different metrics.

Do instead: Interpret differences in context. Consider whether "better" is even the right framing.

"I found a correlation, so that's the cause"

Why this is wrong: Correlation ≠ causation. Many factors correlate with outcomes without causing them.

Do instead: Be cautious with causal language. Say "associated with" or "correlated with" rather than "caused by" unless you have experimental evidence.

"I'll ignore the confounds I can't measure"

Why this is wrong: Unmeasured confounds don't disappear by ignoring them. They limit what you can conclude.

Do instead: Explicitly acknowledge unmeasured confounds in Phase 4. Explain how they limit causal interpretation.

"I'll recommend that Southeast copy Northeast exactly"

Why this is wrong: You don't know if differences are due to replicable practices or immutable characteristics (market size, demographics, etc.).

Do instead: Recommend further investigation to understand whether differences are actionable or structural.

"This comparison answered the question completely"

Why this is wrong: Comparisons typically reveal new questions about root causes, generalizability, and interventions.

Do instead: Identify high-value follow-up questions in Phase 5. Guide next investigations.

"Statistical significance means it matters"

Why this is wrong: With large samples, tiny differences can be statistically significant but practically meaningless.

Do instead: Focus on practical significance (materiality threshold). A 1% difference might be "significant" but not meaningful.

---

Summary

This skill ensures rigorous, fair comparisons by:

1. Defining comparisons explicitly: Clear groups, metrics, and materiality thresholds prevent unstated assumptions
2. Validating segment quality: Ensuring groups are comparable prevents invalid comparisons
3. Comparing systematically: Multi-metric analysis reveals patterns that single metrics miss
4. Explaining differences: Understanding WHY groups differ is as important as knowing WHAT differs
5. Acknowledging limitations: Honest assessment of confounds and causation builds credibility
6. Providing actionable insights: Converting findings into recommendations and follow-up questions

Follow this process and you'll deliver fair, rigorous comparisons that explain not just WHAT differs but WHY, identify actionable opportunities, and guide follow-up investigations.