Building a Monthly Sales Report from Raw Transactions

It's the first Monday of the month. Your manager wants a revenue breakdown by product category, month over month. You have an orders CSV with 40,000 rows of individual transactions. Each row is a single line item: order ID, date, product, category, region, quantity, unit price.

What your manager actually wants is a compact table: one row per month, categories across the top, revenue in the cells. Getting there means filtering out the junk rows, pulling the year and the month off the timestamp, working out revenue per line item, then aggregating and reshaping. Here's how to build that pipeline in ExploreMyData.

The raw data

Imagine a typical orders export. Columns include order_id, order_date, status, customer_id, product_name, category, region, quantity, and unit_price. Mixed in with the real orders are test orders (status = "test") and cancelled ones (status = "cancelled"). Those need to go before you do anything else.

Step 1: Filter out test and cancelled orders

Click the green + in the Pipeline panel and select Filter from the Filter & Sort group. Flip the Action toggle to "Remove matching rows". Add one condition: column status, operator "is", value test. Then click the Or button and add a second: status is cancelled. Apply.

That is the first card in your pipeline sidebar, and it shows you the SQL it built: SELECT * FROM "orders" WHERE NOT ("status" = 'test' OR "status" = 'cancelled'). The row count drops immediately. In a typical dataset you might lose 5-10% of rows here.

This step matters more than people think. One "test" order with a $99,999 amount will wreck your monthly averages if you leave it in.

Step 2: Extract the year and the month (two cards, not one)

Your report needs data grouped by month. The raw order_date column has full timestamps like "2026-02-14 09:23:17". Extract Date Part pulls out one part per step, so this is two passes through the same operation.

Click the green + in the Pipeline panel and select Extract Date Part from the Date group. Pick order_date as the column and set Date part to year. Leave the output name alone and you get a new column called order_date_year, because the default name is the source column plus the part.

Now add the operation a second time, same column, Date part month. That gives you order_date_month. Two cards in the sidebar, two new integer columns. Under the hood each one is a DuckDB EXTRACT(year FROM "order_date")::INTEGER or EXTRACT(month FROM "order_date")::INTEGER.

Keeping the month as a number matters later. Sort by a month-number column and the rows land in calendar order. Sort by a column of month names and you get April, August, December, February.

Step 3: Calculate revenue with Math

Here is the part people trip over. Pivot and Group & Aggregate can only aggregate a column that already exists in the table. Neither one accepts a typed expression, so there is no way to ask for SUM(quantity * unit_price) directly. You have to create the revenue column first.

Click the green + in the Pipeline panel and select Math from the Transform group. The panel is a single formula box. Type:

"quantity" * "unit_price"

Column names go in double quotes. Under "Apply results into", choose New Column and name it revenue. If you leave the name blank the column is called result, which nobody wants to see in a pivot. Apply, and every line item now carries its own revenue figure: 2 units at 149.99 becomes 299.98.

Step 4: Pivot revenue by category and month

This is where the shape changes. Click the green + in the Pipeline panel and select Pivot from the Aggregate group. The panel has three zones:

• Rows (group by): order_date_year and order_date_month
• Columns (pivot on): category
• Values: revenue, with the function set to SUM on the segmented control

The Values dropdown only lists numeric columns when SUM is selected, which is another reason the Math step had to come first. Click Apply and 40,000 rows collapse into one row per distinct year-month pair. This export covers January, February and March of 2026, so three groups, three rows. Each category value becomes its own column. The pipeline sidebar is now five cards deep.

Step 5: Add a percentage-of-total column

Your manager will inevitably ask "what percentage is Electronics?" So let's add that now. Click the green + in the Pipeline panel and select Add Column from the Columns group.

You need two of them. The first is named total_revenue, with the expression "Electronics" + "Apparel" + "Home & Garden" + "Sports", which adds the category columns across each row. The second is named electronics_pct, with the expression ROUND("Electronics" / "total_revenue" * 100, 1). Quote the column names, and mind the order: the second expression only works once the first card exists.

Each Add Column operation becomes its own pipeline card. The card shows you the exact SQL expression, and you can edit or delete either one without disturbing the rest.

Step 6: Pick the final columns and their order

The table still carries columns nobody asked for. Click the green + in the Pipeline panel and select Select Columns from the Columns group. Tick order_date_month, the four category columns, total_revenue and electronics_pct. Every export in this file is from 2026, so order_date_year can go. A "Column order" list appears under the picker with a numbered row per column and up/down arrows, so you can push the columns into the order your manager reads them in.

About ordering the rows: there is no Sort operation in the picker, and pipeline cards cannot be dragged into a different sequence. What you can do is click the order_date_month header in the grid, which sorts the view ascending. This is where the month number earns its keep: 1, 2, 3 sorts the way a human expects, "Feb" does not. Grid sorting is a display setting rather than a pipeline step, so it adds no card, and an export writes the rows in whatever order the query produced them.

The finished pipeline

Look at the pipeline sidebar. Eight cards, in this order:

1. Filter: drop test and cancelled orders
2. Extract Date Part: order_date_year
3. Extract Date Part: order_date_month
4. Math: revenue
5. Pivot: revenue by category, one row per month
6. Add Column: total_revenue
7. Add Column: electronics_pct
8. Select Columns: presentation order

The whole thing runs in your browser. No data was uploaded anywhere. Next month, load the fresh export and the same eight cards rebuild the report against the new rows.

If the categories change, say "Electronics" gets split into "Consumer Electronics" and "Enterprise Hardware", you edit the pivot card and the two Add Column expressions that name the category columns. Everything before the pivot is untouched.

Build your first report pipeline →