I haven't sifted through the dozens of hunks of Creation Club stuff contained in the Skyrim Anniversary Edition yet. What mainly interested me about the new edition were the new quests.
Both are tied to The Elder Scrolls' past: The Cause deals with the Mythic Dawn cult from Oblivion reappearing and trying to open a new gate to the Daedric realm, those scamps. Ghosts of the Tribunal takes place (mostly) in Solstheim and features weapons and armor from Morrowind as rewards.
Oblivion was my first Elder Scrolls game, so naturally I dove into The Cause first. It begins, as do so many quests, with Skyrim's tireless courier running up to you and stuffing a note in your pants. It's a confessional letter from a former member of the revived Mythic Dawn cult, and after following its instructions you find yourself waylaid by a few rabid cult members. One of them carries a note which takes you to another location for some more fights with the cult and another note.
Both of Skyrim AE's new quests rely almost entirely on notes, journals, and letters to tell the bulk of their story. Typically at each stage of the quests you'll either find a note, find a dead body with a note, or make a living person into a dead body and then find a note on them. There are even living characters you don't kill who greet you and then hand you a note rather than actually talk to you. It's pretty clear this is done to avoid having to use actors to record new voice lines for these quests, but it does start to feel weird after a while. It's especially strange to meet the main character of a quest who simply hands you a 10-page letter to detail what they want from you, rather than explain it out loud.
Luckily, the writing in these letters is all very good, and a lot of it is really interesting. I especially enjoyed reading the Mythic Dawn's journals because they're trying to uncover and open an Oblivion gate—but are having a really difficult time with it. They had a member defect and spill his guts about the whole thing, the Vigil of Stendarr (basically religious cops) are all up in their business, and the gate they're trying to open is underground so the mining process has been fraught with accidents, monster attacks, and plenty of cult member deaths. There's something I find appealing about an evil master plan that's going pretty poorly.
Much as I enjoyed the cult's journal entries, I felt differently when reading the journal I found on the corpse of a member of the Vigil of Stendarr. It's an interesting read—the Vigil has been following around a number of suspected cult members, shadowing them to see where they go, breaking into their homes and searching through their stuff for signs of Mythic Dawn affiliation. Detective stuff.
But reading that, all I felt was: Shouldn't I be doing all that detective stuff? How much better would this quest be if I could shadow suspected cult members, skulk around and see what they do all day, wait for them to leave their homes then break in and rifle through their stuff, and arrest and interrogate them myself? Instead I'm stuck reading about it. It's a fun journal but I wound up feeling a bit left out of the real adventure.
In the Morrowind quest (which takes place in Solthsteim) you do, briefly, get to follow a guard to see where he goes, but he only goes about fifty feet in a straight line and it doesn't satisfy the part of me that wants to be a Skyrim detective. In this quest you're on the trail of a heretic last seen trying to enchant a Dwemer weapon with a mysterious gem, which takes you (after a couple more notes) to an unground temple where you can either help or harm his fellow heretics. It's got a bit more sprawl than the Oblivion quest, as you dash around Solthsteim (and back to Skyrim at one point) to gather a bunch of missing artifacts (cool masks) and gems to finish enchanting the weapon detailed in, yes, a note found on a dead body.
Most of the locations of these quests are underground, which makes sense as there's more complexity to adding new overworld locations than instanced dungeons, but they're pretty well designed and compact enough to prevent getting lost trying to follow quest markers. There are some light puzzles (notes can give you clues or solutions). Nicely, both quests let you dress up in cult or heretic robes to freely walk among the other members, which is at least something like being an undercover detective.
I adore Oblivion so I was pretty stoked just to see people running around in Mythic Dawn cloaks again in The Cause, and even the sight of an Oblivion gate is enough to make me happy. Ghosts of the Tribunal I didn't find nearly as interesting, but there are more rewards in the form of cool masks and it does take you around the map a lot more.
I'm not sure the two quests alone really justify the price tag of Skyrim Anniversary Edition, if that's why you're thinking about buying it. But they're decent quests that will give a few hours of extra adventure each, as long as you don't mind doing a lot of reading.