📌 Policy Renewal Reporting & Automation Pipeline

🧩 Problem Statement

Tracking policies nearing expiry across multiple business lines is critical for:

• Ensuring timely renewals
• Avoiding revenue leakage
• Maintaining compliance and client retention

However, challenges included:

• Multiple fragmented data sources (policy systems, underwriting platforms)
• Complex renewal logic (latest transaction, cancellations, endorsements)
• Manual report preparation across departments
• Inconsistent formatting and delayed delivery of renewal extracts

This resulted in operational inefficiencies, delayed insights, and increased risk of missed renewals.

💡 Solution Overview

Designed and implemented an end-to-end automated renewal reporting pipeline that:

• Identifies policies expiring within a defined time window (e.g., 90–120 days)
• Consolidates data across multiple underwriting systems
• Applies complex business rules to determine valid renewal candidates
• Automates report generation, formatting, and delivery

The solution integrates SQL-based data engineering + Power Automate orchestration + Excel automation to deliver production-ready renewal reports.

🔄 End-to-End Architecture

• Multiple Policy Systems (Underwriting Platforms) ↓
• SQL Data Extraction (Stored Procedures) ↓
• Data Transformation (CTEs + Joins + Business Logic) ↓
• Staging Tables (Centralized Dataset) ↓
• Scheduled SQL Jobs (Automated Refresh) ↓
• Power Automate Flows (Orchestration Layer) ↓
• Excel Generation & Formatting (Office Scripts) ↓
• Automated Distribution (Email & SharePoint)

⚙️ Core Components

🗄️ Data Engineering Layer (SQL Server)

✅ Stored Procedures

Built modular stored procedures to:

• Extract policy data across systems
• Filter renewal windows dynamically
• Apply transaction-level logic (new business, renewal, cancellation, endorsement)
• Structure output for downstream reporting

✅ Advanced SQL Techniques Used

Common Table Expressions (CTEs) for:

• Premium aggregation
• Address and enrichment joins
• Policy-level transformations

Complex joins across:

• Policy data
• Client/address datasets
• Broker and salesperson mappings
• External enrichment datasets

Business logic handling:

• Latest transaction selection using effective dates
• Conditional premium calculations
• Multi-source data alignment

⏱️ Scheduling Layer

SQL jobs configured to:

• Execute stored procedures at defined intervals
• Refresh renewal datasets automatically
• Ensure data availability before report generation

👉 This ensures zero manual intervention in data preparation

🔗 Automation Layer (Power Automate)

✅ Flow Capabilities

• Scheduled flows (time-based triggers across regions)
• Dataset queries to fetch processed renewal data
• Loop-based processing for large datasets
• Conditional logic for handling empty or partial datasets

✅ Orchestration Logic

• Trigger → Query dataset → Process rows → Generate file
• Controlled execution for multiple business lines (parallel workflows)
• Data chunking using loop variables to handle volume constraints

📊 Output Layer (Excel + Office Scripts)

• Automated Excel file creation

• Dynamic CSV/table generation

Office Scripts used for:

• Formatting data output
• Structuring final extracts
• Standardizing presentation across teams

Multi-report generation per business unit

🧠 Key Design Highlights

• ✅ Dynamic Renewal Window Logic Automatically identifies policies within upcoming expiry ranges

• ✅ Latest Transaction Handling Ensures the most relevant policy record is selected

• ✅ Multi-Source Data Integration Combines data from underwriting, policy, and enrichment systems

• ✅ Scalable Data Processing Loop-based chunking prevents failures with large datasets

• ✅ End-to-End Automation Eliminates manual intervention from extraction → delivery

📈 Business Impact

• ✅ Improved Renewal Visibility Early identification of expiring policies across portfolios

• ✅ Revenue Protection Reduced risk of missed renewals and contract lapses

• ✅ Operational Efficiency Fully automated workflow replaces manual reporting efforts

• ✅ Standardization of Reporting Consistent structure across multiple business lines

• ✅ Faster Decision-Making Timely availability of renewal data for underwriting teams

🛠️ Tech Stack

• SQL Server – Data extraction & transformation
• T-SQL (CTEs, Joins, Stored Procedures) – Business logic implementation
• Power Automate – Workflow orchestration
• Office Scripts – Excel transformation & formatting
• Excel / CSV Outputs – Final reporting artefacts

🔒 Data Privacy & Confidentiality This repository contains a sanitized version of the implementation: No client-specific identifiers or system names included Business rules generalized for demonstration purposes Structure preserved while ensuring confidentiality

🚀 Key Highlights

• End-to-end automation pipeline (data → report → delivery)
• Strong SQL + workflow orchestration integration
• Real-world insurance analytics use case
• Focus on scalability, accuracy, and operational efficiency

⭐ Why This Project Matters

This project demonstrates the ability to design production-grade data pipelines that directly support business-critical workflows, combining data engineering, automation, and analytics into a single scalable solution.