Two lines would have saved me eleven days of believing a number that was about 40% too high. Both lines are a row count. One before the join, one after.
That is the whole technique. The rest of this is elaboration.
Joining is the operation that ruins more small business analyses than anything else, and it does the damage without a warning. Nothing throws an error. No file is corrupt. The output has the same columns as the input and every value in it is plausible. What changed is how many rows there are, and almost nobody looks at that.
I run mobile proxy lines on Singapore carrier SIMs and a rack of Android phones that people rent by the day. Nearly every question I have about that business needs two files stuck together. Usage against ownership. A supplier’s spreadsheet against my own hardware list. So I join things constantly, and I have broken things constantly.
The count that tells you in one second
Note how many rows the left table has. Do the join. Note how many rows came out.
A join cannot create information. It can only create copies. If the row count went up, some key on the right hand side appeared more than once, and every left row that matched it came back duplicated, with all its values duplicated too.
That last part is what makes this so quiet. Take a table of phone rentals, one row per rental, joined onto a device table to pick up the model name. If one device has two rows in the device table, its rentals double. Sum the rental fee afterwards and you have billed that phone twice in your own reporting.
Nothing about the result looks wrong. The fees are real, the dates are real, and the total is simply larger than it should be. Larger is the direction you were quietly hoping for, which is why it gets through.
Sometimes the count going up is correct. You joined invoices onto customers and now you have one row per invoice, on purpose. Good, but say so out loud, because the customer level columns now repeat and must never be summed again.
A fan out you predicted is a feature. A fan out you did not notice, followed by a total, is how you end up saying a confident wrong number to somebody.
Track three numbers, not one
Rows in. Rows out. Rows that found no partner.
The third one is the one people skip, and its behaviour depends on the join you picked. An inner join drops unmatched rows without mentioning it, so your table gets shorter and looks fine. A left join keeps them and fills the new columns with blanks, which is honest but only helps if you go and count the blanks.
Either way, that count tells you whether you have a data problem or a formatting problem before you waste an afternoon on the wrong one.
Keys fail on formatting far more often than on truth
If a join matches 60% of your rows, your first assumption should be that the keys are dirty. Not that 40% of your business has gone missing.
Whitespace is the most common cause. A trailing space is invisible on screen and absolute to the computer. “Singtel” and “Singtel ” will never match, and squinting at the two cells will never show you why.
Case is next. One system lowercases the email it stores, another keeps whatever the customer typed, the customer used a capital, and that is one person appearing as two.
Leading zeros are the cruel one, because a spreadsheet does it to you on purpose. An identifier of 00471 gets opened in Excel, Excel decides it is a number, and it is saved back as 471. The zeros are gone from the file, not just from the display, and reformatting the column will not bring them back.
There is a fourth cause that is not formatting at all: the key was never unique to begin with. The same human signs up twice with two email addresses and your payment processor is entirely correct to treat him as two customers.
The test for the first three costs a minute. Strip whitespace from both key columns, lowercase both, run the join again, compare the match rate. If it goes from 60% to 98%, you had a formatting problem and now you do not.
What you cannot do is accept the 60% and carry on. The rows that matched are not a random sample of your business. They skew toward the newer records, entered through the newer system, in the newer format, which is exactly the population that will make a trend look like something it is not.
The unmatched rows are where the information is
After a join I pull the rows that found no partner and read a couple of dozen of them. This takes five minutes and it is the highest yield five minutes in the whole process.
The matched rows confirm what you already assumed. The unmatched rows tell you something is wrong with your understanding of your own systems.
I once found a batch of about a dozen devices that never matched anything, because they had been added during a bulk import that wrote the serial number with a different prefix. Every one of them was sitting in the rack, powered on, earning money. In the joined output they did not exist.
Anybody reading only what matched would have concluded those units were idle. The truth was that the file was answering a question about part of the business while carrying the label of the whole thing, which is worse than a gap, because a gap is visible.
Many to many is the one to be afraid of
When the key repeats on both sides, the output is every combination. Four rows on the left sharing a key, three on the right, and you get twelve.
Twelve rows that never existed in either source.
This is how a total triples instead of doubling, and it is why a many to many result can be wildly wrong while nobody can point at the step that did it. The arithmetic afterwards is all correct. It is being performed on rows that were manufactured by the join.
I do not do them. When I find a key repeating on both sides I stop and reduce one side first, usually by grouping it to one row per key. Total the payments per customer, then join the totalled table onto the customer table. The join stays one to many and stays readable by a human six weeks later.
If you cannot state in one sentence how many rows each key has on each side, you are not ready to join yet.
A month is not a self evident thing
Time is a key too, and it fails in its own way.
Usage lands daily. Invoices land monthly. A supplier statement lands whenever the supplier feels like sending it. Joining a daily table to a monthly one means choosing what a month means, and the available answers give different numbers.
Calendar month. A rolling 30 day window. The billing cycle that starts on the day each customer signed up. My proxy lines bill from the signup date, so somebody who joined on the 20th has a cycle that ends on the 19th. Line that up against a calendar month usage export and the two will never agree, and neither of them is wrong.
Pick one, write it at the top of the script, use it everywhere. Picking a different one per analysis is why two of your own numbers disagree and you cannot work out which is broken.
Then there is the clock. My servers log in UTC and I am eight hours ahead of them. A day boundary in the export is not a day boundary in my life, so a join on a date built from raw timestamps puts eight hours of traffic in the wrong day, permanently and without complaint.
Do it in a script
Not by dragging a lookup formula down a column.
A dragged formula has no memory. When it stops two rows short of the bottom, nothing tells you. When somebody sorts the sheet afterwards, some values stay put while their references move. And in March, the only record of what you did in January is a sheet that has been edited fifteen times since.
A script can be run again. That is the entire argument. Delete the output, run it, get the same output back, with the row counts and the match rate printed every single time instead of checked once when you happened to be paying attention.
Twenty lines of Python does this, and so does a SQL query saved in a text file. The repeatability is the point.
The join I got wrong
I was working out which lines were worth keeping. A line costs me roughly $10 a month for the SIM and about $1.50 a month in modem depreciation, so whether a port pays for itself is a question I ask often.
I joined a daily usage export onto my port ownership table. Usage was one row per port per day. Ownership, I assumed, was one row per port.
It was not. That table kept history. When a port changed hands, the old row stayed and a new one was added with a start date I had never had a reason to look at. So every port that had ever been reassigned matched twice and its usage doubled. Around a fifth of my ports have been reassigned at some point, and they skew busy, because the busy ones are the ones I shuffle.
Total data through the rack came out about 40% high. I believed it for eleven days, used it to argue with myself about buying another powered hub at $150 for roughly 30 ports, and repeated the figure to a supplier while we were discussing capacity.
No check caught it. A customer did, when he asked why his line appeared twice on a summary I had sent him.
The fix was one condition: filter the ownership table to current rows before joining, or join on the port and the date range together. I did not write it because I never asked how many rows that table held per port, which is the question that would have prevented all of it and takes about four seconds to answer.
More plain English walkthroughs on the numbers a one person business runs on are at Data Research Analysis Collection.
Get new guides and videos first — join the Telegram channel.