Motivation can be broken up into essentially two parts: (i) Stable homotopy groups of things appear in nature, and (ii) the stable category is technically convenient. I'll try to give some indication of these below, but none of the bullet points do the topic justice... There's lots of love to be had here.
Cobordism. Suppose you're studying manifolds. The first question you might ask is: can I find a list of all manifolds up to diffeomorphism? The answer is no. So you weaken to 'homeomorphism', and the answer is still no. So you weaken to 'homotopy' and the answer is still pretty much no (though you're getting better), so finally you demand a really weak notion: cobordism. Two compact $n$-manifolds are cobordant if their disjoint union is the boundary of some $(n+1)$-manifold (google this to see a picture of a pair of pants, or just look at your pants.) Another way of thinking about it (if you're into deformations), is that a cobordism is a smooth family or 'deformation' of manifolds (fibered over $\mathbb{R}$), and so we might want to know what the various deformations of a given manifold are, in this sense. Anyway, it turns out that the answer to this question (and more structured versions of this question, where you might require the manifolds to be oriented, or have a Spin structure on their stable normal bundle, or be almost complex, or stably framed, etc.) is a certain stable homotopy group. That is, there is a sequence of spaces, $MO(n)$, and maps $\Sigma MO(n) \rightarrow MO(n+1)$ such that the $k$th stable homotopy groups of this spectrum are isomorphic to the cobordism group of $k$-manifolds (under disjoint union). If you require your manifolds to be stably framed you get the stable homotopy groups of spheres! I find this incredible... this was done by Pontryagin (stably framed case), and in general by Thom in the greatest thesis of all time.
Hopf Invariant Hopf famously discovered an essential (i.e. non-nullhomotopic) map $S^3 \rightarrow S^2$, which you can think of as the being defined via $S^3 \rightarrow \mathbb{C}P^1$ and then choosing an identification of $\mathbb{C}P^1$ with the sphere. The mapping cone of this map is a CW-complex and asking whether or not the map $S^3 \rightarrow S^2$ is non-nullhomotopic is equivalent to asking whether or not this mapping cone is homotopy equivalent to a wedge $S^4 \vee S^2$. We can see that it is not since the mapping cone is actually just $\mathbb{C}P^2$ and the cup square of the generator in $H^2$ is the generator in $H^4$. More generally, given a map $S^{2n-1} \rightarrow S^n$ we can form the 2-cell complex given by gluing $D^{2n}$ along this map and ask if the cup square of the generator in degree $H^n$ squares to the generator in degree $2n$. If it does we say this map has Hopf invariant one, otherwise it has Hopf invariant 0. It's natural to ask how many maps of Hopf invariant one can we build, and the answer is: not many. They only exist when $n = 1, 2, 4,$ or $8$. While nowadays we usually learn the proof of this fact using $K$-theory (which is very short), the first proof was inspired very much by the observation that this question is equivalent to asking whether certain elements in a spectral sequencing converging to the stable homotopy groups of spheres (the Adams spectral sequence) are permanent cycles. (To see why the question is stable in the first place, one might note that the Hopf invariant, while defined using the cup square, could have been defined using Steenrod operations, and then it would be a suspension invariant, hence stable, question- so one can attack it via stable homotopy theory.)
Surgery Theory/ Kervaire Invariant. In 1956, Milnor found a curious example of a manifold. He was studying sphere bundles over spheres, and found that there was a bundle of the form $S^3 \rightarrow X \rightarrow S^4$, and that $X$ is homeomorphic to $S^7$, but it is not diffeomorphic to $S^7$. In other words, there exist exotic smooth structures on manifolds. Since the simplest compact manifolds are spheres, it makes sense to study this phenomenon for all spheres, and Milnor and Kervaire did just that. They related the group of homotopy spheres (read: h-cobordism spheres, read: topological spheres by the Poincare conjecture) to the stable homotopy groups of spheres and used this to reduce the problem of classifying exotic structures on spheres to a single problem: When does there exist a manifold of Kervaire invariant one? (The Kervaire invariant is an invariant of a certain quadratic form defined on the middle homology of a framed manifold.) This is kind of vague, but the punchline is that this problem was eventually resolved (up to one case in dimension 126) and the two biggest steps (including the final step) used heavy, heavy stable homotopical machinery. For example, Browder reduced the problem to deciding whether certain elements in the Adams spectral sequence were permanent cycles (this looks familiar...), and this was the method of attack in the Hill-Hopkins-Ravenel approach to resolve all cases except dimension 126.
Cohomology Theories You're probably familiar with ordinary cohomology, and maybe even $K$-theory, but the fact is that there are lots and lots of interesting cohomology theories out there, and the most convenient way to talk about them is to talk about spectra. The basic result is Brown representability. If I have some graded homotopy invariant of spaces that takes cofiber sequences to long exact sequences and has a suspension isomorphism and takes wedges to products, then this is represented by a sequence of spaces $\{E_n\}$ with weak equivalences $E_n \cong \Omega E_{n+1}$ coming from the existence of the suspension isomorphism and the Yoneda lemma. Conversely, given a sequence of spaces $\{E_n\}$ with maps $\Sigma E_n \rightarrow E_{n+1}$, you can cook up a cohomology theory. This means that one can do some pretty formal manipulations inside the category of spectra and produce lots of different cohomology theories, even ones that have no geometric interpretation a priori. These cohomology theories, however, might be very computable and help solve problems or detect nontriviality of certain maps. For example, there is a cohomology theory called tmf which has, as of now, no geometric interpretation, but can detect many nontrivial maps between spheres, and can even be used to prove results in number theory! I should also point out that while the correspondence 'cohomology theory <---> spectrum' is not sharp, it doesn't take much to make it sharp: if by '(co)homology theory' we really mean an invariant on finite CW-complexes that spits out a space and this spitting out of a space takes a point to a point and cofiber sequences to fiber sequences, then the correspondence is an equivalence (of homotopy theories... let's not get into that.)
Cohomology Operations By the previous bullet point, we see that self maps $E \rightarrow E$ of a spectrum give natural transformations of the corresponding cohomology theory. Any map of spaces has to preserve this transformation (by definition of natural), and so we can cook up cohomology operations by cooking up maps of spectra. There's even a machine (you've met it in previous bullet points, it's called the Adams spectral sequence) which computes all maps between spectra, and you can use it to figure out what the cohomology operations for a given theory look like.
Infinite Loop Spaces. When a space has the structure of a group we get extra structure on its homology groups, namely a product structure (called the Pontryagin product). In fact, we only need a weak notion of group: an 'H-space' will do, i.e. a group up to homotopy. The more we ask of this multiplication, the more structure we get on the product (I won't get into how this is done, but it's pretty neat). It turns out we get homology operations if our space is associative 'up to coherent homotopy.' You might ask what this means, and it turns out that it's equivalent to being the loopspace of some other space. Being associative and commutative 'up to coherent homotopy' buys you something more: you're an infinite loop space! That is, given such a space $X$, there is a sequence of spaces $\{X_n\}$ such that $X \cong \Omega^nX_n$, in a compatible way. In formulating what you might mean by 'a map which respects all the multiplication, up to coherent homotopy' you'll probably just write down what it means to have a map between spectra. So studying infinite loop spaces, and constructing operations on them, etc. involves a lot of studying spectra.
Spanier-Whitehead Duality (This is one of the motivations given in Adams' book, which you should read... modulo the part on smash products.) Given a finite $CW$-complex, $X$, I can embed it into a large sphere $S^n$ in a nice way so that the complement deformation retracts onto a finite $CW$-complex, $Y$. This is called the 'Spanier-Whitehead dual' of $X$. The thing is, $Y$ is not determined up to homotopy by $X$. However, the stable homotopy type of $Y$ is determined by $X$, independent of the choice of embedding or sphere (this basically follows from Alexander duality). So if we want to make arguments exploiting duality, it's best to work in the stable category.
Kan-Priddy Map This might not be so useful now, but eventually you'll read the general definition of a map of CW-spectra in the stable category. It's confusing and the word 'cofinal' is used, and people tell you 'cells now- maps later!' and all that jazz. Here's an example I learned from Paul Goerss justifying why one might want to define maps in this crazy way. There is a map in the stable category called the 'Kan-Priddy map', defined as follows. We have a map $\mathbb{R}P^{n-1}_+ \rightarrow O(n)$ given by sending a line to the reflection it defines. We also have a map $O(n) \rightarrow \Omega^nS^n$ given by sending each map to the corresponding symmetry of the sphere, combining gives a map $\mathbb{R}P^{n-1}_+ \rightarrow \Omega^nS^n$, and hence a map $\Sigma^n\mathbb{R}P^{n-1}_+ \rightarrow S^n$ and putting these together for all $n$ and letting $n \rightarrow \infty$ we get a map of spectra $\Sigma^{\infty}\mathbb{R}P^{\infty}_+ \rightarrow S$. This map has the property that the obvious restriction on the source to $\Sigma^n \mathbb{R}P^{\infty}_+$ is nullhomotopic for every $n$, so the map is only defined stably.
Categorical Constructions This one is kinda silly but: being in an additive category is pretty nice. We can add maps, subtract them, finite wedges and products agree... We can think about things as if we're in an abelian category and apply that intuition, and it's really helpful! We can also construct cohomology theories that measure differences between things by taking fibers of maps. For example, there is a cohomology theory called $J$ which, morally, measures the difference between what the homotopy groups of spheres can see and what $K$-theory can see. When I want to construct spectral sequences (which I do), I can do so using really general categorical machinery that doesn't quite work at the level of spaces.
Fiber and Cofiber sequences Agree There is a theorem of Blakers and Massey, sometimes called 'homotopy excision' which says: even though taking homotopy groups does not take cofiber sequences to long exact sequences, it does do so in a certain range depending on the connectivity of the cofiber sequence. In particular, if we keep suspending the cofiber sequences (in which case we're asking about stable homotopy groups) then this is the case. This turns out to be enormously useful (think about every time you make an argument in homology using the long exact sequence of a pair... you can now make that argument with stable homotopy groups, or any other nice spectrum, etc.)
The Stable Category ties together Algebra and Topology Rings, chain complexes over any ring, or differential-graded ring, etc. all live inside the stable homotopy category (they appear as $HR$, Eilenberg-MacLane spectra, and modules over them). You can make constructions like Tor and Ext entirely within the stable category (these correspond to smash products and homotopy classes of maps), and you can generalize these to various ring-y types of spectra as well. The story of localizing a ring or inverting elements also carries over to cohomology theories, and is best played out inside the stable category- this allows one to do homotopy theory and study spaces 'one prime at a time,' which is technically very convenient (working modulo a prime is often much easier than proving things over the integers.)
Anyway, I've certainly left lots and lots out... but this should get you started.
Best Answer
There is not any simple characterization of when a spectrum is equivalent to a suspension spectrum. In particular, it does not suffice for the spectrum to be built out of cells of nonnegative dimension. The issue is that the attaching maps for those cells only need to exist stably (i.e., after suspending enough times), and so may not exist as unstable maps in their actual dimension.
For instance, consider the space $\mathbb{RP}^2$. It is built by attaching a $2$-cell to $S^1$ along a map $S^1\to S^1$ of degree $2$. In particular, it has only a $1$-cell and a $2$-cell (and a basepoint $0$-cell). So if we take the suspension spectrum $\Sigma^\infty\mathbb{RP}^2$ and desuspend it once, we get a spectrum $\Sigma^{\infty-1}\mathbb{RP}^2$ which has only a $0$-cell and a $1$-cell. However, there is no corresponding space, since the degree $2$ attaching map $S^1\to S^1$ cannot be desuspended to a map $S^0\to S^0$. To prove definitively that $\Sigma^{\infty-1}\mathbb{RP}^2$ cannot be equivalent to the suspension spectrum of any space, you can observe that $H_0(\Sigma^{\infty-1}\mathbb{RP}^2)\cong\mathbb{Z}/2$ but $H_0$ of a space can never have torsion.