Skip to content
Picaro.AI Picaro.AI Prompt Library

Analyze cross-ad customer behavior with AMC

On this page, you can use Amazon Marketing Cloud (AMC) data to break down what ACoS / ROAS alone can’t show: “how much does each customer earn us?”, “do the customers ads brought in keep buying months later?”, and “which campaigns have started to saturate?”. It covers customer-based analysis across SP / SB / SD / DSP, all the way to ASIN portfolio, campaign judgment, and budget allocation.

About this page

Section titled “About this page”
ItemDetails
What you can doFrom AMC data, decompose LTV / CAC / NTB / retention / ad-type attribution from a customer perspective, and lay out the inputs for ASIN portfolio, campaign judgment, and budget allocation. Also generates SQL queries for the AMC console
Applies toAMC (Amazon Marketing Cloud). Cuts across SP / SB / SD / DSP and analyzes the customers who touched your ads at the customer level
Data & connection neededAn AMC instance must be connected. Aggregation is limited to segments of 100+ users (AMC’s privacy protection spec). Periods / ASINs / campaigns with too few users return “aggregation suppressed”
ScopeMostly analysis only (read-only). Prompts 9 & 10 only generate SQL queries to run in the AMC console (Picaro never executes SQL on AMC). Neither changes any ad settings

When to use this

Section titled “When to use this”
  • You want per-customer LTV / CAC / true ROAS (including brand halo)
  • You want a monthly brand-wide health check (NTB rate / repeat purchase rate / LTV:CAC trend)
  • You want to sort ASINs by role (new-customer acquisition / repeat retention / scale-down or exit consideration)
  • You want to see cohort retention and LTV growth for new-customer groups by acquisition month
  • You want to compare NTB acquisition power / CAC across ad types (SP / SB / SD / DSP) to decide budget allocation
  • You want to build SQL queries to run in the AMC console

Before you run

Section titled “Before you run”
  • Connected account and AMC instance … confirm the target account has an AMC instance connected. Dashboard users just pick an already-connected account; MCP users must map the AMC instance in their connection settings.
  • Target period … for Prompts 1-8, AMC’s own spec means you cannot specify an arbitrary period (it is fixed by the run, or fixed to a monthly report_month; see the :::caution above for details). The period actually aggregated is stated in each prompt’s output, so check it there. Prompts 9 & 10 (SQL generation) are exempt from this constraint — you can specify any period in the SQL they generate.
  • 100+ users requirement … aggregation is limited to segments of 100+ users (AMC’s privacy protection spec). Periods / ASINs / campaigns with too few users return “aggregation suppressed.” This is a platform spec, not a limitation.
  • Prompts 9 & 10 have a different scope … these two only generate SQL queries to run in the AMC console. Picaro never executes arbitrary SQL on AMC.

Quick reference

Section titled “Quick reference”
GoalWhat you getChangesPrompt
Produce per-customer LTV / CAC / true ROASA table of customer count, revenue, CAC, LTV (with / without halo), repeat purchase rate, and LTV:CAC ratioAnalysis onlyPrompt 1
See a monthly brand-wide health checkMonthly NTB rate, repeat purchase rate, CAC, LTV, LTV:CAC ratio, and a trend judgmentAnalysis onlyPrompt 2
See NTB (new-customer) acquisition strength by ASINPer-ASIN NTB rate, ad spend, ACoS, and scale-up / scale-down labelsAnalysis onlyPrompt 3
Sort ASINs by role (new-customer acquisition / repeat retention / scale-down consideration)A 3-category classification of ASINs and a budget reallocation proposalAnalysis onlyPrompt 4
See cohort retention and LTV for new-customer groups by acquisition monthRetention and LTV of each acquisition-month group, with a trend judgmentAnalysis onlyPrompt 5
See the post-purchase retention funnelM+1 / M+3 / M+6 survival rates and a per-campaign stickiness judgmentAnalysis onlyPrompt 6
Compare NTB acquisition power and revenue by ad typePer-ad-type NTB rate, CAC, ROAS, and a budget allocation recommendationAnalysis onlyPrompt 7
Compare M+1 return rate (customer quality) by ad typePer-ad-type return rate and a sticky / flash classificationAnalysis onlyPrompt 8

Analyze baseline metrics

Section titled “Analyze baseline metrics”

Start with the big picture. Get a handle on per-customer economics (LTV / CAC / true ROAS) and the overall health of the brand.

Prompt 1: Calculate customer LTV / CAC / true ROAS

Section titled “Prompt 1: Calculate customer LTV / CAC / true ROAS”

Calculate per-customer LTV (lifetime value), CAC (customer acquisition cost), repeat purchase rate, and halo-inclusive ROAS for customers acquired through ads. Returns two sets of figures depending on whether revenue includes only the ad-clicked ASIN, or also the other same-brand ASINs the customer bought (the brand halo effect = spillover revenue to same-brand products beyond the ad-target ASIN).

When to use — when you want to gauge ad economics by per-customer lifetime value and acquisition cost rather than ACoS.

What you need — nothing. The period fixed by the AMC run is used as-is; you cannot specify a target period yourself.

Goal: Understand the long-term customer value returned on ad investment and judge whether budget allocation is sound.


