Show Menu
主题×

Query

Description

Query 活动允许您过滤和提取Adobe Campaign数据库中的大量元素。You can define Additional data for the targeted population via a dedicated tab. 此数据存储在其他列中,只能用于进行中的工作流。
活动使用查询编辑器工具。This tool is detailed in a dedicated section .

Context of use

Query 该活动可用于各种类型的用途:
  • 将个人细分为定义消息、受众等目标。
  • 丰富整个Adobe Campaign数据库表的数据。
  • 导出数据。

Configuration

  1. Drag and drop a Query activity into your workflow.
  2. Select the activity, then open it using the button from the quick actions that appear. 默认情况下,将活动预配置为搜索配置文件。
  3. If you would like to run a query on a resource other than the profile resource, go to the activity's Properties tab and select a Resource and a Targeting dimension .
    The Resource allows you to refine the filters displayed in the palette whereas the Targeting dimension , contextual with regard to the resource selected, corresponds to the type of population that you would like to obtain (identified profiles, deliveries, data linked to the selected resource, etc.).
    For more on this, refer to Targeting dimensions and resources
  4. Target 在选项卡中,通过定义和合并规则来运行查询。
  5. You can define Additional data for the targeted population via a dedicated tab. 此数据存储在其他列中,只能用于进行中的工作流。特别是,您可以从链接到查询定位维度的Adobe Campaign数据库表中添加数据。Consult the Enriching data section.
    By default, the Remove duplicate rows (DISTINCT) option is checked in the Advanced options of the Additional data tab of the query. If the Query activity contains many (from 100) additional data defined, it is recommended to uncheck this option, for performance reasons. 请小心,取消选中此选项可能会导致复制副本,具体取决于查询的数据。
  6. Transition 在选项卡中,该 Enable an outbound transition 选项允许您在查询活动后添加出站过渡,即使它不检索数据也是如此。
    The outbound transition's segment code can be personalized using a standard expression and events variables (see Customizing activities with events variables ).
  7. 确认活动的配置并保存工作流。

Targeting dimensions and resources

定位维度和资源可让您定义查询将基于哪些元素确定交付目标。
定位维度在目标映射中定义。For more on this, refer to this section .

Defining the targeting dimension and resource of a query

Targeting dimension and resources are defined when creating a workflow, in the Properties tab of a Query activity.
The targeting dimension can also be defined when creating an audience (see this section ).
链接维度和资源是链接的。因此,可用的定位维度取决于选定的资源。
For example, for the Resource Profiles (profile) , the following targeting dimensions will be available:
While for Deliveries (delivery) , the list will contain the following targeting dimensions:
指定定位维度和资源后,查询中便会提供不同的过滤器。
Profiles (profile) 资源可用过滤器的示例:
Deliveries (delivery) 资源可用过滤器的示例:

Using resources different from targeting dimensions

默认情况下,定位维度和资源设置为目标配置文件。
但是,如果要查找远处的表中的特定记录,使用定位维度中的不同资源可能会很有用。
示例1:通过标签定位的标识配置文件”欢迎返回! .
  • 在这种情况下,我们需要定位档案。We will set the targeting dimension to Profiles (profile) .
  • 我们希望根据交付标签过滤选定的配置文件。We will therefore set the resource to Delivery logs . 这样,我们将直接在交付日志表中过滤,这将提供更好的性能。
示例2:识别未通过提供标签定位的配置文件“欢迎返回!”
在上一个示例中,我们使用了不同于定位维度的资源。This operation is only possible if you want to find a record that is present in the distant table (delivery logs in our example).
If we want to find a record that is not present in the distant table (for example, profiles who were not targeted by a specific delivery), you must use the same resource and targeting dimension, as the record will not be present in the distant table (delivery logs).
  • 在这种情况下,我们需要定位档案。We will set the targeting dimension to Profiles (profile) .
  • 我们希望根据交付标签过滤选定的配置文件。我们无法在交付日志表格中找到所需记录时直接对交付日志进行筛选。We will therefore set the resource to Profile (profile) and build our query on the profiles table.

Enriching data

Additional data 通过该选项卡 Query Incremental query Enrichment ,您可以丰富目标数据,并将此数据转移到以下工作流活动中,以便在其中使用它。特别是,您可以添加:
  • 简单数据
  • 集合
  • 集合
