14.08 Daily Lending Pulse
Daily Lending Pulse Script Documentation
Summary
This document provides an overview of the Daily Lending Pulse script, which is used to generate a daily report on the performance of mortgage and construction loan leads. The report calculates key metrics such as revenue, revenue per lead, and year-over-year (YoY) comparisons. The report is sent to a designated Slack channel every day at 6 am.
The script is installed and executed on a Mac Mini and runs within a virtual environment to manage dependencies. The script is scheduled to run daily using a cron job.
Purpose
The purpose of this script is to automate the generation of a daily financial report for the team. This report helps the business keep track of daily revenue, performance against last year, and key performance indicators (KPIs) related to mortgage and construction loan leads.
Key Metrics
Revenue: Total revenue generated by the leads for both the mortgage and construction segments.
Projected Revenue: An estimate of the total revenue for the month, based on the month-to-date (MTD) revenue.
YoY Revenue %: The percentage difference in revenue compared to the same period last year.
Revenue per Lead (RPL): The average revenue generated per lead.
YoY Revenue per Lead %: The percentage difference in revenue per lead compared to the same period last year.
Script Details
Python Script Location
The main Python script is located at: /Users/joshuaholt/hermes/projects/08-daily-lending-pulse/main.py
Virtual Environment
The virtual environment is located at: /Users/joshuaholt/hermes/projects/08-daily-lending-pulse/env
Dependencies
The Python script requires the following libraries:
requests
prettytable
dotenv
psycopg2
To install these libraries inside the virtual environment, run the following command:
Shell Script
The shell script that invokes the virtual environment and runs the Python script is located at: /Users/joshuaholt/hermes/projects/08-daily-lending-pulse/run_daily_pulse.sh
Contents of the shell script:
Cron Job
The script is scheduled to run every day at 6 am using a cron job.
To view the current crontab, run the following command:
To edit the crontab, run the following command:
The crontab entry to run the script daily at 6 am:
Manual Execution
To manually execute the script, run the following command:
Technical Details
Python Script Workflow
Environment Setup: The script loads environment variables from a
.env
file.Date Calculation: The script calculates the start and end dates for the current month and the same period from the previous year.
Data Retrieval:
The script queries a PostgreSQL database to retrieve revenue and lead data for both mortgage and construction leads.
It performs a year-over-year comparison with data from the previous year.
Metrics Calculation:
The script calculates MTD revenue, projected revenue, revenue per lead, and YoY percentages.
Table Generation: The script generates a formatted table using PrettyTable with the calculated metrics.
Slack Notification: The formatted table is sent to a Slack channel via a webhook URL.
Error Handling
The script includes error handling to catch and log any issues that occur during execution.
Errors are printed to the console for debugging purposes.
Common Issues
Missing Dependencies: If the script fails to run due to missing libraries, make sure the virtual environment is activated and the required libraries are installed.
Database Connection Errors: If the script fails to connect to the PostgreSQL database, verify the database credentials in the
.env
file and ensure the database is accessible.Cron Job Not Running: If the script doesn't run as expected, check the crontab settings and make sure the cron service is running.
Future Maintenance
Adding New Metrics: If new metrics need to be added to the report, modify the SQL queries in the Python script and update the table generation logic.
Updating Slack Webhook: If the Slack channel or webhook URL changes, update the
send_table_to_slack
function with the new webhook URL.Error Debugging: If errors occur, use the manual execution command to test the script and review any error messages printed to the console.
SQL Queries for Professional and Construction Leads
1. Professional Leads (Mortgage Lead)
This query retrieves leads and revenue for the current month (August 2024) and compares them to the same period in the previous year (August 2023). The results include daily lead counts, revenue, and year-over-year (YoY) comparisons for both revenue and revenue per lead.
Construction Leads (Construction Loan Lead)
This query retrieves leads and revenue for construction loans for the current month (August 2024) and compares them to the same period in the previous year (August 2023). The results include daily lead counts, revenue, and year-over-year (YoY) comparisons for both revenue and revenue per lead.
This document should serve as a comprehensive guide to understanding, maintaining, and troubleshooting the Daily Lending Pulse script.
Last updated