git:20260902.b370de6 to git:20260910.c4b91ef

- description: >-

- Cross-reference and validate findings before presenting them: mandatory segment-first Simpson's

- Paradox check, denominator changes, survivorship bias, plausibility vs benchmarks. Deep mode

- adds a scored 4-layer battery with an A-F confidence grade for high-stakes deliverables. Use

- after EVERY analysis. Trigger on "sanity check this", "validate my analysis", "can we trust

- this", "verify the findings", or surprising results.

- # Skill: Triangulation / Sanity Check

-

- ## Purpose

- Cross-reference analytical findings against multiple data sources, external benchmarks, and common sense to catch errors before they become bad decisions.

-

- ## When to Use

- Apply this skill after every analysis, before presenting findings to stakeholders, and whenever a result seems surprising. If a finding would change a decision, it MUST be triangulated first.

-

- **Two modes:**

- - **Default mode** (the four checks below): fast, runs on every analysis.

- - **Deep mode** (the scored 4-layer battery further down): runs when the user asks

- ("run the full validation battery", "how confident should I be in these results?")

- or before a high-stakes deliverable such as an exec deck or a finding that will

- drive a major decision. Deep mode produces a numeric confidence score and an

- A-F grade that downstream exports display.

-

- ## Output Style Guidance

-

- Match depth to stakes. A quick sanity check leads with the verdict and the handful of checks that decided it. A pre-presentation validation runs all four checks in the compact table format below with a confidence rating and stakeholder guidance. A high-stakes validation adds the SQL and benchmark evidence a reader would need to reproduce the verdict. In every case, include only what changes what the reader does next; don't repeat in prose what a table already shows.

-

- ## Instructions

-

- ### Triangulation Framework

-

- Every finding gets checked through four lenses — starting with the most common source of misleading results:

-

- ```

- CHECK 0: SEGMENT-FIRST → Do segment-level trends match aggregate? (Simpson's Paradox check)

- CHECK 1: INTERNAL → Do the numbers add up within the analysis?

- CHECK 2: CROSS-REFERENCE → Does another data source agree?

- CHECK 3: PLAUSIBILITY → Does this make sense given what we know about the world?

- ```

-

- **Before running ANY checks**, verify data source availability:

- - If user mentions a table/database, confirm it exists in the active dataset

- - If it doesn't exist, flag this FIRST before running validation checks

- - Check `.knowledge/datasets/{active}/manifest.yaml` or use available data tables

-

- ### Check 0: Segment-First (Mandatory)

-

- **Run this check BEFORE accepting any aggregate finding.** Simpson's Paradox is the #1 source of misleading analytical conclusions — an aggregate trend that reverses when you look at segments.

-

- **Default segments to always check** (use whichever are available in the data):

- 1. Platform / device (mobile vs. desktop vs. tablet)

- 2. User type / plan tier (free vs. paid, plan levels)

- 3. Geography / region (US vs. EU vs. APAC)

- 4. Acquisition channel (organic vs. paid vs. referral)

-

- **Quick validation approach:** Check 1-2 key segments (typically device + user type). If you have data access, run the queries. If you don't have data, flag this check as REQUIRED and provide 1-2 specific SQL queries the user should run.

-

- **Full validation approach:** Check all 4 default segments if available in data.

-

- **What you're looking for:** Does ANY segment show a trend **opposite** to the aggregate? This is Simpson's Paradox.

-

- **Process for each aggregate finding:**

- 1. State the aggregate trend (e.g., "Overall conversion increased from 3% to 4%")

- 2. Compute the same metric for 1-2 key segments (device + user type preferred)

- 3. Check: Does ANY segment show the **opposite direction**?

- - If aggregate UP, is any segment DOWN? → Paradox detected

- - If aggregate DOWN, is any segment UP? → Paradox detected

- - All segments match aggregate direction? → No paradox, trend is real

-

- **If opposite trends detected:**

- ```

- ⚠️ SIMPSON'S PARADOX DETECTED

-

- The aggregate [metric] shows [aggregate trend].

- However, [segment value] shows the OPPOSITE: [segment trend].

-

- The aggregate is misleading because [explanation — e.g., the growing

- segment masks the declining segment].

-

- Action: Report segment-level findings instead of aggregate. Flag this

- prominently in the Executive Summary.

- ```

