Ecommerce Marketing Analytics in SQL (Dewline Skin)

A lot of lifecycle questions come down to a good query, so I wrote six of them for Dewline Skin, a fictional skincare brand. The data is synthetic. I generated 1,200 made-up customers in SQLite with orders, order items and email events. The queries cover RFM segments, monthly cohort retention, repeat purchase rate by channel, and last-click email attribution within 7 days. There's also a ranked winback list of lapsed repeat buyers, and lifetime value by acquisition channel. I used CTEs and window functions like LAG, NTILE, dense ranking and percent ranking. I'm not claiming any results here, since the data isn't real.