Aggregates

Aggregate functions perform field calculations within a set of customer records. For example, consider Figure 2. If you wish to select customers whose total purchases is greater than $100, you will need to calculate the sum of the Amount values for each customer and examine the total. An aggregate function, such as Sum or Average, combines multiple values like these into one value.

Topics: 

Table 2: Data for Aggregate Functions Examples
 

In TouchPoint, the available aggregate functions are:

Occurrences
Counts the number of records for the customer. For customer 123, Count Occurrences is EQUAL to 4.

Count
Counts the number of unique values for the given field. Customer 123 has four records, but Count Values of Order Number = 3, because order 960306-87 appears twice. 

Sum
Calculates a sum of the selected field for each customer. The Sum of Amount = $88.84 for customer 123. This function is only available for numeric fields from your tables. 

Average
Calculates the arithmetic average of the specified field for each customer. For customer 234, the Average of Amount = $42.40. This function is only available for numeric fields from your tables.

List
Returns the list of unique values for each row that matches the criteria of a specified field. For customer 234, the List of Product Number = CC010;CD040.

Minimum
Returns the minimum value for the specified field per customer. For customer 123, the Minimum of Purchase Date = 03/06/04.

Maximum
Returns the maximum value for the specified field per customer. For customer 123, Maximum of Purchase Date = 05/03/05.

First Row
This function only looks at criteria in the first row and will ignore all other rows in the table. The First Row of Order Number = 960306-87.

Any/All Rows Using the customer data in Figure 2, suppose you wanted to select customers who have only placed orders of $20 or more. In examining the purchase records for customer 123, you can see that, although two of the customer's purchases satisfy this condition, product numbers JH033 and LY100 do not. To prevent TouchPoint from including this customer, you would select the All Rows function.

• • The Any Row function allows you to select customers where at least one of the customer's records matches the condition you are entering.
  • The All Rows function allows you to select customers where every customer record matches the condition you are entering.

To Create a New Aggregate

After selecting a table, the Table Type prompt will appear to select what type of table you are choosing. You will have four options:

• • One Row - This will list the table as a customer table and you will not be able to create aggregates with this option selected.
  • More Than One Row - This will list the table as a transactional table and you will have the option of creating aggregates.
  • I'm not sure, figure it out for me - based upon the selected table, the system will automatically choose what table type it believes to be the best option and it will set the table type for you.
  • Cancel - It will exit out of the Table Type prompt and there will be no changes.
    
    
• Drag the New Aggregate item and drop it into the Criteria tree, where the Aggregate Function window will open.
• In the Aggregate section, you will select what Function and Object to be used.
  
  In the Group section, you will have three options to choose from:
  • All Orders - The aggregate is put into an unfiltered, default group.
  • New - Allows you to customize the Group name and add a Group filter.
  • Select - Allows you to choose a group that already exists to place the aggregate in from the drop-down list.
    
    
• If you select New, click the Add Filter button ( ).
• In the New Group Filter section, drag and drop the field(s) into the criteria panel.
• Choose the appropriate condition(s) and value(s).
• Enter in your name for the filter and press OK to go back to the Aggregate Function window.
• In the Name section, you can either enter a name for the new aggregate or use the default name given.
• Click OK to save changes.

DATE FUNCTIONS

In addition to aggregate functions, TouchPoint offers built-in date functions for calculating intervals between the current system date (i.e. time and date according to the host) and a date field in your database. For example, you run a daily campaign to select all customers who have magazine subscriptions expiring in ninety days. You can use the DaysUntil date function to set up the following statement:

DaysUntil of Expiration_Dt is Equal to 90

During selection, the DaysUntil function looks at the Expiration_Dt and compares it to the current system date on your computer to see if the number of days between the expiration dates equals 90. You can run this same campaign each day without changing the criteria because the DaysUntil function always uses the current system date as the basis for comparison, saving you campaign creation time.

In TouchPoint, the list of available date functions are:

DaysUntil 
Calculates the number of days until the designated date field based on the system date on your computer.

DaysPast
Calculates the number of days past the designated date field based on the system date on your computer.

