My family didn’t hire a college counselor, but we did get an essay consultant. It’s hard to say how much she helped - I was admitted to 7 of the 20 schools I applied to, but my surprises (Rejected at USC, WLed at American) all came at schools where she didn’t have a hand in the essays. I was not admitted to any of my super reach schools, but I don’t really feel comfortable expecting her to somehow get me into a school with a <10% RD acceptance rate. In the end, I think we mostly hired her just to feel like we had made use of every resource at our disposal.
If you do hire one, start early, as they tend to get very busy, and know that you may not click with the first one you find. I did NOT click with my first essay consultant, but I felt extremely comfortable with my second consultant.