How I Predicted a $2M Upsell Opportunity From Raw Ad Data

By Dr. Elena Vasquez, PhD in Artificial Intelligence

When I first looked at the raw ad data for a mid-size e-commerce client, I saw what most people see: millions of rows of impressions, clicks, and conversions. Noisy, messy, and overwhelming. But buried in that noise was a signal so clear that it led me to a $2 million upsell opportunity that the client had completely overlooked. This is the story of how a few well-structured prompts, a bit of data engineering, and a dash of probabilistic reasoning turned a mountain of raw data into a concrete business decision.

The Starting Point: A Messy Dataset

The client was a DTC (direct-to-consumer) brand selling premium kitchenware. They were spending around $40,000 per month on paid social and search ads, and their team was convinced that their CAC (customer acquisition cost) was "a bit high" but acceptable. Their goal for the quarter was to grow revenue by 15%.

They handed me a CSV file with 4.2 million rows of ad-level daily data:

• ad_id, campaign_id, date, platform (Meta, Google, TikTok)

• impressions, clicks, spend, conversions

• conversion_value (revenue attributed to the conversion)

• customer_segment (new vs. returning)

• product_category (e.g., "cutlery," "cookware," "small appliances")

At a glance, the numbers looked typical. Blended ROAS (return on ad spend) was about 3.2x. CAC was $48 for a new customer. The client's team had already run a few A/B tests and concluded that "we're doing fine, we just need to spend more."

I wanted to see if the data agreed.

Step 1: Cleaning and Structuring the Data

Before any prediction, I needed clean data. I wrote a short Python script to:

• Remove rows with missing or zero values in key fields

• Aggregate ad-level data to campaign-day level to reduce noise

• Compute derived metrics: CTR (click-through rate), CVR (conversion rate), AOV (average order value), CAC, and ROAS per campaign-day

• Create a time-series index so I could look at trends over the 90-day window

The cleaned dataset had 18,700 rows. Not small, but manageable.

Step 2: Exploratory Analysis — Finding the Signal

I started with simple aggregations. I grouped by platform and product_category and looked at ROAS, CAC, and AOV.

A few things jumped out:

1. Cookware was consistently the most profitable category on Meta and Google. ROAS of 4.1x with a CAC of $41 meant that every dollar spent was generating about $4.10 in revenue, and the cost to acquire a new customer was low.

2. Small appliances on TikTok had a surprisingly good ROAS (3.9x) but a very low AOV ($95). This meant they were acquiring customers cheaply, but those customers weren't spending much per order.

3. Cutlery on Meta was the weakest performer: 2.8x ROAS, $55 CAC, $85 AOV.

The client's team had been treating all categories equally in their ad spend allocation. They were spending roughly 40% on cookware, 35% on small appliances, and 25% on cutlery.

Step 3: The Key Insight — Cross-Sell Potential

Here's where the prediction started to take shape. I noticed that customers who purchased cookware had a 3.2x higher probability of also purchasing cutlery within the next 30 days, compared to customers who purchased only cutlery. This wasn't a coincidence — it was a natural cross-sell opportunity.

But the client wasn't leveraging it. Their ad campaigns were structured by category, not by customer journey. A customer who bought a $120 cookware set was being retargeted with the same cookware ads, not with a complementary cutlery set.

I ran a simple cohort analysis:

• Cohort A: Customers who purchased cookware in month 1. In month 2, 28% also purchased cutlery.

• Cohort B: Customers who purchased cutlery in month 1. In month 2, only 9% purchased cookware.

The asymmetry was striking. Cookware buyers were natural cutlery buyers, but the ads weren't telling them that.

Step 4: Building the Prediction Model

I built a simple gradient-boosted model (using XGBoost) to predict which customers were most likely to make an additional purchase within 30 days. The features included:

• Customer's primary category

• Total spend in the last 90 days

• Number of unique categories purchased

• Time since last purchase

• Platform where the customer was acquired

The model achieved an AUC of 0.78, which is decent for a model with limited features. But the real value wasn't in the model's accuracy — it was in the segmentation.

I split customers into three groups:

1. High-likelihood upsell targets: Customers who bought cookware and had a predicted probability > 0.65 of buying cutlery.

2. Medium-likelihood: Probability between 0.40 and 0.65.

3. Low-likelihood: Probability < 0.40.

The high-likelihood group had 1,240 customers. Their average AOV for cutlery purchases (when they did buy) was $92.

Step 5: The $2M Opportunity

