Good DLC? Skyrims Dawnguard and Dragonborn.
The former fleshes out a part of TES that was lacking in the vanilla game (Vampirism, Vampire NPCs and Quests, Werewovles etc.) while also retrospectively improving these elements mechanically (Werewolf perk trees made being a Werewolf viable and fun to use). It added new dungeons, locations, quests, characters, weapons and a relatively good plott, which fleshed out some TES lore that is rarely touched on with such focus. 15 quid (actually 6~ quid, as I got it half price when it was finally released on PS3) for another 15-20 hours of gameplay. Not as much value for money as the original game was, but it added quite a bit that retroactively improved the vanilla game, not just as a standalone experience. In other words, subsequent playthroughs have been far more enjoyable then simply loading up a old file to play the DLC.
Dragonborn had an expansive quest that dealt with one of the more intriguing Deities in TES lore. It captured the vibe of Hermaneus Mora exquisitely and rivalled Shivering Isles for the sheer alien-ness of its world in contrast to Skyrims Norse inspired fiction.
It offered a taste of a classic game envisioned within the newest TES engine and gameplay (Morrowind) without feeling like it was rethreading old ground. Again, new Items, Characters, quests and locations all came together to make the DLC feel strong on its own, but also improved the vanilla game with the additions.
Hearthfire is an ultimately forgettable DLC, but it wasn't too costly and it really did add something for the Role Players out there like myself. Adoption was about as well implemented as marriage, but Bethesda and NPCs have a rocky history. It wasn't good, but it wasn't bad either... completely superfluous and ultimately harmless.
Bad DLC? Map packs (CoD), Skin packs (Anything from Namco), Weapon Packs, Character Add-ons (Like Mortal Kombat). With such minute additions it is hard to gauge their value. 2 quid or less might seem reasonable, but most of these add ons are far less then 1/30th of the full game. Paying 60 quid for a premium game then 15 quid for 4 fucking maps is INSANE... how can ANYONE convince themselves its a good idea?
Absolute DLC sins:
*Content cut pre-release to sell as Day One DLC... so that is ALL day one DLC.
*Content cut that removes something core to the game (Metro Last Light difficulty mode that was "the way its meant to be played" or Mass Effect 3's From Ashes)
*DLC is that unlocks content already on the Disc you paid for.