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 14 & 15 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 to decompose the contribution of the brand halo effect (spillover revenue to same-brand products beyond the ad target)
  • 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 find saturating or diminishing-return campaigns early
  • 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 … e.g. “last 90 days” or “2026-Q1”. For cross-month metrics like LTV and retention, 30+ days is recommended (6+ months for cohort retention by acquisition month). Today and yesterday may not be finalized, so a recently closed period is safer.
  • 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 14 & 15 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
Decompose the contribution of the brand halo effectRevenue / LTV composition and a ranking of halo-beneficiary ASINsAnalysis onlyPrompt 2
See a monthly brand-wide health checkMonthly NTB rate, repeat purchase rate, CAC, LTV, LTV:CAC ratio, and a trend judgmentAnalysis onlyPrompt 3
See NTB (new-customer) acquisition strength by ASINPer-ASIN NTB rate, ad spend, ACoS, and scale-up / scale-down labelsAnalysis onlyPrompt 4
Sort ASINs by role (new-customer acquisition / repeat retention / scale-down consideration)A 3-category classification of ASINs and a budget reallocation proposalAnalysis onlyPrompt 5
See cohort retention and LTV for new-customer groups by acquisition monthRetention and LTV of each acquisition-month group, with a trend judgmentAnalysis onlyPrompt 6
See the post-purchase retention funnelM+1 / M+3 / M+6 survival rates and a per-campaign stickiness judgmentAnalysis onlyPrompt 7
Compare NTB acquisition power and revenue by ad typePer-ad-type NTB rate, CAC, ROAS, and a budget allocation recommendationAnalysis onlyPrompt 8
Propose a budget shift to the ad type with the lowest NTB CACA table of source / target, projected NTB lift, and risk notesAnalysis onlyPrompt 9
Compare M+1 return rate (customer quality) by ad typePer-ad-type return rate and a sticky / flash classificationAnalysis onlyPrompt 10
See campaign-level attribution trends month over monthCampaign × month trends and a scale-up / saturated / scale-down 3-way classificationAnalysis onlyPrompt 11
Early-detect saturating / diminishing-return campaignsA table of campaigns with saturation signals and recommended actionsAnalysis onlyPrompt 12
Identify campaigns where customer count and revenue divergeChange rates for customer count / revenue / basket size and a cause hypothesisAnalysis onlyPrompt 13
Generate a CLTV analysis query (run in the AMC console)SQL for the AMC console + projected result schema + execution stepsSQL generation only (run in the AMC console)Prompt 14
Generate a query comparing ACoS by ASIN × campaign typeSQL for the AMC console + projected result schemaSQL generation only (run in the AMC console)Prompt 15

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 — the target period (defaults to the last 90 days).

Using the last 90 days of AMC data, produce for ad-attributed customers:
LTV (lifetime value) / CAC (customer acquisition cost) / repeat purchase rate / true ROAS (including brand halo).
Show both the ad-clicked-ASIN-only figures (no halo) and
brand-wide (with halo) side by side,
and judge whether the LTV : CAC ratio is healthy.

What you can change — the target period (e.g. last 90 days, 2026-Q1; 30+ days recommended for long-term trends).

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: Decompose brand halo contribution

Section titled “Prompt 2: Decompose brand halo contribution”

Decompose the revenue contribution not only of the ad-clicked ASIN but also of the other same-brand ASINs the same customer bought (the brand halo effect = spillover revenue to same-brand products beyond the ad-target ASIN). Makes visible the structure where “you thought you were making ad budget efficient, but actually another ASIN was selling in the background.”

When to use — when you want to know how much spillover revenue occurs beyond the ad-clicked ASIN, and which ASINs benefit.

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

Using the last 90 days of AMC data, decompose ad-attributed customers' purchases
into "ad-clicked ASINs" and "brand halo (other ASINs within the same brand)."
Produce revenue composition / LTV composition / top halo-contributing ASINs.
When the halo share is high, also note which ASINs are the halo beneficiaries.

What you can change — the target period.

What you get back — revenue composition ratios (ad-clicked ASINs / brand halo), LTV composition, and a ranking of halo-beneficiary ASINs. Includes a comment judging the halo share against the typical range (30–50%).

