Promo Product Selection

Time to complete: 45 minutesDifficulty: IntermediatePrerequisites: BigQuery, Google Drive, and Google Sheets connectionsYou’ll build: An agent that selects products for each promo cycle by querying BigQuery, filtering out recently featured SKUs, ranking by commercial potential, and routing the shortlist for approval before handing off to a pricing agent

Why Automate This?

The Problem: Each promotional cycle, someone queries a product database to find candidates, manually cross-references what was featured recently to avoid repeats, applies ranking logic in a spreadsheet, and produces a shortlist. The cross-referencing step gets skipped under time pressure. Products that were just featured reappear. Ranking criteria drift between cycles because the logic lives in someone’s head, not in code. The Solution: A Duvo agent that resolves the cycle theme, builds a non-repetition exclusion list from past cycle archives, injects it directly into the BigQuery query before ranking, and routes the result for approval. Every decision — what was excluded, how candidates were ranked, what the reviewer changed — is recorded in a cycle sheet that becomes part of the archive for the next run. Expected Results:

Recently featured products cannot reappear — the exclusion is applied at query time, before ranking
Ranking logic is consistent and auditable across every cycle
Every cycle’s output is automatically archived and feeds the next exclusion list
Reviewers see a pre-filtered, pre-ranked shortlist and can adjust it with simple commands

What You’ll Build

By the end of this playbook, you’ll have an agent that:

Resolves the cycle theme — from a promo calendar, or by asking the reviewer if none is scheduled
Pulls the last N cycle sheets from Drive and builds a deduplicated SKU exclusion list
Queries BigQuery scoped to the cycle theme, with the exclusion list applied as a NOT IN filter
Ranks results by commercial potential — balancing elasticity, projected sales, and margin
Validates the row count before writing anything
Creates a new cycle sheet in Drive with the ranked shortlist
Routes the shortlist for human approval, supporting inline add and remove commands
Archives the cycle sheet URL and pushes approved SKUs to the pricing agent’s Queue

Connections used:

BigQuery — scoped product query with dynamic exclusion filter
Google Drive — read past cycle sheets; write new cycle sheet
Google Sheets — promo calendar, cycle tracker, shortlist output
Human-in-the-Loop — theme confirmation and shortlist approval
Queue — handoff to the pricing agent

Before You Start

Make sure you have these ready:

BigQuery access — A service account with read access to your product table: the BigQuery Job User role to run queries and BigQuery Data Viewer to read the data. The table should include a category or segment field, a stock status field, and at least one commercial metric per product (elasticity score, projected sales, margin, or similar). Connect BigQuery.

Drive archive folder — A shared Google Drive folder where each cycle’s output sheet will be saved. Connect Google Drive.

Promo calendar — A Google Sheet mapping upcoming cycle dates to themes (e.g. baby, vitamins, household). If you don’t have one, the agent will ask for the theme at runtime instead.

Cycle tracker — A Google Sheet listing past cycle sheet URLs in order. Used to build the exclusion list. Leave it empty for the first run — the agent initializes it after the first approval.

Exclusion window — How many past cycles to look back when building the exclusion list. Two to three cycles is a reasonable default.

Shortlist size — How many products to select per cycle.

Pricing Queue — The name of the Queue your pricing agent reads from. Approved SKUs are pushed here after each run.

Create Your Agent

Click ”+ Create Agent” from your dashboard.
Select “Use Agent Builder”.

Describe Your Workflow

Paste this prompt into the Agent Builder. Replace all bracketed placeholders before generating.

You are a product selection agent. You run once per promo cycle to produce a
ranked shortlist of products for the next promotion.

Each time you run:

1. RESOLVE THE CYCLE THEME
   Open the promo calendar at [promo-calendar sheet URL]. Find the row where the
   cycle date matches today and read the theme.
   If no row matches, ask the reviewer: "No theme is scheduled for this cycle.
   Please provide the promo theme before I continue." Wait for the response.

2. BUILD THE EXCLUSION LIST
   Open the cycle tracker at [cycle-tracker sheet URL]. Take the top [N] rows
   (the most recent cycles). Open each linked sheet from Google Drive and extract
   the SKU column. Merge all SKUs into a flat deduplicated list.
   If the tracker is empty, treat the exclusion list as empty and continue.

3. QUERY BIGQUERY
   Run a SELECT query against [your dataset].[your product table] with:
   - A filter on the category or segment field matching the cycle theme
   - A filter for in-stock products only
   - A NOT IN filter using the exclusion list
   - An ORDER BY clause ranking by commercial potential (see the ranking
     expression defined below)
   - A LIMIT of [shortlist size × 2] to give a buffer for the row count check

   Important: if the exclusion list is empty, omit the NOT IN clause entirely.
   NOT IN with an empty list is invalid in BigQuery and will cause an error.

