I will confine my comments to places where I have spent at least sixty nights.
Hilton Santa Clara - Crazy expensive on weekdays, dirt cheap on weekends, unless there is a sporting event or convention center draw. The property is absolutely not special, purely functional. Very typical lounge. Hotel and lounge can be overrun with kids, with amusement park next door. Car is essential, with few food options within a walk. Parking is free. Nearby light rail to Mountain View and downtown San Jose.
Doubletree SJC. By the airport. A few places within walking distance, but very few. Expensive parking. Reasonably close to light rail. Lounge closed several years ago in an odd phased approach (no longer relevant).
Hilton SFO. Another place in an isolated location with slightly cheaper parking. Another standard lounge, like Santa Clara. Also popular for families with kids looking to save a buck. Nothing elegant here, though nice views of bay and runways/approach/depart.
In my stays - again at least 60 nights each, my best upgrades have just been to rooms with a view. (I'm lifetime Diamond.)