More Analysis Moves for CSV Data You Can Use Today

You have profiled the file. It parses, the columns have types, the row count matches and nothing looks broken. Now someone asks what it says: how do the statuses split by country, did revenue grow, who are the biggest customers, is there a weekly pattern. These questions come up again and again with CSV data, and a small set of moves answers most of them. This guide collects nine, each run as SQL against a real file of 2 million orders, with the output and the trap that goes with it. The aim is a habit of asking, checking and reporting that works the same whether the file has two thousand rows or two hundred million. For the step before this, doing real data analysis on a CSV and profiling a new CSV file cover the first look, and summary statistics for a CSV column in SQL the basics.

Start with the grain and the denominator

Before any number, say what one row is. In the orders file, one row is one order: order_id, customer_id, zip, amount, created_at, status, country and note. That is the grain. A count of rows is a count of orders, a count of distinct customer_id is a count of customers, and an average of amount is an average per order. Each of those is a different question, and most wrong answers in analysis are one of them presented as another.

The file has 2,000,000 orders from 249,916 distinct customers, so the average customer placed about eight orders. Revenue per order is 80.09 and revenue per customer is about 640, and either can be right depending on who asked. Write the denominator next to every rate: "5.7 percent of orders failed in Brazil" and "5.7 percent of failed orders were in Brazil" are different sentences, and the second is usually the one a reader assumes. Percentages of a total, of a row and of a column are three tables, and the pivot below shows how easy it is to print the wrong one.

Check the totals too. Whatever you compute, the pieces must sum to the whole: the revenue by month adds up to the revenue overall, the statuses add up to the order count. A query that groups by a column with NULLs drops or separates them, and the total shifts without any error. A one-line check, count(*) against the sum of the group counts, takes a second and catches it.

Move 1: cross tabs

A cross tab counts rows by two categories at once, and it is the quickest way to see whether two columns are related. In DuckDB the PIVOT statement does it directly; the 200,000-row sample, with status down the side and country across the top:

PIVOT (SELECT status, country FROM read_csv('orders-200k.csv'))
ON country USING count(*) GROUP BY status ORDER BY status;

status     BR    DE    FR    GB    IN    JP    US
failed   5804  5594  5734  5700  5676  5697  5721
paid     5771  5575  5729  5688  5853  5736  5680
pending  5788  5771  5683  5726  5651  5698  5660
refunded 5586  5683  5817  5693  5814  5708  5687
shipped  5689  5797  5820  5729  5811  5516  5715

Every cell is near 5,700, which is what you get when status does not depend on country. A chi-square test of independence makes that a number: chi-square 26.9 on 24 degrees of freedom, p = 0.31, which means a table this uneven would turn up about three times in ten by chance alone, so the data gives no reason to say status and country are related. A low p-value, say under 0.01, would mean the pattern is hard to explain by chance and would be worth a look. Note the discipline: a null result is a finding, and "no relationship" is a legitimate thing to report. Cross tabs from a CSV with SQL covers percentages, totals with GROUPING SETS and the conditional-aggregation form that works in any database.

Move 2: compare periods per day

Comparing this month with last month is the most requested analysis and the one most often done wrong. Revenue by month for the first four months of the test file:

month    revenue     days  mom_pct  per_day
2025-01  13610354.0  31    NaN      439044.0
2025-02  12356780.0  28    -9.2     441314.0
2025-03  13608069.0  31    10.1     438970.0
2025-04  13136102.0  30    -3.5     437870.0

February is 9.2 percent below January, and a person looking at that number would ask what went wrong. Nothing did: February has 28 days, and revenue per day was 441,314, half a percent higher than January's 439,044. The fix is to compare like with like: revenue per day, or the same number of days, or the same weekdays when the business has a weekly rhythm. Quarter by quarter, the same file gave orders of 494,145, 498,993, 503,031 and 503,831 and average order values of 80.09, 80.03, 80.02 and 80.21, which is a business that is flat. Comparing two time periods in a CSV goes through windows, partial periods and year on year.

Move 3: concentration

