That's not only true for rotation matrices. The columns of any matrix are where the unit vectors in the original coordinate system are mapped to when multiplied by the matrix.
Ket's are vectors and written like |u>. Bra's are objects that compute dot products with vectors. They are written like <u|. So <u| |v> = u · v.
However if you have |u> <v| then nothing happens. This is a new object. It will first take dot product with v and then multiply with |u> vector.
Letting e1, e2.. denote the coordinate basis vectors, we can write a matrix A with coefficients a_ij as the sum Σa_ij |ej> <ei|.
This can be rearranged as
Σ_i |Σ_j a_ij ej> <ei|
Written like this, it is clear that the column is where the unit vector ends up.
I remember this poor teaching fellow where I went to Lehigh University where I studied Mechanical Engineering. He was foreign and had the honor of teaching us Linear Algebra. He must have noticed that none of us had even the slightest damn clue what he was talking about. So, the class before every exam, he would literally just give go over the exam problems nearly verbatim and solve them. I came out of that class with a good grade and not the slightest fucking understanding of Linear Algebra beyond the very superficial algebraic laws of multiplying tensor objects (i.e., scalars (0-D tensor), vectors (1-D tensor), and matrices).
I'd like to thank Grant Sanderson (3Blue1Brown) and Mike Cohen (Udemy Linear Algebra course using Matlab and Python) for teaching me linear algebra. And don't forget Gilbert Strang, of course!! (find his course on MIT OCW) The visualizations of the tensors transforming provided by the first two are what really made Linear Algebra start to click for me. I want to go back to Gilbert Strang's course now that I have a better geometric understanding of what is happening and appreciate all his wisdom on the subject.
Also, learning it as part of a numerical computing stack (like python) where you can experiment and visualize is huge for building intuition.
Lots of how, not enough why