Marketing Campaign Validator

Web app to audit marketing campaigns from operational workbooks and folder structures (e.g., OneDrive/SharePoint). It standardizes region/brand paths, validates file presence & month/year headers, checks calculated vs. expected ratios, reconciles line-items across sources, and produces a monthly Word report with findings— all orchestrated from a single screen with saved progress.

What it does

For each selected region and month, the validator walks the folder tree, locates the key inputs (campaign workbook, operations/support file, benchmark matrix), and runs reproducible checks:

• Existence & naming: verifies Soportes/ contains all required inputs and that multi-sheet headers include the correct month/year in specific cells.

• Monthly benchmarks: opens the historical benchmark KPI sheet (e.g., prior-month index), cross-checks against the campaign workbook, and flags deviations.

• Critical ratios: reads numerator and denominator from parameterized cells and confirms the computed ratio matches an expected value (formula integrity).

• Line-item reconciliation: compares counts/values between the campaign ledger and the ops report, using tolerant matching (fuzzy) to absorb label variations.

• Temporal validations: ensures derived fields respect period rules (e.g., days in month × 12 must equal the expected total).
  Simple example: if a campaign ran 3 days and the rule fixes 12 "slots" per day, the system expects 36; if the sheet shows 40, it's flagged.

Results are written to a .docx "Inconsistencies" report, with one section per check, ✅/❌ markers, and row-level details—ready to share with the team.

How it works

Regions/brands are abstracted through a configuration map (codes, relative paths, per-check cells/ranges). The app auto-detects the local base path on Windows/macOS, persists progress in progress_data.json, and exposes discrete processes (buttons) so users can run exactly what they need. Under the hood it uses pandas/openpyxl for cell/range reads, rapidfuzz/difflib for normalization and string matching, and python-docx to build the report with a consistent corporate style.

Technical highlights

• OS-aware path discovery (OneDrive/SharePoint sync) and month-level folder validation.

• Multi-sheet header checks (month/year) with robust normalization (accents, case, spacing).

• Parameterized cells per region for ratios (numerator/denominator/expected) and count ranges for reconciliations.

• Fuzzy matching (configurable threshold) to align item names across heterogeneous sources.

• Reproducible reporting: each process appends/updates its own section in the same document.

Stack (languages and libraries)

• Python 3.x · Flask (UI & orchestration)

• pandas, NumPy (tabular transforms)

• openpyxl (precise Excel reads: cells & ranges)

• python-docx (Word report generation)

• rapidfuzz, difflib, re, unicodedata (normalization & string matching)

• Utilities: glob, platform, json, warnings

Why it's useful for Marketing

It cuts QA time, prevents manual errors, and creates traceability for each verification. Teams get a single source of truth for proper naming, consistent formulas, and reconciled counts, with exportable evidence.

Where it fits / How to adapt

• Agencies & in-house teams: control discounts, caps, pricing, verify per-channel KPIs, and check plan vs. execution consistency.

• Retail / eCommerce: reconcile promotions/coupons between rate cards and sales reports.

• SaaS / B2B: audit funnel stages (MQL/SQL/Won) against campaign sheets and CRM exports.

• Marketplaces: validate bonuses/slots per seller and flight periods.

The region/brand configuration and cell/range "hooks" let you bring the same logic to any company working with operational Excel files that needs formal, repeatable campaign QA—without exposing sensitive data.