Turn messy data into clean insights, automated reports, and decision-ready dashboards. Platform-agnostic — works with CSV, Excel, Google Sheets, or any tabular format.
Before touching any data, assess what you have.
yaml
data_intake:
source: "" # file path, URL, API, database, manual entry
format: "" # CSV, XLSX, TSV, JSON, clipboard paste
rows: 0
columns: 0
file_size: ""
encoding: "" # UTF-8, Latin-1, Windows-1252, etc.
delimiter: "" # comma, tab, pipe, semicolon
health_score: # rate each 0-4, total /20
completeness: 0 # 4=<1% missing, 3=<5%, 2=<15%, 1=<30%, 0=>30%
consistency: 0 # 4=uniform types, 3=minor mixed, 2=significant mixed, 1=chaotic, 0=unusable
accuracy: 0 # 4=verified, 3=plausible, 2=some outliers, 1=many errors, 0=untrustworthy
freshness: 0 # 4=real-time, 3=<24h, 2=<7d, 1=<30d, 0=stale/unknown
structure: 0 # 4=tidy (1 row=1 obs), 3=minor reshaping, 2=pivot needed, 1=multi-header, 0=freeform
issues_found: [] # list every problem before fixing anything
text
START
│
├─ Headers → Normalize (lowercase, snake_case, no spaces/special chars)
│
├─ Duplicates?
│ ├─ Exact duplicates → Remove, keep first
│ ├─ Near-duplicates → Flag for review (fuzzy match on name + address)
│ └─ Intentional duplicates → Leave (e.g., multiple orders same customer)
│
├─ Missing Values?
│ ├─ <5% of column → Fill (mean for numeric, mode for categorical, forward-fill for time series)
│ ├─ 5-30% → Flag + fill with "UNKNOWN" or interpolate with justification
│ ├─ >30% → Consider dropping column or flagging as unreliable
│ └─ Entire row missing key fields → Remove with log
│
├─ Data Types?
│ ├─ Dates as text → Parse to date (try multiple formats, log failures)
│ ├─ Numbers as text → Strip currency symbols, commas, whitespace, convert
│ ├─ IDs/zips with leading zeros → Keep as text (NEVER convert to number)
│ ├─ Phone numbers → Text, standardize format
│ ├─ Mixed types in column → Split or coerce with error log
│ └─ Boolean variants → Map (Yes/No/True/False/1/0/Y/N → consistent)
│
├─ Outliers?
│ ├─ Calculate IQR: Q1 - 1.5×IQR to Q3 + 1.5×IQR
│ ├─ Business logic check (negative revenue? age 200? date in 2099?)
│ ├─ Decide: fix (typo), cap (winsorize), remove, or keep with flag
│ └─ ALWAYS log which outliers were modified and why
│
├─ Standardization?
│ ├─ Text case → Consistent (Title Case for names, UPPER for codes)
│ ├─ Whitespace → Trim leading/trailing, collapse internal
│ ├─ Categories → Map variants ("US"/"USA"/"United States" → "US")
│ ├─ Dates → ISO 8601 (YYYY-MM-DD) internally
│ ├─ Currency → Consistent symbol placement, decimal precision
│ └─ Phone/email → Validate format
│
└─ Structural Issues?
├─ Multi-header rows → Flatten to single header
├─ Merged cells → Unmerge + fill down
├─ Pivot/crosstab → Unpivot to tidy format (1 row = 1 observation)
├─ Multiple tables in one sheet → Split to separate sheets/files
└─ Metadata rows (totals, notes) → Separate from data rows
yaml
cleaning_log:
date: "YYYY-MM-DD"
source_file: ""
rows_before: 0
rows_after: 0
actions:
- action: "removed exact duplicates"
rows_affected: 0
key_columns: ["email"]
- action: "filled missing values"
column: "state"
method: "mode"
values_filled: 0
- action: "removed outliers"
column: "revenue"
criteria: "negative values"
rows_removed: 0
- action: "standardized dates"
column: "order_date"
from_format: "MM/DD/YYYY"
to_format: "YYYY-MM-DD"
parse_failures: 0
notes: ""
#OperationWhen to UseExample1FilterSubset rows by conditionOrders > $1000, Date after 2024-01-012SortOrder by column(s)Revenue descending, then date ascending3Group + AggregateSummarize by categoryTotal revenue by region, avg order by customer4PivotRows → columnsMonthly columns from date rows5Unpivot/MeltColumns → rowsMonth columns back to date rows6Join/MergeCombine datasetsCustomer data + order data on customer_id7DeduplicateRemove redundancyKeep latest record per customer8DeriveCalculate new columnsprofit = revenue - cost, age = today - birthdate9SplitOne column → many"John Smith" → first_name, last_name10ConcatenateMany columns → onecity + state + zip → full_address11Lookup/MapEnrich with reference datastate_code → state_name, product_id → category12WindowRunning calculations7-day moving average, rank within group, running total
text
Which join do you need?
│
├─ Need ALL rows from left table → LEFT JOIN
│ (customers who may or may not have orders)
│
├─ Need ONLY matching rows → INNER JOIN
│ (only customers WITH orders)
│
├─ Need ALL rows from both → FULL OUTER JOIN
│ (reconciliation: find mismatches)
│
├─ Need everything NOT in other table → LEFT JOIN + WHERE right IS NULL
│ (customers who NEVER ordered)
│
└─ Need every combination → CROSS JOIN (rare, use carefully)
(all products × all stores for pricing matrix)
⚠️ ALWAYS check join results:
- Row count: did it explode? (many-to-many join)
- Row count: did it shrink? (keys not matching)
- NULL columns: expected from outer join, unexpected = key mismatch
TaskExcelGoogle SheetsPython (pandas)LookupVLOOKUP, XLOOKUP``VLOOKUP, XLOOKUP``df.merge(), df.map()Conditional sumSUMIFS``SUMIFS``df.groupby().sum()Conditional countCOUNTIFS``COUNTIFS``df.groupby().count()Text splitTEXTSPLIT, LEFT/MID/RIGHT``SPLIT``df.str.split()Date diffDATEDIF, mathDATEDIF``(df.col2 - df.col1).dt.daysRunning totalSUM($A$1:A1)``SUM($A$1:A1)``df.cumsum()RankRANK.EQ``RANK``df.rank()Percent of total=A1/SUM($A:$A)``=A1/SUM($A:$A)``df.col / df.col.sum()Remove duplicatesData → Remove DuplicatesData → Remove Duplicatesdf.drop_duplicates()PivotPivot TablePivot Tabledf.pivot_table()
Pick the analysis that matches the question:
Descriptive (What happened?)
Diagnostic (Why did it happen?)
Predictive (What might happen?)
Prescriptive (What should we do?)
Every finding MUST follow this structure:
text
INSIGHT: [What you found — one sentence]
EVIDENCE: [The specific numbers]
SO WHAT: [Why it matters to the business]
ACTION: [What to do about it]
CONFIDENCE: [High/Medium/Low + why]
Example:
text
INSIGHT: Customer acquisition cost increased 43% in Q3 vs Q2
EVIDENCE: CAC went from $47 to $67, driven by paid search CPC increase (+62%)
SO WHAT: At current LTV of $180, payback period extended from 3.1 to 4.5 months
ACTION: Shift 30% of paid search budget to email/referral channels (CAC $12 and $23 respectively)
CONFIDENCE: High — based on complete Stripe + Google Ads data for full quarter
text
┌──────────────────────────────────────────────────┐
│ EXECUTIVE DASHBOARD — [Period] │
│ │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐│
│ │ Revenue │ │ Customers│ │ Margin │ │ Growth ││
│ │ $XXX,XXX │ │ X,XXX │ │ XX.X% │ │ +XX.X% ││
│ │ ▲ +X.X% │ │ ▲ +XXX │ │ ▼ -X.X% │ │ vs LY ││
│ └─────────┘ └─────────┘ └─────────┘ └─────────┘│
│ │
│ [Trend chart — key metric over 12 months] │
│ │
│ TOP 3 INSIGHTS: │
│ 1. [Insight + action] │
│ 2. [Insight + action] │
│ 3. [Insight + action] │
│ │
│ ┌──────────────────┐ ┌──────────────────┐ │
│ │ By Segment │ │ By Channel │ │
│ │ (table or chart) │ │ (table or chart) │ │
│ └──────────────────┘ └──────────────────┘ │
└──────────────────────────────────────────────────┘
ElementRuleExampleBig numbersAbbreviate with 1 decimal$1.2M, 14.3KPercentages1 decimal, always show direction▲ +12.3%, ▼ -4.1%Currency2 decimals for <$1000, 0 for larger$47.50, $12,000DatesConsistent format throughoutJan 2025, not 01/2025ComparisonAlways include baseline$120K (+15% vs LY)RAG statusUse color + text🟢 On Track, 🟡 At Risk, 🔴 BehindSparklinesShow direction at a glance▁▂▃▅▇ (trending up)
text
What are you showing?
│
├─ Change over time → LINE chart (≤5 series) or AREA (stacked composition)
│
├─ Comparison across categories → BAR chart (horizontal for long labels)
│
├─ Part of whole → PIE (≤5 slices) or STACKED BAR (>5 or over time)
│
├─ Distribution → HISTOGRAM or BOX PLOT
│
├─ Relationship between 2 variables → SCATTER PLOT
│
├─ Geographic → MAP (if location data exists)
│
├─ Ranked list → HORIZONTAL BAR sorted descending
│
└─ Single KPI → BIG NUMBER with trend indicator
⚠️ NEVER use:
- 3D charts (distorts perception)
- Dual Y-axes (misleads readers)
- Pie with >7 slices (use bar instead)
- Rainbow colors (use 2-3 colors max + grey)
yaml
recurring_report:
name: ""
frequency: "" # daily, weekly, monthly, quarterly
owner: ""
recipients: []
data_sources:
- source: "" # file path, API, database
refresh: "" # how data gets updated
format: ""
processing_steps:
- step: "load data"
tool: "" # Python, Excel macro, Google Apps Script
- step: "clean"
rules: [] # reference cleaning protocol
- step: "transform"
operations: []
- step: "analyze"
calculations: []
- step: "format"
template: "" # dashboard template to populate
- step: "deliver"
method: "" # email, Slack, shared drive, API push
quality_checks:
- "Row count within expected range (±20%)"
- "No NULL values in required columns"
- "Totals reconcile with source system"
- "Date range matches expected period"
- "Key metrics pass sanity check (no 10x jumps without explanation)"
error_handling:
- trigger: "data source unavailable"
action: "use cached last-good version + alert"
- trigger: "row count outside range"
action: "pause + flag for human review"
- trigger: "metric exceeds 3× historical std dev"
action: "include anomaly callout in report"
python
"""
Recurring report: [NAME]
Schedule: [FREQUENCY]
"""
import pandas as pd
from pathlib import Path
from datetime import datetime
# --- Config ---
INPUT_PATH = Path("data/raw/")
OUTPUT_PATH = Path("data/reports/")
REPORT_DATE = datetime.now().strftime("%Y-%m-%d")
# --- Load ---
df = pd.read_csv(INPUT_PATH / "source.csv", parse_dates=["date"])
# --- Clean ---
df = df.drop_duplicates()
df = df.dropna(subset=["required_column"])
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
# --- Transform ---
summary = (
df.groupby("category")
.agg(
total=("amount", "sum"),
count=("id", "count"),
avg=("amount", "mean"),
)
.sort_values("total", ascending=False)
.reset_index()
)
# --- Quality Check ---
assert len(df) > 0, "Empty dataset!"
assert df["amount"].isna().sum() / len(df) < 0.05, "Too many missing amounts"
# --- Output ---
output_file = OUTPUT_PATH / f"report-{REPORT_DATE}.csv"
summary.to_csv(output_file, index=False)
print(f"✅ Report saved: {output_file} | {len(summary)} rows")
text
Sheet structure:
├── Assumptions → All editable inputs in ONE place (highlighted cells)
├── Revenue → Formulas reference Assumptions
├── Costs → Formulas reference Assumptions
├── P&L → Pulls from Revenue + Costs
├── Cash Flow → Derived from P&L + working capital
├── Balance Sheet → Derived from Cash Flow
├── Scenarios → Best/Base/Worst toggle that feeds Assumptions
└── Dashboard → Charts + KPIs pulling from P&L/Cash Flow
Rules:
- Inputs = blue font or yellow background (pick one, be consistent)
- Formulas = black font, never hardcode numbers in formula cells
- Every formula traces back to Assumptions or raw data
- No circular references
- Include version number + last-updated date
text
Required columns:
- id (auto-increment or UUID)
- name, email, phone, company
- source (how they found us)
- status (lead → contacted → qualified → proposal → won/lost)
- last_contact_date
- next_action + next_action_date
- deal_value
- notes
Derived columns:
- days_since_last_contact = TODAY() - last_contact_date
- pipeline_stage_days = TODAY() - stage_entry_date
- is_stale = days_since_last_contact > 14
Dashboard metrics:
- Pipeline value by stage
- Conversion rate stage-to-stage
- Average days in each stage
- Stale leads count (action needed)
text
Required columns:
- sku, name, category
- quantity_on_hand, reorder_point, reorder_quantity
- unit_cost, unit_price
- last_received_date, last_sold_date
- supplier
Derived columns:
- stock_value = quantity_on_hand × unit_cost
- margin = (unit_price - unit_cost) / unit_price
- days_of_supply = quantity_on_hand / avg_daily_sales
- needs_reorder = quantity_on_hand <= reorder_point
Alerts:
- 🔴 Below reorder point
- 🟡 Within 7 days of stockout (based on velocity)
- ⚪ Dead stock (no sales in 90 days)
text
Required columns:
- task_id, task_name, description
- assignee, priority (P0-P3)
- status (backlog → in_progress → review → done)
- start_date, due_date, completed_date
- estimated_hours, actual_hours
Derived columns:
- days_remaining = due_date - TODAY()
- is_overdue = due_date < TODAY() AND status != "done"
- effort_variance = actual_hours - estimated_hours
- completion_rate = done_tasks / total_tasks
Dashboard:
- Burndown chart (remaining vs time)
- Status distribution pie
- Overdue tasks list
- Team workload (tasks per assignee)
text
Structure:
- Rows: expense categories + revenue lines
- Columns: Budget | Actual | Variance | Variance %
- Group by: month or quarter
Key formulas:
- variance = actual - budget
- variance_pct = (actual - budget) / budget
- YTD_budget = SUM of months through current
- Run_rate = (YTD_actual / months_elapsed) × 12
Conditional formatting:
- Green: favorable variance (revenue over, cost under)
- Red: unfavorable variance (revenue under, cost over)
- Threshold: flag if |variance| > 10%
yaml
validation:
structural:
- "No duplicate column names"
- "No completely empty columns"
- "No completely empty rows (except intentional separators)"
- "Consistent column count across all rows"
- "Headers in row 1 (no multi-row headers without handling)"
type_integrity:
- "Date columns parse as valid dates"
- "Numeric columns contain no text (except headers)"
- "ID columns are unique where expected"
- "Email columns match basic email pattern"
- "Phone columns are consistent format"
business_rules:
- "Revenue >= 0 (or explain negative = refund)"
- "Dates within expected range (not in future for historical data)"
- "Percentages between 0-100 (or 0-1, consistently)"
- "Status values match allowed list"
- "Foreign keys exist in reference table"
completeness:
- "Required columns have <5% missing"
- "No orphan records (child without parent)"
- "Date ranges are continuous (no gaps in daily data)"
DimensionWeightScore 0-4CriteriaCompleteness25%% of non-null values in required fieldsUniqueness15%% of rows with valid unique keysConsistency20%% of values matching expected format/typeAccuracy20%% passing business rule validationTimeliness10%Data freshness vs expected refreshConformity10%% matching standard formats (dates, phones, emails)
Score = Σ(weight × score/4 × 100)
From → ToBest MethodWatch Out ForCSV → Excelpandas + openpyxlEncoding, date parsing, leading zerosExcel → CSVpandas or openpyxlMultiple sheets, formulas lost, merged cellsJSON → CSVpandas json_normalizeNested objects need flatteningCSV → JSONpandas to_jsonChoose records vs columns orientationExcel → Google SheetsUpload directlyMacros stripped, some formulas differGoogle Sheets → ExcelDownload as .xlsxIMPORTRANGE breaks, custom functions lostPDF table → CSVTabula, pdfplumberLayout detection, merged cells, multi-pageHTML table → CSVpandas read_htmlMultiple tables, nested tables, encoding
text
Garbled text? Try these encodings in order:
1. UTF-8 (default, handles all languages)
2. UTF-8-BOM (Windows exports often add BOM)
3. Latin-1 / ISO-8859-1 (Western European)
4. Windows-1252 (Windows "ANSI")
5. Shift-JIS (Japanese)
6. GB2312 / GBK (Chinese)
Python: pd.read_csv("file.csv", encoding="utf-8-sig")
Detection: chardet or charset-normalizer library
RowsTool Recommendation<10KAny tool (Excel, Sheets, pandas)10K-100KExcel (careful) or pandas100K-1Mpandas with chunking, or DuckDB1M-10MDuckDB, Polars, or database>10MDatabase (PostgreSQL, BigQuery)
pd.read_csv(file, usecols=["col1","col2"])pd.read_csv(file, chunksize=50000)df["status"] = df["status"].astype("category").apply()duckdb.sql("SELECT * FROM 'file.csv' WHERE x > 100")yaml
currency_rules:
- Store amount AND currency code in separate columns
- Never mix currencies in a single column without code
- Use ISO 4217 codes (USD, GBP, EUR, BTC)
- Store exchange rate and rate date used for conversion
- Keep original amount + converted amount as separate columns
- Specify: is this the rate at transaction time or current rate?
yaml
timezone_rules:
- Store timestamps in UTC internally
- Record the source timezone
- Convert to local time only for display
- "End of day" = 23:59:59 in the business timezone, not UTC
- Daylight saving transitions can cause 23h or 25h days
- Aggregate daily data in business timezone, not UTC
DimensionWeight0 (Fail)1 (Poor)2 (Fair)3 (Good)4 (Excellent)Data Cleanliness20%Raw dump, untouchedSome cleaning, inconsistentMost issues addressedClean with documented processSpotless + automated validationStructure15%Unstructured messBasic tabular, issuesProper structure, minor gapsTidy data, good schemaPerfect normalization + relationshipsAnalysis Depth20%No analysisBasic counts/sumsSegmented analysisMulti-dimensional + insightsPredictive + prescriptive + actionsVisualization15%No chartsWrong chart typesAdequate chartsClear, well-labeled chartsPublication-quality dashboardsDocumentation10%NoneMinimal notesColumn descriptionsFull data dictionary + methodologyReproducible + versionedAutomation10%Manual everythingSome formulasTemplate-basedScripted pipelineFully automated + monitoredAccuracy10%UnverifiedSpot-checkedSample-validatedCross-referenced with sourceReconciled + audit trail
Grading: 90+ = Production analytics, 75-89 = Solid work, 60-74 = Needs improvement, <60 = Redo
text
"Clean this CSV" → Run Phase 2 cleaning protocol, output clean file + log
"Analyze this data" → Phase 1 assessment → Phase 4 analysis → insights
"Build a dashboard" → Phase 5 template → populate with data
"Convert this to Excel" → Phase 9 conversion with quality preservation
"Find duplicates" → Dedup analysis with exact + fuzzy matching
"What's wrong with this data?" → Phase 1 health check + Phase 8 validation
"Create a monthly report" → Phase 6 automation template
"Compare these two files" → Join + variance analysis
"Summarize by category" → Group + aggregate + Pareto analysis
"Make this data tidy" → Unpivot/reshape to 1-row-per-observation
"Set up a budget tracker" → Phase 7 Pattern 5 template
"Profile this dataset" → Full Phase 1 + Phase 8 quality score
afrexai-data-analyst — Advanced analytical methodology (DICE framework, statistical analysis)afrexai-fpa-engine — Financial planning & analysis with spreadsheet modelsafrexai-budget-tracker — Personal/business budget managementafrexai-business-automation — Automate recurring spreadsheet workflows⚡ Level up your data game → AfrexAI Context Packs ($47) — industry-specific data templates, KPI frameworks, and reporting automations for SaaS, Fintech, Manufacturing, Ecommerce, and more.
🔗 More free skills by AfrexAI:
afrexai-data-analyst — Complete data analysis methodologyafrexai-business-automation — Workflow automation frameworksafrexai-seo-content-engine — SEO-optimized content creationafrexai-customer-success — Customer retention & health scoringafrexai-devops-engine — DevOps & platform engineering📦 Browse all: AfrexAI Storefront