You're probably thinking of a freelance Online Community Manager or content creator. I happen to be one (not advertising myself here, just letting you know I have experience in the area). My work essentially entails creating content for social media and managing a client's online communities (Twitter, Facebook, YouTube, etc.). I also come up with online promotion plans and find followers for my clients. I've mostly worked with small-to-medium businesses and the occasional individual. Some Community Managers also scour media for opportunities for their clients to appear on radio, traditional media, blogs, etc.
Community Management is pretty pricey, which is why the bulk of my clients have been businesses. Depending on the services you want, a freelancer could range from $15 to $30 an hour, while hiring an actual Social Media Marketing firm could cost $1000 to $3000 a month. That's for a pretty full range of services. If you just want someone to write content for you (appropriate tweets, blog posts, that kind of thing), there are dozens of copy editing services that charge by the hour or by the word. Most are something like .02 cents to 2 cents a word.
In short, that scale of social media management is really not feasible or effective for an unpublished or recently published author unless you have that kind of money to burn. There's also the product itself -- it can be difficult to build a following for a new author. Possible, but difficult. When I was working for an agency, one of our clients was a woman who had self-published a self-help book. I didn't really approve of my boss taking her on as a client because I was afraid we wouldn't be able to do much to help her (social media can only do so much), and I ended up being right. We increased her following, got her name out there, ran promotions, and did everything we were supposed to do, but she only ended up selling a handful of copies.
In a nutshell, I'm saying that social media for new authors is something they should try to handle themselves if at all possible. However, there are places you can go to learn more about it -- a lot of the aforementioned freelancers and agencies offer social media consulting as well.
The trick with social media is NOT to join every single thing you can dig up from the Internet, but to pick 2-5 mediums that you feel most comfortable with and make the most sense for the audience you're trying to reach. For writers/authors, I'd suggest Facebook, Twitter, and blogging as the main outlets, while maybe dipping into Goodreads and possibly YouTube if you have a knack for video blogging.
Social media CAN eat up a whole lot of your time, so it's important to balance it and potentially learn to use a management tool like Hootsuite or Tweetdeck, both of which allow you to schedule posts to go out at certain times so you don't have to constantly go to the sites throughout the day. About 3-5 posts a day for Twitter, 1-3 updates for Facebook, and a set blogging schedule whenever convenient for you is a pretty good target to aim for. Other important factors with social media: don't just talk about yourself; share knowledge and links and fun stuff. Start conversations. Always reply to people who respond to you (unless they're bots or trolls). Follow other like-minded individuals and get to know them. Be yourself. Have fun with it.
If it ever becomes more of a job than your writing is to you, you're doing too much.
Whew, that was a long post. I hope some of it is helpful :)