Lead Compliance Report

The Lead Compliance Report tracks lead data quality and agent compliance with lead management best practices. This report identifies missing or incomplete data on leads, helping managers ensure agents are following proper procedures for lead handling, contact information collection, and documentation.

Key Features

• Agent-Level Tracking: View compliance metrics for each agent individually
• Customizable Date Range: Analyze any time period based on lead creation date
• Clickable Metrics: Each compliance metric links directly to filtered lead views
• CSV Export: Download detailed compliance data for further analysis
• Real-Time Data: Reflects current state of lead data (not historical snapshots)
• OTA Detection: Identifies leads using OTA email addresses

Report Metrics

Total Leads

Total number of leads created by each agent during the reporting period.

Calculation:

• Count of all non-archived leads created by the agent
• Based on lead creation date within selected date range
• Excludes archived leads

Use Cases:

• Baseline for calculating compliance percentages
• Measure agent lead volume
• Identify high-volume agents who may need additional support

---

Missed Follow-Ups

Leads with follow-up dates in the past that have not been closed (not won or lost).

Criteria:

• Lead has a follow-up date (followup_at) set
• Follow-up date is in the past (before current date/time)
• Lead is still open (not closed - closed_at is NULL)
• Lead is not archived

Why This Matters:

• Indicates agents are missing scheduled follow-ups
• Can lead to lost opportunities and poor customer experience
• Shows time management and calendar adherence issues

Calculation:

• Count of leads where followup_at < NOW() AND closed_at IS NULL
• Displayed as count and linked to filtered lead view

Link Functionality:

• Click count to view all missed follow-up leads for that agent
• URL includes date range and agent filter
• Parameter: followup=true

Best Practices:

• Target: 0% missed follow-ups
• Agents should close leads or update follow-up dates daily
• Set realistic follow-up dates based on expected response times

---

Missing Email

Leads where the associated contact has no primary email address.

Criteria:

• Contact has no primary email (contact_emails.is_primary = true is NULL)
• Lead is not archived

Why This Matters:

• Email is essential for communication and marketing
• Prevents automated email campaigns and follow-ups
• Indicates incomplete contact data collection
• May represent incomplete lead qualification

Calculation:

• Count of leads where primary email does not exist
• Joins to contact_emails table to check for primary email
• Displayed as count and linked to filtered lead view

Link Functionality:

• Click count to view all leads with missing emails for that agent
• URL parameter: missEmail=true

Best Practices:

• Target: <5% missing emails
• Agents should collect email during initial contact
• Use email validation to ensure correct format
• Mark email as required field in lead forms

---

Missing Mobile Phone

Leads where the associated contact has no mobile phone number.

Criteria:

• Contact has no mobile phone (contacts.mobile_phone is NULL)
• Lead is not archived

Why This Matters:

• Mobile phone enables SMS communication and follow-ups
• Required for SMS opt-in and text marketing
• Limits communication channel options
• May indicate incomplete contact information

Calculation:

• Count of leads where contacts.mobile_phone IS NULL
• Joins to contacts table to check mobile phone field
• Displayed as count and linked to filtered lead view

Link Functionality:

• Click count to view all leads with missing mobile phones for that agent
• URL parameter: missMobile=true

Best Practices:

• Target: <10% missing mobile (higher than email due to guest preferences)
• Collect mobile during qualification
• Explain benefits of SMS updates (arrival details, special offers)
• Respect guests who prefer not to provide mobile

---

Missing SMS Opt-In

Leads where the contact has not opted in to receive SMS messages.

Criteria:

• Contact has no active SMS opt-in record
• No opt-in record where opt_in_at IS NOT NULL AND opt_out_at IS NULL
• Lead is not archived

Why This Matters:

• Required for legal SMS marketing compliance (TCPA)
• Prevents use of SMS for marketing and automated follow-ups
• Limits communication effectiveness
• May indicate missed opportunity to gain permission

Calculation:

• Count of leads where no active SMS opt-in exists
• Joins to sms_opt_ins table checking for active opt-in
• Active = opt_in_at IS NOT NULL AND opt_out_at IS NULL
• Displayed as count (note: typically no direct link for this metric)

Important Notes:

• SMS opt-in must be explicit and documented
• Cannot send marketing SMS without valid opt-in
• Opt-out requests must be honored immediately
• Compliance is legally required (TCPA regulations)

Best Practices:

