How to Use the Query Activity in Adobe Campaign Classic: Filtering and Targeting

Ask any Campaign Classic developer which activity they use most, and the answer is the Adobe Campaign query activity. It starts nearly every targeting workflow, and most of the problems I am asked to look at in workflows come back to a query that does not select what its author thought it did.

The basic screen is easy. The parts that cause trouble are the dimensions, conditions on collections, and the difference between filtering and adding data. I’ll take each of them through one realistic segment, and show how I check what the query actually does.

What the Adobe Campaign query activity produces

A Query reads from the database and writes a list of matching IDs into a worktable, the same mechanism I walk through in my Campaign Classic workflow tutorial. The outbound transition carries that worktable and its record count to the next activity. If you add extra data, the worktable gets extra columns that later activities and the delivery can use.

The query itself is built in the same editor used for delivery targeting and filters, so anything you learn here transfers. The Adobe Query activity page documents each screen.

Targeting and filtering dimensions in the Campaign query activity

The first screen asks for two dimensions, and this is the decision that matters most.

• The targeting dimension is what the result contains. For most marketing work this is Recipients (nms:recipient). The worktable will hold recipient IDs.
• The filtering dimension is the table you write conditions on. It must be the targeting dimension or a table linked to it.

When both are Recipients, your conditions are written on recipient fields, and anything on a linked collection, such as subscriptions or purchases, is expressed through an “exists” condition. I explain why in my post on the Campaign Classic data model. When the filtering dimension is a linked table, for example Transactions, your conditions are written on transaction fields, and Campaign returns the recipients those transactions belong to.

Adobe’s example in the targeting workflow documentation is a good mental model: to find clients who have held a life insurance policy for more than five years, the targeting dimension is the client and the filtering dimension is the contract.

The choice also affects the SQL. A condition on a collection from the recipient side typically becomes a subquery. Filtering on the linked table directly typically becomes a join. On a large table that difference can be very noticeable, and the data-oriented API documentation recommends filtering on the right dimension over “exists” style conditions where you can.

Which dimension I start from

My default is to start from the recipient side with exists, because marketers can read it and it matches how they describe the segment. I switch the filtering dimension to the linked table when the collection is large and the query is slow, or when I need grouping on the linked records. I do not optimise first. A query someone else can understand next year is worth more than a few seconds saved on a nightly run.

The worked brief: repeat outdoor buyers

The rest of this post uses one segment. It builds on the cus:transaction table from my custom schema guide, which stores purchases with a date, an amount, a category and a link to the recipient.

The brief is hypothetical but typical. Marketing wants recipients who:

• bought in the Outdoor category at least twice in the last 12 months,
• spent at least 200 in total in that category over the same period,
• and they want each person’s Outdoor spend and last Outdoor purchase date available in the email, for a line such as “Thanks for your last visit on 12 August”.

There are two conditions that need grouping (count and sum) and two values that need aggregating into the worktable. That covers most of the hard parts of the Query activity.

Approach 1: filter on the collection with exists

Start with the version most people build first. Targeting and filtering dimension are both Recipients. Click Edit query, choose Filtering conditions, then in the field picker expand the recipient and select the Transactions collection. Choose an “exists” condition and add inner conditions:

• Product category equals Outdoor.
• Transaction date is on or after 365 days ago.

This gives you everyone with at least one Outdoor purchase in the year. It does not handle “at least twice” or “at least 200”, because an exists condition only answers yes or no. The queryDef form shows why:

<queryDef schema="nms:recipient" operation="count">
  <where>
    <condition expr="transaction" setOperator="EXISTS">
      <condition expr="@category = 'Outdoor'"/>
      <condition expr="@transactionDate >= SubDays(GetDate(), 365)"/>
    </condition>
  </where>
</queryDef>

The inner conditions apply to individual transactions. There is nowhere to say “count of matching transactions”.

Approach 2: filter on transactions with grouping

To handle counts and sums, change the filtering dimension. Keep the targeting dimension as Recipients and set the filtering dimension to Transactions. The conditions are now written on transaction fields:

• Product category equals Outdoor.
• Transaction date is on or after 365 days ago.

Because the two dimensions are different, the editor now offers the option to add data grouping conditions. Tick it, and on the grouping condition screen add:

• Count of primary key is greater than or equal to 2.
• Sum of Amount is greater than or equal to 200.

Campaign groups the matching transactions by recipient and keeps only the recipients whose group meets both conditions. That is a SQL GROUP BY with a HAVING clause.

In v7 queryDef syntax, the equivalent is a recipient query with an IN subquery. It is the same structure the console builds, and it is useful when you need to check the count from a script:

var q = xtk.queryDef.create(
  <queryDef schema="nms:recipient" operation="count">
    <where>
      <condition expr="@id" setOperator="IN">
        <subQuery schema="cus:transaction">
          <select><node expr="[@recipient-id]"/></select>
          <where>
            <condition expr="@category = 'Outdoor'"/>
            <condition expr="@transactionDate >= SubDays(GetDate(), 365)"/>
          </where>
          <groupBy><node expr="[@recipient-id]"/></groupBy>
          <having>
            <condition expr="count(@id) >= 2"/>
            <condition expr="Sum(@amount) >= 200"/>
          </having>
        </subQuery>
      </condition>
    </where>
  </queryDef>
);
logInfo("Repeat outdoor buyers: " + q.ExecuteQuery().@count);

