The best location is one the visitor sees upon entry to the museum, ideally near the gift shop, although this is not possible in many facilities. If the café is not highly visible, be sure to have good directional signage and feature it prominently on the website, all maps, and collateral material.
