Comparative Analysis

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.