Data Consolidation and Audit Platform

Web application that centralizes, cleans, and audits sales, leads, campaign, pricing, and discount data from multiple files. It unifies everything into a single source of truth, applies Excel-based business rules, and generates downloadable reports. The portfolio build uses synthetic datasets and generic logic (no sensitive data).

Technical stack (languages and libraries)

• Language: Python 3.x

• Web/API: Flask (WSGI), Jinja2, JSON endpoints

• Data/ETL: Pandas, NumPy, SQLite (sqlite3), openpyxl

• Utilities: glob, re (regex), unidecode, os/pathlib/platform (cross-OS paths)

• File delivery: BytesIO + send_file

• Frontend (minimal): HTML5, CSS, vanilla JS

What it solves

• Automatic integration of scattered Excel/CSV into a master CSV + SQLite store.

• Normalization of cycles (monthly/bimonthly/quarterly), channels (online/offline), segments, and canonized IDs.

• Editable business rules in logica_comercial.xlsx (price floors, discount caps, fixed/percent bonuses by product/segment/channel/year).

• Validations of actual vs. expected (value or %) with flagged deviations: out-of-policy discounts, spend with zero leads/sales, negative values, and period gaps.

• HTML reports with filters (period, cycle, channel, segment) and one-click export (Excel/CSV).

How it works (high level)

1. Scope selection (channel/brand/region) and path resolution (Windows/macOS/OneDrive).

2. Ingest & consolidate → master_datos.csv + datos.db (table registros).

3. Standardize periods, categories, and identifiers.

4. Apply rules from Excel → compute actual vs. expected and classify findings.

5. Explore & export → filtered views + downloadable details for review.

Key outputs

• Data quality dashboard (valid cycles, unknown categories).

• Spend without conversion (spend>0 with leads/sales=0).

• Negative values and missing-by-period checks.

• Price/discount compliance (below/above policy).

• Multi-year comparisons and last-month contrast (peaks/drops in performance).

Architecture and notes

Portable monolith, container-friendly; local SQLite for fast queries. Business logic is decoupled in Excel (changes without deploy). Input sanitization and HTML escaping; generic naming in the public demo. Ready to scale to PostgreSQL/Parquet if volume grows.

Applicability

Commercial (price floors, discount limits, volume bonuses), performance marketing (spend-to-result reconciliation), retail/e-commerce (promo auditing by segment/category), SaaS (tier/benefit validation), and distribution (lists and rebates by region).