4. VALIDATE ROW COUNT
   After the query returns, count the rows. If the count is less than [shortlist
   size], request human review: "Row count mismatch — [actual] of [expected]
   products returned after applying the exclusion filter. Review and confirm
   how to proceed." Stop. Do not write any output until this is resolved.

5. WRITE THE CYCLE SHEET
   Take the top [shortlist size] rows. Create a new Google Sheet in the Drive
   folder [archive folder URL], named "Cycle [YYYY-MM-DD] — [theme]".
   Write the shortlist with these columns: Rank, SKU, Product Name, and the
   commercial metric fields used for ranking.

6. REQUEST APPROVAL
   Request approval. Title: "Product shortlist — [theme] — [date] — [N] items".
   Description: the shortlist as a table, plus a note on how many SKUs were
   excluded by the non-repetition filter.

   Support these commands in the reviewer's response:
   - "Remove: [SKU]" — remove that product from the shortlist and confirm:
     "Removed [SKU] — [product name]. Shortlist now [N-1] items."
   - "Add: [SKU]" — look up that SKU in BigQuery, add it to the table, and confirm:
     "Added [SKU] — [product name]."
   Process all commands before treating the response as a final approval.

7. ARCHIVE AND HAND OFF
   After approval:
   a. Insert a new row at the top of the cycle tracker (row 2, just below the
      header): cycle date and the URL of the new cycle sheet. Do not overwrite
      the header or existing rows.
   b. For each approved SKU, create one Case in the Queue "[pricing queue name]"
      containing the SKU, the cycle theme, and the cycle date. Queues do not
      deduplicate Cases: before creating, check the Queue and skip any SKU that
      already has a Case for this cycle date.

Click “Generate” to create the agent AOP.

Review Generated AOP

Before continuing, verify:

Dataset, table, and field names match your BigQuery schema
The category or segment field values align with the theme names in your promo calendar
Both sheet URLs (promo calendar and cycle tracker) are correct
The exclusion window (N) and shortlist size are set correctly
The archive folder URL points to the right Drive folder
The pricing Queue name matches the Queue your pricing agent reads from

Edit the AOP directly to adjust anything that does not match.

Configure Connections and the Pricing Queue

BigQuery — Confirm the service account can query the product table. Connect BigQuery.
Google Drive — The archive folder. Connect Google Drive.
Google Sheets — For the promo calendar, cycle tracker, and new cycle sheet. Connect Google Sheets.
Human-in-the-Loop — Built in and available for every agent automatically; no setup required.
Pricing Queue — Queues are linked in the builder, not on the Connections page. In the Queue section of the builder header, open the Queue Setup dialog and link the pricing Queue under Adds Cases (Producer).

Your pricing agent consumes the same Queue from the other side: it links the Queue under Processes Cases (Consumer) and enables Runs on new cases, so every approved SKU is picked up automatically. See Queue for the full setup.

Set Up the Promo Calendar

The promo calendar tells the agent which theme to apply without requiring manual input at runtime. It is the single source of truth for the promotional schedule.Recommended structure:

Cycle date	Theme
2025-03-10	baby
2025-03-17	vitamins
2025-03-24	household

Two things to get right:

Date format: use YYYY-MM-DD consistently. The agent compares today’s run date to this column, so the format must match.
Theme values: must exactly match the category (or equivalent) field in BigQuery. A mismatch here returns zero results.

If a cycle date has no row, the agent falls back to asking for the theme via Human-in-the-Loop. This is intentional — it lets you leave future cycles unscheduled and decide last-minute without changing the agent.

Set Up the Cycle Tracker

The cycle tracker is the non-repetition memory. The agent reads it at the start of each run to find which past sheets to pull SKUs from.Recommended structure — newest row at the top:

Cycle date	Sheet URL
2025-03-10	https://docs.google.com/spreadsheets/d/…
2025-03-03	https://docs.google.com/spreadsheets/d/…

If you have existing cycle history, add those sheet URLs here before going live. If you’re starting from scratch, leave it empty — the agent will add the first row after the first approved run.

Define the Ranking Expression

The ranking step is where commercial judgment gets encoded. Rather than sorting by a single field, combine multiple signals into a composite score that reflects what “good” means for your promo cycle.A useful pattern: multiply elasticity × projected volume × margin. This rewards products that are price-sensitive (respond well to discounts), sell in volume, and contribute healthy margin:

ORDER BY ([elasticity field] * [projected sales field] * [margin field]) DESC

