0. Setup & Objectives

File: Online Retail Sales.csv
Context: Sales data from an online store. Each row is the aggregate sales of one product type for a single day.

Learning Objectives:

  • Handle non-standard CSV delimiters (Forward Slash `/`).
  • Clean null categorical data using Formulas.
  • Parse Date strings into DateTime objects.
  • Feature Engineering: Extract Month, Quarter, and Year.
  • Summarize Tool: Grouping and Aggregating data.
  • Sort Tool: Organizing output.
  • Sample Tool: Finding top performers per group.

1. Input & Delimiter Input

Scenario: You drag the CSV onto the canvas, but the data looks messy. All data is in one column.

Problem: The file uses a forward slash / as a delimiter, not a comma.

Action: In the Input Data configuration, change the delimiter to /.

Note: In a real workflow, you would typically attach a Browse tool here to inspect the data quality.

2. Date Parsing DateTime

Problem: The [Date] column is currently a String (text). We cannot extract Month or Year easily from text.

Action: Use the DateTime tool.

  • Format: String to Date/Time
  • Input Pattern: MM/dd/yyyy
  • Output Name: Order Date

3. Handle Nulls Formula

Observation: The [Product Type] column has some Null values.

Action: Use a Formula tool to replace Nulls with "Other".

IF IsNull([Product Type]) 
THEN "Other" 
ELSE [Product Type] 
ENDIF

4. Record ID RecordID

Problem: There is no unique identifier for each row.

Action: Add a Record ID tool to assign a unique number to every transaction.

5. Data Typing Select

Action: Use the Select tool to clean up the schema.

  • Remove the original [Date] string field (we have [Order Date] now).
  • Change [Net Quantity] to Int16.
  • Change [Gross Sales], [Discounts], [Returns], [Total Net Sales] to FixedDecimal (19.2).
FixedDecimal 19.2: Good for currency. 19 total digits, 2 decimal places.

6. Feature Engineering (Date Parts)

We want to analyze sales by Month, Quarter, and Year. We need to create these columns based on [Order Date].

A. Extract Month DT

Use DateTime tool: Date/Time to String. Format: Custom Mon (e.g., Jan, Feb).

B. Create Quarter Form

Use Formula tool:

IF Contains("Jan Feb Mar", [Month]) THEN "Q1"
ELSEIF Contains("Apr May Jun", [Month]) THEN "Q2"
ELSEIF Contains("Jul Aug Sep", [Month]) THEN "Q3"
ELSE "Q4" 
ENDIF

C. Extract Year DT

Use DateTime tool: Date/Time to String. Format: Custom yyyy.

7. Summarize Data Summarize

Goal: Calculate total sales for each Month/Year/Quarter combination.

Configuration:

  • Group By: Year, Quarter, Month
  • Sum: Net Quantity, Gross Sales, Discounts, Returns, Total Net Sales
The Summarize tool changes the "shape" of data. 1,775 rows might become just 36 rows (12 months * 3 years).

8. Sort Results Sort

Goal: Find the highest performing periods easily.

Action: Sort by [Sum_Total Net Sales] in Descending order.

9. Filter Filter

Scenario: We notice Q4 2019 was huge. Let's isolate it.

Action: Filter where:

[Quarter] = "Q4" AND [Year] = "2019"

10. Sampling Sample

Goal: If we go back to the sorted list (before the filter), we want to see just the #1 top month for each quarter.

Action:

  • Sample Method: First N Rows (N=1)
  • Group By Column: Year, Quarter

This picks the top row (since we already sorted!) for every Year/Quarter group.

Bonus Challenges

Try answering these business questions by configuring your tools!
(Hint: These flows have been added to the Simulator Canvas below the main flow so you can test the configurations).

1. What are the highest-selling product types in terms of [Total Net Sales]?
Answer: Baskets, Art & Sculpture, Jewelry, and Home Décor.
Show Solution 1

Summarize → Sort (Branch from Select tool)

1) Summarize tool (Actions grid)

You end up with two rows in the Actions table:

  • Field: Product Type | Action: Group By | Output: Product Type
  • Field: Total Net Sales | Action: Sum | Output: Sum_Total Net Sales

2) Sort tool (Configuration)

  • ✅ Use Dictionary Order = checked
  • Dictionary: English (United States)
  • Name: Sum_Total Net Sales | Order: Descending
2. What is the highest-selling product type in terms of [Net Quantity] for each month?
Answer: Baskets in 5 of the 12 months, Art & Sculpture in 2, Jewelry in 2, Kitchen in 2, and Christmas in 1. (Don’t account for year).
Show Solution 2

Summarize → Sort → Sample (Branch from DT (Month) tool)

1) Summarize tool (Actions grid)

You have three rows in the Actions table:

  • Field: Month | Action: Group By | Output: Month
  • Field: Product Type | Action: Group By | Output: Product Type
  • Field: Net Quantity | Action: Sum | Output: Sum_Net Quantity

2) Sort tool (Configuration)

  • ✅ Use Dictionary Order = checked
  • Name: Sum_Net Quantity | Order: Descending

3) Sample tool (Configuration)

This is the key “pick the winner” step.

  • Select Sample Type: First N rows (radio selected) | N = 1
  • Group by column: ✅ Month checked
3. What product types have the highest [Returns]?
Answer: Similar products to Q1, but useful to analyze raw returns volume.
Show Solution 3

Summarize → Sort (Branch from Select tool)

1) Summarize tool (Actions grid)

Two rows again:

  • Field: Product Type | Action: Group By | Output: Product Type
  • Field: Returns | Action: Sum | Output: Sum_Returns

2) Sort tool (Configuration)

  • ✅ Use Dictionary Order = checked
  • Name: Sum_Returns | Order: Ascending

Note: Ascending puts the smallest numbers at the top (which are usually negative returns). If you strictly want the "highest volume of returns" and they are stored as negative values, ascending effectively surfaces the biggest return volumes.

Tool Reference Guide

Summarize

Summarize

Groups records and calculates sums, counts, means, etc.

Sort

Sort

Orders records (Ascending/Descending).

Sample

Sample

Extracts specific patterns of rows (First N, 1 in N, etc.).