[Data retrieval]
Take LTV, CAC, true ROAS, and repeat purchase rate from get_amc_metric (metric_name="ltv").
This table holds revenue (halo-inclusive total_sales / ad-clicked ASINs only promoted_sales), ad spend total_spend,
buyer count purchase_uv, new-customer count ntb_buyers, and repeat purchase rate rpr_pct in the same row, so numerator and denominator line up structurally.
AMC tools take no period argument and return the report window fixed by the run. Do not assume you can specify an arbitrary period;
always state the returned report_start_date / report_end_date / run_id in the output.
Only when you need LTV trends by acquisition month, also use get_amc_metric (metric_name="cohort"),
specify max_rows_per_table at or above the total row count, and confirm truncated=false before aggregating.
If AMC returns a 403 error, state that "this account has no AMC permission/contract"; do not write that the feature itself does not exist.


[Mandatory rules when producing ratios]
1. For every ratio, state the numerator, the denominator, the target period, and the run_id.
2. Split denominators into the "all buyers (purchase_uv)" family and the "new customers (ntb_buyers)" family,
   and never build a ratio that crosses families (e.g. LTV on an all-buyer denominator ÷ CAC on a new-customer denominator).
3. Always show halo-inclusive (total_sales basis) and halo-excluded (promoted_sales basis) side by side.
4. The LTV:CAC ratio and ROAS computed on the same denominator are the same value by definition. Do not present them as two independent checks.
5. Do not mix ad spend from non-AMC tools (get_dashboard etc.) into the CAC numerator. If you do reference it, always state
   the size of the gap against AMC's total_spend and the reason (whether DSP is counted, differences in aggregation scope).
6. When using several AMC tools together, confirm that the run_id and report window match,
   and do not mix mismatched ones into the same table or the same ratio.
7. When comparing cohorts, align the number of observed months. A shorter-observed cohort having lower LTV is
   a difference in observation window, not in customer quality. Do not present a figure that sums cohorts with different observation lengths as LTV.
8. "below_reporting_floor" is non-disclosure due to the privacy threshold, not zero records. Do not aggregate it as 0.
9. When reporting an improvement in a ratio, always show the actual numerator and denominator alongside it, so the reader can tell whether the change came from a shrinking denominator or a growing numerator.
10. State any value you could not retrieve as "not retrievable." Do not substitute another metric for it.


[Output]
Line up ad-clicked ASINs only (no halo) and brand-wide (with halo), and judge — per denominator family —
whether the LTV:CAC ratio meets {{the threshold you consider healthy (e.g. 3x or higher)}}.
If the judgment splits across families, state that split itself as the conclusion.


Finally, output these 3 items.
1. Recommended action — if unhealthy, present proposals for revising budget allocation and targeting, with priority, expected effect, confidence, and risk (guardrail: do not materially damage ad-attributed revenue or absolute volume)
2. How to execute — budget-allocation changes are applied after the user reviews and approves them (this prompt does not execute them)
3. Measurement — recompute the LTV:CAC ratio quarterly with "measure the effect of an action"; if it keeps deteriorating, go deeper with "diagnose the cause," separating the CAC side (fewer new customers or higher ad spend) from the LTV side (first-purchase ATV or repeat rate)

What you can change — nothing (you cannot specify a target period). You can change the threshold you consider a healthy LTV:CAC ratio (default: 3x or higher). Check the period actually used in the output’s run_id / report_start_date / report_end_date.

What you get back — a table of customer count, total revenue, ad-attributed ASIN revenue, halo-inclusive revenue, CAC, LTV (with / without halo), repeat purchase rate, ROAS, and LTV:CAC ratio. Includes a comment judging whether the LTV:CAC ratio reaches a healthy level (3× or higher).

What the AI will do

  1. Aggregate the last 90 days of AMC data and extract customers who touched ads
  2. Compute per-customer revenue two ways: “ad-clicked ASINs only” and “brand-wide (including halo)”
  3. Divide same-period ad spend by customer count to get CAC
  4. Calculate LTV ÷ CAC and comment on health (3× or higher as a guide)
  5. Segments with fewer than 100 unique customers are excluded as “aggregation suppressed” per AMC spec

Changes — analysis only (read-only). No ad settings are changed.

Next steps — to carry the customer-lens findings into strategy, use Plan your ad strategy; to turn results into a report, use Generate reports.

Prompt 2: Monthly brand acquisition health check

Section titled “Prompt 2: Monthly brand acquisition health check”

A one-page dashboard to see “is the whole brand healthy this month?” It lines up monthly trends of NTB rate / repeat purchase rate / LTV:CAC and flags one deteriorating indicator.

When to use — when you want to monitor overall brand health every month with the same set of indicators.

What you need — the brand name. You cannot specify a target period; the most recent finalized 6 months (months that fall wholly inside a calendar month and whose data ingestion is complete) are selected automatically.

Goal: Monitor the new-customer acquisition trend of {{brand name (e.g. Lululun)}} monthly and use it to catch
deterioration early — produce a monthly brand health check from AMC data.


[Fix the target months first]
Call get_amc_new_customer and check report_start_date / report_end_date and selected_run_id in the response.
Months at the edge of the window that do not contain a whole calendar month (the first and last months), and months that are not
completed in get_data_ingestion_status(frequency=monthly), must be **excluded from the table**. Put the excluded months outside the table as
"reference values, not finalized," and do not use them for the month-over-month judgment or as a candidate for "the most concerning month."
Target the most recent 6 finalized months.


[Monthly table]
For each finalized month, always line up the following in the same row. Never show ratios alone.
- NTB acquired (numerator: new_customers) and its month-over-month change
- Total customers (denominator: total_customers) and its month-over-month change
- NTB rate (= numerator ÷ denominator) and its month-over-month difference in pp
- Make the month-over-month judgment "improving / flat / declining" **based on NTB acquired (the absolute number)**
- Add a "source of the ratio change" column stating whether the change in the ratio came from the numerator or the denominator
  (e.g. if the numerator fell but the denominator fell even further so the ratio rose, write "apparent rise driven by a shrinking denominator"
   and never call it "improving" or "healthy")
