Once the fog of butterflies dissipates, you find yourself in a walled area known as The Fringe, and to escape this there’s the small matter of getting past the goliath Gatekeeper - a terrifying construction of the body parts of various creatures whose job description provides a fair amount of the plot later on. Once you’re past him though, you’ll find yourself searching out the man of the moment: Sheogorath. And once you meet him, alongside his loyal chamberlain Haskill (very much a Jeeves to the big man’s Bertie Wooster), the plot starts ticking.
“The first time you meet Sheogorath he says: ‘You know what? I need a mortal champion and you’re the only one who’s made it to talk with me, so you’re him. You are my champion’,” explains an enthusiastic Nelson. “But you don’t really get an idea of what your real job is going to be. Sheogorath only gives you bits and pieces - he doles out information slowly. He’s the god of madness, and he tends to speak in unintentional riddles and go off on tangents about pudding.”
Right. So anyway, Sheogorath’s thought is that if you’re going to hold any sway in his court whatsoever, you ought to go out and start meeting people, helping them out, pissing them off and basically having a cracking role-play adventure. As with the various guilds and orders of Cyrodiil, your reputation with the houses of Mania and Dementia will rise and fall according to your actions, but there will come a point at which Sheogorath will ask you to make a final decision as to which side you will join and, indeed, of which you shall become leader.
This in turn will have ramifications in later quests and in whose support you’ll have as you battle the forces of the rival daedric prince Jyggalag (mentioned once in a book in Daggerfall, and apparently hotly discussed on the Elder Scrolls lore forums), who’s moseying into the madness uninvited. He’s attempting to render a genocide of sensible-ness upon the Shivering Isles known as The Greymarch, an ancient event that occurs every epoch or two that Sheogorath is naturally anxious to avoid. As Nelson points out, it’s all very much created in the spirit of Neil Gaiman (author of the Sandman graphic novel series and novels like American Gods), with concepts like sanity and madness being given form and personality, and having them clash against each other while mortals like you and I toil away beneath them, subject to their every whim.
One of the key things Sheogorath wants you to do is help create another guardian for the Gates of Madness. As such, searching out the original guardian’s creator and helping him fashion a new one out of body bits is an important part of the main quest, but the chirpy Mark Nelson is reluctant to reveal much more in terms of storyline - and not just to lessen the risk of spoiling our fun.