Your intuition is correct: regular languages can't count. Indeed, a quick way to prove that $L$ isn't regular is to observe that $L\cap \{a^mb^n\mid m,n\geq 0\} = L'\cup\{a,b\}$. Since the intersection of any two regular langauges is regular, and $\{a^mb^n\mid m,n\geq 0\}$ is certainly regular, it must be that $L$ is not – because if it was regular, then its intersection with $\{a^mb^n\mid m,n\geq 0\}$ would have to be regular, and it isn't.
To prove with the pumping lemma, you need to show that, for every $p>0$, there is a string $s$ of length at least $p$ such that every way of writing $s=xyz$ with $|y|\geq 1$ and $|xy|\leq p$ has $xy^nz\notin L$ for some $n\geq 0$. (I originally wrote "just need to show" but deleted "just" because the statement that follows is such a mouthful!)
Your string $s$ works just fine. If we rewrite it in the required form $s=xyz$, the fact that $|xy|\leq p$ means that $x=a^k$, $y=a^\ell$, with $1\leq\ell\leq p$ and $0\leq k\leq p-\ell$. And $z$ is the rest of the string. But, now, for any $n\neq 1$, we have
\begin{align*}
xy^nz &= a^ka^{n\ell}a^{p-k-\ell}(aa)^pb^p(bb)^p\\
&= a^{3p+(n-1)\ell}b^{3p}\,,
\end{align*}
and this string is not in $L$, since the number of $a$s at the front is different from the number of $b$s at the back, so the number of $aa$ substrings is different from the number of $bb$s.
Insofar as that proof works, there's no problem with your choice of $s$. But, if you read through the proof, you'll see that there would have been a bit less writing if you'd just chosen $a^pb^p$. The point is that, when something in the $a^p$ part gets "pumped", it breaks the required property of having the same number of $aa$s as $bb$s.
It's hard to give general advice, because proof is a creative act and you can't just follow recipes. The general scheme is to pick a string where repeating any sequence of characters from the first $p$ will break the property that defines the language.
