I should give two disclaimers: 1) I'm a violinist, and 2) unless there are specific instructions or traditions, no song has to be played in an alternate tuning. (E.g., thinking about violin literature, some pieces call for specific alternate tunings, like Biber's "Mystery sonatas." Some fiddle tunes have strong associations with a tuning, like the use of "Dead man's tuning" in "Bonaparte's Retreat" or "Midnight on the Water.")
Some songs would also be poorly-suited to an alternate tuning, like if they are highly chromatic or change keys.
For me, I feel drawn to alternate tunings for a number of reasons. One is if the song is very centered not just in a particular key but around a particular chord; for instance, this song's A section spends most of its time on the "I" chord, so I enjoy retuning to G D G D and using open strings as drones. In particular, many tunings are "open," that is, emphasizing the root and fifth of a chord and not the third (e.g., in that G major tuning, there are only Gs and Ds, no B strings). This would suit a song that's marked by what Judith Kuhn calls "fourthy-fifthiness" (https://www.google.com/books/edition/Shostakovich_in_Dialogue/FiUxDwAAQBAJ?hl=en&gbpv=1&dq=fourthy-fifthiness+shostakovich&pg=PT122&printsec=frontcover), a song that itself de-emphasizes the third of the chord.
I also turn to alternate tunings for reasons of timbre. Slackening or tightening a string changes its tone, and in particular you can get a lot of resonance by having matched sets of strings tuned to the same relative pitch (e.g. multiple Cs and multiple Gs). These can then vibrate "sympathetically," meaning that if you play a C on one string, any other strings tuned to a C will vibrate if they're not being touched. (You might want to adjust your left-hand technique to make sure you're not unintentionally damping unused strings.) This can provide some of the resonance that marks instruments with sympathetic strings like the sitar or nyckelharpa.
Finally, you might use a tuning to allow actual musical gestures. As a violinist, I'm almost always using alternate-tuned open strings as drones. You might choose to tune a bass string to a note that will be used as a "pedal point" (i.e. if that particular note "keeps going" through several chords).