The subQuery element and the IN set operator came with v7, replacing the older SQLData approach. If you are reading code written for v6, you may still see SQLData fragments, and the v7 migration notes explain how to rewrite them.

Combining conditions in the Adobe Campaign query activity

Conditions in the editor are combined with AND by default. You can switch any line to OR or EXCEPT, and indent lines to group them, which works like brackets. The editor shows the logic as a tree on the left, and I always read that tree before saving, because a single condition at the wrong indent level changes the whole meaning.

A practical example from the same brief: if marketing later says “Outdoor or Camping”, do not add a second category line at the top level with OR. That would return anyone with a Camping purchase, whatever the date. Group the two category lines together under one OR node, and keep the date condition at the level above with AND. Better still, use the “is included in” operator with both values on a single line, which removes the bracket problem entirely.

Adding the spend and last purchase date

The filter is done, but the email needs the values. That is the job of Add data, which is separate from filtering. Filtering decides who is in. Add data decides which columns travel with them.

In the Query activity, click Add data, then:

1. Choose Data linked to the filtering dimension.
2. Select the Transactions collection.
3. In Data collected, choose Aggregates.
4. Add two columns: Sum(@amount) with the alias outdoorSpend, and Max(@transactionDate) with the alias lastOutdoorDate.
5. In the sub-selection, repeat the conditions: category equals Outdoor and date in the last 365 days.

Step 5 is the one people skip. Without it, the sum covers every transaction the recipient ever made, in every category, and the email shows the wrong amount. The filter and the added data are independent, so each needs its own conditions.

In the delivery, the columns appear under Target extension in the personalisation menu, for example <%= targetData.outdoorSpend %>. Format the date with formatDate in the template so it reads naturally.

Checking what the Campaign query activity really does

I never trust a new query on its count alone. Three checks catch most errors.

Look at the SQL. In the workflow properties, on the Execution tab, tick Log SQL queries in the journal. After the run, the SQL appears in the workflow logs. Read the GROUP BY and HAVING clauses and make sure they match the brief. The workflow properties page describes this option. Turn it off afterwards, because it fills the logs.

Look at real rows. Right-click the outbound transition and choose Display the target. Pick three or four recipients and check them by hand against the transaction table in the generic query editor. If one person shows up with a single Outdoor purchase, the grouping is not working.

Test the edges. Use one test profile with exactly two Outdoor purchases totalling 200, and one with two purchases totalling 199. The first should be in, the second out. Boundary values catch > versus >= mistakes quickly.

Other Adobe Campaign query activity features worth knowing

A few things that come up often once the basics are working:

• Predefined filters. Save a condition you use often as a filter under the filters node and pick it in the query instead of rebuilding it. It also means a definition such as “active customer” is maintained in one place.
• Temporary schema. A Query can target the inbound worktable instead of a database table, which lets you filter the result of a previous activity on its added columns.
• Segment codes. You can set a segment code on the query result. It is stored in the worktable and is useful when several branches later meet in a union and you need to know where each person came from.
• Variables in conditions. Values such as a category can come from an instance variable with $(instance/vars/@category), which makes the same workflow reusable for other categories.
• Case sensitivity. String comparisons depend on the database collation. On a case-sensitive database, outdoor and Outdoor are different values. Clean the data at import rather than relying on the query to cope.

Mistakes that cost the most time

• Wrong dimension. Using “exists” when the brief needs counts or sums. If the brief says “at least”, “more than” or “total”, you probably need grouping.
• Add data without its own conditions. The filter is right but the personalised value is wrong.
• Selecting collection fields directly. Putting a collection field in the select instead of an aggregate multiplies rows, one per transaction.
• Comparing date and datetime carelessly. “On or after 365 days ago” with a datetime column includes the time part. For day-level precision, compare with a date expression rather than the current timestamp.

Frequently asked questions

What is the difference between the Query activity and the delivery’s own target?

The delivery’s To tab can hold a query too, built with the same editor. The workflow Query is more flexible because its output can be combined, split, enriched and saved before delivery, and each step leaves a count you can check.

Can the Campaign query activity target something other than recipients?

Yes. Any schema with a suitable link can be a targeting dimension, including custom tables. For a delivery, though, the targeting dimension must match a target mapping. Recipients are covered out of the box, and custom recipient tables need their own mapping.

Why is the count different from the delivery count?

The query selects people. The delivery then removes quarantined and blacklisted addresses, duplicates and typology exclusions during analysis. A lower delivery count is expected, and the delivery’s exclusion log explains the difference.

When should I use exists instead of a different filtering dimension?

For simple presence checks on small or medium collections, exists is clear and readable. For counts, sums or very large linked tables, filter on the linked table directly.

Once you can build a query you trust, the next common problem is duplicates in the result, which my deduplication workflow guide covers.

Last updated on 26 October 2026