I'm going to steer away from console games, since I expect many people to nominate the few games I would otherwise, not to mention that I doubt many people will nominate PC games.
There are two RTS I can think of which I'd consider to be near-perfect: Command&Conquer: Tiberian Dawn (that's the first one, I hated Tiberian Sun) and Starcraft (along with Brood War).
Let's start with Starcraft: The game, in itself, is incredibly fun. I don't know of too many strategy games released back in 1997 that are still widely played, had 3 completely different factions, had multiplayer support on that level and had the kind of campaign with in-depth story Starcraft has. I still remember a bunch of memorable quotes from that game. The cinematics are also memorable, and considering when they were made, extremely high quality. I think I could quote Acturus' entire speech from when he crowns himself Emperor of the Terran Dominion. There's not much else to say, really, and it's not like it matters; anyone who likes RTS has already played Starcraft, everyone who doesn't like RTS, well, they won't care about this game anyhow.
The second one, Command&Conquer: Tiberian Dawn, is equally good. It was released back in 1995 (or was it even earlier?), and I still replay both campaigns entirely from time to time. The gameplay is fun, the universe is fairly decent and there's a fuckton of cutscenes. There's at least one between each mission, and considering that this was made more than 10 years ago, and that Westwood didn't have that much budget back then, the stuff they've put together is fairly neat. From the background on the conflict, to info on tiberium, to mission objectives, everything you get story-wise comes through the cutscenes. The best part is that most of those cutscenes are first-person, meaning that when you're given a mission, people are talking to you, you're not watching a play.
This brings me to one of the reasons I love this game as much as I do: Kane. For those of you who've never played the game, Kane is the leader of the Brotherhood of Nod, the so called "bad-guys". The thing is, the guy is so goddamn charismatic that if the Brotherhood of Nod were to exist, and even knowing that Nod uses terrorism, slaughters entire villages and burns down orphanages, and then use the media to blame it on the GDI (the good guys), murders anyone who gets in their way and that Kane doesn't hesitate to kill his subordinates himself, not to mention some even more inhumane stuff, I would still fucking join them. Call me some sort of fanboy, but that's just how things are. There's just something to the way the briefings with Kane are that makes it impossible to dislike him. As I've said before, it might have something to do with the cutscenes being first-person. When he's talking to you and saying, for example, "my faithful friend" in the last cutscene, or when he lets you choose between 4 monuments to blow up at the end (by the way, the game has 6 possible endings), it makes much more of an impact than watching some actors from third person.
Jam it back in, in the dark.