Aggregate customer data

Instead of a series of contracts we want a one-row summary for each customer.

For each customer, we:

• Calculate the total amount spent

• List the number of active countries

• Derive the "Customer since" date (day of first purchase)

Extract only some customers

Concatenate results using a Concatenate node

Step 1: Drag the "Concatenate" node to KNIME Analytics Platform.

Step 2:Click on the three dots in the bottom corner of the node, to add one extra input port for the node. Connect the Outputs of the Row Filter nodes to the Input ports of "Concatenate" node.

Step 3: Open the Dialog box and decide on the option for the "Duplicate row ID handling" and "Column handling".

List active customers for each country

Step 1:Double Click on the GroupBy node to open the dialog box.

Step 2: In the "Groups"tab. Select the string column "country". In the "Manual aggregation" tab, select the string column "Cust_ID" and aggregation as "Unique concatenate".

Step 3: Select "OK" and close the dialog box, now right click on the node and select on execute to get the list of customer Ids according to the country.

Join tables using a Joiner node

Step 1:Connect the tables to be joined to the input port of Joiner node.

Step 2: Open the dialog box and in "Matching Criteria" select the joining columns for both the Top input ('left' table) and Bottom input ('right' table).

Step 3: In the "Include in Output" section select the type of join.

Step 4: Select "OK" and close the dialog box, now right click on the node and select on execute to get the output of the join operation.

Transform Data using GroupBy and Joiner nodes

In this workflow, a number of ETL operations are performed on the sales_2008-2011.csv dataset. Besides showing what ETL features are, the goal of this workflow is to move from a series of contracts with different customers in different countries to a one-row summary description for each one of the customers. The one-row description includes:

1. The customer's unique ID

2. The total amount of money spent by the customer

3. The countries the customer has been active in

4. The date of the first purchase (the "Customer since" date - this is always useful to estimate customer loyalty)

In the end, the one-row description table is filtered to some customers only and lastly joined with the original input data (inner join). The resulting table is written to a CSV file in a "data" folder located in the workflow folder.

Workflow complete!

Keep the momentum going by exploring Just KNIME It!on the Hub to challenge yourself and see how these nodes can be integrated into more complex workflows and use cases.