Intuition behind Snake Lemma
All these diagram chasing lemmas (snake lemma, $3x3$ lemma, four lemma, five lemma, etc.) follow directly from the "salamander lemma" due to George Bergman, see salamander lemma.
And that is pretty transparent. It is so transparent that for instance it is immediate to see (which no textbook ever mentions) that there are just as easily 4x4 lemmas, 5x5 lemmas, 6x6 lemmas. etc. In other words: once you see the simple idea of the salamander lemma, you can come up yourself with more such diagram chasing lemmas at will.
A special case is easy to see: Imagine $A \leq A' \leq B=B'$, and $C=B/A$ and $C'=B'/A'$ with the obvious maps from a module $M$ to $M'$. The kernels are $0$, $0$, and $A'/A$. The cokernels are $A'/A$, $0$, $0$. The last kernel and the first cokernel are linked.
The snake lemma is then just one of the isomorphism theorems. As you deform $B$ and $B'$ more, how do the kernels and cokernels deform? The last kernel and first cokernel no longer need be isomorphic, but the kernel and cokernel of that linking (snakey) map can be described in terms of the kernels and cokernels already there. A specific relation is the snake lemma.
Example deformation
An example deformation might be helpful: distort the $A' \to B' \to C'$ sequence by quotienting out by some $M \leq B$ (imagine $M=IB$ for some ideal $I$, so we are tensoring the second line with $R/I$). How does this change the kernels and cokernels?
The first line is $$0 \to A \to B \to B/A \to 0,$$ and the second line becomes $$ 0 \to (A'+M)/M \to B'/M \to B'/(A'+M) \to 0$$ so the kernels are $A \cap M$, $M$, and $(A'+M)/A$ and the cokernels become $(A'+M)/(A+M)$, $0$, and $0$. The last kernel and the first cokernel are related, but not equal. One clearly has the relation $0 \to (A+M)/A \to (A'+M)/A \to (A'+M)/(A+M) \to 0$ where the last two nonzero terms are the last kernel and the first cokernel. The first term is weird though. We apply another isomorphism theorem to write $(A+M)/A \cong M/(A\cap M)$ and then the solution is clear: We already have $0 \to A \cap M \to M \to M/(M\cap A) \to 0$ so we splice these two together to get the snake lemma: $$ 0 \to A \cap M \to M \to (A'+M)/A \to (A'+M)/(A+M) \to 0 \to 0 \to 0$$
I think the best motivation for the Snake Lemma has to do with the Long Exact Sequence in (Co)homology. It can also be thought of as intuition, since the long exact sequence is there to repair the non-exactness of a left-exact (or right-exact) functor, and the way to define the connecting homomorphism is through the Snake lemma.