Retrieve total customers from get_amc_total_customer **with the same run_id explicitly specified as new_customer**, and
reconcile it against total_customers on the new_customer side. Only when the denominator cannot be retrieved, write "not computable (reason)."


[Handling metrics that are not available monthly]
- CAC / LTV / LTV:CAC ratio have no monthly breakdown in AMC, so present the latest snapshot from get_amc_ltv_latest_snapshot once, in a separate block,
  always alongside its run_id and report window.
- For repeat purchase rate, get_amc_metric(metric_name=retention) offers only a snapshot by ad_type, but
  cohort_ltv (cohort_month × elapsed month time_slot) from get_amc_metric(metric_name=cohort, max_rows_per_table=5000) together with
  cohort_ntb_buyers from cohort_summary lets you compute an **M1 repeat purchase rate by acquisition month**. If you do compute it, align the elapsed months and
  exclude cohorts whose M1 window is cut off at the end of the window. Do not compare cumulative repeat_purchase_uv directly
  across cohorts, because their observation lengths differ.
- The population for retention is "ad-driven NTB," which is a different population from the monthly table (brand-wide NTB). Put it in a separate block and state the difference in population.
- Do not use any estimated or assumed values; state metrics that cannot be computed as "not computable (reason)."
  If a count is returned but the detail rows are empty, do not treat that as "data exists."


