Intelligent Lead Management System

The Intelligent Lead Management System is designed to streamline and optimize the process of capturing, categorizing, and nurturing leads throughout the sales funnel. The system should enable users to input lead information, including the lead's name, email address, company details, and any additional relevant information. The key feature of this system is the incorporation of an intelligent lead scoring mechanism, allowing users to assign scores based on predefined criteria and engagement metrics. The system should facilitate automated follow-up actions, such as sending personalized emails and setting reminders, triggered by lead behavior or milestones in the sales process. A centralized dashboard should be provided to allow sales teams to visualize lead analytics, track lead interactions, and monitor conversion progress.

Postgres SQL Schemas

These SQL schemas represent the backbone of an Intelligent Lead Management System. The Leads table stores basic lead information, while LeadInteractions and AutomatedActions tables track lead interactions and automated actions, respectively. The LeadScoringCriteria table defines criteria for lead scoring, and LeadScoreHistory maintains a history of lead scores based on those criteria. This structured approach ensures efficient lead management and tracking within the CRM system.

Leads Table:

CREATE TABLE leads (
  tenant_id uuid,
  lead_id uuid,
  name VARCHAR(255),
  email VARCHAR(255),
  company VARCHAR(255),
  lead_score INT,
  engagement_date TIMESTAMP,
  converted BOOLEAN,
  CONSTRAINT FK_tenants FOREIGN KEY(tenant_id) REFERENCES tenants(id),
  CONSTRAINT PK_leads PRIMARY KEY(tenant_id,lead_id));

Lead Interactions Table:

CREATE TABLE leadinteractions (
  tenant_id uuid,
  interaction_id uuid,
  tenant_id uuid,
  lead_id INT,
  interaction_date TIMESTAMP,
  interaction_type VARCHAR(50),
  description TEXT,
  CONSTRAINT FK_leads FOREIGN KEY(tenant_id, lead_id) REFERENCES leads(tenant_id,lead_id),
  CONSTRAINT PK_leadinteractions PRIMARY KEY(tenant_id,interaction_id));

Automated Actions Table:

CREATE TABLE AutomatedActions (
  tenant_id uuid,
  action_id uuid,
  lead_id INT,
  action_type VARCHAR(50),
  action_date TIMESTAMP,
  description TEXT,
  CONSTRAINT FK_leads FOREIGN KEY(tenant_id, lead_id) REFERENCES leads(tenant_id,lead_id),
  CONSTRAINT PK_automatedactions PRIMARY KEY(tenant_id,action_id));

Lead Scoring Criteria Table:

CREATE TABLE LeadScoringCriteria (
  criteria_id uuid PRIMARY KEY,
  criteria_name VARCHAR(255),
  weight INT);

Lead Score History Table:

CREATE TABLE LeadScoreHistory (
  score_id uuid,
  tenant_id uuid,
  lead_id INT,
  criteria_id INT,
  score_value INT,
  timestamp TIMESTAMP,
  CONSTRAINT FK_leads FOREIGN KEY(tenant_id, lead_id) REFERENCES leads(tenant_id,lead_id),
  CONSTRAINT PK_leadscorehistory PRIMARY KEY(tenant_id,score_id));

Sample data

-- Sample data for Tenants table
INSERT INTO tenants (id, name) VALUES
  ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', 'Tenant A'),
  ('e5a2cbb4-9b3a-4f53-8812-53799fe758a1', 'Tenant B');

-- Sample data for leads table
INSERT INTO leads (tenant_id, lead_id, name, email, company, lead_score, engagement_date, converted) VALUES
  ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', '579f0e1a-d5d4-4aeb-b0c6-12d99f7d9c57', 'John Doe', 'john.doe@example.com', 'ABC Corp', 80, '2022-01-01', true),
  ('e5a2cbb4-9b3a-4f53-8812-53799fe758a1', '0e10c2a5-7a2a-46c9-b506-53c47128774c', 'Jane Smith', 'jane.smith@example.com', 'XYZ Ltd', 65, '2022-01-15', false),
  ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', '91be7420-5f6b-4a2c-8e05-b2f956d720f0', 'Bob Johnson', 'bob.johnson@example.com', 'XYZ Corp', 75, '2022-02-01', true),
  ('e5a2cbb4-9b3a-4f53-8812-53799fe758a1', 'c7fca6c9-8343-4e5a-8a3e-6e916485c92a', 'Alice White', 'alice.white@example.com', 'ABC Ltd', 50, '2022-02-10', false);

