Real-Time Customer Data Excel Integration

Project Title

Real-Time Customer Data Integration into a Master Excel Workbook

Objective

Design and deploy a centralized Master Excel workbook that automatically consolidates customer data from 10+ independently maintained sales workbooks (one per location/team). The master must update in real time or near real time, enforce data quality (validation/formatting), and remain accessible only to an administrator. All artifacts will live on the organization’s SharePoint.

Scope & Deliverables

In Scope

1. Master Excel Workbook (SharePoint-hosted)
o Single source of truth with a consolidated Customers_Master table
o Robust Data Validation (dropdowns for State → City, product lists, email/phone formats)
o Conditional Formatting for data quality flags (duplicates, missing mandatory fields, invalid formats)
o De-duplication logic and conflict handling
o Audit & error Log sheet, and Data Dictionary sheet

2. Source Workbooks (10+) Standardization
o Each sales workbook stored in a dedicated SharePoint folder
o Standardized Customers_Source table schema and validation
o Light guidance sheet inside each source file for users

3. Automated Sync
o Near real-time ingestion via Power Automate (trigger on file update/addition)
o Append/Upsert into Master (no manual copying)
o Optional scheduled sweep (e.g., hourly) to catch edge cases

4. Security
o Admin-only access to Master workbook (read/write)
o Optional read-only snapshot for stakeholders (separate viewers’ file or protected sheet)

5. Documentation & Handover
o Technical runbook: flows, connections, schema, validation rules
o Admin quick-reference (how to add a new source, troubleshoot, restore)
o Short training for the admin

Deliverables
• Deployed Master Excel on SharePoint with working automation
• Standardized 10+ Source Workbooks (templates + migrated/validated)
• Power Automate flows (with environment variables) + connections
• Documentation (PDF/Doc + in-workbook Instructions)
• Training session (30–60 min)