- Is this as good as it sound? No trick? No additional fee?
Yes, no tricks, no additional fee. However, it is only valid in regional trains, not in long-distance trains like IC, EC, ICE, night trains, and Flixtrain.
- Are tourists allowed to buy this too?
Yes.
- Are trains that accept them going to be totally packed or not available due to overcrowding?
Especially on weekends and during summer holiday time (which is July and August) you can expect especially trains in tourist regions to be packed (e.g. the trains to Sylt, probably also trains in the Alps region and around bigger cities)
- Can a family plan a whole trip around them? I'm aware they exclude certain higher speed trains, but they still seem like a steal for, say, visiting a region like Cologne, Bonn, Dusseldorf and travel between these cities and within the city.
A family can certainly plan a trip around them, with the limitations that you already mentioned. If you e.g. take a long-distance train with a separate ticket to the Cologne region, you can then use the 9-Euro-ticket to take buses, trams, subway, etc. around those cities and the region.
Personally, I wouldn't use the ticket to do longer trips, since by only using regional trains you would travel for several hours and have to change trains more often (e.g. from Frankfurt to Berlin it would take around 9 hours, instead of 4 hours with a direct ICE train).
A little bit more explanation about the fact that Mark already mentioned in his answer: some IC trains are also classified as regional trains. As Mark mentioned, in those trains, the 9-Euro-Ticket is usually not valid, however (to add even more confusion), DB has reached an agreement with some states for some of those connections, so that the 9-Euro-Ticket is valid.
One example of such a train, where the ticket is valid:

Unfortunately, the relevant description is not translated, it's this sentence:
Das 9-Euro-Ticket ist in allen RE-Zügen der DB Fernverkehr AG Stuttgart, Singen und Konstanz gültig
which means
The 9-Euro-Ticket is valid in all RE trains of DB Fernverkehr AG between Stuttgart, Singen and Konstanz
Another example, where the 9-Euro ticket is not valid on such a train:

Again, the relevant description is not translated, it's the sentence:
Das 9-Euro-Ticket ist in diesem RE der DB Fernverkehr AG nicht gültig
which means
The 9-Euro-Ticket is not valid in this RE of DB Fernverkehr
You can also check a specific connection if the 9-Euro-Ticket is offered. E.g. in my first example, if you click on the 9 Euro price offer, you see that it's that specific ticket which is offered for that connection, and then the 9-Euro-ticket is valid. If it's not offered for a specific connection, it's not valid (like in the second example, where the 9-Euro-ticket is not offered for the RE train).
The screenshots are taken from the German rail planner, which is one of the better train planners in Europe, available in several languages.