# =============================================================================
# COMPETENCY RECORD - Cedric Ancellin's demonstrated engineering skills and
# experience. Third-party technologies and vendors are named as the concrete
# skill surface. Professional background / track record.
# =============================================================================

# ============================================================
# REPORTING, ANALYTICS & FINANCIAL RECONCILIATION - COMPETENCY RECORD
# ============================================================
# Core Purpose:
#   Demonstrated experience building a large operational BI /
#   reporting suite and the financial-reconciliation machinery
#   behind it: 200+ report types across 80+ modules, POS +
#   payment-processor + GL reconciliation, and a scheduled,
#   timezone-aware, multi-recipient report-delivery engine.
# ============================================================

summary: >
  Designed and built a comprehensive operational reporting and
  business-intelligence layer, 200+ report types across 80+
  reporting modules, spanning financial reporting, multi-source revenue
  reconciliation (POS check-level, payment-processor settlement,
  and GL accrual), sales/performance analytics, CRM/marketing
  analytics, operational reports, and multi-property consolidated
  rollups, all served through a uniform reporting framework with a
  scheduled multi-recipient export engine.


# ==================================================================
# 1. REPORTING FRAMEWORK
# ==================================================================

framework:
  description: >
    Engineered a uniform reporting framework so every report is a
    small, consistent, access-controlled unit.
  pattern:
    - "Each report is a self-contained handler (query + routing + parameter docs + security) sharing a common include layer."
    - "A uniform output schema: columns, rows, row-totals, filter params, and graphing hints (bar/pie/line) rendered client-side."
    - "Parametric filtering (date range, venue, zone, type) that serializes cleanly into exports and schedules."
    - "Tenant isolation enforced at the SQL layer, operators see only their own data."
    - "Timezone-aware date/money formatting (minor-unit integers rendered to currency)."
  scale: "80+ reporting modules/endpoints (verified folder count); 200+ distinct report types; transaction-grain through annual-grain aggregation."


# ==================================================================
# 2. FINANCIAL REPORTING
# ==================================================================

financial:
  description: >
    Built financial reporting from transaction detail up to
    executive accrual rollups.
  coverage:
    - "Revenue summary + transaction-level detail by venue, event date, and transaction type (charge / refund / chargeback)."
    - "Finalized vs. adjusted revenue, failed-transaction views, and full-text financial search."
    - "Accrual-based revenue on both calendar and a fiscal calendar (year/month/week-to-week/month-to-month), segmented by revenue type (subscription, services, transactions, tickets, packages, deposits, partners, marketing) with profitability metrics."


# ==================================================================
# 3. MULTI-SOURCE REVENUE RECONCILIATION
# ==================================================================

reconciliation:
  description: >
    Built the reconciliation machinery that ties three independent
    money sources together, the hard part of hospitality finance.
  sources:
    pos: "POS check-level detail (revenue center, check type, tenders, taxes, tips, comps, discounts, server/cashier, open/close)."
    payment_processor: "Payment-processor settlement reconciliation, gross, application fee, processor fee, net collected, variance; charge/refund/chargeback."
    gl_accrual: "GL accrual ledger for revenue recognition and payout booking."
  workflows:
    - "Day-close: freeze revenue, reconcile POS to deposits, post adjustments, manager sign-off."
    - "Tender/deposit reconciliation matching system prepaid totals to bank deposits across properties."
    - "No-show and adjustment tracking distinct from immutable transactional records."


# ==================================================================
# 4. OPERATIONAL, SALES & MARKETING ANALYTICS
# ==================================================================

analytics:
  sales_performance: "Sales by staff/owner/section/table, commission pools and rankings, presale-owner attribution and payouts."
  operational: "Guest-list-by-guest and by-owner reports (expected vs. checked-in, denied, per-cohort breakdowns), scan/attendance reconciliation, waitlist, guest-services operations."
  crm_marketing: "Email-list creation/engagement, demographic breakdowns, microsite engagement, visit/attendance analytics."
  event_package: "Event/program performance, experience enrollment, package/bundle revenue and future bookings."


# ==================================================================
# 5. MULTI-PROPERTY CONSOLIDATION
# ==================================================================

network:
  description: >
    Built consolidated reporting that rolls many properties up to
    the corporate level.
  coverage:
    - "Network revenue/sales/transaction rollups normalized from per-property logs to corporate entity."
    - "Cash and deposit tracking across properties; project/enterprise cost allocation by department, entity, and role."
    - "Executive dashboards, network revenue, year-to-date accrual, profit-margin analytics."


# ==================================================================
# 6. SCHEDULED EXPORT & DELIVERY ENGINE
# ==================================================================

delivery:
  description: >
    Built the engine that turns reports into scheduled, delivered
    artifacts.
  capabilities:
    - "Excel (multi-sheet workbook) and CSV export; one sheet per report in a scheduled package (combined workbooks)."
    - "Cron-driven schedules (daily/weekly/monthly) that are timezone-aware and convert to each recipient's local time."
    - "Multi-recipient email distribution with inline HTML tables plus attached workbooks."
    - "Row caps and memory-safe query limits to protect the platform on large exports."
  cross_reference:
    $ref: "https://zc8.com/competencies/06_data_engineering_warehouse"