For aggregates and collections, an Alias is automatically defined to give a technical ID to a complex expression. 此别名必须是唯一的,之后可方便地查找集合和集合。您可以修改它,为其提供易于识别的名称。
别名必须遵守以下语法规则:只授权字母数字字符和“ ”字符。别名区分大小写。别名必须以“@”字符开头。紧跟在“@”之后的字符不能为数字。例如:@ myAlias_1和@ alias是正确的;而@ MyAlias#和@ Alias不正确。
添加任何其他数据之后,您可以对最初定位的数据应用额外的过滤器级别,以根据定义的附加数据创建条件。
By default, the Remove duplicate rows (DISTINCT) option is checked in the Advanced options of the Additional data tab of the query. If the Query activity contains many (from 100) additional data defined, it is recommended to uncheck this option, for performance reasons. 请小心,取消选中此选项可能会导致复制副本,具体取决于查询的数据。

Adding a simple field

通过添加一个简单字段作为额外的数据,该字段在活动的出站过渡中直接可见。这允许用户检查,例如,查询中的数据是所需的数据。
  1. From the Additional data tab, add a new element.
  2. In the window that opens, in the Expression field, select one of the fields available directly in the targeting dimension or in one of the linked dimensions. 您可以从维度字段中编辑表达式和使用函数或简单计算(集合除外)。
    An Alias is automatically created if you edit an expression that is not a simple XPATH path (for example: "Year(<@birthDate>)"). 如果您喜欢,可以修改它。If you only select one field (for example: "@age"), you do not need to define an Alias .
  3. Select Add to confirm adding the field to the additional data. 执行查询时,活动的出站过渡中将显示与添加的字段对应的其他列。

Adding an aggregate

分配允许从定位维度的字段或链接到定位维度的维度字段计算值。例如:配置文件购买的平均金额。
  1. From the Additional data tab, add a new element.
  2. In the window that opens, select the collection that you want to use to create your aggregate in the Expression field.
    An Alias is created automatically. If you like, you can modify it by going back to the query's Additional data tab.
    此时将打开聚合定义窗口。
  3. Define an aggregate from the Data tab. Depending on the type of aggregate selected, only the elements whose data is compatible are available in the Expression field. 例如,只能使用数字数据计算总和。
    您可以为所选集合的字段添加多个集合。确保定义显式标签以区分活动的出站数据详细信息中不同的列。
    您还可以更改为每个聚合自动定义的别名。
  4. 如果需要,您可以添加过滤器以限制考虑的数据。
    Refer to the Filtering added data section.
  5. Select Confirm to add aggregates.
You cannot create an expression containing an aggregate directly from the Expression field of the New additional data window.

Adding a collection

  1. From the Additional data tab, add a new element.
  2. In the window that opens, select the collection that you want to add in the Expression field. An Alias is created automatically. If you like, you can modify it by going back to the query's Additional data tab.
  3. Select Add . 此时将打开一个新窗口,允许您调整要显示的集合数据。
  4. In the Parameters tab, select Collection and define the number of lines of the collection that you want to add. For example, if you want to get the three most recent purchases carried out by each profile, enter "3" in the Number of lines to return field.
    您必须输入大于或等于1的数字。
  5. From the Data tab, define the fields of the collection that you want to display for each line.
  6. 如果您喜欢,可以添加过滤器以限制考虑到的集合线。
    Refer to the Filtering added data section.
  7. 如果您喜欢,可以定义数据排序。
    For example, if you have selected 3 lines to return in the Parameters tab, and you want to determine the three most recent purchases, you can define a descending sort on the "date" field of the collection that corresponds to the transactions.
  8. Refer to the Sorting additional data section.
  9. Select Confirm to add the collection.

Filtering added data

添加集合或集合时,可指定其他筛选器以限制要显示的数据。
For example, if you want to only process the collection lines of transactions with amounts of 50 dollars and above, you can add a condition on the field corresponding to the transaction amount from the Filter tab.

Sorting additional data

