Icon

Analyze Sales Data Solution

Step 1:

Drag-and-drop these files from the /data folder:

- CRM1.xlsx
- CRM2.csv
- Transactions.csv

The appropriate reader nodes will be created.

Step 2:

Concatenate the two CRM files together into a single table.

Remove whitespace from the email addresses using the replace() function in the String Manipulation node.

Change the type of the Newsletter field to string using a Table Manipulator node. You can also reorder the columns if you like by clicking the dots next to each field in the configuration dialog, and dragging them up and down.

Step 3:

Use the Value Lookup to change the Newsletter field from 0/1 to Yes/No. The lookup table you will need is present in the Table Creator node.

Remove all duplicates from the cleaned dataset using the Duplicate Row Filter node.

Step 4:

Join the cleaned CRM data with the Transactions data from Step 1. Join on Customer ID. Ensure that the resulting table doesn't contain any personally identifying information.

Use the Rule Engine to translate month names to month numbers.

Finally, sort the resulting table on year and month number so that the transactions are in chronological order.

Step 5:

In one branch, use a GroupBy node to calculate, by sales unit, the total price.

In a separate branch, use a Pivot node to calculate, by year and month number, the total price. Create columns for each sales unit.

Step 6:

For the top branch, create a bar chart of sales by unit.

For the bottom branch, create a stacked area chart plotting each of the three sales units using a Stacked Area Chart (Javascript) node. Use RowID for the X-axis.

Step 7 (Optional):

From the top port of the pivoting node, attach a Table View (Javascript) node. Put both the stacked area chart from Step 7 and the Table View (Javascript) into a component, and generate the combined view. Write the table to an Excel file using and Excel Reader node.

CRM 1
Excel Reader
CRM 2
CSV Reader
On year and month number
Sorter
Combine the customer data
Concatenate
Removeduplicates
Duplicate Row Filter
Transactions
CSV Reader
Add Sales Unitto Transactions
Joiner
Revenue per Month and SalesUnit
Pivot
Sales by Unit
Bar Chart (JavaScript)
Newsletter field to string
Table Manipulator
Write Results into an Excel Table
Excel Writer
Value Lookup
Sum sales by Unit
GroupBy
Newsletter code lookup
Table Creator
Create month number
Rule Engine
remove whitespace in email addresses
String Manipulation
Table View (JavaScript)
Visualize Revenue overtime
Stacked Area Chart (JavaScript)

Nodes

Extensions

Links