Although Eternal Eden did not have any revolutionary sidequests in it (mostly "kill" or 'acquire" quests), a key gameplay mechanic made me want to do many of them. Bear in mind, I'm not someone who does a lot of sidequests. In Eternal Eden, encounters weren't random and if you won a battle, that enemy didn't respawn. This made backtracking fun because I didn't have to worry about needless pointless battles when backtracking through old dungeons. I could just explore in peace. This is a big reason I did so many sidequests in that game.
I remember in Septerra Core, some characters would attack each other in battles unless you did the sidequests to help them get along. Fantastic concept, but because the game design featured slow movement and overly long dungeons, backtracking and all that wasn't much fun.
So my point is that sometimes a good sidequest idea in theory could falter due to game design flaws and seemingly "meh" sidequest ideas could be fun due to very good game design.
The chained fetch quest will never be fun for me, though. Or quests where I'm bodyguarding idiots.
And, for me, I tend to like quests that enhance the worldbuilding. The Hunter's Board quests in Phantasy Star 4 were cool because they enhanced the worldbuilding and were fun. Every time I played that game, I did them all, and like I said, I normally don't do sidequests.
Even though the shumi sidequest in FF8 yielded a piddling reward, I learned about the shumi people and moombas. Oh, and my favorite sidequest was hooking up Zell with the shy library girl who had a crush on him. I thought that was cuter than the main love story, which read like a bad version of Romeo and Juliet. So, yeah, I think my all time favorite sidequest is Zell's "love story."