So I actually already know the answer to my question. If $\mathcal{C}$ is an $\infty$-category, write $\operatorname{Fun}^{L}(\mathcal{S},\mathcal{C})$ for the $\infty$-category of colimit-preserving functors from the $\infty$-category $\mathcal{S}$ of spaces to $\mathcal{C}$. Then Theorem 5.1.5.6 of Lurie's Higher Topos Theory applied to the case where the simplicial set is trivial shows us that this functor category is equivalent to $\mathcal{C}$ itself.
There is also a 'proof', but it builds upon 300 pages of utterly abstract and, to me, unenlightening theory. My question, therefore, is whether anyone has a conceptual and intuitive explanation as to why this theorem should be true. Ideally, if I pick a particular object $C$ in $\mathcal{C}$, and a particular space $X$, I would like a recipe that tells me the image of $X$ under the functor $\mathcal{S} \to \mathcal{C}$ corresponding to the object $C$.
If you just want an idea, then it's pretty simple : the functor $Fun^L(\mathcal S,C)\to C$ is evaluation at the point sphere $*$.
Then the statement essentially says that $\mathcal S$ is generated under colimits by $*$. To see why this would hold, forget about $\infty$-categories for a second and think in terms of spaces : what is a CW-complex ?
Well, from $*$, first of all by taking a coproduct you get the $0$ sphere $S^0$. Then, by suspending enough times, you get all spheres $S^n$; recall that in the $\infty$-world, suspension is a colimit, indeed $\require{AMScd}\begin{CD} X @>>> * \\ @VVV @VVV \\ * @>>> \Sigma X\end{CD}$ is a pushout square in the $\infty$-category sense.
Now we have spheres we can get any CW-complex : indeed if you want to glue balls along a sphere, since a ball is just $*$, you simply have to take a pushout $\begin{CD}\coprod_i S^n @>>> X_n \\ @VVV @VVV \\ \coprod_i * @>>> X_{n+1}\end{CD}$
and then taking the colimit gives you $X$ for any CW-complex $X$. Now spaces are essentially the same thing as CW-complexes, so we get all spaces; just starting from $*$.
Therefore a colimit preserving functor is entirely determined by where it sends $*$, and conversely, given any $c\in C$ you can define a functor $\mathcal S\to C$ which sends $*$ to $c$ and any colimit (so any space) to the appropriate colimit in $C$ .
This is essentially the $\infty$-analogue of the statement that $Fun^L(\mathbf{Set},C)\to C$ is an equivalence, for $C$ a cocomplete category (everything here $1$-categorical)