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:

source /Users/joshuaholt/hermes/projects/08-daily-lending-pulse/env/bin/activate
pip install requests prettytable python-dotenv psycopg2

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:

#!/bin/bash
source /Users/joshuaholt/hermes/projects/08-daily-lending-pulse/env/bin/activate
python /Users/joshuaholt/hermes/projects/08-daily-lending-pulse/main.py

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:

crontab -l

To edit the crontab, run the following command:

crontab -e

The crontab entry to run the script daily at 6 am:

0 6 * * * /Users/joshuaholt/hermes/projects/08-daily-lending-pulse/run_daily_pulse.sh

Manual Execution

To manually execute the script, run the following command:

bash /Users/joshuaholt/hermes/projects/08-daily-lending-pulse/run_daily_pulse.sh

Technical Details

Python Script Workflow

1. Environment Setup: The script loads environment variables from a .env file.

2. Date Calculation: The script calculates the start and end dates for the current month and the same period from the previous year.

3. 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.

4. Metrics Calculation:

   • The script calculates MTD revenue, projected revenue, revenue per lead, and YoY percentages.

5. Table Generation: The script generates a formatted table using PrettyTable with the calculated metrics.

6. 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

1. 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.

2. 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.

3. 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.

WITH current_month_leads AS (
    -- Get leads and revenue for August 2024
    SELECT
        DATE(ml.email_sent) AS lead_date,
        COUNT(DISTINCT ml.id) AS lead_count,
        SUM(
            CASE
                WHEN ml.home_type = 'Investment' THEN 0
                ELSE 50
            END
        ) AS revenue
    FROM mortgage_lead ml
    INNER JOIN mortgage_broker_filter_to_mortgage_lead mbftml
        ON ml.id = mbftml."mortgageLeadId"
    WHERE ml.email_sent IS NOT NULL
      AND DATE(ml.email_sent) BETWEEN '2024-08-01' AND '2024-08-31'
    GROUP BY DATE(ml.email_sent)
),
previous_year_leads AS (
    -- Get leads and revenue for August 2023 for YoY comparison
    SELECT
        DATE(ml.email_sent) AS lead_date,
        EXTRACT(MONTH FROM ml.email_sent) AS lead_month,
        EXTRACT(DAY FROM ml.email_sent) AS lead_day,
        COUNT(DISTINCT ml.id) AS lead_count,
        SUM(
            CASE
                WHEN ml.home_type = 'Investment' THEN 0
                ELSE 50
            END
        ) AS revenue
    FROM mortgage_lead ml
    INNER JOIN mortgage_broker_filter_to_mortgage_lead mbftml
        ON ml.id = mbftml."mortgageLeadId"
    WHERE ml.email_sent IS NOT NULL
      AND DATE(ml.email_sent) BETWEEN '2023-08-01' AND '2023-08-31'
    GROUP BY DATE(ml.email_sent), EXTRACT(MONTH FROM ml.email_sent), EXTRACT(DAY FROM ml.email_sent)
)
SELECT
    cm.lead_date AS "Date",
    cm.revenue AS "Revenue",
    cm.lead_count AS "Lead Count",
    COALESCE(py.lead_count, 0) AS "YoY Lead Count",
    COALESCE(py.revenue, 0) AS "YoY Revenue",
    CASE
        WHEN COALESCE(py.revenue, 0) = 0 THEN 'N/A'
        ELSE ROUND(
            100.0 * (cm.revenue - COALESCE(py.revenue, 0)) / COALESCE(py.revenue, 1)
        )::TEXT || '%'
    END AS "YoY Revenue %",
    ROUND(
        CASE
            WHEN cm.lead_count > 0 THEN cm.revenue / cm.lead_count
            ELSE 0
        END, 2
    ) AS "Revenue Per Lead",
    ROUND(
        COALESCE(
            CASE
                WHEN COALESCE(py.lead_count, 0) > 0 THEN COALESCE(py.revenue, 0) / COALESCE(py.lead_count, 1)
                ELSE 0
            END, 0
        ), 2
    ) AS "YoY Revenue per Lead",
    CASE
        WHEN COALESCE(py.lead_count, 0) = 0 THEN 'N/A'
        ELSE ROUND(
            100.0 * (
                (CASE
                    WHEN cm.lead_count > 0 THEN cm.revenue / cm.lead_count
                    ELSE 0
                END) -
                (CASE
                    WHEN COALESCE(py.lead_count, 0) > 0 THEN COALESCE(py.revenue, 0) / COALESCE(py.lead_count, 1)
                    ELSE 0
                END)
            ) / COALESCE(
                (CASE
                    WHEN COALESCE(py.lead_count, 0) > 0 THEN COALESCE(py.revenue, 0) / COALESCE(py.lead_count, 1)
                    ELSE 1
                END), 1
            )
        )::TEXT || '%'
    END AS "YoY Revenue per Lead %"
