On my first play through I managed to get it up to 98% before I croaked, but outside of a few flavor scenes it didn’t appear to have an effect on the events of the game or really help me out. Did I miss something or is it just supposed to be that way?
Piety is needed to build a church on your Earldom and to go to the Crusades.
If you have high piety, you can win the first jousting tournament if you pray.
It’s concentrated on providing a fair bit of flavour text and thus a slightly alternative view into the world. As Urban - the resident Lords of Aswick expert on the forum - pointed out, the mechanical effects of it are few and far between. If I recall correctly, it also plays a part on whether or not you have any chance of becoming Grandmaster towards the end of the crusade branch.
In general, the crusade is where Piety comes more into play than anywhere else in the story.
To compensate on that a bit, piety is not really bunched up with the main stats although it might seem like it. It’s easier to raise and lower outside the significant stat boost points so you can still concentrate on courtesy or martial prowess.