I bought SC2 and played through the campaign and dabbled a bit in multiplayer, but the whole game feels designed for competitive e-sports. A few of the campaign missions are thinly disguised tutorials on how to execute X strategy that might be useful online; most are very well-designed, but the story was a let-down. Gameplay is traditional RTS fare and is, again, very well designed. If you're into competitive RTS gaming, there's no better game out there, no doubt about it - balanced and pure and challenging.
If you're looking for fun, though, there are better RTS games - I don't mean "serious fun", of course, but rather playful fun. SupCom has insane scale, RA3 has wackiness and lulz, Dawn of War has action and tension. Starcraft II is a "checkmate, suckers!" kind of fun (if you're good, at least), but it gives you neither the time nor a reason to sit back and simply be entertained by what's going on.
EDIT: Note that I never really got into custom maps in SC2, so I can't comment on those - I only know that they're apparently better than what Warcraft 3 had to offer, and Warcraft 3 was awesome enough in its day, so that sounds encouraging.