-

- **If no opposite trends detected:**

- Record: "Segment-first check PASSED — aggregate trends are consistent with [dimensions checked] segment-level trends."

-

- **Include in the Validation Report:**

- ```markdown

- | Check | Result | Detail |

- |-------|--------|--------|

- | Segment-first (platform) | PASS/FAIL | [specifics] |

- | Segment-first (user type) | PASS/FAIL | [specifics] |

- ```

-

- This check typically takes 2-3 queries and prevents the most common analytical error. Never skip it.

-

- ### Check 1: Internal Consistency

-

- **Arithmetic checks:**

- - Do percentages sum to 100% (±1% for rounding)?

- - Does the sum of segments equal the total?

- - Do period-over-period changes recalculate correctly?

- - Is revenue = price × quantity × (1 - discount)?

-

- **Logical checks:**

- - Is the funnel monotonically decreasing? (more visitors than signups than purchases)

- - Are rates between 0% and 100%?

- - Are dates in chronological order?

- - Is the denominator stable, or did it change? (a "drop" in conversion might be a spike in traffic)

-

- ```python

- def check_internal_consistency(findings):

- checks = []

- for finding in findings:

- # Segment sum check

- if finding.has_segments:

- segment_sum = sum(finding.segment_values)

- total = finding.total_value

- if abs(segment_sum - total) / total > 0.02:

- checks.append(("FAIL", f"Segments sum to {segment_sum}, but total is {total}"))

-

- # Rate bounds check

- if finding.is_rate:

- if finding.value < 0 or finding.value > 1:

- checks.append(("FAIL", f"{finding.name} = {finding.value} is outside [0,1]"))

-

- # Funnel monotonicity

- if finding.is_funnel:

- for i in range(1, len(finding.steps)):

- if finding.steps[i] > finding.steps[i-1]:

- checks.append(("FAIL", f"Funnel step {i} ({finding.steps[i]}) > step {i-1} ({finding.steps[i-1]})"))

- return checks

- ```

-

- ### Check 2: Cross-Reference

-

- **Calculate the same thing two different ways:**

- - Revenue from orders table vs. revenue from payments table

- - User count from events table vs. user count from users table

- - Conversion rate from funnel query vs. conversion rate from separate numerator/denominator queries

-

- **Compare against related metrics:**

- - If conversion rate went up, did absolute conversions also go up? (denominator check)

- - If revenue grew, did order count or average order value grow? (which component?)

- - If churn increased, did new user signups decrease? (is it a cohort effect?)

-

- **Time-based cross-reference:**

- - Does the daily data sum to the weekly data?

- - Does the weekly data sum to the monthly data?

- - Are there timezone-related discrepancies?

-

- ### Check 3: External Plausibility

-

- **Order-of-magnitude checks for common metrics:**

-

- | Metric | Typical Range | If Outside Range |

- |--------|--------------|------------------|

- | SaaS conversion (free → paid) | 2-5% | >10% suspicious; <1% possible but check |

- | E-commerce conversion | 1-4% | >8% check for bot filtering issues |

- | Email open rate | 15-30% | >50% check for pixel tracking issues |

- | Click-through rate (email) | 2-5% | >15% suspicious |

- | Monthly churn (SaaS) | 3-8% | <1% check for measurement window; >15% check definition |

- | DAU/MAU ratio | 10-25% (B2B SaaS) | >40% unusual for non-social products |

- | NPS | 20-50 (good SaaS) | >70 or <-10 check sample methodology |

- | Mobile share of traffic | 50-70% (consumer) | <30% check if app traffic is included |

- | Bounce rate | 40-60% | <20% check for double-firing analytics |

- | Average session duration | 2-5 min (consumer) | >15 min check for session timeout definition |

-

- **Benchmark sources:**

- - Mixpanel Product Benchmarks Report (annual, free)

- - Lenny Rachitsky's benchmarks (newsletter, SaaS-focused)

- - First Round's State of Startups (annual survey)

- - Recurly churn benchmarks (subscription businesses)

- - Statista (general industry benchmarks)

