Appeal for each tile has a score, higher score means better appeal. So "breathtaking" corresponds to appeal of 4 and above (I think), "charming" is appeal +2 or +3 I think, "average" is appeal 0 or +1, etc. The score is affected by adjacent environment, so things like coast, cliffs and woods in adjacent tiles increase appeal score, whereas mines and industrial zones and other things decrease appeal score.
Like you say, the score affects things like housing from neighborhoods, and can also have other effects, for instance Australia get increased yields from districts with high appeal, there's a pantheon that gives you faith if appeal is high on tile, and the Sea Resort improvement and national parks give you yields based on tile appeal.