L1 Project - Excercises

Customer Transactions Analysis

Step 1.1. Access the Data from File(s)

1. Read CustomerInfoSystem1.xlsx with the Excel Reader node

2. Read CustomerInfoSystem2.table with the Table Reader node

3. Read Stores.csv with the CSV Reader node

Step 1.2. Access the Data from a Database

1. Connect to the database with the SQLite Connector node

2. With two DBTable Selector nodes, select the tables

   • Transactions

   • ProductNrAndPrice

3. Read the tables with two DB Reader nodes

Step 2.1. Clean the Data

Clean the Customer Information System 1 data

1. Exclude the first row and first column with the Table Cropper node

2. Remove rows with duplicate CustomerID with the Duplicate Row Filter node

3. When the age is missing, insert the mean age of the dataset with the Missing Value node

Step 3.2.Tidy up the Workflow with a Metanode

1. Repeat the steps of Data Cleaning and Data Transformation for the Customer Information System 2 data

2. Tidy up the workflow in a Metanode

Step 5.1. Aggregate the Data I

1. Sum the Price for each CustomerID with the Row Aggregator node

Step 6.1. Export the Data

1. Write the table containing the total price per customer with the Excel Writer node

Step 6.2. Visualize the Data I

1. Convert the BasketSize with the Number to String node

2. Show in a BarChart the amount of orders for each BasketSize, for both Online and Onsite Stores

Step 6.4. Components and Reporting

1. Create a component containing the visualization nodes

2. Use the Layout Editor to rearrange the interactive view of the component

3. Select Enable Reporting in the Layout Editor

4. Add a Report Template Creator node before the component and export its view as a PDF report with the Report PDF Writer node

Step 5.2. Aggregate the Data II

1. Calculate the Basket Size of each order (number of products in the order) with the GroupBy node. It can be obtained by counting any column. Retain information about the StoreType

2. Show the number of orders for each Basket Size in the Online Store and Onsite Store respectively with the Pivot node

Step 5.3. Aggregate the Data III

1. In a new branch, repeat step 5.1 with a GroupBy node. This time, for each customer, keep

   1. CustomerID

   2. CustomerGroup

   3. AgeGroup

   4. Sum of Price

   5. Mean Price

   6. Unique Count of ProductNr

Step 6.3. Visualize the Data II

1. Assign a color to each row according to the CustomerGroup column with the Color Manager node

2. In a Scatter Plot show the Sum(Price) and Unique Count(ProductNr)

3. Show the count of occurrences for CustomerGroup in a Bar Chart

4. Show a Parallel Coordinates Plot with

   • AgeGroup

   • CustomerGroup

   • Sum(Price)

   • Unique Count(ProductNr)

5. Create a title with the Text View node

Note: apply the color when possible. In the Bar Chart, in the Group Bar option select Frequency Dimension.

Learning objectives: Access, clean, transform, merge and aggregate data to report and visualize the results.

Workflow description: This workflow accesses customer and transactions data, transforms the data and generates an interactive view with multiple visualizations.

You’ll find the use case information in the blue annotations and the instructions to the exercises in the yellow annotations.

Use Case

Pauline from Sales would like to write a report and visualize the insights on the customer transaction data her team collects every month. This includes the total price per customer and the basket size per store type. Since Pauline receives the data from multiple sources each month, which needs blending, cleaning, and transformation before it can be put into a report, she has reached out to your data team for help to automate the process and thereby save time.  

Your continuous project will be to help Pauline build a workflow to report the monthly insights, by completing each step of the process she repeats every month below.

Data Access

Pauline receives the data from multiple sources, including files, and data base tables. Your first task is to start building the workflow by accessing the different data sources.

Data Cleaning

Now that the data access is established, Pauline asks your help in cleaning the data, since there seems to be duplicates, missing values, and some columns that are not needed.

Data Transformation

Next, you need to merge the cleaned data. To do so, Pauline outlines how she normally transforms some of the data in the rows and reshapes some columns to create the desired form.

Data Export, Visualization and Reporting

Pauline is happy that the data is ready to be exported. Her team needs the results from 5.1 as an Excel file, 5.1 as a Bar Chart, and 5.3. in an interactive dashboard that can be exported as a pdf.

Data Merging

With the data cleaned and in the correct shape, you can now help Pauline to merge the different data sources, by stacking tables and matching or joining others to add the necessary columns.

Data Aggregation

You have now reached the point where the Pauline's data can be aggregated to get the necessary insight concerning the price and basket sizes for customers and stores.

Step 4.1. Merge the Data

1. Concatenate the two customer tables with the Concatenate node

2. Append the StoreType (in Stores.csv) and Price (from the ProductNrAndPrice DB table) to each transaction (stored in the Transactions DB table) with two Value Lookup nodes

3. Join the Customer and Transaction tables on the CustomerID column with the Joiner node.

Note: The first output port of the Joiner shows the rows with a match in the two tables. Use only these rows in the next steps.

Step 3.1. Transform the Data

1. Create a new column AgeGroup with values Adolescent, Adult, Older Adult according to the age value: <18 Adolescents, 18-65 Adults, >65 Older Adults

2. Replace the character "_" with a space " " from the column country Country

3. Split the column CustomerID on the character "_" with the Cell Splitter node

4. Rename the columns created by the split with the Column Renamer node

5. Merge the columns Email and CorporateEmail with the Column Merger node. Keep the Email when both are available.

6. Convert the column Newsletter with the Number to String node

Perform instructions 1 and 2 with two Expression nodes. Click Ask K-AI and copy paste the text of the instruction in the prompt.