A good advisor can help a startup in various ways. Here I describe what I do:
I work quite a bit with pre-angel/pre-seed/pre-accelerator US-based tech startups (or sometimes even executives/scientists/technologists in corporate jobs toying with a startup idea that they'd consider leaving their jobs for), and have done this now for well over a decade while founding/growing my own companies. Most of these founders come to me from hearing about me from another founder, so I am assuming that founders are finding some value in their interactions with me.
At this early-stage in the game, one of the key things I try to do is to separate founders' assumptions about various aspects of the business from facts that they have validated. Another thing founders find useful is examples/ideas of how other startups solved a similar business problem in a creative manner (my voracious reading helps a lot here.) Finally, I also try to take the devil's advocate position on key assertions that the founders make (e.g. why are they & their product uniquely suited to tackle this problem in face of competition) in order to point out counterarguments with the eventual goal of helping them strengthen their case.
My engagements with founders tend to take the form of intense 30-75 minute brainstorming sessions (1-3 sessions), which typically provide them with enough food for thought to start executing on various fronts.
A small percentage of these startups continue to make great progress over a period of 3-9 months and keep me in the loop via brief email updates. Those ones, I help by providing them critical feedback on their pitch decks (multiple iterations until I think that it is crisp and powerful) and then bring on the radars of my early-stage investor contacts (US-based).
A good adviser brings to the table their knowledge of the field, a suggested plan that augments the original business plan for the better, and a no nonsense approach.
They won't hold the startup's hand, but be more of a guide. A lot of startup's who aren't familiar with someone who advises (or consults) often mistake it as a means to slough off vital business and marketing lessons. They won't do it for you. They will teach you how to do if for yourself so you can rinse and repeat the situation.
An adviser is not always an investor, but someone that you can pay to consult your business. They know what it takes to get you going.
If they haven't had any experience in your niche, the best consultants out there will have a good referral list to send you. They may also have references for potential partnerships and gaining good startup capital.