Who are the biggest customers, and how much of the total do they account for? Rank by revenue and accumulate the share. In the test file, the top customer, id 11346, spent 2,328.48 over 17 orders, and the top five were all between 2,100 and 2,330. Across all 249,916 customers, the top 10 percent accounted for 18.5 percent of revenue and the top 1 percent for 2.4 percent. That is nearly proportional, which is what random data looks like. Real customer data is usually far more concentrated: it is common for the top 10 percent to bring half the revenue or more, and the shape of the curve is the finding, so always print the cumulative share and not only the top ten. Finding the top customers by revenue in a CSV shows the window functions and how to handle ties.

Move 4: seasonality

Is there a weekly or monthly rhythm? Average the daily revenue by weekday and express each as an index on the overall average. In the real test file, the seven weekdays came out between 99.7 and 100.2, flat, since the file was generated without a rhythm. To see what a real pattern looks like, I generated a daily series with a weekly cycle and ran the same query: Sunday indexed at 69.9, Saturday at 81.9 and Friday at 117.1. The stronger test is autocorrelation, the correlation of the series with itself shifted by some days. On the synthetic series, lag 7 gave 0.93 and lag 14 gave 0.92, strong, while lag 1 was 0.33; on the real file, lag 7 gave -0.02, which is nothing. A weekly cycle shows as a high value at lags of 7, 14 and 21, and its absence as values near zero. Detecting seasonality in a CSV of daily sales walks through it.

Move 5: look at the shape of a column

A mean is a summary of a distribution, and a poor one when the distribution is skewed. Order amounts in the file had a mean of 80.09 and a median of 67.25, with a standard deviation of 56.60 and a skew of 1.41. The mean is 19 percent above the median, which is the signature of a long right tail. Percentiles describe the typical order better: 25 percent of orders were below 38.50, half below 67.25, 75 percent below 107.90, and 99 percent below 265.91. Tests of normality agree with the eye: the Shapiro-Wilk statistic on a sample of 5,000 amounts was 0.896 with a p-value near 10 to the minus 50, whereas a sample drawn from a true normal distribution gave 0.9997 and p = 0.65. If your next step assumes a bell curve (a t-test, a control chart with three-sigma limits), check first, and consider the log or a different method. Checking if a CSV column is normally distributed has the tests and their traps.

Move 6: sample before you scale

A sample answers a question quickly if you take it correctly. The true mean amount in the 2 million rows was 80.09. Five random samples of 1,000 rows gave 77.58 to 82.09, a spread of 4.50. Five samples of 10,000 gave 79.89 to 80.83, a spread of 0.94, and five of 100,000 gave 79.88 to 80.18, a spread of 0.30. The spread shrinks with the square root of the sample size, so a hundredfold larger sample is ten times tighter. The first 1,000 rows of the file gave 80.85, an error of 0.76, and for a file ordered by time or by customer the top of the file can be badly unrepresentative. Sample at random, use a seed so the result is repeatable and report the sample size. Sampling a CSV for a fast first look covers the methods and when head is fine.

Move 7: draw it in the terminal

You do not need a plotting library to see a shape. DuckDB's bar() function draws a bar from a number, and a query becomes a chart:

month    millions  chart
2025-01  13.61     ██████████████████████████
2025-02  12.36     █████████████▌
2025-03  13.61     ██████████████████████████
2025-04  13.14     █████████████████████▎

(The February bar is short for the reason above, and a good reminder to chart per day when months differ.) For anything you will share, a proper chart from matplotlib is better, and a bar chart with a zero baseline is almost always the right one. Quick charts from a CSV with Python shows both.

Move 8: reconcile two sources

When two files should agree, a bank statement and a ledger, an export and the database, the analysis is a matching problem: which rows pair up, what is left on each side and does the leftover explain the difference in totals? On two small files, a bank statement summing to 1,603.10 and a ledger summing to 1,735.10, a join on amount within three days matched five pairs. The remaining two bank rows (a fee of 12.00 and a refund of 45.00) and one ledger row (75.00) explained the difference of 132.00 exactly: 75.00 plus the 57.00 of bank-only rows. A reconciliation is finished when the unmatched amounts add up to the gap. Reconciling a bank statement CSV with your ledger has the query and the trap with duplicate amounts.

Habits that keep the moves honest