FROM current_month_leads cm
LEFT JOIN previous_year_leads py
    ON EXTRACT(MONTH FROM cm.lead_date) = py.lead_month
    AND EXTRACT(DAY FROM cm.lead_date) = py.lead_day
ORDER BY cm.lead_date;

1. 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.

WITH current_month_leads AS (
    -- Get leads and revenue for August 2024
    SELECT
        DATE(cll.email_sent) AS lead_date,
        COUNT(DISTINCT cll.id) AS lead_count,
        SUM(
            CASE
                WHEN cll.working_with_builder = 'Owner-Builder' OR cll.home_type = 'Investment' THEN 0
                ELSE 35
            END
        ) AS revenue
    FROM construction_loan_lead cll
    INNER JOIN construction_loan_lead_filter_to_construction_loan_lead cllfcll
        ON cll.id = cllfcll."constructionLoanLeadId"
    WHERE cll.email_sent IS NOT NULL
      AND DATE(cll.email_sent) BETWEEN '2024-08-01' AND '2024-08-31'
    GROUP BY DATE(cll.email_sent)
),
previous_year_leads AS (
    -- Get leads and revenue for August 2023 for YoY comparison
    SELECT
        DATE(cll.email_sent) AS lead_date,
        EXTRACT(MONTH FROM cll.email_sent) AS lead_month,
        EXTRACT(DAY FROM cll.email_sent) AS lead_day,
        COUNT(DISTINCT cll.id) AS lead_count,
        SUM(
            CASE
                WHEN cll.working_with_builder = 'Owner-Builder' OR cll.home_type = 'Investment' THEN 0
                ELSE 35
            END
        ) AS revenue
    FROM construction_loan_lead cll
    INNER JOIN construction_loan_lead_filter_to_construction_loan_lead cllfcll
        ON cll.id = cllfcll."constructionLoanLeadId"
    WHERE cll.email_sent IS NOT NULL
      AND DATE(cll.email_sent) BETWEEN '2023-08-01' AND '2023-08-31'
    GROUP BY DATE(cll.email_sent), EXTRACT(MONTH FROM cll.email_sent), EXTRACT(DAY FROM cll.email_sent)
)
SELECT
    cm.lead_date AS "Date",
    cm.revenue AS "Revenue",
    cm.lead_count AS "Lead Count",
    COALESCE(py.lead_count, 0) AS "YoY Lead Count",
    COALESCE(py.revenue, 0) AS "YoY Revenue",
    CASE
        WHEN COALESCE(py.revenue, 0) = 0 THEN 'N/A'
        ELSE ROUND(
            100.0 * (cm.revenue - COALESCE(py.revenue, 0)) / COALESCE(py.revenue, 1)
        )::TEXT || '%'
    END AS "YoY Revenue %",
    ROUND(
        CASE
            WHEN cm.lead_count > 0 THEN cm.revenue / cm.lead_count
            ELSE 0
        END, 2
    ) AS "Revenue Per Lead",
    ROUND(
        COALESCE(
            CASE
                WHEN COALESCE(py.lead_count, 0) > 0 THEN COALESCE(py.revenue, 0) / COALESCE(py.lead_count, 1)
                ELSE 0
            END, 0
        ), 2
    ) AS "YoY Revenue per Lead",
    CASE
        WHEN COALESCE(py.lead_count, 0) = 0 THEN 'N/A'
        ELSE ROUND(
            100.0 * (
                (CASE
                    WHEN cm.lead_count > 0 THEN cm.revenue / cm.lead_count
                    ELSE 0
                END) -
                (CASE
                    WHEN COALESCE(py.lead_count, 0) > 0 THEN COALESCE(py.revenue, 0) / COALESCE(py.lead_count, 1)
                    ELSE 0
                END)
            ) / COALESCE(
                (CASE
                    WHEN COALESCE(py.lead_count, 0) > 0 THEN COALESCE(py.revenue, 0) / COALESCE(py.lead_count, 1)
                    ELSE 1
                END), 1
            )
        )::TEXT || '%'
    END AS "YoY Revenue per Lead %"
FROM current_month_leads cm
LEFT JOIN previous_year_leads py
    ON EXTRACT(MONTH FROM cm.lead_date) = py.lead_month
    AND EXTRACT(DAY FROM cm.lead_date) = py.lead_day
ORDER BY cm.lead_date;

This document should serve as a comprehensive guide to understanding, maintaining, and troubleshooting the Daily Lending Pulse script.