Automations Engineer for Rebate Data Pipeline

We're looking for a developer to build a config-driven consolidation tool that automatically retrieves the rebate attachments directly from our incoming emails, then normalizes them (each in its own distinct layout) into one standard consolidated Excel dataset. Buying groups do not email us data. Once the data is compiled, we send each buying group the information that was submitted for it, so the consolidated dataset also needs to be shareable back to the buying groups. This is Phase 1 of a multi-phase project. Phase 1 is intake and consolidation only. Rebate calculation and eligibility logic will come in a later phase and are out of scope for this engagement. The problem we're solving • The parties who email us data are contractors and distributors. Buying groups do not send data. Roughly 30 contractors, plus multiple distributors, submit rebate reporting files each cycle. • Attachments arrive in many different formats. Each sender uses its own distinct layout, so the tool must map each one individually. Most are Excel or CSV, but some attachments may be PDF, so the solution needs to handle extracting tabular data from PDFs as well. • The sender's email address is the reliable key for identifying which contractor or distributor a file came from. • The same purchase can be reported twice: once by the contractor, and again by the distributor they purchased it from. The tool must catch this overlap and flag it, rather than let the same transaction pass through as if it were two separate records. • Files come as email attachments and are currently reshaped by hand every cycle. We want that eliminated end to end. Scope of work (Phase 1) Build a tool that: 1. Ingests attachments automatically from email. The tool must monitor our rebate inboxes and pull incoming attachments into a working folder for processing, with no manual downloading. We run Microsoft 365, so a Power Automate flow pulling attachments into a SharePoint or OneDrive folder is the expected approach, but we're open to your recommendation. 2. Routes by sender. It identifies which contractor or distributor a file came from using the sender address, and tags every record with its source so we always know who submitted it. 3. Maps each sender's file into one standard consolidated schema using a per-sender mapping template (a config or spreadsheet our team can edit, not logic hardcoded into the program). Layouts have quirks: header rows that aren't row 1, merged cells, multiple tabs, subtotal rows mixed into the data. Some senders may deliver PDFs. 4. Flags potential duplicate records across senders. The same purchase can arrive twice, once from a contractor and once from the distributor they bought from. The tool must identify likely overlaps and flag them for review. It must not silently merge or delete them: both source records and their provenance are preserved, and a person decides how to treat each overlap. 5. Validates and quarantines. Any file or row that fails validation goes to an exception report or queue instead of silently passing bad data through. 6. Handles resubmissions and corrections without double-counting. This is a sender resending a corrected version of its own file, which is separate from the cross-sender overlap described above. 7. Produces a clean master consolidated Excel dataset, the single source of truth. 8. Produces the compiled information to share back with the buying groups. Once the data is consolidated, we send each buying group the submissions that relate to it, so the tool must be able to break the consolidated data out by buying group. Identifying which submissions belong to which buying group relies on a membership reference, which we can provide. 9. Reconciles consolidated totals back to the source-file totals so we can trust the numbers. Explicitly out of scope (Phase 1) • Rebate calculation (rates, tiers, eligibility, member-directory matching), which is a future phase. • Canada, which is not part of this process. • Custom web application or standalone database infrastructure. Deliverables • The consolidation engine (we expect Python, using pandas and openpyxl or equivalent, plus PDF table extraction where needed). • The email-attachment intake automation (a Power Automate flow, or your recommended equivalent for a Microsoft 365 environment). • Per-sender mapping templates, editable by our staff so a layout change doesn't require a developer. • Validation logic and an exception report. • A duplicate-overlap report that flags records submitted by both a contractor and a distributor for review. • Master consolidated Excel output. • Per-buying-group extracts of the submitted data, so the compiled information can be sent back to each buying group. • Documentation: a short runbook, a guide to editing the mapping templates, and a recorded walkthrough. Our environment • Microsoft 365 (Outlook, SharePoint or OneDrive, Excel, Power Automate). What to include in your proposal • Relevant experience parsing irregular Excel layouts (not just clean CSVs) and extracting tabular data from PDFs. Tell us how you'd handle a file where the header starts on row 7 with subtotal rows scattered through the data, and how you'd approach a PDF-based attachment. • Your approach to detecting likely duplicate records across different senders, such as the same purchase reported by both a contractor and a distributor, and how you'd present overlaps for review without losing either source record. • Experience automating email-attachment intake in Microsoft 365 (Power Automate or similar). • Your approach to a config-driven, template-per-sender design, and how a non-developer on our team would update a mapping when a sender changes their columns. • A rough timeline and cost for Phase 1 as described.