Four habits matter more than any of the queries. Keep every query in a file, not in a terminal history, so the result can be reproduced and checked by someone else. Print the count behind every percentage: 50 percent of 6 rows and 50 percent of 6 million rows are different claims. Round at the end: do the arithmetic on exact values and format the result, since rounding each month and then summing drifts. And read the file's gaps: look at the earliest and latest date, the number of days with no rows and the count of NULLs in the columns you used, because a hole in the data is a pattern you will otherwise report as a trend. The test file's timestamps ran from 2025-01-01 00:00:04 to 2025-12-31 23:59:38, which is a full year, and that is the first thing to confirm before any year-level statement.

Rates, ratios and the paradox that catches everyone once

A rate is a ratio, and ratios from groups do not average the way people expect. The standard demonstration needs only four numbers per group, and I ran it in DuckDB so the arithmetic is checked. Two groups apply to two departments, one easy to get into and one hard:

grp    dept  applied  admitted  rate
men    easy  800      480       60.0
women  easy  100      70        70.0
men    hard  200      40        20.0
women  hard  900      200       22.2

Within each department, women are admitted at a higher rate: 70.0 against 60.0, and 22.2 against 20.0. Add the departments together and the picture reverses:

grp    applied  admitted  rate
men    1000     520       52.0
women  1000     270       27.0

Overall, men were admitted at 52.0 percent and women at 27.0 percent. Neither table is wrong. The women applied mostly to the hard department and the men mostly to the easy one, so the overall rate mixes two different mixes of difficulty. This is Simpson's paradox, and it appears in business data whenever a group composition differs: a conversion rate that drops overall while rising in every channel, because traffic shifted toward the low-converting channel. The rule it teaches is to break any rate down by the variable that could be driving the mix before you report the overall number, and to say which of the two views answers the question being asked. If the question is "is any department unfair", the within-department view is the answer. If it is "what share of applicants get in", the overall one is.

A related error is averaging averages. The mean of five monthly average order values is not the average order value over the year unless every month had the same number of orders. Compute the ratio of sums, total revenue over total orders, not the mean of the ratios. In SQL that is sum(amount) / count(*) and not avg(monthly_avg).

Window functions you will keep reusing

Most of the queries above use the same four or five window patterns, and learning them once pays across every file. A running total is sum(x) OVER (ORDER BY day), which adds up everything up to the current row and gives the cumulative revenue line. A share of total is x / sum(x) OVER (), an empty window that spans every row. A change from the previous row is x - lag(x) OVER (ORDER BY day), with lag(x, 7) for the same weekday last week, which is how the month-on-month and autocorrelation numbers were computed. A rank within a group is row_number() OVER (PARTITION BY country ORDER BY revenue DESC), and filtering on it, in DuckDB with a QUALIFY clause, gives the top three customers per country in one statement. A moving average is avg(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), seven days ending today, and the frame clause is what decides whether the first six rows are averaged over fewer days or dropped.

Two cautions. The default frame for a window with an ORDER BY runs from the start of the partition to the current row, and rows with equal sort values are treated as peers and included together, which makes running totals jump in steps when dates repeat. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when you want one row at a time. And a window over an unsorted result is not ordered by the outer ORDER BY: the sort inside OVER and the sort of the final output are separate, so state both. Computing a running total over a CSV and calculating a moving average over a CSV take each in turn.

Dates, time zones and the edges of a period

Almost every comparison here cuts the data by time, and most of the quiet errors come from the edges. A month is a half-open interval, from the first instant of the month up to but not including the first instant of the next, so write created_at >= '2025-02-01' AND created_at < '2025-03-01' and never BETWEEN '2025-02-01' AND '2025-02-28', which drops everything after midnight on the 28th. Truncating with date_trunc('month', created_at) does the same thing without the arithmetic.

Time zones decide which day a late-evening order belongs to. The timestamps in the test file carry no zone, and the first question for any real file is what zone they are in: a business in Sydney whose database stores UTC puts its Monday morning in Sunday's bucket. Convert before grouping, with timezone('Australia/Sydney', created_at) on a true timestamp, and say in the report which zone defines a day (handling time zones in CSV timestamps). Partial periods are the last trap: the current month is incomplete, and comparing it with a full previous month shows a fall that is really a calendar. Either exclude the open period or compare it with the same number of days.

