What is the intuition behind the terminology surrounding Baire's Theorem?

The term meagre is the one that provides the basic intuition. A nowhere dense set is clearly small in a topologically meaningful sense: it’s so small that its closure has empty interior. A meagre set is small in the sense that it can be expressed as the union of just countably many nowhere dense sets. That’s not quite so small as being nowhere dense, since a meagre set can be dense in the whole space (e.g., $\Bbb Q$ in $\Bbb R$), but this is still clearly a notion of smallness. And it’s a nicer one to work with than the basic notion of nowhere dense set, because the meagre sets form a $\sigma$-ideal: all subsets and countable unions of meagre sets are still meagre. (The nowhere dense sets merely form an ideal: all subsets and finite unions of nowhere dense sets are nowhere dense.)

The Baire category theorem then says that if a space is nice enough (complete metric space, locally compact Hausdorff space, etc.), then it isn’t small (i.e., meagre).

More generally, any ideal or $\sigma$-ideal $\mathscr{I}$ of subsets of some set $X$ can be considered a notion of smallness for subsets of $X$. Sets in $\mathscr{I}$ are small; complements of sets in $\mathscr{I}$ are large; and all other subsets are neither small nor large. Some of these notions of smallness turn out to be very useful, and the $\sigma$-ideal of meagre sets of a topological space is one of the useful ones. The ideal of non-stationary subsets of a cardinal of uncountable cofinality is another one that turns out to be very useful.