• Target: 40-60% opt-in rate (industry standard)
• Explain value proposition before asking for opt-in
• Use double opt-in for verification
• Make opt-in easy during booking process
• Never pre-check opt-in boxes (must be user action)

---

Missing Source

Leads without a source attribution.

Criteria:

• Lead has no source (source_id is NULL)
• Lead is not archived

Why This Matters:

• Source tracking is essential for marketing ROI analysis
• Prevents understanding which channels drive bookings
• Limits ability to optimize marketing spend
• Indicates incomplete lead creation process

Calculation:

• Count of leads where source_id IS NULL
• Displayed as count and linked to filtered lead view

Link Functionality:

• Click count to view all leads with missing sources for that agent
• URL parameter: missSource=true

Best Practices:

• Target: 0% missing sources
• Make source a required field in lead creation
• Provide clear source options for agents
• Train agents on proper source attribution
• Default to "Direct" or "Phone" if unclear, not NULL

---

Missing Lost Reason

Lost leads (marked as lost) that don't have a lost reason documented.

Criteria:

• Lead is marked as lost (lost_at IS NOT NULL)
• Lead has no lost reason (lost_reason_id IS NULL)
• Lead is not archived

Why This Matters:

• Lost reasons provide insights into why bookings don't convert
• Essential for identifying patterns and improvement areas
• Required for accurate conversion analysis
• Helps train agents on objection handling

Calculation:

• Count of leads where lost_at IS NOT NULL AND lost_reason_id IS NULL
• Displayed as count and linked to filtered lead view

Link Functionality:

• Click count to view all lost leads missing reasons for that agent
• URL parameter: missLostReason=true

Best Practices:

• Target: 0% missing lost reasons
• Make lost reason required when marking lead as lost
• Provide comprehensive lost reason options
• Include free-text field for additional context
• Review lost reasons monthly to identify trends

Common Lost Reasons:

• Price too high
• Dates not available
• Found another property
• Not ready to book
• No response from guest

---

Missing Lost Reason Detail

Lost leads that have a lost reason but are missing the detailed lost reason.

Criteria:

• Lead is marked as lost (lost_at IS NOT NULL)
• Lead has no lost reason detail (lost_reason_detail_id IS NULL)
• Lead is not archived

Why This Matters:

• Lost reason details provide granular insights beyond primary reason
• Enables deeper analysis of loss patterns
• Helps identify specific issues (e.g., which competitor, which price objection)
• Improves training specificity

Calculation:

• Count of leads where lost_at IS NOT NULL AND lost_reason_detail_id IS NULL
• Displayed as count and linked to filtered lead view

Link Functionality:

• Click count to view all lost leads missing reason details for that agent
• URL parameter: missLostDetail=true

Best Practices:

• Target: <10% missing lost details (some reasons may not require detail)
• Encourage agents to add details when available
• Make detail required for specific primary reasons
• Use details to refine competitive analysis

Example Lost Reason Details:

• Lost Reason: "Price too high" → Detail: "Found Airbnb for $200 less"
• Lost Reason: "Dates not available" → Detail: "Peak summer weekend"
• Lost Reason: "Found another property" → Detail: "Competitor beachfront"

---

Won Without Reservation

Leads marked as won (booked) but without a linked reservation record.

Criteria:

• Lead is marked as won (won_at IS NOT NULL)
• Lead has no reservation (reservation_id IS NULL)
• Lead is not archived

Why This Matters:

• Data integrity issue indicating process breakdown
• Won leads should always have reservations
• Prevents accurate revenue tracking
• May indicate manual process errors or system issues

Calculation:

• Count of leads where won_at IS NOT NULL AND reservation_id IS NULL
• Displayed as count (note: typically no direct link for this metric)

Why This Happens:

• Agent manually marked lead as won without creating reservation
• Reservation created outside of lead workflow
• System error or integration failure
• Lead won through different system

Best Practices:

• Target: 0% won without reservation
• Investigate all instances immediately
• Link reservation retroactively if it exists
• Train agents on proper booking workflow
• Consider making reservation creation required for marking won

---

Missing Estimated Value

Leads without an estimated value or with estimated value of $0 or less.

Criteria:

• Lead has no estimated value (estimated_value IS NULL)
• OR lead has estimated value <= $0
• Lead is not archived

Why This Matters:

• Estimated value is used for pipeline forecasting
• Helps prioritize high-value leads
• Required for accurate revenue projections
• Indicates incomplete lead qualification

Calculation:

