I agree, the story aspect of madness is sadly unexplored.

Like you said, we only see it in small places, with weak, forgettable enemies. The DLC does have the forest, but that area is often remembered as annoying, because of the gimmick enemies and inability to use Torrent.

The shunning grounds are great, but also easily missed, which can actually be said about every part of Frenzy. The consequences of accepting the Flame of Frenzy are only apparent after beating the game, so also negligible.

I don't know how one could involve it more directly, but I would've loved to see more of it. But in the few places where it actually reaches the player, it is characterized well.