> For the complete documentation index, see [llms.txt](https://docs.biglawinvestor.com/knowledge-base/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.biglawinvestor.com/knowledge-base/company/hermes/daily-lead-bot.md).

# 14.01 Daily Lead Bot

{% hint style="info" %}
10–19 Tools / 12 Hermes / 12.01
{% endhint %}

## 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

<figure><img src="https://4262923832-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FOU8nJo04SgxSaEvyKxBd%2Fuploads%2FXeyHHsZiogdh8MAzmqfk%2F2024-06-18%20-%20Daily%20Lead%20Bot%20-%20Example%20Output.png?alt=media&amp;token=bcef466c-ee04-44ab-8507-4e27e697c29b" alt=""><figcaption><p>Example output</p></figcaption></figure>

## 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.**