29. Find how many orders each customer placed

Note: The scenario below uses data from the DESelect Demo Data Package.

Scenario: You want to get all customer information, along with the number of orders they placed.

Note: This scenario uses the feature Aggregations, which is available in DESelect Plus and DESelect Advanced.

1. Input

On the selections overview screen, click on New to create a new selection. 

Drag DESELECT_DEMO_Customers to the Selected Data Extensions section.

2. Output

Hit the Create Data Extension button, enter a name, eg. Customers With Number of Orders, and press Save.

Select all the fields by clicking on the Add All Fields button.

Now we're going to add a Custom Value:

• Under Custom Values on the left, click Add new value.
• Under Name, enter: Number of Orders and hit Next.
• Choose Type: Aggregation.
• Choose Count on the Aggregation Function dropdown.
• Select Data Extension to make the aggregation on:
  • Below the Field section, on the first dropdown select DESELECT_DEMO_Orders.
  • Below the Field section, on the second dropdown select Id.
• Create the relation between your aggregation Data Extension and your Selected Data Extensions:
  • Below the Relation to results section, next to the Field label, select CustomerId from the dropdown.
  • Below the Relation to results section, next to the matches with label, select DESelect_DEMO_Customers from the first dropdown.
  • Below the Relation to results section, next to the matches with label, select Id from the second dropdown.
• Hit Save.

Drag and drop your Number Of Orders custom value to your Target Data Extension.

Click Save Data Extension.

3. Preview

When you click Run Preview on the Preview screen, a results table will be shown. Each row in the Number Of Orders column will contain the number of orders the customer with the corresponding Id placed.