CASE FILE 06CLIENT GENERALISEDALL DATA MOCKED

Store data and ad data, together.

A Shopify store was scaling Facebook ads while refunds and discounts quietly ate the profit. This project put store numbers and ad numbers on one screen.

CLIENTDTC STORE
MODELECOM + PAID SOCIAL
STACKSHOPIFY · META · LOOKER
ENGAGEMENT5 PHASES · FULL SYSTEM
STATUSLIVE · AUTOMATED
THE BOARD — OVERVIEWAI-BUILT · LIVE HTML
EXHIBIT A
1

The strip along the top shows sales, orders, refunds and ad return in one line.

2

The four panels show sales over time, top products, customer mix and ad campaigns.

3

Refunds and discounts sit right next to ad results, so nothing is hidden.

The Brief

Ads said scale. The store said careful.

Ads Manager showed good returns, so the ads kept scaling. But Shopify showed rising refunds and discount codes cutting into profit — and the two reports were never side by side.

We joined the store data and the ad data into one dashboard. Now scaling decisions include the full picture: sales, refunds, discounts and margins.

THE GOAL: scale the ads with the store’s real numbers on the same screen.

✗ THE PROBLEMS

  • ✗ Refund rate invisible next to ROAS
  • ✗ Discount codes eroding AOV unnoticed
  • ✗ Product concentration risk unmeasured
  • ✗ New vs returning mix unknown at spend level
  • ✗ Two dashboards, two Mondays, two stories
The System

How the system works.

Shopify and Meta Ads data are joined into one model. SQL builds the sales, product and margin views, and Looker Studio shows them as one four-panel dashboard with alerts.

Shopify

Orders, products, discounts, refunds, customers.

Meta Ads API

Spend, purchases, campaign detail.

→

Models

Gross→net bridge · product concentration · new-vs-returning.

→

Looker Studio

The quad board: sales, products, customers, campaigns — plus deep-dive pages.

→

Action Layer

Refund-spike and discount-leak alerts to Slack.

The Process

How the project ran.

01

Audit

  • Built the gross→net bridge; found the leakage lines
  • Mapped product concentration and repeat behaviour
02

Measurement Plan

  • Margin-safe scaling defined as a metric set
  • Discount codes classified by intent
03

Implementation

  • Shopify + Meta joined in one model
  • Refund and discount tracking wired in
04

Dashboard Build

  • One screen with four clear views
  • Campaign table with ROAS beside margin context
05

Automate & Optimise

  • Refund-spike + discount-leak alerts
  • Weekly margin-safe scaling summary
The Build

Products and margin, in detail.

Two more pages: the product table and the alerts page. Built in HTML with sample data.

PAGE 02 / 06 — PRODUCTS × MARGINLIVE HTML · MOCK DATA
EXHIBIT B
Product Deep-Dive

Each product, with its real profit.

  • ■ The table shows revenue, units, margin and refund rate for every product.
  • ■ The chart tracks profit margin against the 30% minimum line.
  • ■ The refund list shows why customers return items, with a fix for each reason.
PAGE 06 / 06 — ALERTS + AILIVE HTML · MOCK DATA
EXHIBIT C
Automation Layer

Alerts protect the margin.

  • ■ Refund spikes and discount problems trigger alerts the day they start.
  • ■ The Monday AI summary compares store results with ad spend in one read.
  • ■ Each alert names the fix: update the size guide, change a discount rule, or scale safely.
Deliverables

What was delivered.

The Results

What changed.

PROFIT IN VIEW.

Refunds and discounts now appear next to ad returns, so scaling never hides the real cost.

PRODUCTS, RANKED.

The store knows which products carry the business and watches them closely.

ONE REPORT.

Store and ads stopped having separate meetings about the same money.

NOTE: CLIENT DETAILS GENERALISED · ALL NUMBERS ARE SAMPLE DATA · DASHBOARDS ARE HTML RECREATIONS

Next Steps

Want this for your business?

If your ads look great but your profit doesn’t, this same setup can be built for your store.

★ Book a call →View live dashboard ↗Next case →← All cases