Analyzing 32 TB of Coupon Data: Designing Dynamic Allocation to Eliminate System Waste
- Published on
- Updated
- Reading time
- 10 mins read
On this page

When backend engineers think about system optimization, we typically gravitate toward code-level profiling, tuning HikariCP connection pools, caching hot entities in Redis, or rewriting database queries.
But sometimes, the most catastrophic performance bottlenecks and infrastructure costs do not originate in bad Java code or missing database indexes—they originate in flawed domain assumptions hardcoded into business allocation logic.
At Trendyol, our coupon platform is responsible for allocating, storing, evaluating, and redeeming promotional discounts for tens of millions of active e-commerce customers across multiple countries. Automated marketing jobs and real-time triggers generate coupon records at enormous volume.
Our persistence tier was feeling severe strain:
- Our Couchbase clusters were storing a huge volume of active coupon documents, consuming expensive RAM quota.
- Batch generation pipelines were running long every morning, flooding our Kafka brokers and database write queues.
- Yet a large share of the coupons we generated expired without ever being used.
To understand why our databases were being crushed to store coupons that nobody redeemed, I dove into our 32 TB coupon dataset in Google BigQuery.
What began as an infrastructure capacity investigation turned into a wild backend war story: coupons were being distributed uniformly regardless of their discount value, so I designed a quality-based dynamic limit algorithm that sends high-value coupons to more customers and cuts wasted generation. Along the way, the same analysis uncovered a silent upstream script bug that had left entire countries receiving zero coupons.
The Scale of the Warehouse & The Investigation
Our transactional systems stream every coupon mutation (generation, assignment, basket evaluation, expiration, and checkout redemption) to Google Cloud Storage via Kafka Connect, which continuously ingests into BigQuery.
The primary table analytics_promotions.coupon_lifecycle_events spanned:
- Volume: 32 TB.
- Partitioning: Daily ingestion date (
_PARTITIONDATE). - Clustering:
customer_id,campaign_id,country_code.
Querying 32 TB without running up a large bill requires surgical SQL, because on-demand queries are billed by the bytes they process. By always filtering on partition date boundaries and clustering keys, we narrowed our analytical scans from terabytes down to targeted gigabyte chunks.
The First Query: Distribution vs Redemption by Discount Value
Our legacy generation pipeline used a flat allocation rule: every coupon was distributed under the same limit, whatever its discount value, across every category (Electronics, Fashion, Grocery, Supermarket).
To test whether this flat policy made any business sense, I ran a decile analysis across discount value, comparing how widely each band was distributed with how often it was redeemed:
-- Analysis: Coupon Distribution vs Redemption by Discount Value Decile
WITH campaign_value AS (
SELECT
campaign_id,
NTILE(10) OVER (ORDER BY AVG(discount_value) DESC) AS value_decile
FROM `analytics_promotions.coupon_lifecycle_events`
WHERE _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
AND event_type = 'ASSIGNED'
GROUP BY campaign_id
),
coupon_metrics AS (
SELECT
campaign_id,
COUNT(DISTINCT IF(event_type = 'ASSIGNED', customer_id, NULL)) AS customers_reached,
COUNTIF(event_type = 'ASSIGNED') AS total_assigned,
COUNTIF(event_type = 'REDEEMED') AS total_redeemed,
COUNTIF(event_type = 'EXPIRED') AS total_expired
FROM `analytics_promotions.coupon_lifecycle_events`
WHERE _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY campaign_id
)
SELECT
cv.value_decile,
COUNT(cv.campaign_id) AS total_campaigns,
ROUND(AVG(cm.customers_reached), 0) AS avg_customers_reached,
ROUND(SAFE_DIVIDE(SUM(cm.total_redeemed), SUM(cm.total_assigned)) * 100, 2) AS redemption_rate_pct,
ROUND(SAFE_DIVIDE(SUM(cm.total_expired), SUM(cm.total_assigned)) * 100, 2) AS expired_rate_pct
FROM campaign_value cv
JOIN coupon_metrics cm ON cv.campaign_id = cm.campaign_id
GROUP BY cv.value_decile
ORDER BY cv.value_decile ASC;
The Shocking Finding: Value-Blind Distribution
The query results exposed an alarming reality: distribution was flat across every value decile. A coupon with a token discount was generated for as many customers as one with a generous discount, and a large share of that low-value generation expired unused.
Look at the bottom deciles. Coupons with token discounts were being generated at the same volume as our best offers.
Our backend services were dutifully:
- Running heavy batch generation queries every morning.
- Writing those documents into Couchbase with a TTL.
- Indexing those documents into Elasticsearch.
- Serializing them across Kafka topics.
All for coupons that were likely to expire untouched. We were melting our persistence layer and storage budgets to store digital ghost papers.
Meanwhile, our high-value coupons were held to the same flat limit as everything else, so they reached fewer customers than they could have.
The Upstream Bug: The Country Cohort Black Hole
While digging into the data to parameterize the new limits, I ran a geographical sanity query grouping coupon assignment rates across countries:
-- Sanity Check: Coupon Lifecycle Events by Country Cohort
SELECT
country_code,
currency,
COUNT(DISTINCT customer_id) AS distinct_customers_targeted,
COUNTIF(event_type = 'ASSIGNED') AS total_assigned_coupons,
COUNTIF(event_type = 'REDEEMED') AS total_redeemed_coupons
FROM `analytics_promotions.coupon_lifecycle_events`
WHERE _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY country_code, currency
ORDER BY distinct_customers_targeted DESC;
When the query finished, the output left me staring at the screen in disbelief (absolute numbers redacted):
country_code │ currency │ targeted_users │ total_assigned │ total_redeemed
─────────────┼──────────┼────────────────┼────────────────┼───────────────
TR │ TRY │ … │ … │ …
DE │ EUR │ … │ 0 │ 0
AZ │ AZN │ … │ 0 │ 0
AE │ AED │ … │ 0 │ 0
Active customers in Germany, Azerbaijan, and the UAE were being targeted by marketing campaigns, but zero coupons had been assigned to them.
Root-Cause Debugging: The Silent Exception
We immediately pulled the codebase of the upstream campaign pre-filtering service (a Python/Airflow batch component that filtered candidate users before passing IDs to our high-throughput Java coupon generator).
Deep inside an unheralded utility method, we found this:
# The Culprit: Currency Normalization Utility
def validate_customer_currency(customer_record, campaign_target):
try:
# Legacy check assumed currency was stored as string enum matching campaign
cust_curr = customer_record.get("preferred_currency")
target_curr = campaign_target.get("currency")
# Bug: Upstream user profile service migrated international users
# to ISO-4217 integer codes (EUR=978, AZN=944, AED=784), while
# campaign definitions still used string codes ("EUR", "AZN", "AED").
if cust_curr != target_curr:
return False
return True
except Exception as e:
# Anti-Pattern: Silent catch-all with zero metric logging!
logger.debug(f"Currency mismatch error: {e}")
return False
The user profile service had rolled out an internationalization refactor months earlier, converting customer currency preferences to numeric ISO codes. Because the check silently returned False on the type mismatch (and the catch-all only logged at debug level), the Airflow task exited cleanly with code 0.
Airflow reported green runs every single day. Prometheus reported zero HTTP 500 errors. But customers in entire countries had been silently dropped from every promotional campaign.
Once traced, the fix itself was small: compare normalized currency codes, add strict type assertions, and alert on a counter such as promotion.allocation.dropped_user_count, so a job that drops users can never look green again.
Designing the Quality-Based Dynamic Limit Algorithm
Once the upstream pipeline was repaired, we set out to permanently solve the database waste problem. We replaced the flat, value-blind limit with a Quality-Based Dynamic Limit Algorithm.
[Coupon Definition & Redemption History]
│
▼
┌──────────────────────────────┐
│ Quality Scorer (BigQuery │
│ Daily Batch Job) │
└──────────────┬───────────────┘
│
Quality Score [0.0 - 1.0]
│
▼
┌────────────────────────────────────────────────────────┐
│ Dynamic Limit Allocation Function │
│ │
│ Tier A (High Value): Reach = 100% of eligible users │
│ Tier B (Solid Value): Reach = 60% │
│ Tier C (Low Value): Reach = 25% │
│ Tier D (Token Value): Reach = 0 (On-Demand Only) │
└────────────────────────────────────────────────────────┘
The Mathematical Model
Instead of treating all coupons equally, we compute a normalized coupon quality score S between 0.0 and 1.0, recomputed daily by a BigQuery batch job:
Score (S) = (w1 * V_norm) + (w2 * H_redemption)
Where:
V_norm: The coupon's discount value relative to the basket it targets, normalized and capped at1.0.H_redemption: Historical redemption ratio of comparable coupons (coupons redeemed / coupons assigned).
The coupon's distribution limit L(S) is determined by a stepped piecewise function with a floor and ceiling:
public class DynamicAllocationEngine {
// Share of the campaign's eligible audience that gets the coupon pre-generated
private static final double TIER_A_REACH = 1.00;
private static final double TIER_B_REACH = 0.60;
private static final double TIER_C_REACH = 0.25;
private static final double TIER_D_REACH = 0.00;
public AllocationDecision calculateLimit(CouponDefinition coupon, long eligibleAudience) {
double score = computeQualityScore(coupon);
if (score >= 0.80) {
// High-value coupons: widest reach to prevent promotion starvation
return new AllocationDecision(reach(eligibleAudience, TIER_A_REACH), AllocationStrategy.EAGER_PRELOAD);
} else if (score >= 0.45) {
// Solid value: moderate reach
return new AllocationDecision(reach(eligibleAudience, TIER_B_REACH), AllocationStrategy.EAGER_PRELOAD);
} else if (score >= 0.15) {
// Low value: restrictive reach
return new AllocationDecision(reach(eligibleAudience, TIER_C_REACH), AllocationStrategy.EAGER_PRELOAD);
} else {
// Token value: zero pre-generation
return new AllocationDecision(reach(eligibleAudience, TIER_D_REACH), AllocationStrategy.LAZY_ON_DEMAND);
}
}
private double computeQualityScore(CouponDefinition c) {
double value = Math.min(1.0, c.getDiscountRatio() / 0.30); // 30%+ of the basket counts as top value
double redemptionHistory = c.getComparableRedemptionRate();
return (0.70 * value) + (0.30 * redemptionHistory);
}
private static long reach(long eligibleAudience, double share) {
return Math.round(eligibleAudience * share);
}
}
The Lazy "On-Demand" Generation Pattern
Notice the AllocationStrategy.LAZY_ON_DEMAND for Tier D coupons.
Instead of generating and persisting a token-value coupon for every eligible customer up front, we generate zero documents.
If a customer actually shows intent for that campaign, for example by opening its landing page or adding a qualifying item to the basket:
- The service detects that the customer is eligible for a Tier D campaign.
- It executes an asynchronous, lightweight on-demand coupon synthesis.
- The coupon is written to Couchbase on the fly.
We only spend database disk, RAM, and write IOPS at the exact moment a customer demonstrates real intent.
Infrastructure Impact & Production Results
We deployed the Dynamic Limit Allocation Engine in staging, verified consistency against our load-test data generator, and rolled it out gradually across production traffic.
The impact showed up on both the infrastructure and the business side.
1. Generation Waste & System Load
By no longer pre-generating low-value coupons at the same volume as our best offers, we stopped writing, indexing and serializing documents that were likely to expire unused. Wasted generation dropped, and system load improved with it: fewer documents written to Couchbase, fewer indexed into Elasticsearch, and fewer events serialized through Kafka by the morning batch.
2. Business Impact
Because the limit now follows coupon quality, high-value coupons reached more customers instead of being capped at the same flat limit as token discounts. The geographic sanity check paid off too: it exposed entire countries receiving zero coupons, a gap no dashboard had shown.
What Staff Engineers Should Take Away
- Big Data Is a Production Debugger: Do not leave BigQuery or Snowflake solely to data analysts and product managers. As backend and distributed systems engineers, querying warehouse access patterns often reveals systemic architectural flaws that APM tracing tools can never see.
- Beware the "Silent Green" Pipeline: Airflow tasks, batch workers, and cron scripts that catch exceptions without incrementing anomaly counters are ticking time bombs. A job returning exit code 0 while producing 0 outputs is an outage.
- Question Static Business Constants: Whenever you see
MAX_LIMIT = 15orDEFAULT_TIMEOUT = 5000hardcoded in your domain layer, ask: What happens to our database tier if 50 million users hit this simultaneously? Dynamic, context-aware limits preserve infrastructure stability and maximize business yield.