• Count of leads where estimated_value IS NULL OR estimated_value <= 0
• Displayed as count (note: typically no direct link for this metric)

Best Practices:

• Target: <15% missing value (some early-stage leads may not have estimates)
• Encourage agents to estimate value during qualification
• Use average booking value as starting point
• Update estimate as lead progresses
• Make estimated value required for high-priority leads

How to Calculate Estimated Value:

• Number of nights × estimated nightly rate
• Similar property average booking value
• Historical booking value for repeat guests
• Seasonal average for date range requested

---

OTA Email Addresses

Leads where the contact's primary email is from an Online Travel Agency (OTA) platform.

Criteria:

• Primary email contains:
  • @airbnb.com
  • @booking.com
  • @vrbo.com
• Lead is not archived
• Case-insensitive match

Why This Matters:

• OTA emails are often masked/proxy addresses
• May not be guest's real email
• Can bounce or stop working after booking
• Limits direct communication and marketing
• Indicates booking came through OTA channel

Calculation:

• Count of leads where primary email ILIKE '%airbnb.com' OR '%booking.com' OR '%vrbo.com'
• Displayed as count (note: typically no direct link for this metric)

Why This Happens:

• Booking originated on OTA platform
• OTA provides masked email for privacy
• Guest hasn't provided direct email
• OTA policy requires using platform email

Best Practices:

• Target: Track as percentage, no specific goal (depends on channel mix)
• Attempt to collect guest's personal email during communication
• Use OTA messaging system for initial contact
• Explain benefits of providing direct email (special offers, easier communication)
• Track OTA percentage to understand channel dependence

---

Accessing the Report

1. Navigate to Reports in the main menu
2. Select Lead Compliance from the report type dropdown
3. Set the Start Date and End Date for the reporting period
   • Report is based on lead creation date (created_at)
4. Click Run Report to generate the report
5. Review agent-by-agent compliance metrics
6. Click on any metric count to view the filtered lead list
7. Click Export CSV to download the data

Report Layout

Columns Displayed

The report displays one row per agent with the following columns:

1. Agent Name: Agent's full name (first name + last name)
2. Total Leads: Count of all leads created by agent in period
3. Missed Follow-Ups: Count with clickable link
4. Missing Email: Count with clickable link
5. Missing Mobile: Count with clickable link
6. Missing SMS Opt-In: Count (no link)
7. Missing Source: Count with clickable link
8. Missing Lost Reason: Count with clickable link
9. Missing Lost Detail: Count with clickable link
10. Won Without Res: Count (no link)
11. Missing Value: Count (no link)
12. OTA Emails: Count (no link)

Sorting

• Default sort: By agent name (alphabetical)
• Can be sorted by any column (click column header)
• Useful sorts:
  • By Total Leads (identify high-volume agents)
  • By Missed Follow-Ups (identify agents needing time management help)
  • By Missing Email (identify data collection issues)

---

Link Functionality

Clickable Metrics

Several metrics provide clickable links that open the Call Center Leads view with pre-applied filters:

URL Pattern:

/admin/call-center/leads?dateStart=[START]&dateEnd=[END]&agentId=[AGENT_ID]&[FILTER]=true

Available Filters:

• followup=true - Shows missed follow-up leads
• missEmail=true - Shows leads with missing email
• missMobile=true - Shows leads with missing mobile phone
• missSource=true - Shows leads with missing source
• missLostReason=true - Shows lost leads with missing reason
• missLostDetail=true - Shows lost leads with missing detail

Using the Links:

1. Click any linked count in the report
2. Opens Call Center Leads view in new tab/window
3. Filtered to specific agent and compliance issue
4. Date range matches report parameters
5. Agent can immediately take action on non-compliant leads

Example: If Agent "Sarah Johnson" has 15 missed follow-ups:

• Click the "15" in the Missed Follow-Ups column
• Opens: /admin/call-center/leads?dateStart=2024-01-01&dateEnd=2024-01-31&agentId=42&followup=true
• Shows all 15 leads with past follow-up dates for Sarah
• Sarah can update follow-up dates or close leads immediately

---

Export Functionality

CSV Export

Unlike the Executive Summary (which exports to PDF), the Lead Compliance Report exports to CSV for data analysis and manipulation.

CSV Contents:

• One row per agent
• All metrics as separate columns
• Agent ID included for cross-referencing
• Column headers match report display

File Naming:

• Format: Lead_Compliance_Report_YYYY-MM-DD.csv
• Date represents report generation date

