Skip to content

SKILL.md

Latest commit

 

History

History
147 lines (109 loc) · 9.18 KB

SKILL.md

File metadata and controls

147 lines (109 loc) · 9.18 KB
Raw

Meta Price-Campaign Winner Analysis

End-to-end workflow for the recurring "which price wins" decision on Meta Ads. The user almost always asks for the same three tests in one image; multi-city runs (3-4 cities in one session) are now standard.

1. Trigger conditions

  • User sends one or more Meta Ads export xlsx files with the same campaign structure across cities (e.g. Calgary-3-PRICES.xlsx, Denton-3-prices.xlsx).
  • Each file has 3 price variants tagged (Price A2), (Price B), (Price C2), (Price D2), etc. — extract with regex.
  • User asks for 3 methods / three tests: profitability, SARIMAX 60-day, purchase rate, with bootstrap CIs.

2. The three tests (always run all three)

# Test What it measures Method
1 Profitability ROAS Return per $1 spent Point = Σrevenue / Σspend. CI = day-level resample (n=3,500 or 10,000 per request), recompute ratio each iter, percentile(2.5, 97.5).
2 SARIMAX 60-day forecast Trajectory of daily purchases Auto-select order via pmdarima.auto_arima. Forecast 60 days forward, plot with 95% CI.
3 Purchase Rate (CVR) Purchases / clicks Point = Σpurchases / Σclicks. CI = day-level resample, recompute ratio, percentile(2.5, 97.5).

Bootstrap count is task-specific — user has used 10,000 in one session and 3,500 in another. Always read the count from the current request; do not hard-code.

3. Meta export file structure — the column-index trap

Pitfall #1 (this bites every time): Meta's Raw Data Report sheet has:

  • Row 1: a band/title row with merged cells (mostly Unnamed: N)
  • Row 2: the actual column NAMES — Campaign name, Day, Amount spent (USD), Impressions, Purchases, Link clicks, Result rate, Results value, etc.
  • Row 3+: the data

Because the file has an extra band-row before the real header, pd.read_excel(header=1) skips that band but the column NAMES in the header row are at logical positions 0..N (with Campaign name at logical index 1, not 0), and the integer index used to access the underlying columns in df.iloc[:, k] is also offset by 0 from logical position — so what you read in df.iloc[:, 8] is NOT Amount spent (USD), it is the 9th column of the dataframe which is one column to the right of what the header label suggests.

Concrete column indices that work (verified across all 4 cities in the 2026-06-02 session):

Field df.iloc[:, k] Header label (logical pos)
Campaign name 1 1
Day (date) 2 2
Spend (Amount spent USD) 8 8
Impressions 9 9
Purchases (Results) 13 13
Purchase ROAS 19 19
Link clicks 20 20
CTR (all) 21 21
Result rate (CVR) 22 22
Results value (REVENUE) 24 24
Results ROAS 26 26
Reporting starts 27 27
Reporting ends 28 28

Symptom when you get it wrong: revenue sum = $0 for every campaign. That's the tell. Always sum revenue first as a sanity check; if you see 0, you've used the wrong column index.

Reproduction recipe: See references/meta_export_layout.md.

4. SARIMAX model-selection rules

Auto-select is non-trivial because training-data length varies wildly (Waukesha = 121 days, Edmonton = 33 days, Denton = 20 days). Rules:

  1. Seasonal cutoff: Use SARIMA only if training data has ≥ 28 days (≥ 2 full weekly cycles for m=7) AND ACF at lag 7 is significant (|acf[7]| > 2/√n — Bartlett CI). Otherwise fall back to plain ARIMA.
  2. Differencing (d): ADF test on the first-differenced series; d = 1 if p > 0.05, else d = 0.
  3. Auto-arima config:
    • seasonal=use_seasonal, m=7 if seasonal else 1
    • d=d, D=1 if seasonal else 0
    • max_p=3, max_q=3, max_P=2, max_Q=2
    • stepwise=True, information_criterion='aic'
  4. Fallback: If auto_arima raises, fit SARIMAX(1, d, 1) × (1, 1, 1, 7) or ARIMAX(1, d, 1) directly.
  5. Forecast horizon: 60 days forward from end of training data. Flag with ⚠ forecast horizon (60d) > training data (Nd) when N < 60 — extrapolation is unreliable.

5. Bootstrap on ratio metrics — the right way

For ratio metrics (ROAS, CVR) the wrong way is to bootstrap each day's ratio then take mean of means (that understates uncertainty). The right way:

point = sum(numerator_daily) / sum(denominator_daily)
for b in 1..B:
    idx = random_choice_with_replacement(range(n_days), n_days)
    boot[b] = sum(numerator[idx]) / sum(denominator[idx])
ci = percentile(boot, [2.5, 97.5])