What the AI will do

  1. Extract ad-attributed customers and classify each customer’s purchases into “ad-clicked ASINs” and “other ASINs within the same brand”
  2. Compute revenue composition and LTV composition across the two buckets
  3. Rank halo-beneficiary ASINs by revenue
  4. If the halo share is unexpectedly high / low, explain what it means (brand loyalty / one-off purchases)
  5. Segments below 100 unique customers are excluded per AMC spec

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

Next steps — to sort the beneficiary ASINs by role, use Prompt 5; to turn results into a report, use Generate reports.

Prompt 3: Monthly brand acquisition health check

Section titled “Prompt 3: 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 target period (defaults to the last 6 months).

Using the last 6 months of AMC data, produce a brand-wide monthly health check.
Present a table lining up, by month, NTB rate (share of new customers, on a unique-customer basis), repeat purchase rate, CAC, LTV, and LTV:CAC ratio.
Judge each indicator as "improving / flat / declining,"
and call out one indicator to watch.

What you can change — the target period (e.g. go longer, like the last 12 months; 3+ months recommended to see trends).

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 4: See NTB (new-customer) acquisition strength by ASIN

Section titled “Prompt 4: 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 period (defaults to the last 30 days).

Using the last 30 days of AMC data, produce ASIN-level NTB (new-customer)
performance for SP / SB / SD ads.
Present a table lining up, by ASIN, total purchases, NTB purchases, NTB rate, attributed revenue, ad spend, and ACoS.
Separately present ASINs with poor NTB acquisition efficiency (candidates to scale down ads)
and the top 10 ASINs with a high NTB rate and room to scale up.

What you can change — the target period (e.g. last 30 days, last month).

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 5.

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

Section titled “Prompt 5: 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 6: See cohort retention and LTV by acquisition month

Section titled “Prompt 6: 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 — the target period (defaults to the last 12 months).

Using the last 12 months of AMC data, produce retention and LTV for new-customer groups by acquisition month.
Present a table lining up, by acquisition month, group size (customer count), revenue at 1 / 3 / 6 months after first purchase, and per-group LTV.
Judge, as a monthly trend, whether more recently acquired customers are retaining better.

What you can change — the target period (e.g. 2025-04 to 2026-03; 6+ months recommended to see the trend across acquisition months).

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 7; to turn results into a report, use Generate reports.

Prompt 7: See the post-purchase retention funnel

Section titled “Prompt 7: 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).

Using the last 6 months of AMC data, produce the post-purchase retention funnel.
Present a table lining up, at 1 / 3 / 6 months after first purchase, surviving customers, survival rate, and average repeat-purchase interval.
Split by acquisition campaign, and judge which campaigns bring in customers who stick
and which do not.

What you can change — the target period (e.g. since 2025-10) / 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 10; 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 8: Compare NTB acquisition power and revenue by ad type

Section titled “Prompt 8: 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 — the target period (defaults to the last 30 days).

Using the last 30 days of AMC data, produce, by ad type (SP / SB / SD / DSP):
buyer count, attributed revenue, NTB rate, CAC per NTB customer, and NTB ROAS.
Judge which ad type budget should go to, by NTB acquisition efficiency.

What you can change — the target period (e.g. last 30 days, last month).

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 a concrete budget-shift plan, use Prompt 9; for strategy-level allocation, use Plan your ad strategy.

Prompt 9: Propose a budget shift to the lowest-NTB-CAC ad type

Section titled “Prompt 9: Propose a budget shift to the lowest-NTB-CAC ad type”

Compare NTB acquisition cost (CAC) per customer across ad types and produce a concrete plan to shift budget to the most efficient type. Goes as far as “source / target / projected NTB lift.”

When to use — when you want a concrete plan (amount / projected effect / risks) for moving budget between ad types.

What you need — the target period (defaults to the last 30 days) and the budget amount to shift (e.g. ¥500,000).

Using the last 30 days of AMC data, compare CAC per NTB customer across SP / SB / SD / DSP.
Show the projected NTB lift if ¥500,000 is shifted to the lowest-CAC type,
and the amount that should be pulled from the highest-CAC type.
Also note the risks of the shift (scale ceiling / saturation signals).

What you can change — the target period / shift amount (e.g. ¥500,000; can also be given as a percentage like 10% of current).