Use Cases for CSV:

• Import into Excel for further analysis
• Calculate compliance percentages (e.g., Missed Follow-Ups ÷ Total Leads)
• Create charts and visualizations
• Track compliance trends over time
• Combine with other data sources

---

Interpreting Results

Compliance Percentage Calculations

While the report shows raw counts, you can calculate percentages to understand compliance:

Formula: (Non-Compliant Count ÷ Total Leads) × 100 = Non-Compliance %

Examples:

Agent: Sarah Johnson

• Total Leads: 150
• Missed Follow-Ups: 12
• Missed Follow-Up Rate: (12 ÷ 150) × 100 = 8%

Agent: Mike Chen

• Total Leads: 200
• Missing Email: 30
• Missing Email Rate: (30 ÷ 200) × 100 = 15%

Red Flags to Watch For

High Missed Follow-Ups (>10%)

• Agent may be overwhelmed with lead volume
• Time management issues
• Calendar not being used effectively
• May need workload rebalancing

High Missing Email (>10%)

• Agent not collecting complete contact information
• Skipping qualification steps
• May need training on data collection
• Could indicate low-quality leads

High Missing Source (>5%)

• Agent not documenting lead origin
• May indicate manual lead entry issues
• Prevents marketing attribution
• Requires process reinforcement

Won Without Reservation (>0%)

• Critical data integrity issue
• Investigate immediately
• May indicate system problems
• Requires manual correction

Benchmark Targets

Based on industry best practices:

Metric	Target	Acceptable	Needs Attention
Missed Follow-Ups	0%	<5%	>10%
Missing Email	0%	<5%	>10%
Missing Mobile	<5%	<15%	>25%
Missing SMS Opt-In	N/A (40-60% opt-in is normal)
Missing Source	0%	<2%	>5%
Missing Lost Reason	0%	0%	>0%
Missing Lost Detail	<5%	<15%	>25%
Won Without Res	0%	0%	>0%
Missing Value	<10%	<20%	>30%
OTA Emails	N/A (depends on channel mix)

---

Use Cases

Performance Management

Individual Coaching:

• Review report with each agent monthly
• Identify specific compliance gaps
• Create improvement action plans
• Track progress over time
• Recognize agents with high compliance

Training Needs:

• Identify common gaps across team
• Develop targeted training programs
• Address systemic process issues
• Improve data collection procedures

Workload Balancing:

• High missed follow-ups may indicate overload
• Redistribute leads if needed
• Adjust lead routing rules
• Monitor after changes

Data Quality Improvement

System Improvements:

• Make fields required based on high missing rates
• Improve form validation
• Add data quality checks
• Enhance user interface clarity

Process Refinement:

• Update lead intake procedures
• Clarify data collection requirements
• Simplify workflows
• Document best practices

Marketing Attribution

Source Tracking:

• Ensure all leads have proper source attribution
• Enables accurate marketing ROI
• Identify channel performance
• Optimize marketing spend

OTA Channel Analysis:

• Track OTA email percentage
• Understand channel mix
• Measure direct booking success
• Plan channel strategy

Compliance & Legal

TCPA Compliance:

• Monitor SMS opt-in collection
• Ensure proper documentation
• Avoid compliance violations
• Protect against legal issues

Data Completeness:

• Ensure contact information is collected
• Meet data retention requirements
• Support customer service needs
• Enable effective communication

---

Best Practices

Regular Review Cadence

Weekly:

• Managers review for critical issues (Won Without Res, Missed Follow-Ups >20%)
• Quick check on new agents
• Immediate intervention if needed

Monthly:

• Full team review
• One-on-one coaching sessions
• Trend analysis
• Set improvement goals

Quarterly:

• Comprehensive compliance audit
• Policy and procedure review
• Training program assessment
• System improvement planning

Agent Accountability

Individual Goals:

• Set specific compliance targets for each agent
• Example: "Reduce missed follow-ups to <5% by next month"
• Track progress in monthly reviews
• Tie to performance evaluations

Team Goals:

• Overall team compliance targets
• Celebrate team improvements
• Share best practices from top performers
• Create healthy competition

System Improvements

Make Critical Fields Required:

• Email (if missing rate >5%)
• Source (always required)
• Lost Reason (when marking lead as lost)
• Estimated Value (for qualified leads)

Add Validation:

• Email format validation
• Phone number format validation
• Source dropdown (prevent free text)
• Lost reason required before lost date

