Sale Lead State Activity Documentation¶

Overview¶

The Sale Lead State Activity module (sale.lead.state.activ) is a lightweight tracking model that records historical state transitions for leads. This model provides a simple audit trail of when leads move through different stages of the sales pipeline, enabling analytics on time-in-state, conversion velocity, and bottleneck identification in the lead management process.

Model Information¶

Model Name: sale.lead.state.activ Display Name: State Key Fields: None (simple tracking model)

Features¶

❌ Audit logging enabled (_audit_log)
❌ Multi-company support (company_id)
❌ Full-text content search (_content_search)
❌ Unique key constraint

Purpose and Use Case¶

This model serves as a historical state tracker for lead progression. Each time a lead's state changes, a new record is created in this model capturing: - Which lead changed state - What state it entered - When the transition occurred

This simple but powerful design enables: - Time-in-state analysis: Calculate how long leads spend in each stage - Conversion metrics: Measure velocity through the pipeline - Bottleneck detection: Identify stages where leads get stuck - Historical reporting: Reconstruct lead journey over time

Key Fields Reference¶

All Fields¶

Field	Type	Required	Description
`lead_id`	Many2One	❌	Reference to the sale.lead record
`state`	Selection	❌	The state the lead entered
`date`	Date	❌	Date when the lead entered this state

State Values¶

The state field uses the same values as the sale.lead model:

State	Description
`new`	Lead entered "new" state
`qualified`	Lead was qualified
`pending_initial_meeting`	First meeting scheduled
`waiting_for_client`	Awaiting client response
`pending_next_meeting`	Follow-up meeting scheduled
`preparing_quotation`	Preparing quote/proposal
`converted`	Converted to opportunity
`on_hold`	Placed on hold
`won`	Successfully won
`lost`	Opportunity lost
`voided`	Invalid/spam lead
`referred`	Referred to another party

How It Works¶

Automatic State Tracking¶

When a lead's state is updated, a new state activity record is automatically created (typically via triggers or model write overrides in the parent sale.lead model):

# Example of state change triggering state activity creation
# (Implementation would be in sale.lead model)
lead.write({"state": "qualified"})

# Automatically creates:
get_model("sale.lead.state.activ").create({
    "lead_id": lead.id,
    "state": "qualified",
    "date": datetime.now().strftime("%Y-%m-%d")
})

Reading State History¶

Access the complete state history of a lead:

lead = get_model("sale.lead").browse(lead_id)

# View all state transitions chronologically
for state_activ in lead.state_activ:
    print(f"{state_activ.date}: {state_activ.state}")

# Output:
# 2025-01-01: new
# 2025-01-05: qualified
# 2025-01-08: pending_initial_meeting
# 2025-01-12: converted

API Methods¶

1. Create State Activity Record¶

Method: create(vals, context)

Creates a new state activity record (typically called automatically, not manually).

Parameters:

vals = {
    "lead_id": int,        # Required: Lead ID
    "state": str,          # State entered
    "date": str            # Date in YYYY-MM-DD format
}

Returns: int - New state activity record ID

Example:

# Manual creation (normally automatic)
state_activ_id = get_model("sale.lead.state.activ").create({
    "lead_id": 123,
    "state": "qualified",
    "date": "2025-01-05"
})

2. Query State History¶

Method: search(condition, context) and search_browse(condition, context)

Query state activity records for analysis.

Example:

# Find all state transitions for a specific lead
lead_history = get_model("sale.lead.state.activ").search_browse([
    ["lead_id", "=", lead_id]
], order="date asc")

for record in lead_history:
    print(f"{record.date}: {record.state}")

Search Functions¶

Search by Lead¶

# Get all state changes for specific lead
condition = [["lead_id", "=", lead_id]]

Search by State¶

# Find all transitions into "converted" state
condition = [["state", "=", "converted"]]

Search by Date Range¶

# Find state changes in date range
condition = [
    ["date", ">=", "2025-01-01"],
    ["date", "<=", "2025-01-31"]
]

Analytics and Reporting¶

Calculate Time in Each State¶

def calculate_time_in_state(lead_id):
    """Calculate days spent in each state for a lead"""

    # Get all state transitions ordered by date
    states = get_model("sale.lead.state.activ").search_browse(
        [["lead_id", "=", lead_id]],
        order="date asc"
    )

    time_in_state = {}

    for i, state in enumerate(states):
        state_name = state.state
        start_date = datetime.strptime(state.date, "%Y-%m-%d")

        # Calculate end date (next state or today)
        if i < len(states) - 1:
            end_date = datetime.strptime(states[i+1].date, "%Y-%m-%d")
        else:
            end_date = datetime.now()

        # Calculate days in this state
        days = (end_date - start_date).days

        # Accumulate if state was entered multiple times
        if state_name in time_in_state:
            time_in_state[state_name] += days
        else:
            time_in_state[state_name] = days

    return time_in_state

