Ecommerce warehouse: LTV by first product purchased

The problem

• Google Ads, Microsoft Ads, Meta and Klaviyo each reported their own version of performance.
• First-order ROAS did not answer which products created the best customers.
• Order lines and transaction IDs were not available in the same analysis layer as media spend.

The build

• BigQuery paid media warehouse.
• Google Ads, Microsoft Ads, Meta and Klaviyo connected.
• Full order lines and transaction IDs loaded into the warehouse.
• Models for revenue, MER, channel claims and product cohorts.
• AI-assisted queries used to validate claims and stress test anomalies.

The account moved from "which campaign got the sale?" to "which first purchase creates the best customer?"

Why it matters

Some products look weak on first-order ROAS but create valuable repeat customers. Others look strong in-platform but do not hold up against warehouse data. Product-level LTV changes what you are willing to pay for a new customer.