What you get back — a table by ad type of NTB CAC, source / target, projected NTB lift, and risk notes. Accounts not running DSP have the comparison and shift plan built on SP / SB / SD only.

What the AI will do

  1. Compute NTB CAC by ad type
  2. Identify the lowest- and highest-CAC types
  3. Linearly estimate the NTB lift from shifting the specified budget (e.g. ¥500,000) to the lowest-CAC type, using current CAC
  4. Also note the risks of the shift (scale ceiling / saturation signals / seasonality)
  5. Ad types below 100 unique customers are excluded per AMC spec

Changes — analysis and proposal only (read-only). It only builds a shift plan; it does not change budgets.

Next steps — to execute the allocation, first turn it into an allocation plan in Plan your ad strategy, then apply it through each ad type’s approval flow.

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

Section titled “Prompt 10: 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 — the target period (defaults to the last 90 days; at least 2 months of data are needed to observe the 1-month return).

Using the last 90 days of AMC data, compare the 1-month return rate of NTB customers
acquired via SP / SB / SD / DSP.
Present a table lining up, by ad type, NTB acquired, customers who returned within 1 month, return rate, and a customer-quality indicator.
Judge which ad type brings in customers who stick.

What you can change — the target period (at least 2 months of data are needed to observe the 1-month return).

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.

Read campaign signals

Section titled “Read campaign signals”

Tell growing campaigns apart from ones that are starting to saturate or stall, early, from month-over-month movement.

Section titled “Prompt 11: See campaign-level attribution trends month over month”

Aggregate how monthly “buyer count / revenue / NTB rate” trends at the campaign level. Lets you sort into “growing campaigns,” “saturated campaigns,” and “candidates for scale-down / exit.”

When to use — when you want an overview of each campaign’s growth, saturation, or stall by monthly trend, to set a scale-up / scale-down policy.

What you need — the target period (defaults to the last 6 months).

Using the last 6 months of AMC data, produce the campaign × month trend of
buyer count, attributed revenue, and NTB rate.
Classify campaigns into 3 categories — scale up / saturated / scale-down or exit consideration —
and also list the top campaigns by NTB acquisition efficiency per unit of ad spend.

What you can change — the target period (e.g. since 2025-12; 3+ months recommended for trend judgment).

What you get back — a table of buyer count, attributed revenue, and NTB rate by campaign × month, plus a “scale up” / “saturated” / “scale-down or exit consideration” classification and a ranking of NTB acquisition efficiency per unit of ad spend.

What the AI will do

  1. Aggregate AMC data at campaign × month granularity
  2. Judge the trend from month-over-month growth in buyers and revenue
  3. Judge “whether NTB is drying up” from the NTB-rate trend
  4. Sort into “scale up / saturated / scale-down or exit consideration” from the monthly-trend pattern
  5. Campaign × month cells below 100 unique customers are excluded per AMC spec

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

Next steps — to narrow to only campaigns showing deterioration, use Prompt 12; to turn results into a report, use Generate reports.

Prompt 12: Early-detect saturating / diminishing-return campaigns

Section titled “Prompt 12: Early-detect saturating / diminishing-return campaigns”

Detect “saturation signals” — declining NTB rate / rising CPA / revenue plateau — monthly, as a signal to tighten budget or add new keywords. Where Prompt 11 is comprehensive, this is the alert version that extracts only “campaigns showing deterioration.”

When to use — when you want to pick out, as alerts, only the campaigns showing signs of saturation or stalling among all campaigns.

What you need — the target period (defaults to the last 6 months; trend judgment needs at least 3 consecutive months of data).

Using the last 6 months of AMC data, early-detect saturating / diminishing-return campaigns.
Check monthly: NTB-rate decline / CPA rise / revenue plateau,
and list campaigns with "2 or more saturation signals" as alerts.
Also include action proposals (tighten budget / add new keywords / expand targets).

What you can change — the target period (trend judgment needs at least 3 months of data).

What you get back — a table by campaign of the number of saturation signals, the main deteriorating metric, the monthly trend, and the recommended action. Campaigns with less than 3 months are listed separately in a “judgment deferred” section.