Five checks before you trust a number

Run these before you send any result. The pieces add up to the whole, which catches NULLs and filters that dropped rows. The row count of the final query matches what you expected from the grain, which catches a join that multiplied rows: a join to a table with several rows per key makes the revenue total larger, and the symptom is a total that no longer matches the file. The earliest and latest dates fit the period you are describing. A second tool or a hand calculation on a tiny subset agrees: take five rows, add them up with a pencil and compare. And the answer survives a change of definition: if "customer" means email and not ID, does the conclusion hold? The ones that fail these checks are far more often wrong data handling than interesting results.

Writing the result so it can be used

A number without its context is a rumor. State the file and the date it was exported, the filter (all orders, or only completed ones), the grain, the denominator and the n for every percentage. Show the query beside the result, or link to it, so a reader can run it. If you rounded, say so, and if you excluded rows, say how many and why. Prefer a small table to a paragraph of numbers and a sentence of interpretation to a chart with no caption. When a result is a null, say so plainly: "no difference in status by country (p = 0.31, n = 200,000)" is a complete finding, and it saves the next person from rerunning the analysis. If a result will be reused, save the cleaned data as Parquet next to the query so the next run does not start from the raw CSV (why Parquet beats CSV for repeated analysis).

A definition changes the answer more than a method does

The most consequential choice in the whole analysis is often the one made before any SQL: what counts. Take the plain question "what was our revenue in 2025?" on the test file. Summing amount over every row gives 160,174,646.36. But the file includes orders with every status, and the split is almost even:

status    orders  amount        pct_of_all
failed    399533  32017945.09   19.99
paid      400113  31990982.76   19.97
pending   400329  32077864.50   20.03
refunded  400135  32069815.84   20.02
shipped   399890  32018038.17   19.99

Each status holds about a fifth of the money. If revenue means orders that were paid or shipped, the figure is 64,009,020.93, which is 40.0 percent of the total. If it means everything except failed orders, it is 128,156,701.27. If refunds are subtracted, it is lower again. The three answers differ by a factor of two, and every one is a defensible reading of the question. Nothing in the file says which is right; that is a business definition, and the analyst's job is to ask for it, write it down in the report and apply it the same way every time.

The practical habit is to put the definition in one place. A view, a CTE named counted_orders or a column in the cleaned file carries the filter, and every other query reads from it, so that "revenue" in the dashboard and "revenue" in the finance report cannot drift apart. When two people report different numbers for the same thing, the cause is nearly always a definition, not arithmetic, and it is quicker to compare the filters than the queries. Write the filter in plain words next to the number: "revenue, paid and shipped orders, 2025, in the order currency, before tax".

The same applies to other words in the questions you will get. "Active customer" needs a window, "new customer" needs a first-order date and a rule for people who ordered before the file begins, and "conversion" needs a numerator and a denominator that both exist in the data. A file of orders cannot say how many visitors did not order, and a conversion rate computed over customers who ordered is 100 percent by construction. Check that the thing you are asked to measure exists in the columns you have, before measuring it.

When these moves mislead

Every move here describes the file, and none of them says why. A cross tab shows two columns move together, and it cannot say that one causes the other. A period comparison shows a change and not its cause, and a week with a holiday, a promotion or an outage inside it looks like a trend. A sample from a file that is itself a biased slice (only completed orders, only customers who logged in) estimates that slice, not the population you care about. Concentration depends on how you define a customer, and if the same person appears under two IDs, the top-customer table is wrong in a way no query shows.

Be careful with small groups too. A rate computed on 12 rows moves a lot with one row, and a ranking of 50 regions by a rate will put the small regions at both ends by chance alone. Report counts, show intervals where you can and do not rank groups with a few rows. When the question is a decision with money behind it, the analysis here is a starting point for a proper study: a designed comparison, a model that accounts for the other variables or a person who knows how the data was collected. The tools for running these moves are surveyed in CSV tools worth knowing, the SQL used throughout is described in DuckDB's documentation, and the statistical background for the distribution and independence tests is in the NIST/SEMATECH e-Handbook of Statistical Methods.