I disagree with the notion that Piety is a useless tree, I think it's just misunderstood. I only consider it if I've already got a religion, or a strong enough Pantheon that I know I'm going to get a religion. It also helps to be going wide (well, wide and religion go together anyways).
Suddenly, the opening bonus to getting Shrines and Temples up helps with building these in later cities. Cheaper purchases of everything is good all around. Temples providing a gold bonus makes them profitable to build everywhere and also offsets the cost of the prerequisite Shrine. The extra faith off those two buildings strengthens your religion by making every city with them generate another five faith, regardless of anything else. Reformation beliefs are quite powerful (if you get two belief buildings like Pagoda/Cathedral, Sacred Sites is hilariously powerful for culture in eras you can't usually spam great works). Religious tolerance IMO is still a bit weak; even if you get a second religion in your city there's no guarantee the pantheon bonus is any good for you. I guess it could be micromanaged through your trade routes (peacefully), or captured missionaries.
By the end of that, a Holy Site becomes 6 faith, 3 gold, 3 culture, plus base tile yield, and potentially enhanced if you're Korean, or through World Congress, or New Deal under Freedom. This makes it possibly the best tile in the game; and you get a free Great Prophet to finish the tree.
In my eyes, it's situational but quite useful, if used to buff up an already powerful religion. I wouldn't commit to it before getting well underway though; if you don't have a religion in the bag already, I wouldn't gamble any early Social Policies on something that might not work out.