What the AI will do

  1. Compute the monthly change in NTB rate / CPA / revenue at campaign × month granularity
  2. Set a “deterioration flag” on each and extract campaigns with 2+ flags
  3. Identify the main deteriorating metric of each campaign
  4. Auto-suggest actions matched to the deteriorating metric (NTB-rate decline → new keywords / CPA rise → tighten bids / revenue plateau → expand targets)
  5. Campaigns with less than 3 months of monthly data are excluded as “judgment deferred”
  6. Campaign × month below 100 unique customers are excluded per AMC spec

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

Next steps — to carry out the actions, use the approval flow in Adjust bids or Add negative keywords.

Prompt 13: Identify campaigns where customer count and revenue diverge

Section titled “Prompt 13: Identify campaigns where customer count and revenue diverge”

Identify campaigns where “customer count is up but revenue is down (basket size shrinking)” or “customer count is down but revenue holds (higher unit price).” Structural shifts that headline revenue alone hides.

When to use — when you want to catch early the structural shift in customer count and unit price that revenue up/down alone won’t reveal.

What you need — two periods to compare (e.g. the last 3 months vs the prior 3 months; both periods must have data).

Using AMC data comparing the last 3 months with the prior 3 months, identify campaigns
where customer count and revenue movements diverge.
Present a table lining up, by campaign, customer-count change rate, revenue change rate, basket-size (spend per customer) change, and likely cause.
Split into the two patterns "more customers, less revenue (price down)"
and "fewer customers, revenue held (price up)."

What you can change — the two periods to compare (e.g. the last 3 months vs the prior 3 months). Both periods must be present.

What you get back — for each diverging campaign, the customer-count / revenue / basket-size change rates, a pattern classification, and a likely-cause hypothesis.

What the AI will do

  1. Compute customer-count and revenue change rates at campaign × month granularity
  2. Extract the two patterns: more customers / less revenue, and fewer customers / revenue held
  3. Also note the basket-size (revenue ÷ customers) change
  4. Hypothesize likely causes (promo discount / price hike / product-mix shift / sale period)
  5. Campaigns below 100 unique customers are excluded per AMC spec

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

Next steps — to write up the structural shifts in 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).

Prompt 14: Generate a CLTV analysis query (for the AMC console)

Section titled “Prompt 14: Generate a CLTV analysis query (for the AMC console)”

Generate a SQL query for CLTV (customer lifetime value) analysis that you can run in the AMC console. Picaro does not run the query on AMC — you run the generated query yourself in the AMC console.

When to use — when you want a template CLTV-analysis query to run yourself in the AMC console.

What you need — the target period (e.g. 12 months; specify within AMC’s data-retention window).

Generate a query for CLTV (customer lifetime value) analysis in AMC.
Structure it to include per-segment purchase count / average purchase amount / computed LTV.
Cover the past {{target period (e.g. 12 months)}} of data.

What you can change — the target period (e.g. 12 months; within AMC’s data-retention window).

What you get back — a SQL query runnable in the AMC console + the projected result schema + execution-step guidance. Running it requires console access to your AMC instance.

Changes — SQL generation only (run in the AMC console). Execution is done manually in the AMC console (Picaro does not execute SQL on AMC).

Next steps — run the generated query in the AMC console, then roll the results into a report with Generate reports.

Prompt 15: Generate an ASIN × campaign-type ACoS comparison query (for the AMC console)

Section titled “Prompt 15: Generate an ASIN × campaign-type ACoS comparison query (for the AMC console)”

Generate a SQL query to compare ACoS by SP / SB / SD campaign type per ASIN. This one is also executed in the AMC console.

When to use — when you want a query to run an ASIN × campaign-type ACoS comparison in the AMC console.

What you need — the target period (e.g. 90 days) and the ASIN scope (key ASINs).

For the key ASINs of the past {{target period (e.g. 90 days)}}, generate a query that compares
ACoS by SP / SB / SD campaign type using AMC data.
Structure it so the result is an ASIN × campaign type × ACoS table.

What you can change — the target period (e.g. 90 days) / the ASIN scope.

What you get back — a SQL query runnable in the AMC console + the projected result schema (ASIN × campaign type × ACoS).

Changes — SQL generation only (run in the AMC console). Execution is done manually in the AMC console (Picaro does not execute SQL on AMC).

Next steps — run the generated query in the AMC console, then roll the results into a report with Generate reports.

Section titled “Related categories”