Having recently started to participate in the Reddit OpenTTD community, I've found that even more new players there ask the question about best/must-have mods than the do here. So, I took some time to make my suggestions, which I'm copying here.
-----
I wouldn't say that there are any "must have" modifications. Plain OpenTTD by itself is very robust, although if there is a "must have" at this point, I would skip the stable version 1.3.2 and download a nightly instead, which has introduced the new Cargo Distribution (cargodist) patch. It radically improves distribution of passengers, mail and goods. It will move into stable OpenTTD probably in the 1.4.x version. But, still, good play can be had (and has been had since 2006) with the non-CD version.
Most NewGRFs have no problem operating with each other, and typically the few that do not expressly note which ones don't work with each other, if not going out of their way to disable themselves automatically. The only major issue you might have is that NewGRFs of the same type aren't balanced against each other. For example, trains from one set might be insanely expensive compared to trains from another site. Newer versions of NewGRFs are starting to provide options for better balancing, either through auto-detecting other sets or allowing you to modify the costs via parameter settings.
Here's my rundown for most popular NewGRFs.
INDUSTRY: It's pretty much either FIRS or ECS Vectors. Pikkabird's PBI/UKRS Industries is a distant third, but it's getting a major revamp in the near future. If you like the standard industries but want it tweaked just a little more, look at OpenGFX+ Industries.
PLANES: The most popular is Aviators Plane Set (AV8), second would be the never-finished-always-updating World Airliners Set (WAS). Both are compatible with all available industry sets. There's a few other small sets out there as well, mostly just eyecandy though.
SHIPS: NewShips is one of the oldest and most popular, and was recently updated to work with FIRS. FISH is becoming just as popular, and is developed by the same person who created FIRS, but it works with all the other industry sets as well. There's a ferry set and an old sailing ships set as well which are also compatible.
TRAINS: Most popular is probably 2CC Trains of the World, which isn't 100% complete but is pretty broad in the variety and time frame. The costs can be expensive, but you can modify them in parameters. UK Revival Set (UKRS) v2 is extremely vast in time scale and variety, and that's before you use the expanded Add-On set. There's also North American Revival Set (NARS) v2 and Canadian Trains for more North American flavor. The latter three share common developers and are fairly balanced against each other because of that. There are lots of train NewGRFs for regional action too: Germany, France, Netherlands, Finland, Spain, Australia, South America, Japan. Also, if you're happy with the default trains but want to see some technical and performance improvements, there's OpenGFX+ Trains. With the exception of NARS2, most should be compatible with all industry sets. Because of its age, I don't remember if NARS2 is compatible with FIRS, but you can combine it with another set to get some compatible rail cars that you can attach. I'd suggest Canadian Trains since they offer similar styles, but you might get a lot of repetition for engines. If that doesn't work for you, then I'd suggest OpenGFX+ Trains.
ROAD VEHICLES: eGRVTS is probably the most popular set. For more variety, check out HEQS and George's Long Vehicles. Lots of smaller sets as well, and some regional variety sets. There's also an OpenGFX+ Road Vehicles set that improves the default vehicles, including adding ECS & FIRS compatibility (thus you don't need to download ECS & FIRS Original Vehicles). The others should be up-to-date with industry compatibility.
AIRPORTS: Right now I'd only suggest OpenGFX+ Airports, which improves the default airports by adding 4-direction rotation. There are a few other sets which simply change the style of graphics, but because of changes in OpenTTD code, they're glitchy and/or broken, and don't have anybody to take care of them. I'm actually working with Planetmaker on improving and importing the graphics for most of these sets into OpenGFX+ Airports and expanding the set.
STATIONS: I can't really suggest one is any more popular than the other. Most station sets are designed around a region (Canadian Stations, Dutch Stations, British Stations, etc.). Industrial Station Renewal, the older Industrial Stations, and DWE Industry station sets all are focused on industrial themes. CHIPS and VAST are fairly popular as well.
With the exception of industry changes, none of these NewGRFs give you any sort of significant improvement other than visual and aesthetics. They're all just a change of pace from the vanilla. The REAL game changers come with competitive AI players (none I'd recommend over the other) and GameScripts, which give you new goals and challenges (again, none I'd recommend over others). Any single one of these will get you to up your game a bit, and almost all of them can be mixed and matched to get a really complex and competitive game. Or, you could go the opposite way and find AIs that serve exclusive tasks (like servicing only mail, only passengers trains, only airlines, only buses) and build your own cooperative game.
