Skip to content

Filter on related record totals

After these steps, the audience compares a count, sum, minimum, maximum or average across related records with a threshold, for example donors whose gifts add up to 1,000 or more.

Before you begin

Edition
Advanced, Professional, and Ultimate
Permission
Campaign Audience Builder: User permission set group or one that contains it. See Permission sets.

Related Records asks whether one related record meets the conditions. Aggregate calculates across all of them: a Contact with four gifts of 300 has no single gift over 1,000, but a sum of 1,200.

  1. On the canvas, select the node to start from, select the + next to it, choose Aggregate and select Use this filter. The window New Aggregate Filter opens.

  2. Under Set Aggregation Level, choose the related records to calculate across, for example the Opportunities of the Contact. An option with an arrow lets you go one level deeper, such as from Opportunities to their products. If an object is related in more than one way, each way is listed.

  3. Under Aggregate Function, choose the calculation. See Functions.

  4. Under Field, choose the field to calculate. Count needs no field.

  5. Choose the Operator and enter the Value to compare with, then select Next.

  6. Optionally, add conditions for the records that count, for example only won Opportunities. Each level has its own step. See Define filter conditions.

  7. Select Done.

The aggregate appears on the canvas as its own node, marked Aggregate. Undo and Redo in the header cover every step of the window.

FunctionCalculatesFields
SumThe total of the fieldNumber, currency, percent
CountHow many related records matchNo field
Count Unique ValuesHow many different values the field hasMost field types, including text, picklist, date and number
MinimumThe lowest valueNumber, currency, percent, date, date/time, time
MaximumThe highest valueNumber, currency, percent, date, date/time, time
AverageThe averageNumber, currency, percent, date, date/time, time

The calculated value can also appear as a column in the results and go into Campaign Member fields or data extension columns. See Related data and Data Inclusions.