I don’t know about other tests, but there is nothing remotely bullshitty about the National Geographic tests. They are solid science. However, most people who know something about their ancestry will probably learn little new from a DNA test.
The main limitation has been mentioned: the National Geographic tests only trace the exclusively paternal line (for men) and the exclusively maternal line (for men and women). This means, for example, that a man ordering both tests to learn about his great, great, great grandparents could only get information about 1/16th of them (one great, great, great grandfather out of 16 and one great, great, great, grandmother out of 16).
The other limitation is that they often tell you what you already know. For example, I have last name that is common in NW Ireland, I trace my paternal ancestry to NW Ireland, and I found out that my Y-chromosome is strongly associated with NW Ireland. Whoopee!!
On the other hand, if my Y-chromosome turned out to be strongly associated with Greece and my family used to have a Greek milkman, that would be interesting. If you think you might be a bastard, this test is for you!!
Results can also be interesting if they show that you have a rare type associated with a specific place.
One other way the test can be useful is if you have a exact match with another person who shares your surname. There is a decent chance such people are related within the time span covered by genealogies. If the person is not a known relative but shares a known relative with you, you may be able to substantially add to your known family tree.
The mitochondrial DNA test for female ancestors is less informative because it does not have a surname associated with it. In my case, I found that the mitochondrial type was fairly common in the general area where I think my maternal ancestors lived. Again, this isn’t very interesting. It could have been interesting if, for example, my known maternal ancestors were from the Barbary Coast and the haplotype was rare and associated Norway and there was a family legend of pirates and kidnapping…