The Leads Report Engine

An analytics platform with 76+ columns and a powerful, modular filtering system.

Modular Filter Architecture

The report's power comes from its modular design. Each of the 8 filter categories is a self-contained module that plugs into the main query, allowing for complex, multi-faceted data analysis. All filters are combined with AND logic, while selections within a single filter use OR logic.

📍 Location

Hierarchical State -> County filtering.

🏭 Industry

Solar, Roofing, HVAC, etc.

🚦 Campaign Status

Active, Paused, Inactive, etc.

📢 Advertiser

Google, Meta, Microsoft, etc.

🌐 Origin

Direct, Referral, Campaign, etc.

💻 Platform

Organic, Paid, etc.

🏷️ Lead Type

Verified, Unverified, Email-Only.

👥 User Relation

Account Manager, Sales Rep.

Advanced Feature: Cascading Filters

The Location filter is a prime example of the system's advanced capabilities. It uses a hierarchical, cascading structure where the available County options dynamically update based on the selected State(s). This is achieved via a dedicated API endpoint that fetches relevant child options, ensuring users only see valid choices.

Behind the Scenes: Query Building

The system uses specialized builders to construct different parts of the final report query. Filters are applied to each builder, which are then combined to generate the complete dataset. This separation of concerns allows for optimized performance and easier maintenance.

Leads Query Builder

Gathers core lead data from consumer and product tables.

Cost Query Builder

Aggregates advertising costs from daily ad cost tables.

Campaign Query Builder

Collects campaign and company relationship data.

Example: Combined Filter Query

$builder->whereln('l.state_abbr', ['CA', 'TX']) ->whereln('l.county', ['Los Angeles', 'Harris']) ->whereln('i.name', ['Solar', 'Roofing']);

State Management & User Experience

Making Reports Usable

  • 🔗
    URL Serialization: All selected filters, columns, and date ranges are encoded into the URL, making any report fully bookmarkable and shareable.
  • 💾
    User Presets: Users can save their complex filter and column configurations as named presets for one-click access in the future.
  • ⚡
    Performance: The frontend uses lazy loading for filter options, debounced search, and clear loading indicators to ensure a smooth user experience, even with large datasets.