CAC formulas (clean — they sum to total spend):
New CAC = (Total D2C Spend − Retention spend) ÷ New Customers. Retention spend = WhatsApp CRM + Meta Post-Purchase campaigns.
Repeat CAC = (WhatsApp CRM + Meta PP) ÷ Repeat Users. Apr Meta PP = ₹5.22L (vs Feb ₹1.81L) — retention budget scaled 2.9x.
Blended CAC = Total D2C Spend ÷ (New + Repeat unique users) — overall cost-per-engaged-customer.
| State | Rev (₹L) | % Share | vs Feb |
|---|---|---|---|
| Tamil Nadu | 25.23 | 16.3% | +44.8% |
| Maharashtra | 18.94 | 12.2% | +19.0% |
| Karnataka | 17.17 | 11.1% | +1.4% |
| Uttar Pradesh | 10.71 | 6.9% | +4.0% |
| Delhi (+NCR) | 10.31 | 6.7% | +34.4% |
| Top 5 Total | 82.36 | 53.2% | +20.7% |
| City | Rev (₹L) | % Share | vs Feb |
|---|---|---|---|
| Bangalore | 11.31 | 7.3% | +6.8% |
| Mumbai | 6.51 | 4.2% | +33.9% |
| Chennai | 6.48 | 4.2% | +25.8% |
| Pune | 3.90 | 2.5% | +22.3% |
| Hyderabad | 3.42 | 2.2% | −0.9% |
| Top 5 Total | 31.62 | 20.4% | +16.1% |
| Carrier | Apr Shipments | Apr RTO % | Feb Shipments | Feb RTO % | Δ pp |
|---|---|---|---|---|---|
| Delhivery | 9,405 | 12.48% | 9,762 | 17.32% | −4.84pp |
| Shiprocket | 9,388 | 14.68% | 9,034 | 19.96% | −5.28pp |
| Overall | 18,793 | 13.58% | 18,796 | 18.59% | −5.01pp |
Source: BQ phd-database-475011.phd_clickpost_raw.master_combined, latest status per AWB (ROW_NUMBER ORDER BY Ingestion Date DESC). Matches ClickPost dashboard within 0.3pp (Apr Delhivery: BQ 12.48% vs ClickPost UI 12.45%; Feb Shiprocket: BQ 19.96% vs UI 20.26%). Click the headline KPI for full 12-month trend.
| # | Product | Spend (₹L) | Revenue (₹L) | ROAS | % Share | Mar Rev (₹L) | Trend vs Mar |
|---|---|---|---|---|---|---|---|
| 1 | Panthenol Hydrating Gel Sunscreen SPF 60 PA++++ | 41.93 | 51.52 | 1.23x | 18.3% | 51.28 | +0.5% · new #1 |
| 2 | 10% Niacinamide Spot Correcting Serum | 55.50 | 44.35 | 0.80x | 15.8% | 59.46 | −25.4% |
| 3 | 2% Alpha Arbutin Depigmentation Serum | 24.40 | 30.24 | 1.24x | 10.7% | 66.77 | −54.7% |
| 4 | 0.3% Pure Retinol Face Serum | 37.38 | 25.21 | 0.67x | 9.0% | 28.64 | −12.0% |
| 5 | Vitamin C Brightening Moisturizer | 0.72 | 15.89 | 22.07x | 5.6% | — | NEW IN TOP 5 |
| Top 5 Total | 159.93 | 167.21 | 1.05x | 59.4% | — | — | |
Spend sourced from BQ meta_airbyte.ads_insights, ad-name → adset → campaign regex fallback (aliases: Spot Correcting = Niacinamide; Depigmentation = Alpha Arbutin; Anti-Acne = Salicylic). Click any row for full spend / revenue / ROAS history.
| Channel | MRP Sales | Total Sales (Gross) | Discount % | Ads Spend | Ads Sales | TROAS | vs Feb |
|---|---|---|---|---|---|---|---|
| Amazon | ₹64,09,041 | ₹56,59,634 | 12% | ₹2,84,152 | ₹22,22,243 | 19.92x | +2.2% |
| Flipkart | ₹41,69,403 | ₹36,43,793 | 13% | ₹1,08,424 | ₹14,71,186 | 33.61x | −14.2% |
| Myntra | ₹24,43,435 | ₹21,36,299 | 13% | ₹17,623 | ₹29,459 | 121.22x | −17.6% |
| Nykaa NEW | ₹40,31,522 | ₹36,03,140 | 11% | ₹3,50,000 | — | 10.29x | Launch |
| Purplle | ₹13,04,808 | ₹11,61,246 | 11% | ₹1,23,823 | ₹3,30,933 | 9.38x | −10.5% |
| Meesho | ₹8,78,753 | ₹6,78,546 | 21% | — | — | — | −11.0% |
| Instamart | ₹1,32,305 | ₹1,15,301 | 12% | — | — | — | +1,212% |
| FK Minutes | ₹42,736 | ₹37,614 | — | — | — | — | +18.8% |
| Total Marketplaces | ₹1,94,11,003 | ₹1,70,35,573 | 12% | ₹8,83,022 | ₹40,53,821 | 19.08x | +17.7% |
| Step | D2C | MP | Total | vs Feb (Total) |
|---|---|---|---|---|
| Total Sales (Gross) | 154.89 | 170.36 | 325.24 | +14.6% |
| Net Sales | 118.49 | 115.84 | 234.33 | +18.0% |
| Gross Margin | 82.94 | 89.20 | 172.14 | +13.3% |
| CM1 | 70.38 | 63.71 | 134.10 | +10.8% |
| Marketing Spend | −194.17 | −8.93 | −203.09 | −1.2% |
| CM2 | −123.78 | +54.78 | −68.99 | +₹15.52L |
| CM2 % | −104.5% | +47.3% | −29.45% | +13.09pp |
1st source — GMV / Marketplace daily: PHD Master · UE+MP Daily Tracker (April-26 tab row 33).
2nd source — D2C marketing + funnel: PHD Website Report · Apr'2026 tab (daily granularity for Meta/Google/WhatsApp spend & revenue, sessions, ATC, conv rate).
3rd source — Monthly compiled: PHD Master · Final_Monthly (Apr'26 column for orders/AOV/CAC/repeat/funnel/top products).
4th source — Supply chain:
BigQuery phd-database-475011.phd_clickpost_raw.master_combined (RTO, NDR, shipments).
Excluded per spec: Platinum Factory (Creative) and Influencer Marketing.
| Metric | Value | Source / Formula |
|---|---|---|
| Total Sales (D2C+MP) | ₹3,25,24,399 | April-26 BF33 (Unit Economics tab) |
| Total Spends (D2C+MP) | ₹2,04,51,614 | D2C ₹1,95,58,728 + MP Ads ₹8,92,886 |
| D2C Total Sales | ₹1,54,88,825 | Apr'2026 col J Total |
| D2C Spends (Meta+Google+WA) | ₹1,95,58,728 | Apr'2026 col F Total |
| Meta / Google / WA Spend | ₹1,77,79,676 / ₹16,36,923 / ₹1,42,129 | Apr'2026 cols C, D, E |
| Meta / Google / WA Revenue | ₹1,01,67,754 / ₹32,23,694 / ₹17,07,585 | Apr'2026 cols G, H, I |
| Meta / Google / CRM / Blended ROAS | 0.57 / 1.97 / 12.01 / 0.79 | Apr'2026 cols T, U, V, W |
| D2C Orders / AOV / IPO / Discount % | 19,058 / ₹813 / 2.10 / 30.4% | Final_Monthly Apr |
| New Customers | 14,735 | Apr'2026 col Q Total |
| Meta PP (Post-Purchase) Spend | Apr ₹5.22L · Feb ₹1.81L | BQ meta_airbyte.ads_insights, regex match on campaign_name LIKE '%PP%' / '%RETENTION%' / '%REPEAT%' |
| New CAC (acquisition) | ₹1,283 | (D2C Spend − Retention spend) ÷ New = (1,95,58,728 − 6,63,964) ÷ 14,735. Retention = WA CRM ₹1.42L + Meta PP ₹5.22L. |
| Blended CAC | ₹1,061 | D2C Spend ÷ (New + Repeat) = 1,95,58,728 ÷ (14,735 + 3,702) |
| Repeat CAC (retention) | ₹179 | (WA CRM + Meta PP) ÷ Repeat Users = (1,42,129 + 5,21,835) ÷ 3,702. Math check: 1283×14,735 + 179×3,702 = 1,95,65,821 ≈ Total Spend ✓ |
| Repeat Rev / % / Orders / Users / AOV | ₹36L / 23.0% / 4,323 / 3,702 / ₹823 | Final_Monthly Apr |
| Sessions / ATC / Checkout / Conv Rate / Cost-Session | 7,32,191 / 73,726 / 37,322 / 2.60% / ₹26.7 | Apr'2026 cols Z, AC, AK, AG, AJ |
| Funnel rates (ATC / Checkout / Conv) | 10.07% / 5.10% / 2.60% | Each ÷ Sessions × 100 |
| RTO % (Overall) | Apr 13.58% · Feb 18.59% | BQ master_combined, LATEST status per AWB only (ROW_NUMBER PARTITION BY AWB ORDER BY Ingestion Date DESC, rn=1). Naïve ANY_VALUE(status) picks any of the 2.4 historical statuses per AWB and gives wrong numbers — the bug that produced the original 8.75%/12.79%. Latest-status BQ matches ClickPost UI within 0.3pp. Click the KPI for 12-month trend. |
| MP Total Gross / TROAS / CM2 | ₹1.70Cr / 19.08x / +₹54.78L | April-26 AW33, AZ33, BU33 |
master_combined status field gives wrong numbers due to status-mapping mismatch — Feb BQ-settled gave Delhivery 25% but ClickPost UI shows 17.56%. Now using ClickPost as authoritative, weighted by per-carrier BQ shipment volume. Apr Overall = 13.60% (Delhivery 12.45%, Shiprocket 14.76%); Feb Overall = 18.86%. Apr is 5.26pp BETTER than Feb.