# Usage
result = calculate_time_in_state(123)
# Returns: {"new": 5, "qualified": 3, "pending_initial_meeting": 7, "converted": 0}

Conversion Velocity Analysis¶

def analyze_conversion_velocity(start_date, end_date):
    """Analyze average time from new to converted"""

    # Find all leads converted in date range
    converted_states = get_model("sale.lead.state.activ").search_browse([
        ["state", "=", "converted"],
        ["date", ">=", start_date],
        ["date", "<=", end_date]
    ])

    velocities = []

    for conv_state in converted_states:
        # Find first "new" state for this lead
        first_state = get_model("sale.lead.state.activ").search_browse([
            ["lead_id", "=", conv_state.lead_id.id],
            ["state", "=", "new"]
        ], order="date asc", limit=1)

        if first_state:
            start = datetime.strptime(first_state[0].date, "%Y-%m-%d")
            end = datetime.strptime(conv_state.date, "%Y-%m-%d")
            days = (end - start).days
            velocities.append(days)

    if velocities:
        avg_days = sum(velocities) / len(velocities)
        return {
            "avg_conversion_days": avg_days,
            "min_conversion_days": min(velocities),
            "max_conversion_days": max(velocities),
            "total_conversions": len(velocities)
        }

    return None

# Usage
stats = analyze_conversion_velocity("2025-01-01", "2025-01-31")
# Returns: {"avg_conversion_days": 14.5, "min_conversion_days": 7, ...}

Pipeline Stage Distribution¶

def get_current_stage_distribution():
    """Get count of leads currently in each stage"""

    # Get all leads
    all_leads = get_model("sale.lead").search([])

    distribution = {}

    for lead_id in all_leads:
        # Get most recent state for this lead
        recent_state = get_model("sale.lead.state.activ").search_browse(
            [["lead_id", "=", lead_id]],
            order="date desc",
            limit=1
        )

        if recent_state:
            state_name = recent_state[0].state
            distribution[state_name] = distribution.get(state_name, 0) + 1

    return distribution

# Usage
pipeline = get_current_stage_distribution()
# Returns: {"new": 45, "qualified": 23, "pending_initial_meeting": 12, ...}

Model	Relationship	Description
`sale.lead`	Many2One	Parent lead being tracked

Common Use Cases¶

Use Case 1: Lead Journey Timeline¶

# Display complete timeline of a lead's journey
lead = get_model("sale.lead").browse(lead_id)

print(f"Timeline for Lead: {lead.title}")
print("-" * 50)

for state_record in lead.state_activ:
    print(f"{state_record.date}: {state_record.state}")

# Output:
# Timeline for Lead: Enterprise CRM Inquiry
# --------------------------------------------------
# 2025-01-01: new
# 2025-01-03: qualified
# 2025-01-05: pending_initial_meeting
# 2025-01-10: waiting_for_client
# 2025-01-15: preparing_quotation
# 2025-01-20: converted

Use Case 2: Identify Stalled Leads¶

# Find leads stuck in "waiting_for_client" for more than 7 days
from datetime import datetime, timedelta

threshold_date = (datetime.now() - timedelta(days=7)).strftime("%Y-%m-%d")

# Get all recent "waiting_for_client" states
stalled_states = get_model("sale.lead.state.activ").search_browse([
    ["state", "=", "waiting_for_client"],
    ["date", "<=", threshold_date]
])

stalled_leads = []

for state_record in stalled_states:
    # Check if lead is still in this state (no newer state exists)
    newer_states = get_model("sale.lead.state.activ").search([
        ["lead_id", "=", state_record.lead_id.id],
        ["date", ">", state_record.date]
    ])

    if not newer_states:
        # Lead is still in waiting_for_client state
        days_stalled = (datetime.now() - datetime.strptime(state_record.date, "%Y-%m-%d")).days
        stalled_leads.append({
            "lead_id": state_record.lead_id.id,
            "lead_title": state_record.lead_id.title,
            "days_stalled": days_stalled
        })

print(f"Found {len(stalled_leads)} stalled leads")
for lead in stalled_leads:
    print(f"  {lead['lead_title']}: {lead['days_stalled']} days")

Use Case 3: Performance Dashboard Metrics¶

