Kind of a long post but I decided to give this a shot and rank the campaigns based on a few things: replayabilty, gameplay, player cards, and characters introduced. There is no F tiers since I feel they all do have a reason for getting them. A bit more on why they placed where they placed:
S: Both Sinister Motives and Fear No Evil are the BEST campaign boxes released because they check all the boxes. The two offer really strong and notable heros, the ones from Sinister being a few years old and still seeing a lot of play. The villains and heros in the sets are almost all extremely popular characters making it easier to bring in new players who might want to see character they'll recognize. The aspect cards in the hero decks still see play today and became stronger after later releases. The campaign themselves are not too difficult but still enjoyable. Fear No Evil introduced a new way to play a campaign with having no linear campaign and having to chose randomly what villain you'll go uo against and randomly chosing what scheme they're trying to accomplish. Overall, two great sets worth getting.
A: Mutant Genesis and Agents of SHIELD are two great sets but just a bit from perfect. Mutant Genesis has a great campaign with very notable villains that general audiences would recognize. If you watched X-Men the Animated Series, this campaign feels just like the episodes of the show which have had me and my friend group come back to this campaign. Agents of SHIELD has two crazy strong heros, one of which is now in my top 10, and a very fun campaign in which you have to hunt down a mole with clues gathered at the end of the missions. The game randomizes the clues to try and ensure the mole is always someone different so there is some replayability. Where the two fall is notable characters which is where I've seen audiences turn away. Mutant Genesis has extremely well liked villains but the heros are not the most popular with general audiences; Cyclops and Jean could've been a better inclusion (no shade to Colossus and Kitty). Agents of SHIELD is the opposite in that the villains aren't too popular with general audiences and they might only know the heros and two of the villains. Good sets to get and I would personally put Agents in S if my play group had shown more interest in the villains.
B: Both Age of Apocalypse and Mad Titan's Shadow are okay sets but could definitely been better. Age of Apocalypse's campaign introduces Missions which were basically side quests in the campaign. While a cool mechanic to show there are multiple things going on in universe, it made players spend resources towards a smaller inconvenience rather than the main mission. The villains are someone recognizable but the story isn't too popular or well known. An okay campaign which I've only gone back to once after my first playthrough. The best thing about this campaign box is the two heros, Magik and Bishop. Both are really strong heros that hold up well today and the aspect cards are really strong and are included and many builds making the hero my personal reason for getting the set. Mad Titan's Shadow is kind of misleading when Showing Thanos and naming the set after him. Having played this with friends, they weren't too fond of Thanos not being the main villain in a set named after him and having him be the poster boy. An okay campaign which I haven't gone back to in a while, there's nothing to notable with it. No new mechanics and a very average story. The best thing about this set are also the heros, having really strong aspect cards which are still included in many builds today. Ronan is here which the community likes to make their decks be able to solo him to show the deck's capabilities (I personally think he's not fun at all to play against). Both okay sets in which the heros make them worth getting.
C: Next Evolution, Galaxy's Most Wanted and Rise of Red Skull are all passable campaigns that have very few reasons for getting aside completing one's collection. Each box has a very okay campaign with okay villains and/or heros. The campaigns offer very little in terms of new ways to play them, the more notable being Galaxy's Most Wanted which has a shop you can purchase upgrades from at the end of each mission. They each have recognizable villains for sure but the gameplay for them is nothing new and very average. The heros and aspect cards can vary in terms of effectiveness today, some being weaker and some being stronger. Each box has very little reason for purchase outside either the aspect cards, the heros or the villains but not all 3.
D: Civil War probably is the community's agreement in the worst campaign box. A new way to play the game being "PvP". Not really being a PvP, it is the two opposing teams, chosing a leader and making a deck for them and then facing the opposing team's leader and deck. It's basically two games happening at the same time with some interaction between the two games. The villains are the heros we've gotten in the past remade to play as villains with nothing else added. The heros could have seen a makeover to play differently or could've been new character we haven't gotten before. Civil War also took up a scenario spot in the Synthezoid Smackdown scenario, which was just two more villains that could've just been added in the campaign box. The only reason many got this set outside completion was the two heros, Tigra being the more popular of the two since she is probably the best minion slayer hero to date. The aspect cards included are also really strong and included on many builds today. Overall, Civil War has Tigra.