Improve UX:

• Clear field labels
• Helpful tooltips
• Inline validation
• Smart defaults

Training Materials

Create Job Aids:

• "How to Properly Document Lost Leads"
• "Contact Information Collection Best Practices"
• "Source Attribution Quick Reference"
• "Follow-Up Management Guide"

Onboarding Checklist:

• Include compliance expectations
• Review each metric and why it matters
• Practice scenarios
• Regular check-ins during first 90 days

---

Troubleshooting

Report Shows No Data

Possible Causes:

• No leads created in selected date range
• All leads are archived
• Database connection issue

Solutions:

• Verify date range includes lead creation dates
• Check if leads exist but are archived
• Expand date range
• Contact support if persists

High Compliance Issues Across All Agents

Possible Causes:

• System not enforcing required fields
• Recent process change
• New lead source without proper setup
• Integration issue

Solutions:

• Review system field requirements
• Check recent configuration changes
• Verify lead source integrations
• Review onboarding materials

Links Not Working

Possible Causes:

• Permission issues
• Browser blocking pop-ups
• URL encoding issue

Solutions:

• Verify user has access to Call Center Leads
• Enable pop-ups for the domain
• Try copying URL and pasting in new tab
• Clear browser cache

Won Without Reservation Issues

Possible Causes:

• Manual lead closing without reservation
• Reservation created in different system
• Integration failure
• Old leads before reservation linking implemented

Solutions:

• Create reservation and link to lead
• Mark lead as lost if no reservation should exist
• Document issue for system improvements
• Review agent training on booking workflow

---

Technical Notes

Data Freshness

The Lead Compliance Report queries current lead data in real-time:

• Not a historical snapshot
• Shows current state of leads created in date range
• Lead status may have changed since creation
• Contact information reflects latest data

Example: If you run a report for January leads in March:

• Will show current contact information (may have been updated in February)
• Will show current lost reasons (may have been added later)
• Missed follow-ups are calculated as of "now" (report run time)

Query Performance

The report uses a single optimized SQL query:

• Left joins to contact tables
• Subquery for SMS opt-ins (finds most recent)
• Groups by user_id (agent)
• Filters by company and date range
• Excludes archived leads

Performance Considerations:

• Larger date ranges take longer
• Companies with many agents may be slower
• Contact table joins add overhead
• Generally completes in <5 seconds for typical datasets

URL Generation

Report generates clickable URLs for supported metrics:

• Base URL includes date range
• Agent ID appended as parameter
• Specific filter flag added (e.g., followup=true)
• URL encoding handles special characters
• Only generated for agents with user_id (not "Unknown")

---

Related Reports

Executive Summary Report:

• High-level performance metrics
• Agent conversion rates
• Revenue by agent
• Complements compliance view

Call Center Analytics:

• Lead source performance
• Conversion funnel analysis
• Time-to-close metrics
• Pairs well with source compliance

Agent Performance Reports:

• Individual agent metrics
• Lead volume and conversion
• Revenue generation
• Use compliance data for coaching

---

Frequently Asked Questions

Q: Why do some agents show as "Unknown"?

A: Leads may be assigned to deleted users or have no user_id. These are grouped under "Unknown" and cannot be filtered (no agent ID).

Q: Can I filter the report to specific agents?

A: The current report shows all agents. Use the CSV export and filter in Excel, or use the clickable links to view specific agents' non-compliant leads.

Q: Why is my OTA email count so high?

A: If you receive many bookings through Airbnb, VRBO, or Booking.com, these platforms provide proxy emails. This is normal. Focus on collecting direct emails during guest communication.

Q: Should missed follow-ups ever be acceptable?

A: Target is 0%, but <5% is acceptable due to edge cases (lead just came in, agent on PTO, etc.). >10% indicates systemic issues.

Q: How often should I review this report?

A: Managers should review weekly for critical issues and monthly for full team review. Share results with agents monthly.

Q: Can I see historical trends?

A: Run the report for different date ranges and export CSVs. Compare month-over-month. Consider creating a spreadsheet to track trends over time.

Q: What if an agent has 0% compliance issues?

A: Excellent! Recognize and reward this agent. Ask them to share best practices with the team.

Q: Why doesn't every metric have a clickable link?

A: Some metrics (SMS Opt-In, Won Without Res, Missing Value, OTA Emails) don't have corresponding filters in the Call Center Leads view yet. These may be added in future updates.