[State the run_id]
AMC tools do not share run_id across tools (passing one tool's run_id to another returns 0 records).
Always state the selected_run_id and report window of every tool you used in the output.


[Judgment and next action]
Judge "improving / flat / declining" from the month-over-month change in NTB acquired, and name the single most concerning month.
For that month, check whether the NTB rate rose at the same time; if it did, always attach the actual changes in numerator and denominator and
state explicitly that the rise in the ratio must not be read as a return to health.
If you judge it declining, dig into the drivers with Prompt 7 (Compare NTB acquisition power and revenue by ad type). Re-run in 30 days
(once the target month becomes a finalized month), and if the deterioration continues, escalate to "diagnose the cause."

What you can change — the brand name only (you cannot specify a target period; it is fixed to the most recent finalized 6 months).

What you get back — a table of monthly NTB rate, repeat purchase rate, CAC, LTV, and LTV:CAC ratio, plus a trend judgment (improving / flat / declining) and a summary of the single most concerning indicator.

What the AI will do

  1. Aggregate AMC data by month
  2. Compute NTB rate / repeat purchase rate / CAC / LTV by month
  3. Judge the monthly trend direction of each indicator
  4. Judge overall health from the LTV:CAC ratio trend
  5. Extract and flag the single most deteriorating indicator
  6. Month cells below 100 unique customers are excluded per AMC spec

Changes — analysis only (read-only). No ad settings are changed.

Next steps — to fold it into a monthly report, use Generate reports.

Decide your ASIN portfolio

Section titled “Decide your ASIN portfolio”

Sort products at the ASIN level: which ASINs bring in new customers, and which ones you should scale back.

Prompt 3: See NTB (new-customer) acquisition strength by ASIN

Section titled “Prompt 3: See NTB (new-customer) acquisition strength by ASIN”

Break down, at the ASIN level, “how many new-to-brand (NTB) customers it brought in” and “whether that was worth the ad spend.” Lets you judge whether an ASIN is stronger at acquiring new customers or retaining repeat customers.

When to use — when you want to identify scale-down and scale-up candidates by new-customer acquisition power.

What you need — the target month (optional, e.g. 2026-07). NTB by ASIN is at monthly (report_month) granularity; a day count such as “the last 30 days” cannot be specified. If the month you specify is not yet finalized, the latest finalized month actually retrievable is used instead.

Goal: Understand NTB (new-customer) acquisition efficiency by ASIN and use it to decide optimal ad budget allocation.


[Retrieval and period]
AMC's NTB by ASIN is fixed to monthly (report_month); an arbitrary period such as "the last 30 days" cannot be specified. Take the latest run and state run_id / run_date / report_start_date / report_end_date up front.
Set the target month to the **last complete month** whose report_end_date reaches the end of the month ({{target month (e.g. 2026-07)}} is assumed; if the last complete month in the actual data differs, use that one and state the difference). Exclude a month that ends mid-month as a partial month.
Retrieve all records, state the LIST total count / the selected run's count / the retrieved count / whether truncated up front, and do not rank anything if the retrieval is partial.


[Aggregation rules (must follow)]
1. One row is ASIN × ad_product_type × report_month. **Sum across ad_product_type up to the ASIN level** before building the table (without summing, the same ASIN splits into several rows).
2. `below_reporting_floor` is not an ASIN — it is a **non-disclosed aggregate bucket**. **Always exclude** it from the per-ASIN table and from any ranking, and **always state the share** it represents of the target month's total purchases, NTB purchases, and ad-attributed revenue. It must not be treated as 0 records.
3. `ad_spend` is missing for some ad_product_type values. **Actually count and state which ad_product_type values always have ad_spend = 0.** Compute ACoS **with numerator and denominator aligned, using only rows where ad_spend > 0 (total ad spend ÷ total ad-attributed revenue on those same rows)**. Using the revenue of all ad_product_type values as the denominator understates ACoS. For ASINs where the two methods differ widely, show the difference alongside.
4. `total_dsp_spend` is a single brand-wide constant. Do not prorate or add it to ASINs.
5. Because of AMC's privacy floor (a lower bound on distinct user count), **some ASINs do not exist in the export at all**. Reconcile against your top ASINs by ad performance, name explicitly the **key ASINs missing from AMC**, and state their ad spend and revenue scale.


[Output]
Build a table by ASIN of total purchases, NTB purchases, NTB rate, ad-attributed revenue, ad spend, and ACoS (by the method in rule 3).
Mark ASINs with an NTB rate below {{lower bound (e.g. 20%)}} and an ACoS above {{upper bound (e.g. 30%)}} as "scale-down candidates," and the top 10 by NTB rate that are within the ACoS bound as "scale-up candidates." **If there are 0 matches, state "0 matches" and do not loosen the thresholds on your own.**


Finally, output these 3 items.
1. Recommended action — present cutting ad spend on scale-down candidates by {{reduction rate (e.g. 20%)}} and strengthening bids on scale-up candidates, with priority, expected effect, confidence, and risk (guardrail: a drop in Orders of {{tolerance (e.g. within -10%)}}). Also state, on the basis of **the share of the non-disclosed bucket and the ASINs missing from AMC**, the scope this judgment does not reach.
2. How to execute — increases and decreases in ad spend are applied after the user reviews and approves them (this prompt only generates the proposal and does not execute it)
3. Measurement — in 30 days, re-retrieve NTB rate and ACoS with "measure the effect of an action" and decide Keep if improved, Revert if worse. **On re-retrieval, fix the run, use the same ACoS calculation method as this time, and check that any improvement in the ratio does not come from a shrinking denominator (total purchases).**

What you can change — the target month (in monthly report_month units; a period expressed in days cannot be specified). The thresholds — the NTB-rate lower bound, the ACoS upper bound, the ad-spend reduction rate, and so on.

What you get back — a table per ASIN of total purchases, NTB purchases, unique customers, NTB unique customers, NTB rate, attributed revenue, ad spend, and ACoS, with “scale down” / “scale up” labels.

What the AI will do

  1. Extract per-ASIN purchase counts and new-customer counts from AMC data
  2. Compute NTB rate (NTB customers ÷ all customers) and ad spend per NTB
  3. Rank ASINs by NTB acquisition efficiency (NTB ÷ ad spend)
  4. Split into the top 10 ASINs (room to scale up) and the bottom (scale-down recommended)
  5. ASINs with fewer than 100 unique customers are excluded per AMC spec

Changes — analysis only (read-only). No ad settings are changed.

Next steps — to tidy up ASIN classification rules, use Classify ads with labels and naming rules; to sort them by role, use Prompt 4.

Prompt 4: Sort ASINs by role (new-customer acquisition / repeat retention / scale-down or exit consideration)

Section titled “Prompt 4: Sort ASINs by role (new-customer acquisition / repeat retention / scale-down or exit consideration)”

Classify each ASIN into 3 categories: “ASINs strong at acquiring new customers,” “ASINs contributing to repeat-customer retention,” and “ASINs with poor ROI that are candidates to consider scaling down, pausing, or exiting.” Used to organize the role of each ASIN across the portfolio. The third category is not “ASINs to stop immediately” — it means candidates to consider scaling down, pausing, or exiting, grounded in data.

When to use — when you want to organize the role of your whole ASIN portfolio and set a budget-allocation policy.

What you need — the target period (defaults to the last 90 days).

Using the last 90 days of AMC data, classify the ASINs currently running ads on SP / SB / SD
into 3 categories: "ASINs strong at acquiring new customers," "ASINs contributing to repeat-customer retention,"
and "ASINs with poor ROI that are candidates to consider scaling down, pausing, or exiting."
State the classification criteria (NTB rate / ad ROI / repeat purchases) explicitly,
list the top 5 ASINs of each category with their metrics,
and propose a budget reallocation plan.

What you can change — the target period (90+ days recommended to absorb seasonality).

What you get back — a table per ASIN of category, NTB rate, ad ROI, repeat purchases, and recommended action, plus a top-5-ASIN list for each category and a budget reallocation plan.

What the AI will do

  1. Extract per-ASIN NTB rate / ad ROI / repeat purchases
  2. Auto-classify into the 3 categories by a 3-axis score (criteria stated before judging)
  3. Extract the top 5 ASINs of each category
  4. Propose which ASIN group budget should go to
  5. ASINs below 100 unique customers are excluded per AMC spec

Changes — analysis and proposal only (read-only). No ad settings are changed.

Next steps — to build the concrete budget-allocation plan, use Plan your ad strategy; to turn results into a report, use Generate reports.

Analyze retention by acquisition month

Section titled “Analyze retention by acquisition month”

Track whether customers who bought once keep buying, by acquisition month and by elapsed month.

Prompt 5: See cohort retention and LTV by acquisition month

Section titled “Prompt 5: See cohort retention and LTV by acquisition month”

Treat “new customers who first bought in the same month” as one group (cohort), and aggregate by acquisition month how much they keep buying at M+1 / M+3 / M+6. Makes visible “whether more recently acquired customers retain better or worse” and “when they churn.”

When to use — when you want to check, as a monthly trend, whether customer stickiness changes by acquisition period.

What you need — nothing. Acquisition-month cohorts use the period held by the AMC run as-is; you cannot specify an arbitrary period (only if you want to switch runs can you pick from the list of available run_date values).

Goal: Understand the retention trend of new customers and use it to decide whether to continue or revise acquisition tactics —
produce the retention metrics and cumulative revenue of {{cohort_metric}} from AMC's acquisition-month cohorts.


[Data source and fixing the run]
Use get_amc_metric(metric_name="cohort", max_rows_per_table=5000), and always state up front the
selected_run_id / report_start_date / report_end_date / generated_at from the response.
This tool takes a run_date argument (when omitted, the latest run is selected automatically). If you pass a run_date that does not exist,
metadata.tables[*].available_runs comes back listing the selectable run_id / run_date / report periods, so
pick from there when you want to fix a run. There is, however, no argument for specifying an arbitrary analysis period.
The report period is decided on the run side, so do not act as though you specified a period — declare the period the run returned
as the analysis period as-is. A different metric (retention etc.) has a separate run numbering series, and the same run_date as cohort may not
exist (passing cohort's latest run_date to retention returns 0 records). Therefore
do not mix it into the same table as the figures from this analysis. If you must mix them, state both run_ids and periods and
note explicitly that they are not comparable.


[Reconciling row counts]
Reconcile by checking that "the selected run's row_count and returned_rows match and truncated is false."
metadata.tables.*.list_row_count is the total row count of the LIST accumulated by weekly runs, not the total row count of the selected run.
Do not use it as the reconciliation target. Only when they mismatch or truncated=true, state "not finalized."


[Cohort size]
cohort_ltv.cohort_ntb_buyers is null. Always take cohort size from cohort_summary.cohort_ntb_buyers by
joining on cohort_month.


[Handling of summed frequencies]
Sum purchase_frequency ({{frequency_buckets}}) over cohort_month × time_slot and present the row counts before and after
summing. However, do not treat the summed repeat_purchase_uv as a unique customer count.
Whether the buckets are mutually exclusive or cumulative is not documented in the tool spec and cannot be determined. Define the retention metric as
"summed repeat purchase UV ÷ cohort NTB count" and state that definition; do not assert it as a "retention rate."
Summing revenue (repeat_product_sales) by addition is fine.


[🔴 Handling of censored observation windows (most important)]
1. Each cohort's final time_slot is a partial period cut off by report_end_date. Do not use it as a finalized value;
   exclude it from aggregation. (State which slots you excluded.)
2. Exclude the first and last cohort_month values whose acquisition month itself is a partial month (the months at the edges of the report period).
   Their cohort sizes are not comparable with other months.
3. Do not line up cohorts with different observation lengths in the same table. Do not build a table that sums all time_slots into a
   "cumulative LTV" and orders it by acquisition month. More recent cohorts come out smaller because their observation window is shorter,
   not because retention got worse.
4. Always compare at an aligned "N months after acquisition" point (N = {{align_months}}).
   Do not put a cohort with fewer than N complete time_slots in the table; state it as "insufficient observation period."


[Output]
Present a table meeting the above, with acquisition month × cohort size × repeat purchase UV rate at each N-month point × N-month cumulative revenue ÷ NTB.
List excluded cohorts, excluded time_slots, and metrics that cannot be computed as "not retrievable (reason)" outside
the table. Do not use any estimated or approximate values, and never fill missing values with 0.
There is no equivalent of a below_reporting_floor flag, so a suppressed small cell cannot be distinguished from 0. Do not assert 0.


[Judgment]
On the basis of a comparison aligned to the same observation point, judge whether the retention metric of more recently acquired
cohorts is higher or lower than older ones. Do not claim deterioration or improvement on the basis of figures whose periods are not aligned. If the observation
period required for a judgment is not available, state "cannot judge (insufficient observation period)" and indicate when it will become judgeable.


[Next Action]
Only when deterioration is confirmed on an observation-aligned basis, dig into the drivers by campaign with Prompt 6 (See the post-purchase retention funnel).
Note, however, that the retention tables have no cohort_month axis and come from a different run and period, so
confirm that they cannot be used for a per-cohort driver decomposition before proceeding.


[Finalized data boundary]
Do not treat months whose ingestion is incomplete (fully_missed / in_progress in get_data_ingestion_status) as
finalized values.

What you can change — nothing (you cannot specify a target period). The period of the run that was used is always stated in the output.

What you get back — per acquisition month, group size, revenue at 1 / 3 / 6 months after first purchase, per-group LTV, and a monthly trend judgment. Months where group size is under 100 customers are not shown, per AMC spec.

What the AI will do

  1. Group “new customers whose first purchase came through ads” by acquisition month
  2. Aggregate how much revenue each group generated at M+1 / M+3 / M+6
  3. Trend per-group LTV across months (improving / declining / flat)
  4. Groups below 100 unique customers are excluded per AMC spec

Changes — analysis only (read-only). No ad settings are changed.

Next steps — to see the retention curve by campaign across elapsed months, use Prompt 6; to turn results into a report, use Generate reports.

Prompt 6: See the post-purchase retention funnel

Section titled “Prompt 6: See the post-purchase retention funnel”

Aggregate “what percent of customers came back N months after first purchase” by slot (M+1 / M+3 / M+6, etc.). Splitting by acquisition campaign also lets you judge “which campaigns brought in customers who stick.”

When to use — when you want to see at which point customers tend to drop off, and which acquisition campaigns bring in customers who stick.

What you need — the target period (defaults to the last 6 months). AMC cannot always narrow to exactly that period, though, and the period actually aggregated is stated in the output.

Goal: Identify the acquisition campaigns that produce stickier customers and use that for next period's budget allocation.


## Retrieval
1. Retrieve AMC's retention metrics at campaign granularity (retrieve all rows; do not aggregate from the leading sample).
2. At the same time, retrieve campaign-level attribution (ad spend, ROAS, NTB purchases) **from the same run**, plus its monthly breakdown.
3. **AMC metrics may pick a different run and a different aggregation period per metric.** For each table you retrieve, read the run and the aggregation period (start date, end date) from the response and always state them up front. **Do not join or compare tables whose aggregation periods do not match.**
4. If you cannot restrict the period to {{target period (e.g. last 6 months)}}, adopt the aggregation period actually returned as-is, and state that "the requested period cannot be specified; the actual period is XX to XX."


## Verifying counts
5. Reconcile the row count of the campaign-level table against the total headcount of the brand-level (ad-type-level) table from the same run, and show the coverage rate. **Do not use the cumulative row count in the metadata (list_row_count) as the denominator** (it is the accumulation of weekly runs, not the total row count of the selected run). If the total exceeds 100%, state it as a possible double count.


## Building the table
6. Build a table by acquisition campaign of surviving customers (defined as the number of first-time buyers who purchased again within the period) and survival rate at 1 and 3 months after first purchase. Use the survival rates AMC returned as-is; do not recompute them yourself.
7. If the M6 survival rate or the average repeat-purchase interval is absent from that table's schema, state "not computable" and **do not substitute**. Do not repurpose a metric that is only available at brand level for a per-campaign judgment.


## Judgment (apply all 3 guards)
8. Treat all of the following as **outside the judgment**, and report the reason for exclusion and its scale separately.
   - Non-disclosed bucket rows that fell below the aggregation floor (aggregate rows labeled "reporting floor" etc. rather than a campaign name). **Do not treat them as 0 people — show the actual number** and also give their share by ad type. They are not individual campaigns, so they are not a target for budget changes.
   - Rows with an empty campaign name
   - Rows with fewer than {{minimum NTB (e.g. 500)}} NTB buyers (the sample is small and the survival rate is unstable)
9. **Censored-observation guard (mandatory)**: if the survival-rate table has no acquisition month, customers from the entire aggregation period are mixed into one row, and **customers acquired most recently enter the denominator without having had time to be observed 3 months out, which drags the M3 survival rate down.** Using the monthly breakdown, compute for each campaign the "share of NTB acquired in the last 3 months," and **always exclude campaigns above {{recent-skew cap (e.g. 30%)}} from budget reduction as "judgment deferred."** Also show the median for the well-retained group and for the poorly-retained group, to indicate how far censoring is distorting the judgment. If the range of monthly data used for this diagnosis is shorter than the survival rate's aggregation period, state that the estimate leans toward the upper bound.
10. Of what remains, mark an M3 survival rate at or above {{lower bound (e.g. 10%)}} as "good retention" and below it as "poor retention."


## Output
Finally, output these 3 items.
1. Recommended action — present a budget-reduction proposal for poor-retention campaigns, with priority, expected effect, confidence, and risk. **Show the expected effect as an amount, using the actual ad spend joined from attribution in the same run.** Any figure resting on an assumption, such as the reduction rate, must be flagged as "an assumption, not a value AMC returned." **Campaigns with a low survival rate but a markedly high ROAS must be shown separately and treated as candidates for adding a repeat-purchase path first, rather than for reduction.** Campaigns classified as "judgment deferred" must be left out of the reduction proposal and shown in a separate table together with when they will be re-judged.
2. How to execute — budget-reduction proposals are applied after the user reviews and approves them. This prompt changes no budgets or bids whatsoever.
3. Measurement — in 30 days, re-retrieve the M3 survival rate and NTB purchases with "measure the effect of an action," and decide Revert if NTB purchases drop by more than 10%. **On re-retrieval the run has advanced and the aggregation period has slid with it, so this is not a comparison of the same population.** Record the run and aggregation period for both points in time, state the difference in period, and judge on that basis. Keep this run, period, and figures in a table as the baseline.

What you can change — the target period (e.g. since 2025-10; if it cannot be retrieved as specified, the actual aggregation period is used instead) / whether to split by acquisition campaign or view overall only.

What you get back — a table of surviving customers, survival rate, and average repeat-purchase interval by elapsed month after first purchase. When split by campaign, the top and bottom are shown with “stickier / weaker retention” labels.

What the AI will do

  1. Extract first-purchase customers from AMC data
  2. Aggregate whether a repeat purchase occurred in each slot (M+1 / M+3 / M+6, etc.)
  3. Visualize the survival-rate curve (drop-off points)
  4. When a campaign split is specified, aggregate by acquisition path and rank the difference in retention
  5. Segments below 100 unique customers are excluded per AMC spec

Changes — analysis only (read-only). No ad settings are changed.

Next steps — to compare which ad types acquire stickier customers, use Prompt 8; to turn results into a report, use Generate reports.

Compare by ad type

Section titled “Compare by ad type”

Compare which of SP / SB / SD / DSP brings in new customers most efficiently, as an input for budget allocation.

Prompt 7: Compare NTB acquisition power and revenue by ad type

Section titled “Prompt 7: Compare NTB acquisition power and revenue by ad type”

Compare, for each of SP / SB / SD / DSP, “how many buyers it brought in,” “what the NTB rate was,” and “how much attributed revenue.” An input for shifting budget between ad types.

When to use — when you want to compare which ad type brings in new customers most efficiently and decide where to steer budget.

What you need — nothing. By default, roughly 2 years of rolling cumulative data is returned and you cannot specify a period (short-term movement is checked separately in the monthly table, but because that table has no ad spend, NTB ROAS / NTB CAC cannot be computed there).

Goal: Compare NTB (new-customer) acquisition efficiency by ad type and use it to revise budget allocation.


[Primary source]
Use get_amc_metric(metric_name="ads_attribution", max_rows_per_table=5000).
This table takes ad type (ad_product_type) as its row granularity and returns everything needed for the comparison from a single source and a single period.
There is no need to gather ad spend from another tool and align windows by hand.
Fields to use: ad_product_type / purchase_uv / ntb_uv / ntb_uv_pct / ntb_purchases / ntb_purchase_pct /
cost (ad spend) / product_sales / ntb_sales / ntb_sales_pct / roas / ntb_roas / cpp_uv / ntb_cpp_uv (NTB CAC) / atv / ntb_atv / clicks_uv


[Stating the run and period (mandatory)]
AMC has no argument for an arbitrary period and returns only the period fixed by the run. Always state selected_run_id /
report_start_date / report_end_date / generated_at up front, and write that "this is not a period you specified but
a rolling cumulative window of roughly two years." Reconcile row counts with the selected run's row_count /
returned_rows / truncated, and do not use list_row_count, which weekly runs have accumulated, as the denominator.


[Output]
Build a table by ad type (SP / SB / SD / DSP) of the following. For every ratio, show the actual numerator and denominator alongside.
- Buyer count (purchase_uv) / NTB buyer count (ntb_uv) / NTB rate (ntb_uv_pct)
- Ad spend (cost) / NTB-driven revenue (ntb_sales)
- NTB ROAS (ntb_roas) ← the primary KPI. Use the value MCP returns as-is; do not recompute ntb_sales ÷ cost yourself
- NTB CAC (ntb_cpp_uv) ← a supporting KPI shown alongside. Also use the returned value as-is
- For reference: overall ROAS (roas) / ATV (atv) / NTB ATV (ntb_atv)


[Judgment]
Present ad types whose NTB ROAS falls below {{lower bound (e.g. 70%)}} of the overall average as "scale-down candidates"
(NTB ROAS is a higher-is-better metric). Also show alongside the types whose NTB CAC exceeds {{upper bound (e.g. 130%)}} of the overall average,
and for types where the two judgments disagree, state "judgment mismatch" and do not put them in the scale-down candidates
(the size of NTB-driven revenue and the cheapness of acquisition cost say different things).


[Stating the constraints (mandatory)]
1. Ad types are not mutually exclusive (the same customer can be judged NTB under several types).
   Use this only for relative comparison between types; do not sum the 4 types and speak about the brand as a whole. State this constraint in the output.
2. NTB here is "ad-driven NTB," a different population from the brand's total new-customer count. Do not conflate them.
   If you show brand-wide NTB revenue for reference, use ntb_revenue from get_amc_new_customer and
   note that its run_id and window differ and it cannot be joined directly with the table above.
3. Because this is roughly two years of accumulation, recent changes in tactics are barely reflected in these figures. To see short-term movement,
   use ads_attribution_monthly (report_month granularity; returns purchase_uv / ntb_uv / ntb_sales by ad_product_type) in a
   separate table, and state that the monthly table has no cost, so monthly NTB ROAS / NTB CAC cannot be computed.
   Months whose report_end_date does not reach the end of the month are excluded as partial months.
4. Estimates and substitutes are prohibited. State metrics you cannot retrieve as "not retrievable (reason)."


[Drilling down to the ASIN level is out of scope for this prompt]
Check NTB efficiency by ASIN with Prompt 3 (See NTB (new-customer) acquisition strength by ASIN).
The per-ASIN table (NTB by ASIN) is missing ad_spend entirely for some ad types, so
do not use it to compare costs across ad types.


Finally, output these 3 items.
1. Recommended action — present a budget reallocation proposal for the scale-down candidates, with priority, expected effect, confidence, and risk.
   State that the basis for the amounts is "roughly two years of cumulative actual ad spend," and not a "budget cap" or "the most recent monthly amount."
   Guardrail: if NTB acquired would fall by more than {{tolerated drop (e.g. -15%)}}, the shift is not recommended.
2. How to execute — budget-shift proposals are applied after the user reviews and approves them (this prompt changes no budgets or bids whatsoever)
3. Measurement — separate the metrics by sensitivity. NTB acquired can be judged in ads_attribution_monthly over {{short-term evaluation period (e.g. 30 days)}}.
   NTB ROAS / NTB CAC are roughly two-year cumulative and will not move in the short term, so re-retrieve them after {{long-term evaluation period (e.g. 3 months)}}.
   On re-retrieval, record the run_id and report period, and compare windows of the same width before judging improvement or deterioration.

What you can change — nothing (you cannot specify a target period; because it is roughly 2 years of accumulation, recent changes in tactics are barely reflected). The thresholds used to judge NTB ROAS / NTB CAC scale-down candidates can be changed.

What you get back — a table by ad type of buyer count, attributed revenue, NTB rate, CAC per NTB customer, and NTB ROAS, plus a budget-allocation recommendation. Accounts not running DSP get SP / SB / SD only.

What the AI will do

  1. Split AMC data by ad type and aggregate buyers and revenue
  2. Compute NTB rate and CAC per NTB
  3. Rank ad types by NTB ROAS (NTB revenue ÷ ad spend)
  4. Check whether budget allocation is skewed (over-concentrated in one type)
  5. Ad types below 100 unique customers are excluded per AMC spec

Changes — analysis and proposal only (read-only). No ad settings are changed.

Next steps — for strategy-level allocation, use Plan your ad strategy.

Prompt 8: Compare M+1 return rate (retention) by ad type

Section titled “Prompt 8: Compare M+1 return rate (retention) by ad type”

Compare “which ad type brings in customers who stick” by the 1-month return rate. Measures the “customer quality” that NTB volume alone can’t show.

When to use — when you want to compare ad types not just by new-acquisition count but by whether the customers they bring in stick (customer quality).

What you need — nothing. Roughly 24 months of rolling cumulative data is always returned, and you cannot specify a period.

Using {{brand/account}}'s AMC ad attribution data, compare the M+1 (one month after acquisition) return rate of NTB customers acquired via SP/SB/SD/DSP.


[Premise: the period cannot be chosen]
AMC's retention table accepts no period specification and always returns a rolling cumulative value of roughly 24 months in a single row. A return rate for an arbitrary period (such as 90 days) cannot be retrieved, so do not try to narrow the period. Instead, state the run_id, report_start_date, and report_end_date actually returned, exactly as returned, and write up front that "this is not a specified period but a roughly 24-month accumulation."


[Steps]
1. Retrieve NTB acquired, M+1 returning customers, and M+1 return rate by ad type. Use the return rate MCP returns as-is; do not recompute it yourself. State the retrieved count / total count and whether there was truncation. Reconcile the total row count with the selected run's row_count/returned_rows/truncated, and do not use list_row_count, which is the accumulation of weekly runs.
2. Take NTB CAC from the NTB unit cost (ntb_cpp_uv) in the ad attribution table. **Confirm and state that it came from the same run_id and the same report period as the return rate.** Runs and windows differ per metric, so do not line up figures from different runs side by side.
3. [Mandatory] Inspect observation-window bias. The cumulative table has no acquisition-month axis, and NTB acquired just before report_end_date have not finished being observed at M+1. From the NTB counts in the monthly attribution table, compute for each ad type the censoring rate = "acquisitions in the last 2 months / acquisitions over the whole period," and always show both of the following.
   - The raw return rate (the lower bound)
   - The corrected return rate with the censored cohort assumed to have returned 0 and removed from the denominator (the upper bound)
   As a check, confirm that the sum of monthly NTB counts roughly matches the cumulative NTB count. If it does not match, the monthly table does not cover the whole period, so state that and abandon the correction.
4. Compare the raw ranking with the corrected ranking, and **state any pair that swaps as "cannot judge."** Use only the pairs whose order does not change in your conclusion.
5. Judge which ad type has the highest retention. If the difference is under 1pp, write "effectively equivalent" and do not assert superiority on retention alone. In that case, bring NTB CAC in as a further basis for the judgment.


[Mandatory data-quality checks]
- Do not treat aggregate rows such as "Below reporting floor" as 0. They carry actual numbers, so confirm and state whether they are included in the aggregation.
- Do not guess a specific ad type for rows with an empty ad_type field. State the count and exclude them as unclassified.
- Do not assert that a row with 0 returning customers is genuinely 0. Check that it is not censoring, a new campaign, or the aggregation floor.
- If any ad type has 0 matching records, state "0 matches," and check the data-retrieval status before asserting genuine zero performance. Explicitly mark ad types that could not be retrieved as "not retrievable" and judge using the remaining types only.
- **Ad types are not mutually exclusive** (the same customer can be judged NTB under several types). Use this only for relative comparison between types; do not sum the 4 types and speak about the brand as a whole. State this constraint in the output.
- NTB here is "ad-driven NTB," a different population from the brand's total new-customer count. Do not conflate them.
- The current month is a partial month, so do not treat it as the final point of a trend. Check the data ingestion status and do not treat unfinalized months as finalized values.


[Constraints]
- This result is analysis only. Do not execute budget changes. Do not call any write tools.


Finally, output these 3 items.
1. Recommended action — state a guide amount for shifting budget toward the ad type with higher retention ({{shift rate (e.g. 10%)}}), the rationale, priority, expected effect, confidence, and risk. State that the basis for the amount is "actual ad spend," not a "budget cap," and add that the budget cap must be confirmed separately. If you extrapolate the expected effect from average CAC, show by what multiple the spend on the shift target would grow, and caveat it as "an upper bound that does not price in worsening marginal CAC — not a forecast." Guardrail: the shift is not recommended if NTB acquired would fall by more than {{tolerated drop (e.g. -15%)}}.
2. How to execute — hand this allocation-change proposal to an approval/execution prompt in the "adjust budgets" category. Write the notes to hand over with it.
3. Measurement — separate the metrics by sensitivity. NTB acquired can be judged from monthly attribution over {{short-term evaluation period (e.g. 30 days)}}. The M+1 return rate is a roughly 24-month accumulation and will not move in the short term, so re-retrieve it **after {{long-term evaluation period (e.g. 3 months)}}**. On re-retrieval, always record the run_id and report period, compare windows of the same width, and recompute the censoring correction each time before comparing. Judge Keep if improved, Revert if worse.

What you can change — nothing (you cannot specify a target period; because it is roughly 24 months of accumulation, recent changes in tactics are barely reflected). The guide budget-shift rate can be changed.

What you get back — NTB acquired, 1-month returners, and return rate by ad type, with a “sticky” / “flash” classification. Accounts not running DSP get SP / SB / SD only.

What the AI will do

  1. Extract NTB customers by ad type
  2. Determine whether each NTB made a second purchase by M+1
  3. Rank return rate by ad type
  4. Classify as “sticky (high return)” / “flash (low return)”
  5. Ad types below 100 unique customers are excluded per AMC spec

Changes — analysis only (read-only). No ad settings are changed.

Next steps — to allocate budget toward ad types with more sticky customers, use Plan your ad strategy; to turn results into a report, use Generate reports.

Generate SQL for the AMC console

Section titled “Generate SQL for the AMC console”

Rather than having Picaro run the analysis, build SQL queries to run yourself in the AMC console. This is the only section whose scope is “SQL generation only” (Picaro never executes SQL on AMC).

Section titled “Related categories”