当您向查询的数据添加集合或集合时,您可以根据字段的值或定义的表达式指定是否要应用排序(无论是升序还是降序)。
For example, if you want to save only the transaction that was carried out most recently by a profile, enter "1" in the Number of lines to return field of the Parameters tab, and apply a descending sort on the field corresponding to the transaction date via the Sort tab.

Filtering the targeted data according to additional data

Once you have added additional data, a new Output filtering tab appears in the Query . This tab allows you to apply an additional filter on the data initially targeted in the Target tab, by taking into account the added data.
For example, if you have targeted all of the profiles that carried out at least one transaction and an aggregate calculating the average transaction amount carried out for each profile was added to the Additional data , you can refine the population initially calculated using this average.
To do this, in the Output filtering tab, simply add a condition on this additional data.

Example: personalizing an email with additional data

以下示例演示了在电子邮件中向查询添加不同类型的其他数据,以及它用作个性化字段的用法。
For this example, custom resources are used:
  • 扩展了配置文件 资源,以添加允许保存每个配置文件的忠诚度点的字段。
  • 创建了事务 处理资源,并识别了由数据库中的配置文件执行的所有购买。每次交易均保存购买的日期、价格和产品。
  • A products resource was created and references the products available for purchase.
目标是向至少保存了一个事务的配置文件发送电子邮件。通过此电子邮件,客户端将收到上次执行的事务提醒以及其所有事务的概述:购买的产品数量、支出总数,提醒他们已累计的忠诚度积分总数。
此时将显示工作流:
  1. Add a Query activity, which allows you to target the profiles that have carried out at least one transaction.
    From the query's Additional data tab, define the different data to be displayed in the final email:
    • The simple field of the profile dimension corresponding to the loyalty points. Refer to the Adding a simple field section.
    • 两个集合基于事务集合:购买的产品数量和支出的总金额。You can add them from the Data tab of the aggregate configuration window, using the Count and Sum aggregates. Refer to the Adding an aggregate section.
    • 返回所花的金额、日期和上次交易的产品的集合。
      To do this, you have to add the different fields that you want to display from the Data tab of the collection configuration window.
      To return only the most recent transaction, you have to enter "1" for the Number of lines to return and apply a descending sort on the Date field of the collection from the Sort tab.
    If you would like to check that the data is correctly transferred by the activity's outbound transition, start the workflow for the first time (without the Email delivery activity) and open the query's outbound transition.
  2. Add an Email delivery activity. 在电子邮件内容中,插入与查询中计算的数据对应的个性化字段。You can find it via the Additional data (targetData) link of the personalization fields explorer.
现在可以执行您的工作流。在查询中定位的配置文件将收到一封个性化电子邮件,其中包含通过其事务计算的数据。

Query samples

Targeting on simple profile attributes

以下示例演示了一个查询活动,该活动配置为在18到30岁之间定位男性,住在伦敦。

Targeting on email attributes

以下示例显示了一个配置为目标配置文件的查询活动,该配置文件使用电子邮件地址域“orange.co.uk”。
以下示例显示配置为电子邮件地址的目标配置文件的查询活动。

Targeting profiles whose birthday is today

以下示例显示了一个配置为目标配置文件的查询活动,其生日为今天。
  1. Drag the Birthday filter in your query.
  2. Set the Filter type to Relative and select Today .

Targeting profiles who opened a specific delivery

以下示例显示了一个查询活动,该活动配置为过滤以标签“夏季时间”打开交付的配置文件。
  1. Drag the Opened filter in your query.
  2. Select the delivery then click Confirm .

Targeting profiles for whom deliveries failed for a specific reason

以下示例显示一个配置为过滤配置文件的查询活动,这些配置文件因其邮箱已满而失败。This query is only available for users with administration rights and belonging to the All (all) organizational units (see this section ).
  1. Select the Delivery logs resource in order to filter directly in the delivery log table (see Using resources different from targeting dimensions ).
  2. Drag the Nature of failure filter in your query.
  3. 选择要定位的失败类型。 Mailbox full ​在我们的情况下。

Targeting profiles not contacted during the last 7 days

以下示例显示了一个配置为过滤配置文件的查询活动,该活动在过去天内未联系在一起。
  1. Drag the Delivery logs (logs) filter in your query.
    Select Does not exist in the drop-down list, then drag the Delivery filter.
  2. 如下配置过滤器。