Create a summary list creating-a-summary-list
This use case details the creation of a workflow which, after collecting files and following several enrichments, lets you create a summary list. The example is based on a list of contacts who made purchases in a store.
The following data structure is used:
Its purpose is to:
- To use the various options of the enrichment activity
- To update the data in the database following a reconciliation
- To create a global “view” of the enriched data
To create a summary list, you need to follow these steps:
- Collecting and loading a “Purchases” file in the work table of the workflow
- Enriching the imported data by creating a link to a reference table
- Updating the “Purchases” table with the enriched data
- Enriching the “Contacts” data with an aggregate calculation from the “Purchases” table
- Creating a summary list
Step 1: Load the file and reconcile the imported data step-1--loading-the-file-and-reconciling-the-imported-data
The data to be loaded is “Purchase” related data with the following format:
Product Name;Product price;Store
Computer;2000;London 3
Tablet;600;Cambridge
Computer;2000;London 5
Computer;2000;London 8
Tablet;600;Cambridge
Phone;500;London 5
This data is contained in a “Purchases.txt” text file.
-
Add the File collector and Data loading (file) activities to the workflow.
The File collector activity lets you collect and send files from and to the ۶Ƶ Campaign server.
The Data loading(file) activity lets you enrich the work table of the workflow with the collected data. For more on this activity, refer to this page.
-
Configure the File collector activity to collect text (*.txt) type files from the selected directory.
The File collector activity lets you manage the absence of a file in the source directory. To do this, check the Process file nonexistence option. In this workflow, a Wait activity has been added to try another file collection if it is missing from the directory at the time of collection.
-
Configure the Data loading (file) activity using a sample file with the same format as the data to be imported.
Click the Click here to change the file format… link to rename the columns using the internal names and labels of the “Purchases” table.
Once the data has been imported, enrichment is carried out by creating a link to a reference table which matches the “Stores” schema.
Add the Enrichment activity and configure it as follows:
-
Select the main set made up of the data from the Data loading(file) activity.
-
Click Add data, then select the A link option.
-
Select the Define a collection option.
-
Select the “Stores” schema as a target.
For more on the various types of links, refer to Enriching and modifying data.
In the following window, you need to create a join condition by selecting the source field (in the main set) and the target field (belonging to the “Stores” schema) to configure data reconciliation.
Now the link is created, we’re going to add a column to the work table of the workflow from the “Stores” schema: the “ZipCode Reference” field.
- Open the enrichment activity.
- Click Edit additional data.
- Add the “ZipCode Reference” field to the Output columns.
The data in the work table of the workflow after this enrichment will be as follows:
Step 2: Write enriched data to the ‘Purchases’ table step-2--writing-enriched-data-to-the--purchases--table
This step details how to write the imported and enriched data to the “Purchases” table. To do this, we need to use an Update data activity.
A reconciliation between the data in the work table of the workflow and the Purchases targeting dimension must be carried out before the data in the Purchases table is updated.
- Click the Reconciliation tab of the enrichment activity.
- Select the targeting dimension, the “Purchases” schema in this case.
- Select a “Source expression” for the data in the workflow table (the “storeName” field in this case).
- Select a “Destination expression” for the data in the “Purchases” table (the “storename” field in this case).
- Check the Keep unreconciled data coming from the work table option.
In the Update data activity, the following configuration is needed:
- Select the Insert or update option in the Operation type field to avoid creating new records each time the file is collected.
- Select the By directly using the targeting dimension value for the Record identification option.
- Select the “Purchases” schema as a Document type.
- Specify the list of fields to be updated. The Destination column lets you define the fields of the “Purchases” schema. The Expression column lets you select the fields in the work table to carry out a mapping.
- Click the Generate an outbound transition option.
Step 3: Enrich ‘Contact’ data step-3--enriching--contact--data-
The “Contacts” schema is physically linked to the “Purchases” schema. This means you can use another option of the “Enrichment” option: adding data linked to the filtering dimension.
The purpose of this second enrichment is to create an aggregate on the purchase schema to calculate the total amount of purchases for each identified contact.
-
Add a query type activity that lets you recover all Contacts stored.
-
Add an Enrichment activity then select the main set resulting from the previous query.
-
Click add Data.
-
Click the Data linked to the targeting dimension option.
-
Click the Data linked to the filtering dimension option in the Select fields to add window.
-
Select the Purchases node then click Next.
-
Change the Collected data field by selecting the Aggregates option.
-
Click Next.
-
Add the following expression to calculate the purchase total for each contact: “Sum(@prodprice)”.
To prepare the summary list, you need to add fields from the “Purchases” fields and from the first enrichment: the “ZipCode Reference” field.
-
Click the Edit additional data… link in the enrichment activity.
-
Add the “Store name” and “Purchases / Zip Code Reference” fields.
-
Click the Properties tab.
-
Change the second link to create only one line.
Step 4: Create and add to a summary list step-4--creating-and-adding-to-a-summary-list
The last step involves writing all the enriched data to a list.
- Add a List update activity to the workflow. This activity must be linked to the outbound transition of the second enrichment activity.
- Select the Create the list if necessary (Calculated name) option.
- Select a value for the calculated name. The label chosen for the list is the current date: <%= formatDate(new Date(), “%2D/%2M/%2Y”) %>.
Once the workflow is executed, the list will include:
- a list of contacts,
- a “Total purchases” column,
- a “Store name” column,
- a “Zip Code Reference” column entered for all stores contained in the store reference schema.