This is day-level cluster bootstrap of the ratio, which is what you want for a campaign-level metric.

6. Visualization (the only one Pouya wants)

3-panel horizontal layout, one PNG per city:

| Test 1: ROAS bars        | Test 3: CVR bars         | Test 2: SARIMAX 60-day  |
| + 95% bootstrap CI        | + 95% bootstrap CI       | overlay of all 3 prices |
| winner starred            | winner starred           | solid=history, dashed=  |
|                           |                          | forecast, shaded=95% CI |

Style rules (per user preference, hard-coded in the template):

  • Horizontal 3-panel, figsize ≈ (20, 6.5), dpi 140.
  • Bar value labels: ≥ 15 pt bold, centered above bar.
  • CI labels: ≥ 9–10 pt, below the lower error bar.
  • One color per price, consistent across all 3 panels (A2=blue, B=green, C2=orange, D2=red).
  • "★ WINNER" annotation above the best bar in each of panels 1 & 2.
  • SARIMA panel legend lists each price with its fitted model label (e.g. Price A2 — SARIMA(1,1,1)(1,1,1,7)).
  • Title is {CITY} — 3-Price Campaign Winner Analysis, 20pt bold.
  • Save to /root/.hermes/output/city_3price/{City}.png.

Reusable code: see templates/analyze_template.py — copy, change FILES dict, run. Whole pipeline takes ~30-60s per city.

7. Output format (per city)

One paragraph, ~3-5 sentences, in this order:

  1. State the winner per test (ROAS winner / CVR winner / SARIMAX winner).
  2. Note whether all 3 tests agree, and on what (e.g. "all three point to A2").
  3. One quantitative anchor (e.g. "A2 ROAS 3.50× vs C2 3.41× vs D2 3.18× — A2 is +0.09× over C2, well inside the CIs").
  4. Caveat: training-data length, forecast reliability, whether CIs overlap.

Deliver visualizations first, text second (per Pouya's standing preference — also captured in memory). Use MEDIA:/root/.hermes/output/city_3price/{City}.png to inline-send.

⚠ Cross-platform mirroring pitfall (captured 2026-06-02): When this chat session is mirrored to another platform (TUI ↔ Telegram), the MEDIA: inline path and the send_message tool both may not render as a visible image on the receiving end, even when the tool returns a successful message_id. Symptom: user says "This is text / Where are the pngs?" or "I don't see zip" repeatedly. Do NOT retry the same delivery mechanism twice — pivot on the first complaint to a guaranteed fallback (ZIP with a pull-hosted path, base64 in a code block, or ask the user which channel to use). The 3-retry loop on a broken delivery path is one of Pouya's top frustrations.

8. Pitfalls (full list)

  • P1 — Column index trap: see §3. Symptom: revenue = $0. Fix: use indices 8/9/13/20/24 for spend/impressions/purchases/clicks/revenue.
  • P2 — Stale bootstrap count: don't hard-code 10,000; read from the request.
  • P3 — SARIMA on too-short series: if data < 28 days, don't force SARIMA. If horizon (60) > training N, flag it explicitly.
  • P4 — Bootstrap of ratio metrics: resample DAYS (not rows from the numerator alone); recompute the ratio each iter.
  • P5 — Unnamed: 0 confusion: the dataframe has more integer columns than the user thinks. Always df.head(1).to_string() to see the actual layout before coding the loader.
  • P6 — Currency: revenue is in dollars, not thousands. Don't divide by 1000.
  • P7 — Aggregate row at top: Meta's export has an "All" row right after the header. The day-detection regex (pd.to_datetime(df['c2'], errors='coerce').notna()) filters it out correctly because "All" is not a date — but if you change the loader, re-verify.
  • P8 — Filename casing is messy: Denton-3-prices.xlsx, Calgary-3-PRICES.xlsx, Edmonton.xlsx (no "3-prices" suffix!), Waukesha-3-prices.xlsx. The loader keys are hard-coded — don't try to glob.

9. Quick-start (full reusable script)

The complete runnable template is at templates/analyze_template.py. Workflow:

# 1. Inspect a file's layout (one-liner)
python3 -c "import pandas as pd; df=pd.read_excel(FILE, header=None); print(df.iloc[2].tolist())"

# 2. Edit FILES dict in templates/analyze_template.py, set N_BOOT from request

# 3. Run
python3 analyze_template.py
# → 4 PNGs in /root/.hermes/output/city_3price/{City}.png
# → console summary table

10. Reference files

  • references/meta_export_layout.md — the xlsx layout in detail + a copy-paste loader.
  • references/sarimax_model_selection.md — the auto-selection rules + ACF significance test + worked example.
  • references/bootstrap_on_ratios.md — why day-level resample, why NOT row-level.
  • templates/analyze_template.py — the full self-contained runnable script.