WeekDaysUntil
Calculates the number of business weeks until the designated date field based on the system date on your computer. The WeekDaysUntil function considers the complete week as one business week (i.e. Monday through Friday). The week begins 12 am Monday, ends at midnight Friday and ignores Saturday and Sunday. The results from using WeekDaysUntil will pull from the entire business week that the query lands on. WeekDaysUntil = 0 will pull everything from the current business week.

WeekdaysPast
Calculates the number of business weeks past the designated date field based on the system date on your computer. The WeekDaysPast function considers the complete week as one business week (i.e. Monday through Friday). The week begins 12 am Monday, ends at midnight Friday and ignores Saturday and Sunday. The results from using WeekDaysPast will pull from the entire business week that the query lands on. WeekDaysPast = 0 will pull everything from the current business week.

WeeksUntil
Calculates the number of weeks until the designated date field based on the system date on your computer. The WeeksUntil function considers the complete week as one calendar week (i.e. Sunday through Saturday). The cutoff date for the week is midnight between Saturday and Sunday. The results from using WeeksUntil will pull from the entire week that the query lands on. WeeksUntil = 0 will pull everything from the current week.

WeeksPast
Calculates the number of weeks past the designated date field based on the system date on your computer. The WeeksPast function considers the complete week as one calendar week (i.e. Sunday through Saturday). The cutoff date for the week is midnight between Saturday and Sunday. The results from using WeeksPast will pull from the entire week that the query lands on. WeeksPast = 0 will pull everything from the current week.

MonthsUntil
Calculates the number of months until the designated date field based on the system date on your computer. The MonthsUntil function considers the complete month as one month (i.e. January 1 through February 1). The month begins 12 am of the first day of the month and ends at midnight on the first day of the next month. The results from using MonthsUntil will pull from the entire month that the query lands on. MonthsUntil = 0 will pull everything from the current business month.

MonthsPast
Calculates the number of months past the designated date field based on the system date on your computer. The MonthsPast function considers the complete month as one month (i.e. January 1 through February 1). The month begins 12 am of the first day of the month and ends at midnight on the first day of the next month. The results from using MonthsUntil will pull from the entire month that the query lands on. MonthsPast = 0 will pull everything from the current business month.

YearsUntil
Calculates the number of years until the designated date field based on the system date on your computer. The YearsUntil function considers the complete year as one year (i.e. 2015 through 2016). The year begins 12 am of the first day of the year and ends at midnight on the first day of the next year. The results from using YearsUntil will pull from the entire year that the query lands on. YearsUntil = 0 will pull everything from the current year.

YearsPast
Calculates the number of years past the designated date field based on the system date on your computer. The YearsPast function considers the complete year as one year (i.e. 2015 through 2016). The year begins 12 am of the first day of the year and ends at midnight on the first day of the next year. The results from using YearsPast will pull from the entire year that the query lands on. YearsPast = 0 will pull everything from the current year.

Aggregate Library

Like segments, commonly used aggregates groups can be created and then saved into libraries to be used for future use. There are no limits on how many aggregates that you can create and save to the library.

How to Publish a Segment to the Aggregate Library

• In the Aggregate Library tab, Right-click over Library (  ) and select New Group (  ).
• In the Aggregate Group Editor dialog box, click the SQLManager icon () in the Table section to select the appropriate table and press OK.
• In the Functions section, you can add, remove or edit the selected functions.
• In the Filter section, you can add a filter by pressing the Add button (  ) or editing the existing filter by clicking in the Filter area.
• In the Group Name section, you can either enter a custom name for the group or use the default name given.
• In the Tags section, you can click the ellipses button () to choose which tags you will apply to the Aggregate group.
• Press Publish to save all changes.

Tags
You can create and apply tags to all your aggregates for an additional layer of filtering. Once a tag has been added, it will be saved and will show in a list of tags that has already been created. The option to create a tag is only available when an aggregate is published. 

How to Add a Tag

• In the Aggregate Library, right-click on the Aggregate Group Name.
• Select Tags ().
• In the Tag Editor dialog box, enter in the name for the tag.
• Click the Add button ().
• Press OK to save the changes.