As a Digital & Brand Marketing Manager overseeing Rp 7.7B+ in total ad budget across TikTok Shop and Shopee, I built this full analytics system from scratch — in Google Sheets — to ensure every rupiah of budget was allocated based on real margin data, not just surface-level GMV. No third-party BI tool. No data team. Just structured thinking, advanced formulas, and a relentless focus on profitability.
Context
A growing beauty brand was running simultaneously on TikTok Shop and Shopee, managing 43 active SKUs and an affiliate program through GMV Max — with a total 5-month budget exceeding Rp 7.7 billion.
Despite strong top-line GMV, there was no clear view of which products were actually profitable after platform admin fees, affiliate commissions, and seller discounts were deducted. Revenue was being mistaken for business health.
All data lived in isolated raw exports from three separate seller dashboards. There was no infrastructure to connect them, no threshold logic to trigger decisions, and no consistent method to evaluate affiliate video efficiency.
Critical Questions That Couldn't Be Answered
My Approach
I designed and built 3 interconnected Google Sheets systems — each solving one layer of the analytics problem — with automated formulas that refresh whenever fresh exports are pasted in. No BI tool license, no data engineer, no dashboarding software required.
Unified dashboard aggregating daily GMV from TikTok Shop and Shopee. Tracks MoM delta, channel revenue mix, ads spend efficiency, and cost-per-conversion — updated by pasting raw platform exports into designated input sheets.
Evaluates TikTok affiliate videos at the individual video level. Pulls creator data from 3 separate raw exports, calculates ROAS per video, and auto-flags scale / optimize / kill with a month-filter dropdown for period comparison.
Calculates true per-SKU profitability after deducting: COGS, platform admin fee, affiliate commission, seller brand discount, and platform subsidy. Auto-classifies via multi-condition IFS into Winning / Potential / Slow Moving with margin health flags.
The GMV Max tracker evaluates performance at the individual video level. Each video gets a ROAS score derived from cross-referencing 3 separate raw data exports (campaign spend, creator data, video GMV) — and is automatically routed to a scale / optimize / kill decision. Creator IDs anonymized below.
| Creator | Product Type | GMV (indexed) | ROAS | Ads Spend (indexed) | System Decision |
|---|
The fee waterfall is calculated using a multi-step settlement formula built in Google Sheets. Each deduction layer (seller discount → platform admin fee → affiliate commission → COGS) is computed sequentially, enabling the system to isolate exactly where margin is lost before any ad budget decision is made.
ETL process follows a Power Query-style paste-and-refresh workflow: raw settlement exports are pasted into input tabs, and all downstream calculations update automatically via ARRAYFORMULA chains — no manual recalculation needed.
The Decision Framework logic uses nested IFS with 5 concurrent conditions evaluated per SKU: sales volume tier, net margin threshold, admin fee rate, seller discount level, and HPP ratio — outputting a single actionable status badge.
| SKU Code | Product Category | Qty Sold | Avg Sell Price | Net Margin % | Platform Fee Rate | Seller Discount % | Sales Status | Margin Health |
|---|
The system doesn't just report — it produces clear operational signals. Every SKU and every affiliate video is automatically routed to a recommendation based on multi-condition IFS logic evaluated in real time.
Before the System
Budget decisions were made on raw GMV alone — no visibility into whether revenue translated to settlement after fees. Platform admin costs, affiliate commissions, and seller discounts were siloed or untracked.
Affiliate video evaluation required manually cross-referencing 3 separate exports. No standardized ROAS threshold existed for scale vs kill. Decision-making was slow and inconsistent.
After the System
Any team member can paste fresh platform exports and instantly receive: a margin-ranked SKU table, creator ROAS leaderboard, and daily GMV trend — with zero formula knowledge required.
The tracker caught 2 SKUs being actively scaled at near-zero margin after all fees — enabling course-correction before significant additional spend was wasted.