The Challenge: Scalability at Massive Volume

Handling digital marketing automation at a scale of over a billion emails daily requires more than just delivery; it requires intelligent audience identification. To achieve this, an engagement scoring system is used to evaluate sending, opening, and clicking behaviors over time. These behaviors are categorized into "engagement buckets" that power downstream targeting and personalization.

Originally, this system relied on a traditional time-window summarization approach. Email activity was ingested and summarized across multiple rolling time windows, specifically 15, 30, 60, 90, and 180 days. For every unique email ID, the system maintained records of daily distinct events to avoid over-representation.

However, this traditional architecture had a fundamental drawback: it required repeated, large-scale summarization over historical data. This translated into high compute costs, excessive storage requirements, and limited scalability as the data volume grew.

The Innovation: Bitmask and ClickHouse-Based Architecture

To address these inefficiencies, the Netcore Research Lab re-architected the system with a primary focus on efficiency and cost reduction. Their innovative approach resulted in a 75% reduction in infrastructure costs. The core shift led by the team involved moving away from the traditional method of storing engagement as individual rows; instead, they pioneered a method of representing historical data using bitsets.

Modelling with Bitsets

For every email ID of a given client, the system now maintains three 256-bit integers, one for each event type:

• Sent activity
• Open activity
• Click activity

Each bit within the integer represents one day of history, tracking from the current day (bit 0) back to 256 days in the past. This approach compacts 256 days of engagement history into a single integer per event type.

Streamlined Daily Updates

The daily incremental process is now a constant-time bit operation rather than a large table scan:

• Shifting: The existing bitset is shifted left by one bit to age the history by one day.
• Updating: The least significant bit (bit 0) is set to 1 if the event occurred that day, or 0 otherwise.

Performance Gains and Technical Impact

By utilizing bitmask and ClickHouse-based modeling, calculating rolling windows becomes trivial. To find a 15-day open count, the system simply sums the last 15 bits of the "open" integer. This method is highly CPU and memory-efficient.

Why This Approach Works

• Speed: It is orders of magnitude faster than row-level aggregation.
• Storage Efficiency: It eliminates the need to store all raw historical events in massive tables for daily summarization.
• Optimized Execution: Using a database engine with native support for 256-bit integers and fast bitwise operations allowed the entire daily workflow to be executed in a single query.

The results were dramatic. A complete refresh for all users consistently finishes in approximately 10 minutes, utilizing only 1/4th of the hardware required by the previous system.

Final Thoughts

This transition reinforced a vital lesson in data engineering: scaling analytics is often about finding better representations of data rather than simply using bigger machines. By rethinking the data model, the architecture achieved massive cost reductions and faster daily processing while remaining simple and scalable.