14.01 Daily Lead Bot

10–19 Tools / 12 Hermes / 12.01

Executive Summary

The Daily Lead Bot script connects to the PostgreSQL database, retrieves lead data for various categories, and sends a comprehensive summary to Slack. This report includes current, projected, and average monthly leads, along with visual indicators to show performance against targets.

Example Output

Technical Summary

Overview

The script performs the following key functions:

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

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

  3. Queries Lead Data: Executes SQL queries to calculate lead metrics for various categories and professional degrees.

  4. Processes Results: Analyzes data to determine current month leads, projected leads, and average monthly leads, adding visual indicators.

  5. Sends Slack Notification: Formats the results into a Slack message and posts it using a webhook.

Detailed Steps

  1. Import Necessary Libraries:

    • Handles environment variables, logging, database connection, HTTP requests, and JSON processing.

  2. Load Environment Variables:

    • Uses dotenv to load database credentials and Slack webhook URL from a .env file.

  3. Main Function:

    • Connect to PostgreSQL Database:

      • Connects using credentials from environment variables.

    • Define Tables and Professional Degrees:

      • Lists tables for different lead types and professional degrees.

    • Query Lead Data:

      • For each table and degree, constructs and executes SQL queries to calculate:

        • Current Month Leads: Total leads created this month.

        • Projected Leads: Estimated leads for the full month based on current data.

        • Average Monthly Leads: Average leads per month over the past year.

    • Process Query Results:

      • Retrieves query results and determines performance indicators:

        • :target-achieve: Projected leads meet or exceed the average.

        • :target-uncertain: Projected leads are within 10% of the average.

        • :target-fail: Projected leads are significantly below average.

    • Construct Slack Message:

      • Builds a formatted Slack message with sections for each category and professional degree, including data and visual indicators.

    • Send to Slack:

      • Posts the constructed message to Slack using the webhook URL.

    • Error Handling:

      • Logs errors and returns appropriate HTTP status codes.

Key SQL Query Components

  • current_month_leads: Counts leads created in the current month.

  • minutes_elapsed: Calculates the number of minutes passed in the current month.

  • total_minutes_in_month: Calculates the total minutes in the current month.

  • last_12_months_average: Computes the average number of leads per month over the last year.

  • Final Select Statement: Combines these calculations to determine current, projected, and average leads.

Visual Indicators

  • Achieve (Green): Projected leads ≥ Average leads.

  • Uncertain (Yellow): Projected leads within 10% of average leads.

  • Fail (Red): Projected leads < Average leads.

Slack Message Structure

  • Header and Date:

    • Announces the daily report and includes the current date.

  • Lead Data Sections:

    • Lists current, projected, and average leads for each category and degree.

  • Action Buttons:

    • Provides links for more data, analytics, and rankings.

Usage

  • Running the Script:

    • Can be executed locally for testing.

    • Suitable for deployment in environments with the necessary environment variables and database access.

Error Handling

  • Logs errors and sends detailed error messages.

  • Returns appropriate HTTP status codes.

Last updated