# Calculate key performance metrics for sales dashboard
def get_lead_metrics(start_date, end_date):
    """Generate comprehensive lead performance metrics"""

    metrics = {}

    # 1. New leads in period
    new_leads = get_model("sale.lead.state.activ").search([
        ["state", "=", "new"],
        ["date", ">=", start_date],
        ["date", "<=", end_date]
    ])
    metrics["new_leads_count"] = len(new_leads)

    # 2. Conversions in period
    conversions = get_model("sale.lead.state.activ").search([
        ["state", "=", "converted"],
        ["date", ">=", start_date],
        ["date", "<=", end_date]
    ])
    metrics["conversions_count"] = len(conversions)

    # 3. Conversion rate
    if metrics["new_leads_count"] > 0:
        metrics["conversion_rate"] = (metrics["conversions_count"] / metrics["new_leads_count"]) * 100
    else:
        metrics["conversion_rate"] = 0

    # 4. Lost leads in period
    lost_leads = get_model("sale.lead.state.activ").search([
        ["state", "=", "lost"],
        ["date", ">=", start_date],
        ["date", "<=", end_date]
    ])
    metrics["lost_leads_count"] = len(lost_leads)

    # 5. Voided (spam) leads
    voided_leads = get_model("sale.lead.state.activ").search([
        ["state", "=", "voided"],
        ["date", ">=", start_date],
        ["date", "<=", end_date]
    ])
    metrics["voided_leads_count"] = len(voided_leads)

    return metrics

# Usage
monthly_metrics = get_lead_metrics("2025-01-01", "2025-01-31")
print(f"New Leads: {monthly_metrics['new_leads_count']}")
print(f"Conversions: {monthly_metrics['conversions_count']}")
print(f"Conversion Rate: {monthly_metrics['conversion_rate']:.1f}%")
print(f"Lost Leads: {monthly_metrics['lost_leads_count']}")
print(f"Voided Leads: {monthly_metrics['voided_leads_count']}")

Best Practices¶

1. Don't Delete Historical Records¶

# Bad: Deleting state history loses valuable analytics data
get_model("sale.lead.state.activ").delete([old_record_id])

# Good: Keep all historical records for complete audit trail
# Query with date filters if you only need recent data
recent_states = get_model("sale.lead.state.activ").search([
    ["date", ">=", "2025-01-01"]
])

2. Use Ordered Queries for Timeline Analysis¶

# Bad: Unordered query makes timeline analysis difficult
states = get_model("sale.lead.state.activ").search_browse([["lead_id", "=", lead_id]])

# Good: Always order by date when analyzing progression
states = get_model("sale.lead.state.activ").search_browse(
    [["lead_id", "=", lead_id]],
    order="date asc"  # Chronological order
)

3. Aggregate for Performance¶

# Bad: Loading all records for simple counts
all_records = get_model("sale.lead.state.activ").search_browse([])
new_count = len([r for r in all_records if r.state == "new"])

# Good: Use search with conditions
new_count = len(get_model("sale.lead.state.activ").search([["state", "=", "new"]]))

# Better: Use database aggregation for large datasets
db = database.get_connection()
result = db.query("SELECT state, COUNT(*) as count FROM sale_lead_state_activ GROUP BY state")

Performance Tips¶

1. Index on Common Query Fields¶

The model should have database indexes on: - lead_id (for lead-specific queries) - state (for state-based analytics) - date (for date range queries)

2. Limit Historical Queries¶

# When only recent data is needed, use date filters
recent_activities = get_model("sale.lead.state.activ").search([
    ["date", ">=", "2024-01-01"]  # Only last year
])

3. Batch Analytics Processing¶

# For large-scale analytics, use direct SQL
db = database.get_connection()
query = """
    SELECT
        lead_id,
        state,
        date,
        LEAD(date) OVER (PARTITION BY lead_id ORDER BY date) as next_date
    FROM sale_lead_state_activ
    WHERE date >= %s AND date <= %s
    ORDER BY lead_id, date
"""
results = db.query(query, start_date, end_date)

Troubleshooting¶

"No state activity records found"¶

Cause: State activity records may not be automatically created if lead state changes don't trigger creation Solution: Verify that sale.lead model properly creates state activity records on state changes, or manually backfill historical data

"Duplicate state entries on same date"¶

Cause: Multiple state changes on the same day, or manual record creation Solution: This is normal behavior; use time component if finer granularity is needed (modify date field to datetime)

"Incomplete timeline for lead"¶

Cause: State activity tracking was not enabled from the beginning, or records were deleted Solution: Historical data cannot be recovered; ensure future state changes are tracked properly

Integration Points¶

Internal Modules¶

sale.lead: Parent model being tracked
Reporting dashboards: Source data for analytics widgets
Workflow automation: Triggers based on time-in-state
Sales analytics: Pipeline velocity and conversion metrics

Version History¶

Last Updated: 2025-01-05 Model Version: sale_lead_state_activ.py Framework: Netforce

Additional Resources¶

Sale Lead Documentation: sale.lead
Lead Analytics Guide: Dashboard configuration
Workflow Automation: Time-based triggers

Support & Feedback¶

For issues or questions about this module: 1. Check parent sale.lead model for state change logic 2. Verify state activity records are being created on state transitions 3. Review database indexes for query performance 4. Use date filters to improve query performance on large datasets

This documentation is generated for developer onboarding and reference purposes.