I think what I liked about the theme songs for TNG, VOY and DS9 is that they could use a few notes or lines from the theme within the scores of individual episodes from time to time throughout each series to drive home an emotion or an idea, or even just a musical voice as we see a shot of the ship, or space station, or a Captain, or colleagues/friends in a touching moment. And for me, there was an emotional connection there to the theme songs in that way. They couldn't really do that with the "Faith" song without sounding like bad elevator music.
I think the opening few lines of the song have a pretty nice melody, and I really love what the cello does under "But my time is finally near" in the season 1-2 arrangement. They did use snippets of the theme orchestrally a couple of times in the first season, and there's an instrumental version under the end titles of the pilot.
Although I agree, "Archer's Theme" works better as a leitmotif.