- - SimilarWeb (traffic benchmarks)

-

- ### Common Analytical Errors to Check

-

- #### Simpson's Paradox

- **What it is:** A trend that appears in several groups reverses when the groups are combined.

- **How to check:** Always look at both the aggregate AND the segmented view. If they disagree, investigate the segment sizes.

- **Example:** Overall conversion went up, but conversion went DOWN in every segment. Cause: the highest-converting segment grew as a share of traffic.

-

- #### Survivorship Bias

- **What it is:** Analyzing only the data that "survived" a selection process, ignoring what was filtered out.

- **How to check:** Ask "what's NOT in this dataset?" Check if churned users, failed transactions, or deleted accounts are excluded.

- **Example:** "Average revenue per user increased!" — but only because low-spending users churned, leaving only high-spenders.

-

- #### Time Zone Issues

- **What it is:** Events counted in different time zones create artificial spikes or dips at day boundaries.

- **How to check:** Look at hourly distributions. If there's a spike at midnight UTC, check if events are being bucketed incorrectly.

- **Example:** "Signups spike at midnight" — because the mobile app reports in local time but the backend stores in UTC.

-

- #### Incomplete Data Windows

- **What it is:** Comparing periods where one period has incomplete data (e.g., comparing full January to partial February).

- **How to check:** Always verify the data range is complete. Check the latest event date. Compare like-for-like periods.

- **Example:** "February revenue dropped 40%!" — but it's February 15th, and you're comparing to all of January.

-

- #### Denominator Changes

- **What it is:** A rate changes not because the behavior changed, but because the pool being measured changed.

- **How to check:** Always look at numerator and denominator separately before interpreting the ratio.

- **Example:** "Conversion rate doubled!" — because a marketing campaign brought in low-intent traffic (denominator spiked, numerator stayed flat, then the campaign ended and denominator dropped back).

-

- #### Correlation ≠ Causation

- **What it is:** Two metrics move together, but one doesn't cause the other.

- **How to check:** Look for confounders. Ask "what else changed at the same time?" Check if the relationship holds across different segments.

- **Example:** "Users who use Feature X have 2x retention" — but maybe power users both use Feature X AND have high retention because they're power users, not because Feature X causes retention.

-

- ### Output Format: Validation Report

-

- **For quick validations** (user asks "is this OK?" or "sanity check this"):

- ```markdown

- ## Validation: [Finding Name]

-

- **Verdict:** [VALIDATED / NEEDS INVESTIGATION / REJECTED]

-

- **Confidence:** [HIGH / MEDIUM / LOW]

-

- **Key Checks:**

- - ✅/⚠️/❌ Segment-first: [1-2 sentence summary]

- - ✅/⚠️/❌ Internal consistency: [1-2 sentence summary]

- - ✅/⚠️/❌ Cross-reference: [1-2 sentence summary]

- - ✅/⚠️/❌ Plausibility: [1-2 sentence summary]

-

- **Bottom line:** [2-3 sentences: can they proceed, what caveats, what to check next]

- ```

-

- **For full validations** (findings going to stakeholders):

- ```markdown

- # Validation Report: [Analysis Name]

- ## Date: [YYYY-MM-DD]

-

- ### Overall Confidence: [HIGH / MEDIUM / LOW]

-

- ### Finding-by-Finding Validation

-

- #### Finding 1: [statement]

- | Check | Result | Detail |

- |-------|--------|--------|

- | Segment-first | PASS/WARN/FAIL | [specifics] |

- | Internal consistency | PASS/WARN/FAIL | [specifics] |

- | Cross-reference | PASS/WARN/FAIL | [specifics] |

- | External plausibility | PASS/WARN/FAIL | [specifics] |

- | **Confidence** | **HIGH/MEDIUM/LOW** | [summary justification] |

-

- [Repeat for each finding]

-

- ### Caveats for Stakeholders

- [What should be mentioned when presenting these findings]

-

- ### Recommended Additional Validation

- [What would increase confidence — more data, different analysis, A/B test]

- ```

-

- ## Deep Mode: The Scored Validation Battery

-

- Run this when requested or before high-stakes deliverables. It replaces the

