Using 100M+ Historical Clicks to Predict CPC/CPA/CPM for Volume Buying

Introduction

SulacoTec operates in the click-brokering layer of the ad stack: ingesting bid requests, buying traffic in bulk, and reselling performance to downstream customers. Historically, bid prices were tuned using heuristics and small samples of performance data, which made it hard to price large-volume deals with confidence.

As a Senior Software Engineer, I was tasked with turning a large historical click-processing database into a decision engine. The goal was to predict reasonable CPC, CPA, and CPM ranges for given traffic segments so the business could negotiate and execute bulk click purchases with more predictable margins.

Context

Over time, SulacoTec accumulated ~100 million rows of historical data across:

• Bid requests and clearing prices.
• Clicks, conversions, and post-click outcomes.
• Traffic sources, geos, devices, and creative metadata.

However, this data mostly sat in operational tables used for reporting, not as an input to predictive decisions. There was a clear opportunity: use the historical record to estimate expected performance for new campaigns and traffic types.

Problem

The business needed to answer a seemingly simple question:

“If we buy N clicks from this traffic source and geo, what CPC/CPA/CPM should we target to stay profitable for a given customer?”

In practice, this meant:

• Dealing with noisy, sparse, and sometimes inconsistent historical data.
• Capturing non-obvious relationships (e.g., device × geo × time-of-day).
• Delivering answers quickly enough to be useful in negotiations and campaign setup.

Constraints & Requirements

• Large dataset: ~100M historical rows had to be processed efficiently without blocking operational systems.
• Laravel-first: the prediction system needed to integrate with an existing Laravel control panel used by account managers.
• Explainability: outputs had to be understandable; account teams needed to see how predictions were derived.
• Pragmatic, not academic: performance and stability were more important than using the “fanciest” machine learning model.

Implementation Highlights

1) Data modelling & feature engineering

• Consolidated historical logs into an analysis-ready schema grouped by key segments: traffic source, geo, device type, and placement category.
• Computed aggregated metrics such as historical CPC, CPA, CTR, and conversion rate for each segment and time window.
• Cleaned and normalised inconsistent records (e.g., missing conversions, duplicate clicks) to reduce noise in the predictions.

2) Prediction logic

• Implemented segment-level models that estimated expected value per click and per impression, then derived suggested CPC/CPM ranges.
• Incorporated configurable safety margins so pricing could be conservative for new or volatile segments.
• Exposed both point estimates and confidence bands, allowing account managers to see “aggressive”, “balanced”, and “conservative” bids.

3) Laravel integration

• Built a Laravel module where users could select a customer, traffic segment, and volume, then request bid guidance.
• Implemented background jobs to run heavier aggregations asynchronously so the UI stayed responsive.
• Added simple visualisations (spark lines, trend chips) to show how current predictions compared to historical performance.

Impact & Outcomes

While exact financials are confidential, the new system improved both decision quality and confidence for bulk click purchases:

• More accurate bid proposals for a key customer, reducing the risk of underpricing or overpaying for traffic.
• Clearer understanding of which traffic segments were historically profitable versus fragile.
• A more stable revenue stream as pricing for volume deals became less ad hoc and more model-driven.

Key Learnings

• In adtech, old data is a strategic asset; the hard part is shaping it into something operations teams can actually use.
• Segment-level, explainable models can be more valuable than opaque models that are hard for account teams to trust.
• Integrating predictions into existing tools (Laravel control panels, reports) is as important as the models themselves.
• Even simple predictive guidance can materially stabilise revenue when pricing bulk click deals.