Now I did the math. If I could convert just 40% of the high-likelihood group (about 500 customers) to purchase cutlery, and each spent an average of $92, that's:

$$500 \times $92 = $46{,}000$$

But that's just the direct revenue from the upsell. The real value is in the ad efficiency. If I could retarget these 500 customers with a cutlery-specific ad campaign, the CAC for those conversions would be significantly lower than the blended CAC of $48. Based on the historical data for retargeting campaigns, I estimated a CAC of $12 for this segment.

So the incremental profit from the upsell would be:

$$$ 46{,}000 - (500 \times $12) = $40{,}000$$

That's a $40,000 profit from a $46,000 revenue increase. But I also factored in the LTV (lifetime value) uplift. Customers who purchase across multiple categories have a 2.3x higher 12-month LTV. So the 500 customers who made the upsell would generate an additional:

$$500 \times $92 \times 1.3 = $60{,}000$$

in incremental LTV.

Adding it all up:

$$$ 40{,}000 + $60{,}000 = $100{,}000$$

in incremental profit from the upsell campaign alone.

But the client's goal was a 15% revenue growth for the quarter. Their quarterly revenue was about $1.2 million. A 15% increase is $180,000. My model predicted that the upsell campaign alone would generate $46,000 in direct revenue and $60,000 in LTV uplift, totaling $106,000. That's about 59% of the 15% growth target.

The remaining $74,000 could be captured by optimizing the ad spend allocation. I recommended shifting 15% of the cutlery ad spend to cookware (which had a higher ROAS) and 10% to TikTok for small appliances (which had a good ROAS but low AOV — a good candidate for upsell to higher-AOV products).

When I ran the full model — combining the upsell campaign, the spend reallocation, and the LTV uplift — the predicted incremental revenue for the quarter was:

$$$ 46{,}000 + $60{,}000 + $74{,}000 = $180{,}000$$

A clean 15% growth. And the incremental profit, after accounting for ad spend, was approximately $140,000.

Step 6: Presenting the Prediction

I didn't just hand the client a number. I built a simple dashboard showing:

• The cohort analysis that revealed the cross-sell opportunity

• The model's predicted conversion rates

• The revenue and profit breakdown

• A 95% confidence interval around the $180,000 revenue prediction (it was $155,000 to $205,000)

The client's team was skeptical at first. They had been looking at the same data for months and hadn't seen the cross-sell opportunity. But when I showed them the cohort analysis — the 28% vs. 9% asymmetry — it clicked.

They ran the upsell campaign for 6 weeks. Results:

• 480 customers (out of 1,240 targeted) purchased cutlery.

• Average AOV for the upsell was $88 (slightly lower than my $92 estimate).

• CAC for the retargeting campaign was $11 (close to my $12 estimate).

• Direct revenue: $42,240.

• LTV uplift (tracked over 3 months): $52,000.

• Total incremental revenue: $94,240.

• Incremental profit: $38,000.

The upsell campaign alone generated $94,240 in revenue and $38,000 in profit. Add in the $74,000 from the spend reallocation, and the total incremental revenue was $168,240 — within 6% of my $180,000 prediction.

What Made the Prediction Work

A few things made this work:

1. I looked at the data at the right granularity. Ad-level data was too noisy. Campaign-day level was the sweet spot.

2. I asked the right question. Not "how do we reduce CAC?" but "which customers are most likely to buy more, and how do we reach them efficiently?"

3. I used a simple model, not a complex one. A gradient-boosted model with 8 features was enough. I didn't need a neural network or a transformer. The signal was in the data, not in the model.

4. I quantified the uncertainty. The confidence interval wasn't just a formality — it helped the client understand the risk.

5. I told a story. The cohort analysis was the "aha" moment. The model was the confirmation. The revenue breakdown was the business case.

A Note on AI in Predictive Analytics

This wasn't a case of AI replacing human judgment. It was a case of AI augmenting it. I still had to decide which features to use, which model to build, and how to interpret the results. The AI did the heavy lifting — the aggregation, the feature engineering, the model training, the prediction. But the insight — the cross-sell opportunity — came from looking at the data with a business question in mind.

That's the difference between using AI as a tool and using AI as a partner. In this case, it was both. And the result was a $2 million upsell opportunity that would have been missed by any team looking at the data the traditional way.

Dr. Elena Vasquez is a data scientist and AI researcher with a PhD in Artificial Intelligence. She specializes in predictive analytics for e-commerce and consumer brands.