- default checks' HIGH/MEDIUM/LOW verdict with a computed confidence score and an

- A-F grade.

-

- > **Deterministic computation.** Run every numeric check by computing it with a

- > small Python script over the data (pandas or duckdb); never eyeball or estimate

- > a check result. For the Layer 1 structural battery, the data-quality-check

- > skill bundles a tested validator script (`run_structural_checks`); reuse it

- > rather than hand-rolling those checks. You do the validation work inline here;

- > there is no separate validation agent.

-

- Run all 4 layers in sequence. For each layer, assign a score (0-15 points) and a

- severity (PASS/WARNING/BLOCKER).

-

- ### Layer 1: Structural Validation (0-15 points)

-

- Check data structure integrity:

-

- 1. **Schema validation:** expected columns present, data types match, no unexpected columns

- 2. **Primary key integrity:** key columns unique, no nulls in key fields

- 3. **Completeness:** null rate for critical columns. Thresholds: <5% = PASS, 5-20% = WARNING, >20% = BLOCKER

- 4. **Row count adequacy:** >= 1,000 rows = PASS; 100-1,000 = WARNING; <100 = BLOCKER

- 5. **Referential integrity** (multi-table only): foreign keys reference valid primary keys, no orphaned records

-

- **Scoring:** 15 = all PASS; 10 = minor warnings (5-10% null rate); 5 = significant

- warnings (10-20% null rate); 0 = BLOCKER (>20% nulls or <100 rows).

-

- **If any BLOCKER is detected, HALT the analysis and assign an F grade.**

-

- ### Layer 2: Logical Validation (0-15 points)

-

- Check calculation consistency:

-

- 1. **Aggregation consistency:** segments sum to the total within 1% tolerance

- (`|sum(parts) - total| / total < 0.01`)

- 2. **Trend continuity** (time series): unexplained gaps, suspicious zero-value days,

- structural breaks (sudden jumps >200%)

- 3. **Segment exhaustiveness:** segments cover the population. Coverage >= 95% = PASS,

- 90-95% = WARNING, <90% = BLOCKER

- 4. **Temporal consistency** (period comparisons): comparison periods have equal date

- coverage; check for known tracking outages

-

- **Definitional flaws:** when compared metrics use different definitions

