I love making lists, so I'll totally join for this one. Keep in mind that I am coming at this from the perspective of someone who has heavily modded this game (mods folder is 163GB) and I play the game for screenshotting, CAS, and live gameplay and not for building.
#1: Vampires
I would not be playing the game without this pack. The gameplay and depth it offers to Sims almost totally lacking in that area is fantastic. Being a vampire to hunt Sims and grow your vampire created some very addicting gameplay for me when this pack first came out. And with certain CC and some mods, the variety of Sims you can create is fantastic. Demonic Sims, for example. It's the main reason I'm able to do my story. Would highly recommend to anyone that likes supernatural gameplay. My only complaint is that I find the world to be uninteresting and don't often visit it as a result.
#2: Werewolves
I was surprised with this pack, I really didn't expect to enjoy it as much as I did, but this pack actually had me wishing I was playing TS4 when I had to do other things. I find Werewolves to be incredibly fun and diverse and I always appreciate packs that let me create Sims that may live life totally differently from the average Sim.
And as much as I dislike TS4 worlds as a whole, this one is among the least disliked for me. It's pretty and peaceful... until the full moon comes out, anyway. But for negatives, I suppose for me, given the nature of Werewolves, I find it harder to justify them being in the modern world/setting (like a city) over Vampires, so while that's not the pack's fault, I do use them and create them far less than Vampires as a result.
#3: Spa Day
I enjoy making my Sim couples be very close and intimate, so the ability for them to give each other proper massages (not that 2 second shoulder rub animation in the romantic category) is quite nice to have. I also enjoy having my Sims be into fitness and health, so I find at least some mild enjoyment in having them do yoga and meditate. Sound effects are nice too.
Nothing about this pack particularly stands out in the way of negatives, I just don't value it as much as the other 2 packs listed above. It does its job and is a solid pack, especially with the refresh.
#4: Strangerville
I do appreciate what this pack was trying to do, with its unique theme that hasn't been seen in other Sims games (as far as I know) and the Possessed emotion jump-scared me numerous times, which I enjoyed a lot. With certain mods, this pack is pretty fun for me. But I dislike the rather linear "quest" that it has, since I feel that runs antithetical to the nature of the game. And because of this, I often only do it just once per save. While I like that it can be replayed, doing so has very little purpose as well.
#5: Realm of Magic
The magic itself can be pretty fun and as far as TS4 occults go, Spellcasters are far from the worst to me.
The worlds are not interesting to me at all and so I hardly use them. And while I said it was fun, Spellcasters are definitely the most overpowered occult in the game, since over-charging can be easily avoided on a max-level Spellcaster.
Why cook when you can summon food? Why work when your Sim can just garden, grow a money tree, then copy said tree over and over? (Though in fairness, the Painting skill can be abused this way too) Why even focus on needs at all when you can just brew a potion to fill them, then copy said potion over and over? Need help? Summon a clone of yourself. While I admit that the magic is cool, it heavily trivializes an already trivial game. Vampires are a close second to me in this regard. At least the sun is a threat in the early stages of vampirism.
#6: Parenthood
Meh. I'm not very interested in family play in general and I found maxing out all character values to be very hard. Three were easy, two felt like a chore. I don't think it's an awful pack, but I do concur it's very overrated.
#7: My Wedding Stories
I really only like this pack for the world, it's got some great scenery. That's really it. It's just a place I send newly married Sims to so they can pretty much do nothing but Woohoo all day on their honeymoon.
#8: Outdoor Retreat
My view of this one is mostly negative. I'm not really a fan of the vacation packs since they heavily focus on the game's worlds and said worlds are often disappointing. Herbalism isn't an interesting skill to me, there's nothing to really do in the world besides hike and scour for plants... and I guess have your Sims Woohoo outdoors or something. Some nice scenes for screenshots, but that's about it.
#9: Jungle Adventure
Another vacation pack I hardly used after I got it. It just makes me miss TS3 World Adventures.
#10: Dine Out
Honestly, I just haven't really used it. I like watching my Sims cook and since you can't be a chef with this pack, I don't make them go to restaurants at all. However, I do want to give it a try. It'd be nice if I can use it for date variety.
#11: Dream Home Decorator
I'm not a builder, so this pack holds zero value to me.
#12: Journey to Batuu
While Dream Home Decorator still fits within the theme of the game, this pack absolutely doesn't. If you could put how much I care about this pack as a negative number, it'd be pretty far down there.