The key element is not to make the music too on-the-nose. If the players are listening to the soundtrack and it's producing the same feeling as each other's actions or a GM's narration, then they are likely to drift between these things a little too much. When music in movies (for the most part) takes center stage, it's likely there's no dialogue. Since RPGs are entirely created through real-life dialogue (as well as miniatures, terrain and sometimes drawings and diagrams...hm, did I just disprove this whole blog post?!?) it's important that your music provide some contrast to what you're doing - something where listening to the music, then listening to others, is switching gears.
The most effective Vampire: the Masquerade music cue I ever used was not a horror movie soundtrack, nor a goth track, but was this:
So let's talk about some other soundtracks that have particular feelings to them and how to use contrast in your campaign to make these soundtracks effective. Again, these are all soundtracks currently on sale for $5 or less.
Beasts of the Southern Wild
|Beasts of the Southern Wild, currently $5|
So what kind of campaigns could you create with Beasts of the Southern Wild? A countrified horror game would be a good choice, since the hope/comfort of the music could contrast with the struggles of the characters. Or you could make it a highly urbanized game, and let the country elements provide the contrast - whether in the city or the country, people's struggles are very similar. If you wanted the darker elements to provide the contrast, the game could be a game of community building where relationships were strong but danger couldn't be fully avoided. Then the warm parts of the soundtrack would match the tone of the game and the anxiety/difficulty of the music would be the contrast. When in a dire situation, the players could draw on the emotion of the music to remember the "good times" of the campaign.
The vocal tracks are a little bit more of an "occasion", marking Mardi Gras and other celebrations in the movie, so you might use them for a similar reason here.
Dexter - Season 1
|Dexter (Special Edition), currently $6|
These traits make it perfect for a fantasy game in which perhaps reality itself isn't as the players know it to be (a world with magic or monsters), because it lets you feel that things are a little bit off base without drowning in it, and the human elements of the soundtrack will let you stay grounded. Not to mention the Cuban songs, which are down to earth, will provide a great contrast to the alien-ness of a fantasy world.
Don't play Lord of the Rings soundtracks in your fantasy game, who cares about that garbage? Play the Dexter soundtrack, it's way better for fantasy. Similarly, in a Star Wars game, (except for maybe the first John Williams opening cue at the first session where you're reading your roller caption text - you were going to do that, right?), don't play Star Wars music, play funk music or hip hop. The contrast will make the game come alive.
When you're feeling inspired to create a campaign, however it happens, look for music that will both complement and contrast with what you're trying to do.