Comparing ad returns month to month
Ads reporting: worked example
ROAS measures sales per RM1 of ad spend. Compare each store with the previous month only after orders settle. This check flags gaps and numbers that aren’t ready to report.
All stores and numbers on this page are made up. The example was written for this portfolio.
What the check returns
Amounts in RM; ROAS = ad sales ÷ ad spend
| Store | Month | Spend | Ad sales | ROAS | vs last month | Status |
|---|
| A | Jul | 1,000 | 5,000 | 5.0 | No prior data | OK |
|---|
| A | Aug | 1,200 | 5,400 | 4.5 | −10.0% | OK |
|---|
| A | Sep | 1,100 | 6,050 | 5.5 | +22.2% | Not final yet |
|---|
| B | Jul | 800 | 2,400 | 3.0 | No prior data | OK |
|---|
| B | Sep | 900 | 3,600 | 4.0 | No Aug data | OK |
|---|
| C | Aug | 500 | 2,600 | 5.2 | No prior data | Check orders |
|---|
| C | Sep | 0 | 0 | No spend | Not comparable | No spend |
|---|
- Not final yet: the data was downloaded on 3 Oct, before September’s orders had settled, so the +22.2% can still change.
- No Aug data: a missing month is shown as a gap, so September is not compared with July.
- Check orders: only 81% of the sales credited to ads became confirmed orders.
From the result to a recommendation
In this made-up example, Store A’s August spend rose while ROAS fell from 5.0 to 4.5. I would review the campaigns and keywords before recommending a budget increase.
September’s apparent improvement is provisional. Wait for orders to settle and compare confirmed sales before raising spend; the +22.2% calls for another check.
For technical reviewers
The same example as sample data, one SQL view and eight tests you can run.
Show the SQL and tests
A window function, LAG(), finds each store’s previous month.
SELECT store, month,
ROUND(ad_sales_cents * 1.0
/ NULLIF(spend_cents, 0), 2) AS roas,
LAG(month) OVER w AS prior_month
FROM ad_month
WINDOW w AS (PARTITION BY store ORDER BY month);
-- compare only when prior_month is the
-- calendar month before
Download SQL and sample data
Download Python tests
Save both files in one folder and run the command with Python 3. Eight tests cover each flag, a duplicate month and bad input.
python check_roas_example.py
Back to ads reporting · Contact me