It depends, for wikis with high traffic already, probably not, for low traffic wikis, yes. Although it helps increase traffic to the wiki, it doesn't mean there will be new contributors, maybe just readers.

Prior to using the cross-wiki footer(Apr 2010), there wasn't much change in traffic to spotlit wikis. After the footer was used there was much clearer traffic spikes on some wikis.

Here are a couple of examples of a traffic spike during a spotlight:

You can view your wikis traffic at: quantcast.com, just change the url. access removed by Wikia, though you can see any changes in your admin dashboard quickstats.