**Lead Engine CRM – Category Mapping & Review Workbook Enhancements**

### Project Overview I have an existing Python-based Lead Engine CRM which consolidates multiple property-industry data sources into a single master database. The system currently: * Imports multiple source spreadsheets. * Deduplicates records. * Tracks bounces, unsubscribes and repaired emails. * Creates a master database. * Creates a review workbook for email quality review. * Calculates distances from a base location. * Produces campaign-ready exports. The project is already built and working. This job is for enhancements and workflow improvements. --- ### Required Changes #### 1. Category Mapping From Folder Structure The system should read the parent folder name of each source file and automatically assign the Category field. Example: ESTATE AGENTS / Propertymark_Processed.xlsx → Category = Estate Agent SURVEYORS / RICS_Surveyors_Processed.xlsx → Category = Surveyor ARCHITECTS / RIBA_Processed.xlsx → Category = Architect CONVEYANCERS / CLC_Processed.xlsx → Category = Conveyancer ENGINEERS / Engineers_Processed.xlsx → Category = Structural Engineer This should replace any keyword guessing or manual mapping where possible. --- #### 2. Subcategory From Source File Subcategory should be generated from the source file name. Examples: Propertymark_Processed.xlsx → Propertymark Property_Ombudsman_Processed.xlsx → Property Ombudsman CLC_Processed.xlsx → CLC Rightmove_Sales_Estate_Agents_Processed.xlsx → Rightmove Sales Google_Maps_Estate_Agents_Processed.xlsx → Google Maps Estate Agents --- #### 3. Review Workbook Source Column Add a Source column to the Review Workbook. This is required so records can be filtered by source during email quality reviews. Examples: * Propertymark * Property Ombudsman * Rightmove * Google Maps Estate Agents * RICS * PCA * Law Society Conveyancing * etc. --- #### 4. Full Row Colour Coding Review Workbook should colour the entire row instead of individual cells. Suggested colours: * Opened = Light Green * Bounced = Light Red * Unsubscribed = Light Orange * Website Clicked = Light Blue Purpose is faster visual scanning during review. --- #### 5. Town and County Add Town and County fields to: * Master Database * Review Workbook These should be populated during postcode processing. --- ### Future Workflow Requirements (For Awareness) The system will later need to support: * Manual additions of new businesses discovered during field research. * Manual additions of homeowner enquiries. * Referral tracking. * Stable long-term record management. These items are not necessarily part of this job but should be considered when making structural changes. --- ### Existing Developer The existing developer (Ansan Lawati) built the current version and should be invited to this project as he already understands the codebase and workflow.