It's interesting that each governor's role changes in each stage of the game.
Castellan
Early game: Protects your cities from barbarians and civs designed around early aggression.
Mid-game: Protects you from spies and gives you more reasons to build walls and encampments.
Late game: Protects you from nukes.
Diplomat
Early game: Manipulates loyalty to let you steal cities peacefully and defend yourself from the same.
Mid-game: Helps with amenity shortages.
Late game: Locks down any one city-state for you.
Cardinal
Early game: Helps get your newly founded religion up and running at full capacity.
Mid-game: Makes it nearly impossible to completely exterminate your religion, even with a strong enemy invasion.
Late game: Gives super-apostles for that final religious victory push.
Steward
Early game: Lets you go both tall and wide at the same time. No need to choose just one.
Mid-game: Prevents you from getting resource-screwed, and makes obsolete resources more useful.
Late game: Turns one city into a production powerhouse for unit spam, wonder spam, whatever you want.
Surveyor
Early game: The go-to governor for coastal starts. Also helps get more governors sooner.
Mid-game: Helps new expansions become useful sooner. Also helps with amenity shortages just like diplomat.
Late game: Gives appeal for neighborhoods, national parks, and seaside resorts.
Educator
Early game: Gets you out of the early game.
Mid-game: Irritates Pedro.
Late game: Lets you make that big push for science victory or, failing that, just nuke the world into oblivion.
Financier
Early game: Helps civs with trade route bonuses do their thing faster.
Mid-game: Dolla dolla bills.
Late game: Makes new expansions useful instantly and lets you make that final push for cultural victory.