Adapt this to your data model. If your elasticity score is an index (higher = more elastic), multiply it as-is. If it’s a coefficient (negative values), transform it first. Add an in-stock filter so out-of-stock products are excluded before ranking.The exact expression belongs in the AOP, not hardcoded in a separate query — this way it is versioned and reviewable alongside everything else the agent does.

Apply the Exclusion Filter

The exclusion filter is the most important correctness guarantee in this pattern. The right approach is to apply it inside the BigQuery WHERE clause, not as a post-query filter. This ensures that:

The ORDER BY and LIMIT operate on the already-filtered pool
You always get exactly the right number of results
Excluded products never appear in intermediate results the agent might act on

The agent builds the clause dynamically from the extracted SKUs:

AND [sku field] NOT IN ('SKU-001', 'SKU-042', 'SKU-107')

Two edge cases to handle in the AOP:

Empty exclusion list: NOT IN () is invalid SQL in BigQuery. Instruct the agent to omit the clause entirely when the list is empty.
SKU format inconsistency: if past sheets store SKUs differently from how BigQuery stores them (e.g. SKU-001 vs 001), the filter silently fails to exclude anything. Add a normalization instruction: "Strip any non-numeric prefix before comparing SKUs across sources."

Configure the Row Count Check

The row count check is a hard stop before any output is written. It protects against a specific failure mode: the exclusion window growing large enough that BigQuery cannot return a full shortlist for the theme.

Before writing the cycle sheet:
Count the rows returned. If the count is less than [shortlist size]:
- Request human review: "Row count mismatch — [actual count] products returned,
  [expected count] expected. The exclusion window may be too wide for this theme.
  Options: (a) approve a smaller shortlist for this cycle, (b) reduce the exclusion
  window, (c) expand the theme scope."
- Stop. Do not write partial output.

Running with a partial shortlist is almost always worse than surfacing the problem — it makes the downstream pricing run produce an incomplete result with no warning.

Schedule the Agent

Open your agent and click the Schedule button (the calendar icon) in the agent header.
In the Schedules tab, pick a frequency preset — Every Week or Every Month — or describe the cadence in plain language, like “every second Monday at 7am”.
Click Add schedule to save it.

If the cycle timing is irregular or driven by an upstream process, skip the schedule: have the upstream agent add a Case to a Queue this agent consumes, or start the agent via the API instead.

A scheduled run uses the connections of whoever created the schedule. If that person is missing the BigQuery, Google Drive, or Google Sheets connection, the cycle is silently skipped — and after repeated failures the schedule disables itself and notifies its owner. Create the schedule as a user who has all three connections set up.

Skipped cycles are never backfilled, and that includes overlap: if the previous cycle’s run is still active — for example, its shortlist approval is still waiting in Requests — the next tick is skipped, not queued. Respond to each cycle’s approval before the next one is due.See Scheduling Agents for setup details.

Test Your Agent

Run manually before going live. Cover these scenarios:

Test case	What to verify
Theme found in promo calendar	Query scoped to correct category; sheet named with theme
No theme in promo calendar	Agent asks for the theme; does not query until answered
First run, empty cycle tracker	No exclusions applied; tracker row added after approval
Normal run with history	Excluded SKUs absent from query result and output sheet
SKU format mismatch across sheets	Excluded SKUs still caught after normalization
Row count below target	Agent requests review and stops; no sheet written
Reviewer sends “Remove: [SKU]“	SKU removed from shortlist; confirmation posted
Reviewer sends “Add: [SKU]“	SKU fetched from BigQuery, added to table, confirmed
Final approval	Cycle tracker updated; correct number of Cases created in pricing Queue
Run retried after approval	No duplicate Cases — SKUs with an existing Case for this cycle are skipped

Expected Results

In Google Drive:

A new cycle sheet after each approved run, named Cycle [YYYY-MM-DD] — [theme]
The cycle tracker grows by one row per cycle

In your Requests:

A pending approval per cycle with the ranked shortlist, the cycle theme, and the exclusion count — respond from Requests, a live run, Slack, or Teams
Inline add/remove commands processed before final sign-off

In the pricing Queue:

One Case per approved SKU, carrying the SKU, cycle theme, and cycle date

Troubleshooting

BigQuery returns fewer rows than expected

The exclusion window may be too wide for the catalog size within this theme. Reduce N, or widen the theme filter (e.g. include a parent category rather than a leaf category).
If the NOT IN list was empty and the clause was still injected, BigQuery errors. Verify the agent omits the clause when the list is empty.

Recently excluded SKUs are appearing in the shortlist

The agent may be comparing SKUs that are formatted differently across sheets. Add an explicit normalization step to the AOP.
Confirm the agent is reading the SKU column specifically, not product name or an adjacent column.

