RTS campaigns are never good and they never really have been.
Yeah, no.... There have been some pretty great ones over the years. Particularly enjoyed Starcraft, DOW 2 and the Command and Conquer ones back in the day, it's all just a matter of taste though i suppose. Once i really dig into skirmish and multiplayer i quickly run out of patience for campaign missions unless they are really exceptional.
EDIT: Warcraft 3 would be on the list for me as well, had great fun. Back when Blizzard was Blizzard.
The COH 1 campaign and its subsequent expansions were great, granted it was about 10 years ago so i'm sure it hasn't aged particularly well, but I found myself actually caring about some of the characters. The COH 2 campaign sucked though and the most recent one for COH 3 was even worse.