From my perspective I judge DLC based on a criteria, sometimes a DLC can benefit from simply having a good main game to build its foundation on, so that helps it, in the case of for example Bloodborne, where its a great DLC but most of its good things come from the main game being really good.
Instead of just writing a list and posting pretty picture i will also write how i judge a DLC as good or bad.
For me the criteria goes like this;
1) Content/Price Ratio: Since I'm paying extra money for more content i want to know how much content im getting for the price im paying.
2) Quality: While more content is always good, the new content has to be of good quality for example: if it adds extra levels, then the level design quality, if it adds a main story campaign how good the main story is.
3) Improvements: DLC/Expansions should strive to improve the main game, this is their first priority imo. Does it improve gameplay? Does it improve story? Does it improve level design?
4) Fresh Experience: I love it more when DLC/Expansion doesnt just stick to the main formula and instead goes for that fresh experience, like building a new world and throwing the player there. This gives the feeling of a distinct purchase that feels rewarding and fresh.
Expansion that checks all of this is instantly good in my book, regardless how disappointing the main game is.
Example of short-comings within a DLC/Expansion;
- Poor quality but tons of things to do; which means the expansion is not really at the same level of the main game but it offers a lot of new content.
- Good quality but very short and high price expansion; some of them are good but they can be completed in few hours thus lacking content.
- More of the same; The main game was good but the expansion didnt improve it, it simply just added more of the same!
As for my favorite DLC/Expansion pack here is a small tier list based on the ones i played.
S Tier:
- The Frozen Throne (Warcraft 3) S+
- Blood and Wine (Witcher 3) S+
- Brood War (Starcraft)
- Shivering Isles (The Elder Scrolls IV: Oblivion)
- Minerva's Den (Bioshock 2)
- Fallout New Vegas collection
- Lord of Destruction (Diablo 2)
- The Old Hunters (Bloodborne)
A Tier:
- The Shadow of Death (HoMM 3)
- War of the Chosen (Xcom2)
- Hearts of Stone (Witcher 3)
- Artorias of the Abyss (Dark Souls)
- Age of Conquers (AoE 2)
- Brave New Worlds (Civ V)
- Undead Nightmares (Red Dead Redemption)
- Citadel and Lair of the Shadow Broker (Mass Effect)
- Awakening (Dragon Age Origins)
- Throne of Bhaal (Baldur's Gate 2)
- Bloodmoon (Morrowind)
- Dragonborn (Skyrim)
- Opposing Force (Half-Life)
- Mask of the Betrayer (Neverwinter Nights)
B Tier:
- Ringed City (Dark Souls 3)
- The Pitt and Point Lockout (Fallout 3)
- Dawnguard (Skyrim)
- Specter of Torment (Shovel Knight)
- The Knife of Dunwall (Dishonored)
- Episode 1 and 2 (Half-Life 2)
- Hollow Knight DLCs
- Death of the Outsider (Dishonored 2)
- Torna the Golden Country (Xenoblade Chronicles 2)
- The Following (Dying Light)
This small tier list, if you notice the two S+ above are 2 expansions i consider the best ever made. Those are all good DLC/Expansions btw, so dont look at the B tier and think i dont like them.
Blood and Wine is the gold standard for modern expansions and the classic goes to Frozen Throne.
The Frozen Throne improved a lot on the game, added lore, added great story campaign and characters, more modes and spawned an entire Esport genre that today many gamers and people in gaming make living out of it.
Great all around and legendary Expansion.
Blood and Wine is known, it basically checks all the boxes on the gold standard dlc.
Huge content for a fair price? Check
Great high quality throughout the expansion? Check
New fresh landscape and area that feels distinct from the main game and provides the player the feeling of new experience? Check
Improved story from the main game? Check
Better Gameplay? Check it adds better enemies, better boss fights, better weapons, more gwent cards, new soundtrack album, new skill tree and UI improvements.
Great and memorable side quests? Check
Better Open world map and exploration? Check
Overall package its god tier DLC/Expansion.
Compared to other DLC/Expansion it stands out, when compared to others it probably adds more content at cheaper price and with the same tier of quality as well, which makes it my choice if i had to make one.
Also this video talks about it better than anything i will write here.