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 鈥渧iew鈥 of the enriched data
To create a summary list, you need to follow these steps:
- Collecting and loading a 鈥淧urchases鈥 file in the work table of the workflow
- Enriching the imported data by creating a link to a reference table
- Updating the 鈥淧urchases鈥 table with the enriched data
- Enriching the 鈥淐ontacts鈥 data with an aggregate calculation from the 鈥淧urchases鈥 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 鈥淧urchase鈥 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 鈥淧urchases.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 51黑料不打烊 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 Load data from a file.
-
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 鈥淧urchases鈥 table.
Once the data has been imported, enrichment is carried out by creating a link to a reference table which matches the 鈥淪tores鈥 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 鈥淪tores鈥 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 鈥淪tores鈥 schema) to configure data reconciliation.
Now the link is created, we鈥檙e going to add a column to the work table of the workflow from the 鈥淪tores鈥 schema: the 鈥淶ipCode Reference鈥 field.
- Open the enrichment activity.
- Click Edit additional data.
- Add the 鈥淶ipCode 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 鈥楶urchases鈥 table step-2--writing-enriched-data-to-the--purchases--table
This step details how to write the imported and enriched data to the 鈥淧urchases鈥 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 鈥淧urchases鈥 schema in this case.
- Select a 鈥淪ource expression鈥 for the data in the workflow table (the 鈥渟toreName鈥 field in this case).
- Select a 鈥淒estination expression鈥 for the data in the 鈥淧urchases鈥 table (the 鈥渟torename鈥 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 鈥淧urchases鈥 schema as a Document type.
- Specify the list of fields to be updated. The Destination column lets you define the fields of the 鈥淧urchases鈥 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 鈥楥ontact鈥 data step-3--enriching--contact--data-
The 鈥淐ontacts鈥 schema is physically linked to the 鈥淧urchases鈥 schema. This means you can use another option of the 鈥淓nrichment鈥 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: 鈥淪um(@prodprice)鈥.
To prepare the summary list, you need to add fields from the 鈥淧urchases鈥 fields and from the first enrichment: the 鈥淶ipCode Reference鈥 field.
-
Click the Edit additional data鈥 link in the enrichment activity.
-
Add the 鈥淪tore name鈥 and 鈥淧urchases / 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 鈥淭otal purchases鈥 column,
- a 鈥淪tore name鈥 column,
- a 鈥淶ip Code Reference鈥 column entered for all stores contained in the store reference schema.
