The steps for building a query in Adobe Campaign are as follows:
All these steps are available in the generic query editor. When a query is created in another context, some steps can be left out.
The Query activity is presented in this section.
Select the table containing the data you want to query in the Document type window. If necessary, filter the data using the filter field or the Filters button.
In the Data to extract window, select the data to display: these fields will make up the output columns.
For example, select Age, Primary key, Email domain and City. The results will be organized based on this selection. Use the blue arrows to the right of the window to change the column order.
You can edit an expression by inserting a formula into it or running a process on an aggregate function. To do this, click the Expression column field, then select Edit expression.
It is possible to group output column data: to do this, check Yes in the Group column of the Data to extract window. This function generates a result around the checked grouping axis. An example of a query with grouping is available in this section.
The Handle groupings (GROUP BY + HAVING) function lets you “group by” and select what has been grouped (“having”). This function applies to all fields in the output column. For example, this option lets you group all choices of an output column and recover a specific type of information, such as recipients between 35 and 50.
For more on this, refer to this section.
The Remove duplicate rows (DISTINCT) function lets you deduplicate identical results obtained in the output column. For example, if you take a census by selecting the Last name, First name and Email fields in the output column, those with identical data will be eliminated, since it means the same contact has been entered several times in the database: only one result will be taken into account.
The Sorting window lets you sort column content. Use the arrows to change the column order:
In this example, the data is sorted in ascending order based on recipient age.
The query editor lets you filter data to refine your search.
The filters offered depend on the table which the query concerns.
Once you select the Filtering conditions you will access the Target elements section: this lets you define how to filter the data to collect.
To create a new filter, select the fields, operators and values required for creating the formula to be verified in order for data to be selected. It’s possible to combine several conditions (for more on this, refer to Defining filter conditions).
To use previously saved filters, open the drop-down list by clicking the Add button, click Predefined filter and select the one you want.
The filters created in the Generic query editor are available in other query applications and vice versa. To save a filter, click the Save icon.
For more on creating and using filters, refer to Filtering options.
As shown in the following example, to recover all English-speaking recipients, select: “recipient language equal to EN”.
You can directly access an option by typing the following formula in the Value field: $(options:OPTION_NAME).
Click the Preview tab to view the result of the filtering condition. In this case, all English-speaking recipients are displayed with their name, first name and email address.
Users familiar with SQL language can click Generate SQL query to view the query in SQL.
Once you have configured the restriction filters, you will access the Data formatting window. This window lets you re-arrange output columns, transform data, and change the upper/lower case of the column labels. It also lets you apply a formula to the final result using a calculated field.
For more information on the types of calculated fields, refer to Creating calculated fields.
Unchecked columns will not be shown in the data preview window.
The Transformation column lets you change a column label to upper or lower case. Select the column and click in the Transformation column. You can choose:
The Data preview window is the last stage. Click Start the preview of the data to get your query result. It is available in columns or in XML format. Click the Generated SQL queries tab to view the query in SQL format.
In this example, data is sorted in ascending order based on recipient age.
By default, only the first 200 lines are displayed in the Data preview window. To change this, enter a number in the Lines to display box and click Start the preview of the data.