The Conquistador is fairly heavily designed in favor of Terra or any other map type where going off and exploring a New World equivalent is a major element. The idea is that, come the late Medieval era, the Old World is filling up, and the age of new exploration is approaching (and as Spain, you should definitely have an extensive recon fleet by this point). Since it's Medieval, you should certainly be able to use Conquistadors first for homeland battle if needed, and get them nice and experienced, but then, once focus shifts overseas, they're basically meant as a companion piece to Caravels. Send the ships over first to get a lay of the coastlines, then bring in the Conquistadors to basically own the New World. It's a fairly well-laid out process for them: send out a fleet of their Knights that can withstand any stray barbarian Triremes that may be running about, heavily explore the new continent to find prime city locations, particularly any with Natural Wonders, conquer any native city-states that may be taking up those locations with that nice absence of city penalty, and complete the Conqustador's role by going ahead and using them to settle those spots you've laid out. On Continents it's a bit less potent, given there's actual civs there and not just city-states to conquer, but it's still possible, and in the early Renaissance there's a chance opponents haven't settled near Natural Wonders yet, especially normally weaker ones like Old Faithful, the Barringer Crater, and the Grand Mesa.
IMO it's one of the more interesting and dynamic UUs, and super appropriate to the civ it belongs to, alongside being crazy potent for a particular strategy you would already try to pursue as Spain, but even useful for the Renaissance in general should you receive it from a CS.
Plus it's not THAT much more expensive than a Knight, it's only more by 15 points of production. In any good city that's two or three turns tops worth of time, and their placement means you can produce them or upgrade Horsemen well before their intended purpose is to come into play.