14.03 Lending Monthly Reports

Executive Summary

The Lending Monthly Reports script connects to the PostgreSQL database, retrieves lead data for mortgage brokers, generates monthly reports, and uploads them to Digital Ocean Spaces. This process ensures mortgage brokers have timely and accurate reports of their lead data.

Example Output

Technical Summary

Overview

The script performs the following key functions:

• Loads Environment Variables: Reads configuration values from a .env file.

• Connects to PostgreSQL Database: Establishes a connection to retrieve lead data.

• Queries Lead Data: Executes SQL queries to fetch lead metrics for construction and professional mortgage leads.

• Generates CSV Reports: Creates CSV files for each broker's monthly leads.

• Uploads to Digital Ocean Spaces: Uploads the generated CSV files to a specific folder in Digital Ocean Spaces.

Detailed Steps

Import Necessary Libraries

Handles environment variables, logging, database connection, CSV processing, and Digital Ocean Spaces interaction.

Load Environment Variables

Uses dotenv to load database credentials and Digital Ocean Spaces credentials from a .env file.

Main Function

Connect to PostgreSQL Database

Connects using credentials from environment variables.

Fetch Loan Officers

Retrieves the list of mortgage brokers from the database.

Get Prior Month and Year

Calculates the previous month and year to determine the reporting period.

Query Lead Data

For each broker, constructs and executes SQL queries to fetch:

• Construction Leads: Lead data related to construction mortgages.

• Professional Leads: Lead data related to professional mortgages.

Generate CSV Reports

Writes the fetched lead data into CSV files for each broker, following a specific naming convention.

Upload to Digital Ocean Spaces

Uploads the generated CSV files to a designated folder in Digital Ocean Spaces using the boto3 library.

Error Handling

Logs errors encountered during the process and provides detailed error messages.

Key SQL Query Components

• Construction Leads Query: Fetches lead data related to construction mortgages.

• Professional Leads Query: Fetches lead data related to professional mortgages.

Usage

• Running the Script: The script can be executed locally or scheduled to run periodically using a cron job.

• Environment Setup: Requires a .env file with database and Digital Ocean Spaces credentials.

Detailed Workflow

Import Libraries

• Uses psycopg2 for database connection.

• Uses csv for CSV file handling.

• Uses os for operating system operations.

• Uses datetime for date calculations.

• Uses boto3 for Digital Ocean Spaces interaction.

• Uses dotenv for environment variable management.

Load Environment Variables

• Loads DO_SPACES_KEY, DO_SPACES_SECRET, DO_SPACES_ENDPOINT, and DO_SPACES_REGION for Digital Ocean Spaces.

• Loads database connection parameters.

Main Execution

• Connect to Database: Establishes connection using psycopg2.

• Fetch Loan Officers: Retrieves broker data.

• Determine Reporting Period: Calculates prior month and year.

• Query Data and Generate Reports: Executes SQL queries and generates CSV reports.

• Upload Reports: Uses boto3 to upload reports to Digital Ocean Spaces.

• Error Handling: Catches and logs errors.

SQL Queries

• Construction Leads Query: Retrieves construction mortgage leads.

• Professional Leads Query: Retrieves professional mortgage leads.

CSV Generation

• Creates CSV files with headers and lead data for each broker.

Digital Ocean Spaces Upload

• Uploads CSV files to a specified folder, creating a hierarchical structure based on year and month.

Cron Job Integration

• Can be scheduled using a cron job to run on the 1st of every month at 10 PM UTC.

Summary

The script ensures that monthly lead reports are automatically generated and securely uploaded, providing mortgage brokers with up-to-date and organized lead data.