Cycle tracker is not updating after approval

Verify the agent has write access to the tracker sheet.
Confirm the AOP says to insert a new row at the top, just below the header — not overwrite the header or clear the sheet.
The agent should write the URL of the newly created cycle sheet, not the tracker’s own URL.

Row count check fires unexpectedly

The eligible pool for this theme is smaller than the shortlist — the same causes as “BigQuery returns fewer rows than expected” above. Reduce the exclusion window, widen the theme filter, or approve a smaller shortlist for this cycle.
Increasing the query LIMIT will not help: with the shortlist size × 2 buffer in the AOP’s BigQuery query instructions, the check only fires when fewer eligible products exist, and a larger LIMIT cannot add eligible products.
Confirm the theme value matches the BigQuery category field exactly — a near-miss match silently shrinks the pool.

Pricing Queue is missing Cases for some SKUs

Confirm the agent reads from the post-approval shortlist (after any Remove/Add commands) when creating Cases in the Queue.
Check that the pricing agent’s Case trigger is enabled (Runs on new cases) so Cases don’t sit as Pending. See Queue.

A cycle didn't run

The schedule creator is missing a connection: scheduled runs use the connections of whoever created the schedule. A missing BigQuery, Drive, or Sheets connection skips the cycle silently, and repeated failures disable the schedule and notify its owner. Re-add the connection and re-enable the schedule.
The previous cycle’s run was still active: if the last shortlist approval was never answered, the run stays active and the next tick is skipped. Clear the pending request in Requests.
Skipped cycles are not backfilled. To catch up, start the agent manually with Start Work.

Duplicate Cases in the pricing Queue

Queues do not deduplicate Cases. If the run was retried or the approval was processed twice, each pass creates its own Cases. Keep the guard from step 7b of the AOP prompt: check the Queue for an existing Case with the same SKU and cycle date before creating one.
Delete the extra Cases from the Queue before the pricing agent picks them up.

Take It Further

Once the core cycle is running reliably, consider these extensions: Add a price gap check Before writing the cycle sheet, compare each selected product’s promo price against the current best competitor price. Flag any product where the gap is below your threshold — but do not block the run.

For each selected product, compare [promo price field] to [competitor best price].
If the gap is less than [threshold]%: add a warning flag to that row in the cycle
sheet and send a Slack notification to [channel] with the SKU and gap percentage.
Continue the run regardless.

Add a reference price compliance check If your promotions are subject to regulations around reference pricing lock periods, add a hard stop:

Before writing output, verify that [reference price lock date] is at least [N] days
before [promo activation date] for every selected product.
If any product fails: request human review and stop.

Chain into a full pipeline The selection agent is the entry point for a multi-agent pipeline. Approved SKUs move to a pricing agent that fetches competitor prices and proposes discounts, which then moves to an approval agent that formats and sends the final sign-off summary. See Queue for how to connect agents this way. Prune the cycle tracker The tracker only needs to hold the last N cycles. Add a cleanup step after inserting the new row:

If the tracker has more than [N + 2] rows, delete the oldest rows so only the
most recent [N + 2] remain. The exclusion window never looks further back than N,
so older rows serve no purpose.

Giving Recurring Runs a Memory

The design principles behind this playbook, generalized to any recurring selection workflow

Human-in-the-Loop

How to write approval gates that support reviewer commands

Queue

How to hand off approved SKUs to a downstream pricing agent

Requests

Where approval requests appear and how to respond

BigQuery

How to connect and query BigQuery from an agent

Google Drive

How to read and write files in a shared Drive folder

Google Sheets

Promo calendar, cycle tracker, and shortlist output

Scheduling Agents

How to run on a recurring promo cycle schedule

Welcome

Getting Started

Examples

Building Agents

Running Agents

Agent Features

Skills

Connections

Playbooks

Solutions

Analytics

Advanced

Reliability

Security

Resources

Organizations

Teams

Promo Product Selection

Why Automate This?

What You’ll Build

Before You Start

Expected Results

Troubleshooting

Take It Further

Giving Recurring Runs a Memory

Human-in-the-Loop

Queue

Requests

BigQuery

Google Drive

Google Sheets

Scheduling Agents

​Why Automate This?

​What You’ll Build

​Before You Start

​Expected Results

​Troubleshooting

​Take It Further

​Related

Giving Recurring Runs a Memory

Human-in-the-Loop

Queue

Requests

BigQuery

Google Drive

Google Sheets

Scheduling Agents

Why Automate This?

What You’ll Build

Before You Start

Expected Results

Troubleshooting

Take It Further

Related