Spot Meta Ads Creative Fatigue and ROAS Decay from Ads Manager CSV

How to export Meta Ads Manager data with time breakdowns

Looking at a single 30-day total in Meta Ads Manager hides creative fatigue. For example (sample numbers), an ad that achieved a 4.2x ROAS in Week 1 and fell to a 0.9x ROAS in Week 4 still shows a respectable 2.4x blended average across the month. Exporting with a Time breakdown exposes the decay curve.

1. Open Meta Ads Manager, select the Ads tab, and set your date range to the last 28 or 60 days.
2. Click the Breakdown dropdown, choose By Time, and select Day or Week.
3. Select the Performance and Clicks (or Engagement / Video Engagement) column preset and add Purchases, Purchase conversion value, Purchase ROAS (return on ad spend), and Cost per results.
4. Click Reports > Export table data, choose Export as .csv (uncheck Include summary rows), and click Export.

Three creative fatigue formulas and SQL equivalents

1. Peak Link CTR vs. Current Week Link CTR (FIXED LOD)

Creative fatigue appears in CTR (link click-through rate) and CPC (cost per link click) 5 to 10 days before CPA spikes. Compare each ad's latest weekly Link CTR against its best historical week (among weeks with at least 2,000 impressions) using a FIXED Level of Detail expression.

xmetric / Tableau formula ([Weekly Link CTR]):

SUM([Link clicks]) / NULLIF(SUM([Impressions]), 0)

xmetric / Tableau formula ([Peak Weekly Link CTR]):

{ FIXED [Ad name] :
  MAX(IF [Impressions] >= 2000 THEN [Link clicks] / [Impressions] END)
}

SQL equivalent:

WITH weekly_ad_stats AS (
  SELECT
    "Ad name" AS ad_name,
    "Ad set name" AS ad_set_name,
    "Reporting starts" AS week_start,
    SUM("Amount spent (USD)") AS spend,
    SUM("Impressions") AS impressions,
    AVG("Frequency") AS frequency,
    SUM("Link clicks") * 100.0 / NULLIF(SUM("Impressions"), 0) AS link_ctr_pct,
    SUM("Amount spent (USD)") / NULLIF(SUM("Purchases"), 0) AS cpa,
    SUM("Purchase conversion value") / NULLIF(SUM("Amount spent (USD)"), 0) AS purchase_roas
  FROM meta_ads_export
  WHERE "Ad name" IS NOT NULL
  GROUP BY 1, 2, 3
),
scored AS (
  SELECT
    *,
    MAX(link_ctr_pct) FILTER (WHERE impressions >= 2000) OVER (PARTITION BY ad_name) AS peak_link_ctr_pct,
    MIN(cpa) FILTER (WHERE spend >= 100) OVER (PARTITION BY ad_name) AS best_cpa,
    MAX(week_start) OVER () AS latest_week
  FROM weekly_ad_stats
)
SELECT
  ad_name,
  ad_set_name,
  ROUND(spend, 2) AS latest_week_spend,
  ROUND(frequency, 2) AS latest_frequency,
  ROUND(peak_link_ctr_pct, 2) AS peak_ctr_pct,
  ROUND(link_ctr_pct, 2) AS current_ctr_pct,
  ROUND((link_ctr_pct - peak_link_ctr_pct) * 100.0 / NULLIF(peak_link_ctr_pct, 0), 1) AS ctr_drop_from_peak_pct,
  ROUND(best_cpa, 2) AS best_cpa,
  ROUND(cpa, 2) AS current_cpa,
  ROUND(purchase_roas, 2) AS current_roas
FROM scored
WHERE week_start = latest_week
  AND spend >= 100
ORDER BY latest_week_spend DESC;

2. True Weighted CPA and ROAS Across Days (Avoiding Averaged Ratios)

Never average the Purchase ROAS (return on ad spend) or Cost per results columns across daily rows in a spreadsheet: a day with 10 in spend and 8.0x ROAS would receive the same weight as a day with 1,000 in spend and 1.1x ROAS. Always compute ratios from summed numerators and denominators in xmetric.

xmetric / Tableau formula ([True Period CPA]):

SUM([Amount spent (USD)]) / NULLIF(SUM([Purchases]), 0)

xmetric / Tableau formula ([True Period ROAS]):

SUM([Purchase conversion value]) / NULLIF(SUM([Amount spent (USD)]), 0)

3. Thumbstop Ratio (3-Second Video Plays / Impressions)

For video creatives, add 3-second video plays to your Meta Ads export to distinguish hook fatigue (falling Thumbstop ratio) from landing page or offer fatigue (stable Thumbstop and Link CTR, but falling conversion rate).

xmetric / Tableau formula ([Thumbstop Rate]):

SUM([3-second video plays]) / NULLIF(SUM([Impressions]), 0)

Where Meta Ads Manager, Data Studio (formerly Looker Studio), and xmetric fit

• Data Studio (formerly Looker Studio) Requires Paid 3rd-Party Partner Connectors for Meta Ads: Data Studio (renamed from Looker Studio in April 2026; free self-service tier, Pro at $9 per user per project per month as of October 2026) has no free Google-built connector for Meta Ads; connecting Meta Ads to Data Studio requires a paid third-party partner connector subscription (such as Supermetrics, Funnel, or Power My Analytics). Dropping your Meta Ads CSV into xmetric is free and needs no account or connector subscription.
• Unique Reach & Frequency Across Daily Rows: Because Reach is a deduplicated unique user count, summing daily Reach values across 30 daily rows overcounts unique reach and understates 30-day cumulative Frequency. Use daily/weekly Frequency to watch week-over-week saturation trends, or export a second un-broken-down summary CSV to inspect cumulative 30-day reach.