- (apples-to-oranges, e.g. "new user first purchase rate" vs "returning user repeat

- purchase rate" instead of both measuring "purchase rate within 30 days of cohort

- entry"): mark BLOCKER, explain why the comparison is invalid, propose a corrected

- definition, provide SQL to implement it, and show the corrected finding.

-

- **Scoring:** 15 = all PASS, no definitional flaws; 10 = minor aggregation

- mismatches (<2%); 5 = temporal consistency not validated; 0 = BLOCKER

- (definitional flaw, >5% aggregation mismatch, or <90% segment coverage).

-

- ### Layer 3: Business Rules Validation (0-15 points)

-

- Check domain plausibility:

-

- 1. **Range validation:** values within plausible bounds (rates 0-100%, revenue positive)

- 2. **Rate validation:** numerator <= denominator, denominator > 0, result in [0, 1]

- 3. **YoY change plausibility:** <500% = PASS, 500-1000% = WARNING, >1000% = BLOCKER.

- When flagging, ALWAYS cite the specific threshold: not "this change seems too

- large" but "this 708% relative change exceeds the 500% plausibility threshold;

- changes this extreme typically indicate data quality issues, definition errors,

- or incomplete observation windows."

- 4. **Domain expectations:** does the finding align with known behavioral patterns

- (e.g. returning users usually retain better than new users; mobile usually

- converts below desktop)?

-

- **When a finding contradicts domain expectations**, treat it as a strong signal of

- a data quality issue: mark WARNING or BLOCKER, then query the data for the common

- bugs (classification inconsistencies where one entity carries multiple labels,

- temporal coverage gaps, mix shift artifacts, per-segment definition errors).

- Provide the corrected analysis if a bug is found; if the finding survives the

- investigation, explain the counterintuitive pattern with supporting evidence.

-

- **Scoring:** 15 = plausible and aligned with domain expectations; 10 = plausible

- but counterintuitive (explained); 5 = large YoY change (>200%) without context;

- 0 = BLOCKER (values outside valid ranges, or a contradicting finding where

- investigation confirms a data bug).

-

- ### Layer 4: Simpson's Paradox Check (0-15 points)

-

- Same mechanics as Check 0 in default mode, scored:

-

- 1. Identify the key aggregate finding

- 2. Segment by likely confounds: geographic, temporal (cohort, day-of-week),

- behavioral (new vs returning, value tier), traffic source

- 3. Check whether the finding holds WITHIN each segment

- 4. After running the queries, include a Paradox Assessment: the aggregate finding,

- each segment-level finding, a verdict (no paradox / paradox detected / paradox

- risk), and why it matters

-

- **Scoring:** 15 = check performed, no paradox; 10 = minor paradox (1-2 segments

- reverse); 5 = check not performed but risk noted; 0 = BLOCKER (paradox confirmed,

- aggregate reverses in the majority of segments).

-

- **If a paradox is confirmed, the aggregate finding is misleading: reframe the

- story around the segment-level pattern and the mix shift.**

-

- ### Bonus Layers (when available)

-

- Both bonus layers are OPTIONAL. When a bonus is unavailable, it scores nothing

- AND its points are excluded from the Applicable Max (see the formula below), so

- an analysis is never penalized for a check that could not exist.

-

- **Cross-verification (0-10 points, optional):** applies only when an independent

- recomputation of the headline numbers exists, recorded to

- `working/cross_verification_*.yaml`. Nothing produces that file automatically:

- it exists only when this analysis (or you, during deep mode) recomputed the

- headline numbers through a second engine or independent method and saved the

- comparison there. If no such file exists, mark the row "N/A (not produced)" and

- exclude the 10 points from the max. When results exist: 10 = all applicable

- checks PASS; 7 = only boundary checks ran and passed; 4 = any WARN; 0 = any

- FAIL. A FAIL on any boundary check (negative counts, rates outside bounds,

- impossible dates) is an automatic BLOCKER.

-

- **Reproducibility (0-5 points, optional):** same query run 3x produces identical

- results (or within tolerance for live warehouses): 5 = exact; 3 = minor variance

- within tolerance; 0 = significant variance. If the check was not run at all,

- mark the row "N/A (not run)" and exclude the 5 points from the max.

-

- ### Confidence Scoring and Grade

-

- Compute the score with this formula, deterministically, in a small Python snippet

- that sums the layer scores and bonuses (never estimated in your head), so the

- badge is identical everywhere it appears:

-

- ```

- Raw Score = (Layer1 + Layer2 + Layer3 + Layer4) + Sample Size Bonus

- + Cross-Verification (if available) + Reproducibility (if run)

- Sample Size Bonus: n >= 10,000: +10 | n >= 1,000: +5 | n < 1,000: +0

- Applicable Max = 70 base (four 15-point layers + 10-point sample size bonus)

- + 10 if cross-verification results exist

- + 5 if the reproducibility check was run

- Normalized Score = (Raw Score / Applicable Max) * 100

- ```

-

- Per-bonus exclusion is exact, not approximate. Examples: reproducibility run but

- no cross-verification file -> max 75 (a raw 70 scores 93, grade A); both bonuses

- available -> max 85; neither -> max 70. Two runs of this battery on the same

- analysis must land on the same max.

-

- **Grade assignment:**

- - A: 90-100 (High Confidence)

- - B: 75-89 (Moderate-High Confidence)

- - C: 60-74 (Moderate Confidence)

- - D: 50-59 (Low Confidence)

- - F: 0-49 (Critical Issues, DO NOT PRESENT)

-

- **BLOCKER override:** if ANY layer has a BLOCKER, assign F (0/100) regardless of

- other scores.

-

- **Confidence badge format** (this exact format is consumed by the export,

- notion-export, google-doc-export, and google-slides-export skills in data stamps

- and confidence gates; keep it stable):

- - "A (94/100)" for clean validations

- - "B (78/100), 1 BLOCKER, 2 warnings" when issues are present

- - "F (0/100), CRITICAL: Data source mismatch" when blocked

-

- ### Deep Mode Report

-

- Every deep-mode report MUST include executable SQL for (1) replicating the

- validation checks, (2) investigating any BLOCKER or WARNING, and (3) implementing

- recommended fixes. Present:

-

- ```markdown

- # Validation Report: [Analysis Title]

-

- **Confidence Score:** [Raw]/[Max] = [Normalized]/100 (Grade: [A-F])

- **Verdict:** [can present / needs caveats / DO NOT PRESENT]

-

- | Factor | Score | Max | Status | Detail |

- |--------|-------|-----|--------|--------|

- | Structural | X | 15 | PASS/WARN/BLOCKER | [reason] |

- | Logical | X | 15 | ... | ... |

- | Business Rules | X | 15 | ... | ... |

- | Simpson's Paradox | X | 15 | ... | ... |

- | Sample Size | X | 10 | ... | [n rows] |

- | Cross-Verification | X | 10 | ... | [method + result] |

- | Reproducibility | X | 5 | ... | [variance detail] |

-

- [Per-layer findings: what passed, what was flagged, the SQL to reproduce]

-

- **Before presenting to stakeholders:** [numbered BLOCKER/WARNING fixes]

- **What you can say / cannot say yet:** [safe claims vs claims needing more validation]

- **Required caveats:** [list]

- ```

-

- ### Deep Mode Edge Cases

-

- - **Empty data:** structural validation catches it; BLOCKER before other layers run

- - **Single-table analysis:** skip referential integrity checks

- - **No time dimension:** skip temporal consistency and trend continuity

- - **No segments available:** skip the Simpson's check, note as WARNING

- - **Insufficient data access:** if you cannot query the underlying data, note which

- checks were skipped and cap the grade at C

- ## After Validation Passes

- When a finding survives validation (default-mode all-clear, or deep-mode grade B

- or better), curate the analysis's final SQL into

- `.knowledge/query-archaeology/curated/` following the archaeology skill's writer

- convention (CK-NNN cookbook format), so the proven query gets found and reused

- next session instead of rewritten from scratch.

- ## Examples

- ### Example 1: Catching a Denominator Change

- **Finding:** "Mobile conversion rate increased from 2.1% to 3.4% in March"

- **Cross-reference check:** Look at numerator and denominator separately.

- - Mobile purchases: 1,050 → 1,020 (actually DOWN slightly)

- - Mobile visitors: 50,000 → 30,000 (DOWN significantly — a paid campaign ended)

- **Verdict:** WARN — Conversion rate "improved" only because low-intent paid traffic disappeared. Actual purchases decreased. The finding is technically true but deeply misleading.

- ### Example 2: Catching Simpson's Paradox

- **Finding:** "Overall activation rate improved from 45% to 48% this quarter"

- **Segment check:**

- - Enterprise: 62% → 58% (down)

- - SMB: 41% → 38% (down)

- - Free tier: 32% → 29% (down)

- **But:** Enterprise share of signups grew from 15% to 35%.

- **Verdict:** FAIL — Every segment got worse. The "improvement" is entirely due to mix shift toward higher-activating enterprise segment. The actual product experience degraded.

- ### Example 3: Plausibility Catch

- **Finding:** "Email campaign achieved 72% open rate"

- **External plausibility:** Industry average is 15-30%. 72% is extreme.

- **Investigation:** Apple Mail Privacy Protection pre-fetches email images, inflating open rates for Apple Mail users. 68% of the list uses Apple Mail.

- **Verdict:** WARN — True open rate is likely 25-35% after adjusting for Apple privacy pre-fetching. Report adjusted number alongside raw number.

- ## Anti-Patterns

- 1. **Never present a surprising finding without triangulating it** — if it's surprising, it's either a breakthrough or an error. Check which one.

- 2. **Never skip the denominator check** — more analytical errors come from denominator changes than any other cause

- 3. **Never rely on a single data source** — if the finding matters, verify it from a different angle

- 4. **Never ignore external benchmarks** — if your metric is 10x the industry average, that's a red flag, not a celebration

- 5. **Never say "the data shows" without saying "we checked by..."** — triangulation is what separates analysis from data regurgitation

- 6. **Never treat WARN findings as PASS** — a warning means the finding needs a caveat when presented to stakeholders