The effect of social media depends on the the social media. I do not follow all of the various social media mechanisms, but here is a quick run-down.
Facebook: Facebook gives a site another presence where people are, but it requires work just like a web page. You have to work it. It also offers advertising opportunities. It is not always beneficial for every situation, however, if you have something to sell or your site offers a fair amount of user participation, articles, and such, it should really help.
Twitter, Pinterest and others: These are sharing sites. I will use Twitter as an example. If a user finds an interesting article, they may tweet about it (share it). That tweet may be re-tweeted (shared again). This gets the word out about your site and helps people find your content. It may not set your site on-fire, but one popular article and well placed tweet, can really a lot of drive traffic.
Keep in mind that Facebook and traditional search are visited and preferred by different mind-sets. Twitter too. Twitter tends to be timely and trendy while traditional search tends to be more research. Facebook is more personal and people want to connect to you or your product differently than they can on search.
Some may argue, but generally, social media links are of small value. They are
nofollow (generally) and they do not seem to appear in link profiles such as Google Webmaster Tools, but may show up elsewhere. As well, I do not generally see these links in search. What is good about the links is that they DO drive traffic. One tweet from quite a long time ago, does drive a few users to my site several times a week. Facebook is good for this too. The more tweets and links on Facebook, the more traffic is driven to your site.
In the early years of Facebook and Twitter, these links where looked to by search engines as something of high value. There may still be an effect, but I suspect the effect is rather small these days compared to before. Instead, the value of social media is in participation. It can drive traffic which will not have a direct effect on SEO, but a direct effect on who sees your site. Now understand this. These links can get you mentions in blog posts and articles which would count for SEO. In that way, social media is really beneficial. For example, a highly respected news paper article from a long time ago linked to my site. Not only does this drive traffic (smaller these days), but the link from that site is of extremely high value. Social media can spark these kinds of links that may not have happened any other way. These higher quality links can really add up and effect your site. Even moderate or less than moderate quality links can really help your link profile since it is assumed that they are organic.
You will NOT want to participate in too many social media sites. You will want to chose which ones make sense. Facebook for me does not make sense. It is more work and my site being dynamic and automatic (for the most part) means that Facebook would have to follow rapid changes in the nature of about 10,000-50,000 per month. However, Twitter may make sense since this can be automated and special pattern discoveries from my data could spark interesting articles which would be of interest and suit the interest of Twitter followers. If you have an image based site, Pinterest may help. If you create videos, YouTube.
Each social media site has unique value that may or may not help you. You will have to research these sites and figure out what works best for your situation and go with that. I do warn you that all of these sites require work. Do not do what a friend on mine did. He is in a unique position where Twitter would really help him and he would be a leader in his field on Twitter, however, he only tweeted a few times and never really developed a Twitter following nor did he follow anyone else. He is literally the expert in his field too having been the first expert and a pioneer. It is a lost opportunity for him to gain more work and recognition (not that he does not have enough already). It would open up opportunities to expand and offer more services. You have to work social media for it to work for you.
The last and final benefit of social media.
You have the opportunity to show the world your expertise and become a leader in your field if it applies. This is one area where social media really excels. Success in social media can really translate into being recognized as an expert in your field and may find you new business opportunities, or a better job if that is what is preferred. One example, I received a highly desirable job offer simply because I filled in and answered some unanswered questions on a forum when Cricket Liu was on vacation. I was seen as an expert that could keep pace with the leading expert. This can work for you too.