-- Sample data for leadinteractions table
INSERT INTO leadinteractions (tenant_id, interaction_id, lead_id, interaction_date, interaction_type, description) VALUES
  ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', '1bd3625e-1a64-48aa-a69b-0ebf06d3b0e2', 1, '2022-01-05', 'Meeting', 'Discussed product features and pricing'),
  ('e5a2cbb4-9b3a-4f53-8812-53799fe758a1', '2a2b3d44-77cd-49f8-9f9e-20a45ec3c453', 2, '2022-01-20', 'Call', 'Follow-up on feature requests'),
  ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', '3c458193-b7d3-4dfb-ba43-26760dbf6c14', 3, '2022-01-25', 'Email', 'Sent product brochure'),
  ('e5a2cbb4-9b3a-4f53-8812-53799fe758a1', '3c458193-b7d3-4dfb-ba43-26760dbf6c15', 1, '2022-01-30', 'Demo', 'Provided a product demo');

-- Sample data for AutomatedActions table
INSERT INTO AutomatedActions (tenant_id, action_id, lead_id, action_type, action_date, description) VALUES
  ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', 'a5c3d86d-bf28-45df-bdba-22bb72f456a0', 1, 'Email Campaign', '2022-02-01', 'Sent promotional email to lead 1'),
  ('e5a2cbb4-9b3a-4f53-8812-53799fe758a1', '7d2ec8f5-8a3a-4f63-bc4a-c54f9fb5ebef', 2, 'Webinar Registration', '2022-02-05', 'Lead 2 registered for the webinar'),
  ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', 'acb5e8e1-3b6a-4a83-854a-48167032d7d8', 3, 'Product Demo', '2022-02-10', 'Scheduled a product demo for lead 3'),
  ('e5a2cbb4-9b3a-4f53-8812-53799fe758a1', 'c4f8ae57-8d2a-4b65-b930-56ec85b960f4', 4, 'Follow-up Call', '2022-02-15', 'Scheduled a follow-up call for lead 4');

-- Sample data for LeadScoringCriteria table
INSERT INTO LeadScoringCriteria (criteria_id, criteria_name, weight) VALUES
  ('9a0f11b7-94e7-4b6b-891c-6a3b27f8ac9e', 'Engagement Level', 5),
  ('c2f671c9-8d82-4a04-9f58-64c8107e13f8', 'Company Size', 3),
  ('dea5f5d2-9e65-4ae4-9344-51e3771426bb', 'Lead Source', 2),
  ('b621145a-d55f-4c10-94e2-19b4857502ce', 'Product Interest', 4);

-- Sample data for LeadScoreHistory table
INSERT INTO LeadScoreHistory (score_id, tenant_id, lead_id, criteria_id, score_value, timestamp) VALUES
  ('e26ea61f-3af4-45c4-a509-b0e88f7a5017', 'd5f126e8-be47-48f1-ac8d-2f69e960f04c', 1,  '9a0f11b7-94e7-4b6b-891c-6a3b27f8ac9e', 20, '2022-02-10'),
  ('db32d3f9-7b1c-4be5-88df-c4ef1b2a6a77', 'e5a2cbb4-9b3a-4f53-8812-53799fe758a1', 2, 'c2f671c9-8d82-4a04-9f58-64c8107e13f8', 15, '2022-02-12'),
  ('a8c57b1d-8a25-4f1c-9dcd-7a3a670acff5', 'd5f126e8-be47-48f1-ac8d-2f69e960f04c', 3, 'dea5f5d2-9e65-4ae4-9344-51e3771426bb', 12, '2022-02-15'),
  ('f7c9169b-44f0-47d3-bf25-cd7a827c3b82', 'e5a2cbb4-9b3a-4f53-8812-53799fe758a1', 4, 'b621145a-d55f-4c10-94e2-19b4857502ce', 18, '2022-02-20');