Update to Supermetrics Marketing Agent

{
  "description": "Ecommerce paid acquisition analysis from live ad and store data: blended and per-platform ROAS with day-of-week patterns, promo anomaly detection, SKU-level performance, new vs returning customer ROAS, AOV and basket composition, subscription retention cohorts and pre-promo planning. Use for Shopify and other online stores advertising on Google, Meta and TikTok.",
  "included_files": [
    {
      "relative_path": "agents/openai.yaml",
      "size_in_bytes": 251
    },
    {
      "relative_path": "assets/aov_summary.md",
      "size_in_bytes": 2928
    },
    {
      "relative_path": "assets/customer_acquisition_brief.md",
      "size_in_bytes": 3032
    },
    {
      "relative_path": "assets/dow_budget_strategy.md",
      "size_in_bytes": 4220
    },
    {
      "relative_path": "assets/icon-small.png",
      "size_in_bytes": 2921
    },
    {
      "relative_path": "assets/media_buyer_alert_email.md",
      "size_in_bytes": 3228
    },
    {
      "relative_path": "assets/promo_plan_doc.md",
      "size_in_bytes": 3955
    },
    {
      "relative_path": "assets/retention_cohort_report.md",
      "size_in_bytes": 3915
    },
    {
      "relative_path": "assets/sku_performance_report.md",
      "size_in_bytes": 2692
    },
    {
      "relative_path": "references/ecommerce-marketer-guide.md",
      "size_in_bytes": 8130
    },
    {
      "relative_path": "scripts/anomaly_detector.py",
      "size_in_bytes": 9339
    },
    {
      "relative_path": "scripts/aov_analyzer.py",
      "size_in_bytes": 4845
    },
    {
      "relative_path": "scripts/blended_roas.py",
      "size_in_bytes": 8025
    },
    {
      "relative_path": "scripts/customer_type_roas.py",
      "size_in_bytes": 4150
    },
    {
      "relative_path": "scripts/promo_planner.py",
      "size_in_bytes": 5564
    },
    {
      "relative_path": "scripts/sku_performance.py",
      "size_in_bytes": 5684
    },
    {
      "relative_path": "scripts/subscription_cohorts.py",
      "size_in_bytes": 5890
    }
  ],
  "name": "ecommerce-roas-analysis",
  "skill_md_contents": "---\nname: ecommerce-roas-analysis\ndescription: Ecommerce paid acquisition analysis from live ad and store data: blended and per-platform ROAS with day-of-week patterns, promo anomaly detection, SKU-level performance, new vs returning customer ROAS, AOV and basket composition, subscription retention cohorts and pre-promo planning. Use for Shopify and other online stores advertising on Google, Meta and TikTok.\n---\n\n# Ecommerce roas analysis\n\n## Workflows\n\n### Cross-platform ROAS and purchase behavior\n**When to use:** the user is doing a periodic (usually monthly) review of paid acquisition efficiency across their main ecommerce platforms, and wants to surface day-of-week patterns to inform bid strategy.\n\n**Platforms typically involved:** Google Ads and Facebook Ads at minimum. Sometimes TikTok Ads, Pinterest Ads, Snapchat Ads, Amazon Ads.\n\n### Step 1 — Pull the data\n> Pull the last 30 days of daily ad spend and purchase conversion value from [Google Ads, Facebook Ads, etc.].\n\nDaily granularity matters — day-of-week analysis requires it. Make sure data has conversion value (revenue), not just count. If conversion value isn't populated, this workflow can't run as designed; surface that and offer the count-based version instead.\n\n### Step 2 — Compute blended ROAS\nUse `scripts/blended_roas.py`. The script computes blended ROAS daily, weekly, and by day-of-week. Mention the double-count caveat once when introducing the number, not repeatedly.\n\n### Step 3 — Visualize the trend and patterns\nTwo questions worth surfacing, often as two charts:\n\n**\"Is ROAS trending up, down, or stable?\"** A line chart of daily blended ROAS over the 30-day window, with a horizontal reference line at the period average. The chart makes drift visible — ROAS that's volatile day-over-day tells a different story than ROAS drifting in one direction.\n\n**\"Which days of the week perform best?\"** This is the prime case for a heatmap: rows = day of week (Monday-Sunday), columns = a single column if data is daily-only, or hour columns if hourly data is available. Cell color = ROAS or conversion rate, with a diverging red-to-green scale centered on the period average. Above-average days appear green, below-average appear red, intensity shows magnitude.\n\nIf a heatmap is overkill for the user's data shape, a sorted horizontal bar chart of average ROAS by day of week works as the fallback.\n\n### Step 4 — Budget weighting recommendation\n> Build a strategic plan for adjusting weekly budget allocation to weight spending more heavily toward the days demonstrating the strongest purchase intent.\n\nUse `assets/dow_budget_strategy.md`. The recommendation should propose a weighting (e.g. Tuesday +20%, Friday -15%) and explicitly flag what would invalidate the strategy — usually a promo period scrambling normal patterns.\n\n### Step 5 — Output\nDefault delivery: charts in chat plus a markdown recommendation. For a ROAS view the team will reopen, offer once to publish it as a live Studio dashboard (with `manage_dashboards`). If the user wants a Word doc to share with the team, use the `docx` skill.\n\n### Common variations\n- **Add TikTok, Pinterest** — same workflow, expand the platform list. Watch for platforms with low spend producing noisy daily numbers.\n- **Compare to prior 30-day period** — pull a second window and overlay both lines on the trend chart.\n- **Break down by product category** — needs product-level data from Shopping/catalog feeds. Available if the user runs Shopping campaigns; not available if they run purely image/video.\n\n---\n\n### Promotional campaign anomaly detection\n**When to use:** the user is running a high-stakes promo (BFCM, holiday sale, flash sale) and wants to catch cost-per-purchase anomalies before they burn the day's budget.\n\n**Platforms typically involved:** Facebook Ads most often (BFCM spend concentrates there for many DTC brands). Google Ads where applicable.\n\n### Step 1 — Pull yesterday's promo data\n> Pull yesterday's data for the [promo campaign filter — e.g. campaigns containing \"BFCM\" or \"Black Friday\"] on Facebook Ads. Include cost, impressions, and total purchases per ad set.\n\nUse the user's actual filter convention. Confirm if unclear. Ad-set granularity matters — a campaign-level 3x spike might be driven by one bad ad set, not the whole campaign.\n\n### Step 2 — Pull the baseline\nGet the prior 6 days of data for the same campaigns. The script computes the rolling-average baseline.\n\n### Step 3 — Flag the anomaly\nUse `scripts/anomaly_detector.py`. Default threshold: yesterday's cost-per-purchase >3x the trailing 6-day average. The script identifies which ad sets are responsible and flags candidate causes (tracking failure vs real performance issue).\n\nIf no anomaly is detected, surface that clearly. \"No anomaly, performance within expected range\" is a valid and useful answer.\n\n### Step 4 — Visualize the spike (if one exists)\nThe question the chart needs to answer: **which ad sets are anomalous, by how much, and what's the likely cause?**\n\nA line chart showing the trailing 7 days of cost-per-purchase per anomalous ad set works well — most days sit in a horizontal band, then yesterday's value spikes upward. The spike day gets highlighted in amber. A reference line at the baseline average makes the deviation visible.\n\nWhen multiple ad sets are anomalous, a sorted horizontal bar chart of yesterday's CPP-to-baseline ratio surfaces which are the worst — the user fixes the biggest bleeders first.\n\nFor non-anomalous ad sets, no chart is needed — just the headline.\n\n### Step 5 — Verify before alerting\nBefore drafting any alert email: verify the spike is real, not a tracking issue. The most common causes of an apparent 3x spike are pixel failures, renamed events, or deduplication issues — not real performance failures. The script's candidate-cause column points at likely explanations; investigation is on the user.\n\n### Step 6 — Draft the urgent alert\nUse `assets/media_buyer_alert_email.md`. The email needs to be short, specific (named ad sets), and recommend a concrete action.\n\nDefault delivery: markdown inline so the user reviews before sending. If Gmail is connected, offer to save as a draft. If the user prefers Slack and a Slack connector is available, post the alert to the relevant channel instead — often faster during a promo when the media buying team is fielding many messages per hour.\n\n### Common variations\n- **Baseline isn't 6 days, it's same-promo-week-last-year** — the script only supports a trailing baseline (`--baseline-days`); compute ratios against last year's promo week ad hoc.\n- **Monitor ROAS, not cost per purchase** — same workflow, swap the metric. Pass `--metric roas`.\n- **Hourly granularity, not daily** — supported on Facebook and Google but noise floor is high. Use only for high-spend ($50k+/day) campaigns.\n\n---\n\n### Product / SKU-level performance\n**When to use:** the user wants to know which specific products are driving paid-acquisition revenue, not just which campaigns. Used to inform creative direction, inventory planning, and bid strategy.\n\n**Platforms typically involved:** an ecommerce backend (Shopify, BigCommerce, WooCommerce, Adobe Commerce, Squarespace, Wix, PrestaShop, Centra, Ecwid, Amazon Seller Central, TikTok Shop) + ad platforms with product-level reporting (Google Shopping, Meta Catalog ads, TikTok Shop ads).\n\n### Step 1 — Pull product-level sales\n> Pull the last 30-60 days of product-level sales from [Shopify / BigCommerce / WooCommerce]: SKU, product name, units sold, revenue, gross margin if available.\n\n### Step 2 — Pull ad-driven attribution\n> Pull product-level performance from [Google Shopping / Meta product catalog / TikTok Shop ads]: SKU, impressions, clicks, spend, attributed purchases, attributed revenue.\n\n### Step 3 — Join and rank\nUse `scripts/sku_performance.py`. The script:\n- Joins ad-driven attribution with total sales\n- Computes the % of each SKU's revenue that came from paid ads\n- Surfaces high-margin SKUs underspending on ads (opportunity)\n- Surfaces low-margin SKUs overspending on ads (cut candidates)\n- Flags SKUs with high return rates if return data is available\n\n### Step 4 — Visualize the SKU portfolio\nThe question the chart needs to answer: **which products deserve more ad investment, which deserve less?**\n\nA scatter plot works well: x-axis = total revenue, y-axis = ad ROAS for that SKU. Dots colored by margin tier (high/medium/low margin), sized by units sold. The user sees the high-revenue, high-ROAS, high-margin winners immediately.\n\nA secondary view: top 20 SKUs by ad-attributed revenue, with bars colored by margin tier. Surfaces concentration — often 5-10 SKUs drive 80% of ad revenue.\n\n### Step 5 — Build the report\nUse `assets/sku_performance_report.md`. The doc should recommend specific products for:\n- **Scale up** — high ROAS, high margin, inventory available\n- **Hold** — performing as expected\n- **Cut from ads** — low ROAS or low margin, ad spend not earning\n- **Add to ads** — strong organic sales, not yet promoted, high margin\n\n### Common variations\n- **Add inventory levels** — if an inventory feed is available, surface SKUs with low stock to avoid promoting out-of-stock products.\n- **By collection or category** — instead of SKU-by-SKU, group into categories for higher-level decisions.\n- **Seasonal SKUs** — for seasonal products, surface the year-over-year same-period comparison.\n\n---\n\n### New vs returning customer ROAS by channel\n**When to use:** the user wants to know whether their ad spend is acquiring new customers or just re-converting existing ones. Different channels often have very different splits.\n\n**Platforms typically involved:** ad platforms + ecommerce backend with customer-type segmentation (Shopify, BigCommerce, etc.) + GA4 if available (for the customer-type dimension on conversions).\n\n### Step 1 — Pull channel-level spend\n> Pull the last 30-60 days of spend by channel from [ad platforms].\n\n### Step 2 — Pull purchases segmented by customer type\n> Pull purchases segmented into \"new customer\" (first-time buyer) and \"returning customer\" (repeat) from [Shopify / GA4]. Break out by channel.\n\nPrefer the store's own flag (Shopify `order_is_returning_customer`, with `order_utm_source` for channel) — it is purchase-based. GA4's `newVsReturning` counts prior sessions, not prior purchases, so it isn't a buyer split; GA4's first-time purchaser metrics are closer.\n\n### Step 3 — Compute split ROAS\nUse `scripts/customer_type_roas.py`. Outputs per channel:\n- Total ROAS\n- New-customer ROAS (revenue from new buyers / spend)\n- Returning-customer ROAS\n- % of revenue from new customers\n- Cost per new customer (CAC)\n\n### Step 4 — Visualize the split\nThe question the chart needs to answer: **which channels acquire new customers, and which mostly serve repeat buyers?**\n\nA stacked bar chart per channel works well: each bar shows the channel's total revenue, split into new and returning customer revenue. Surfaces channels that are mostly retargeting vs mostly acquisition.\n\nA secondary view: paired bars per channel showing new-customer-CAC and returning-customer-CAC side by side. Some channels acquire new customers at $X but the channel's apparent ROAS is inflated by returning-customer revenue.\n\n### Step 5 — Build the brief\nUse `assets/customer_acquisition_brief.md`. The interesting finding usually centers on which channels are real acquisition vs which are retargeting in disguise.\n\n### Common variations\n- **Compare to organic** — many ecommerce businesses get more new customers via organic than paid. Surface organic for context.\n- **Subscription / repeat purchase business** — the workflow becomes \"first-purchase ROAS\" vs \"subsequent-purchase ROAS\" where channels driving subsequent purchases are particularly valuable.\n- **By acquisition cohort** — track new customers acquired this month by their first-purchase channel; see how they behave over the next 90 days.\n\n---\n\n### AOV and basket composition\n**When to use:** the user wants to understand average order value (AOV) and items-per-order patterns, often as a precursor to upsell, cross-sell, or free-shipping-threshold decisions.\n\n**Platforms typically involved:** ecommerce backend (Shopify, BigCommerce, WooCommerce, etc.) + ad platforms for channel-level breakdown.\n\n### Step 1 — Pull order-level data\n> Pull the last 30-90 days of order-level data from [ecommerce backend]: order value, item count, products, channel attribution if available.\n\nIf channel attribution isn't on the order data, join with ad-platform purchase data by date/customer.\n\n### Step 2 — Compute AOV and basket patterns\nUse `scripts/aov_analyzer.py`. Outputs:\n- AOV by channel\n- Items per order distribution\n- AOV percentiles (P25, P50, P75, P90) to understand the distribution shape\n- Free-shipping-threshold proximity (% of orders just under common thresholds like $50, $75, $100)\n\n### Step 3 — Visualize the patterns\nThe question the chart needs to answer: **what does the basket look like, and where's the upsell opportunity?**\n\nA box plot of AOV by channel surfaces both central tendency and distribution. A complementary histogram of order values with vertical reference lines at common free-shipping thresholds shows the upsell opportunity.\n\nA bar chart of items-per-order distribution (1, 2, 3, 4+ items) by channel shows which channels drive single-item vs basket-building purchases.\n\n### Step 4 — Build the summary\nUse `assets/aov_summary.md`. The most-useful section is usually the free-shipping-threshold analysis — if a meaningful % of orders are just under the threshold, raising or lowering it has predictable effects.\n\n### Common variations\n- **Pre/post threshold change** — if the user has changed their free-shipping threshold recently, compare AOV distributions pre/post.\n- **Promotional vs non-promotional periods** — AOV typically rises during promos (people buy more to hit discount thresholds). Surface the contrast.\n- **First-time-buyer AOV vs repeat-buyer AOV** — repeat buyers usually have higher AOV. The size of the gap tells you how much retention is worth.\n\n---\n\n### Subscription / retention cohort analysis\n**When to use:** the user runs a subscription business (Recharge on Shopify, native subscription products) or a repeat-purchase ecommerce business and wants to track retention by acquisition cohort.\n\n**Platforms typically involved:** subscription platform (Recharge), payments (Stripe), or ecommerce backend with subscription/repeat-purchase data + ad platforms for cohort acquisition cost.\n\n### Step 1 — Pull cohort data\n> Pull customers grouped by acquisition month for the last 12 months. For each cohort, pull monthly revenue or active subscriber count for each month since acquisition.\n\n### Step 2 — Pull acquisition costs\n> Pull marketing spend per month over the same period. If channel-level CAC is available, pull that too.\n\n### Step 3 — Compute retention metrics\nUse `scripts/subscription_cohorts.py`. Outputs:\n- Cohort retention curve (% of cohort still active by month-since-acquisition)\n- Cohort cumulative revenue per customer\n- Time-to-payback (months for cumulative revenue per customer to exceed CAC)\n- Cohort LTV at 3/6/12/24 month horizons\n\n### Step 4 — Visualize the cohort behavior\nThe question the chart needs to answer: **how does customer behavior change with cohort age, and are recent cohorts behaving like older ones?**\n\nTwo complementary visualizations:\n- **Retention curves**: line chart with one line per cohort. X-axis = months since acquisition, Y-axis = % of cohort still active or % of cohort generating revenue. Steeper curves = worse retention; flatter curves = better retention. Overlapping curves means recent cohorts behave like older ones; diverging curves (with newer cohorts lower) is a warning sign.\n- **Cumulative cohort revenue per customer**: line chart with one line per cohort showing $ revenue per customer over time. Reach to CAC level marks the payback moment.\n\n### Step 5 — Build the report\nUse `assets/retention_cohort_report.md`. The audience is typically the marketing leader and possibly the CFO — retention numbers feed unit economics decisions.\n\n### Common variations\n- **By acquisition channel** — different channels often produce cohorts with very different retention. The cheapest CAC channel often has the worst retention.\n- **By acquisition campaign or promo** — heavy promo cohorts (50% off first month) typically have worse retention than full-price cohorts. Worth surfacing.\n- **By product mix on first purchase** — for ecommerce, the first product purchased often predicts retention. Subscription-product-first vs one-time-product-first cohorts behave very differently.\n\n---\n\n### Pre-promo planning model\n**When to use:** the user is planning a high-stakes promotional period (BFCM, summer sale, etc.) and needs to set budget, AOV, and ROAS targets based on historical performance.\n\n**Platforms typically involved:** ad platforms + ecommerce backend, both showing prior promo periods.\n\n### Step 1 — Pull prior promo data\n> Pull spend, purchases, revenue, AOV, and ROAS from the equivalent promo period last year (and the year before if available) across all relevant channels.\n\nUse the actual promo dates, not \"last November\" — promos have specific date windows that matter.\n\n### Step 2 — Pull baseline data\n> Pull the same metrics from the trailing 30 days before the prior promo started. Used to compute the promo lift over baseline.\n\n### Step 3 — Model the upcoming promo\nUse `scripts/promo_planner.py`. Inputs:\n- Last year's promo numbers (auto-derived from the pulled data)\n- Year-over-year growth assumption (user provides)\n- Planned budget for the upcoming promo\n\nOutputs:\n- Projected purchases, revenue, AOV, ROAS for the upcoming promo\n- Per-channel projected spend and outcomes\n- Stretch and conservative scenarios\n\n### Step 4 — Visualize the plan\nA bar chart comparing prior year actuals vs proposed plan for each metric (spend, purchases, revenue, AOV, ROAS) makes the proposal scannable.\n\nA second chart projects daily spend over the promo period — useful for media buyers planning the pacing.\n\n### Step 5 — Build the plan\nUse `assets/promo_plan_doc.md`. The plan must include:\n- Targets per metric (with stretch and conservative bands)\n- Per-channel allocation\n- Daily pacing\n- Anomaly thresholds (when to alert the team mid-promo)\n- Post-promo measurement plan\n\n### Common variations\n- **First-time-running-this-promo** — no prior data. Use industry benchmarks from web search; surface the higher uncertainty explicitly.\n- **Different mix this year** — if the channel mix differs from prior year (e.g. adding TikTok), the prior-year model needs adjustment. Surface what's being assumed.\n- **Multi-week promo** — model per-week pacing if the promo runs more than 3-4 days. Customer behavior often differs early-promo vs late-promo.\n\n---\n\n## Ad hoc analysis\n\nFor one-off questions outside these seven workflows (\"what's the AOV distribution across our top SKUs in this campaign?\", \"is there an LTV difference between Facebook and Google traffic?\", \"did this discount code drive incremental purchases or cannibalize existing demand?\"), write fresh analysis code. The \"Choosing the right visualization\" framework in the SKILL.md tells you which chart family fits.\n\n---\n\n## A note on promo-period analysis\n\nNormal-period and promo-period analysis behave differently:\n\n- **Conversion rates spike during promos** because intent is high. Day-of-week patterns from non-promo periods don't predict promo behavior.\n- **CPMs spike during promos** because everyone is bidding harder. Cost-per-purchase increases during BFCM aren't always efficiency loss — it might be competitive pressure.\n- **Pixels and tracking break more during promos** because traffic volume is higher and edge cases get exposed. Before alerting on an anomaly, verify tracking is healthy.\n\nWhen the user is doing post-promo analysis, frame as \"vs. last year's same period\" rather than \"vs. last month\" — same-period comparison removes the seasonal confound.\n\n## Background\n\nChart choices, principles and gotchas for this area: [references/ecommerce-marketer-guide.md](references/ecommerce-marketer-guide.md).\n\n## Before you analyze\n\n- Load saved targets, budgets and known context with `manage_business_context` (`action: get`) and judge results against them, not generic benchmarks.\n- Query correctly: follow the `marketing-data-queries` skill for discovery, field IDs and platform quirks.\n- Check tracking first. If platform conversions and GA4 or backend numbers disagree by more than 20%, or spend continues with zero conversions, say so before any optimization advice.\n- Pull, then analyze, as separate steps. Run one heavy query at a time.\n- Evidence discipline: treat changes on fewer than ~10 conversions as noise, ignore items under 1,000 impressions, don't call learning-phase campaigns failures, check for shared budgets before budget advice, and re-pull before conceding a disputed number.\n- Answer shape: conclusion, business impact, next actions, then \"How I got this\" (one line per data pull).\n- Helper scripts in `scripts/` take a CSV and print a table. Run them if you can execute code; otherwise compute the same thing directly. Templates in `assets/` shape the final deliverable.\n\n## After the analysis\n\n- Analysis only recommends. If the user wants to act (pause, budget shift, negatives), use the `campaign-management` skill, with explicit approval for each change.\n- For a recurring or shareable result, offer once to publish it as a live Supermetrics Studio dashboard with `manage_dashboards` (static preview first, then the live upload).\n"
}