What is the idea behind stationary sets?
Some intuition might be given by the following informal analogy with measure theory: if we have a measure space of measure 1, then club sets are analogous to subsets of measure 1, while stationary sets are analogous to sets of positive measure.
In other words, club sets contain "almost all" ordinals, while stationary sets contain "a positive proportion" of them.
One answer to your question about intuition is simply that stationary sets arise very naturally once you begin to think of the natural measure surrounding club sets. The stationary sets are simply those that have positive outer measure with respect to the club filter. So if you care about club sets being large, then the concept of stationary sets arises naturally.
More specifically, if you consider the collection of sets that either contain or omit a club, then you have a natural two-valued measure (measure 1 means containing a club, measure 0 means omitting a club). The stationary sets are precisely the sets that do not have measure 0, and this is the same as having outer measure 1.
Many uses of the club sets rely on the fact that they can be thought of as the large subsets of $\kappa$, in these sense that they have measure 1 with respect to this measure. However, many of these uses generalize from club to stationary because it is sufficient for the application that the set is larger merely in the sense that it does not have measure $0$, rather than actually have measure $1$.
By the way, I'm not so sure I share your intuition that the club sets are those that "contain big enough ordinals". On the one hand, if $C$ is the set of limit ordinals below $\kappa$, then it is club, but if I add one (or any fixed non-zero ordinal) to every ordinal in $C$, making $D=\{\lambda+1\ |\ \lambda\in C\}$, then it would seem to have just as big ordinals or bigger, but $D$ is not club.
For purposes of intuition, I've found the following reformulation of the definition useful. A subset $S$ of $\kappa$ is stationary if and only if, for any countably many finitary operations on $\kappa$, say $f_n:\kappa^{r_n}\to\kappa$, there is an $\alpha\in S$ closed under all the $f_n$'s. In the terminology of universal algebra, this says that every algebra with underlying set $\kappa$ and with countable type has a subalgebra that is a member of $S$. By using Skolem functions, one can also rephrase the definition in model-theoretic terms: Any structure with universe $\kappa$ for a countable language has an elementary submodel whose universe is a member of $S$. (That bring us into the general neighborhood of the somewhat deeper comments in Andres Caicedo's answer.)