KNIME_​project_​exam_​1

Step 1: Access the data from Files

1. Read Dirty Data csv with the CSV Reader node

2. Read Missing data csv with the CSV Reader node

3. Read Warehouses.csv with the CSV Reader node

Step 2: Clean text casing

1. Standardize nearest_warehouse casing (e.g. "nickolson" → "Nickolson")

2. Standardize season casing (e.g. "winter" → "Winter")

Step 3: Fix malformed dates

1. Flag rows with non-standard date formats (space, dash, or slash separators) using Rule Engine

2. Split flagged rows from correctly formatted rows with Row Splitter

3. Reformat flagged dates to yyyy-MM-dd with String Manipulation (regex)

4. Merge fixed and original rows back together with Concatenate

5. Convert the date column from String to Date&Time type

Step 4: Fix geo and outlier errors

1. Correct customer_lat sign errors (positive values flipped to negative) with Math Formula

2. Detect and null out extreme outliers in order_price and distance_to_nearest_warehouse using Numeric Outliers (IQR, multiplier 3.0)

Step 5: Recompute order_total

1. Calculate the correct order_total from order_price, coupon_discount, and delivery_charges

2. Compare the calculated value to the original order_total

3. Flag rows where the difference exceeds 1 as mismatches

4. Overwrite order_total with the corrected value for flagged rows

5. Remove temporary helper columns

Step 6: Impute remaining missing values

1. Fill missing order_price and distance_to_nearest_warehouse values (created by outlier removal) using median imputation

Step 7: Clean and impute missing_data.csv

1. Fill missing nearest_warehouse and season using most-frequent-value imputation

2. Fill missing order_price, order_total, distance_to_nearest_warehouse, customer_lat, and customer_long using median imputation

3. Lowercase the latest_customer_review text with String Manipulation

4. Infer missing is_happy_customer values from review keywords using Rule Engine (negative keywords → False, positive keywords → True, default True)

5. Remove the temporary review_lower helper column

Step 8: Combine cleaned datasets

1. Concatenate the cleaned Dirty Data and Missing Data branches into a single 1000-row table

Step 9: Join warehouse coordinates

1. Join the combined orders table with warehouses.csv on nearest_warehouse = names

2. Append warehouse latitude and longitude to each order row for use in map visualizations

3. Fix date column type consistency using Column Auto Type Cast (yyyy-MM-dd)

4. Remove the duplicate names column (redundant with nearest_warehouse after the join)

5. Rename lat/lon to warehouse_lat/warehouse_lon to distinguish from customer_lat/customer_long

6. Count distinct products in shopping_cart and append as basket_size

7. Extract month number from date using Date&Time Part Extractor

Step 10: GroupBy Aggregate revenue for View 1

1. Group by Month (Number) and sum order_total to get monthly revenue

2. Group by season and sum order_total to get seasonal revenue

3. Convert Month (Number) to string for chart compatibility (season is already a string, no conversion needed)

Step 11: Visualize View 1 — Sales Overview

1. Chart monthly revenue as a bar chart (Month vs Sum of order_total)

2. Chart seasonal revenue as a bar chart (season vs Sum of order_total)

3. Chart warehouse revenue as a bar chart (nearest_warehouse vs Sum of order_total)

4. Add an interactive season filter to all three charts within a Component

Step 12: Aggregate & Visualize View 2 — Geography & Logistics

1. Chart distance_to_nearest_warehouse vs delivery_charges as a scatter plot to check for a price/distance relationship

2. Chart customer_long vs customer_lat as a scatter plot to show geographic distribution of customers

3. Group by nearest_warehouse and count order_id to get order volume per warehouse

4. Chart order count by warehouse as a bar chart

5. Add an interactive warehouse filter to all three charts within a Component

Step 12: GroupBy Aggregate order count for View 2

1. Group by nearest_warehouse and count order_id to get order volume per warehouse

Step 13: Visualize View 2 — Geography & Logistics

1. Chart distance_to_nearest_warehouse vs delivery_charges as a scatter plot

2. Chart customer_long vs customer_lat as a scatter plot showing geographic distribution

3. Chart order count by warehouse as a bar chart

4. Add an interactive warehouse filter to all three charts within a Component

Step 14: Aggregate & Visualize View 3 — Customer Satisfaction

1. Group by is_happy_customer and count order_id to get happy/unhappy customer totals

2. Chart the happy/unhappy breakdown as a pie chart

3. Chart basket_size distribution grouped by is_happy_customer as a box plot

4. Convert is_happy_customer to a numeric flag (1/0) using Rule Engine

5. Group by nearest_warehouse and average the numeric happiness flag to get happiness rate per warehouse

6. Chart happiness rate by warehouse as a bar chart

7. Add an interactive season filter to all three charts within a Component