Icon

Analyze Sales Data

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 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.

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.

Newsletter code lookup
Table Creator

Nodes

Extensions

Links