To directly answer the title question, yes, non-business travelers can absolutely benefit from airline loyalty programs (i.e. earning points/miles as well as status.) Whether it's worth it for you in particular, though, will be extremely dependent on your particular situation and what options are available to you on the routes you normally fly, as well as whether you typically need to take luggage, etc.
Since specific examples were requested, I'll use myself as an example. I live in the Southeast U.S. and fly round-trips within the U.S. typically a few times per year and inter-continental round-trips about once a year. While there are sometimes slightly cheaper advertised fares to my domestic destinations on some of the very-low-cost airlines, by the time I add all of the fees and other associated costs of flying one of those airlines, they would often end up costing as much or more than Southwest or Delta. This is especially true due to the fact that the very-low-cost airlines usually have infrequent flight schedules that are much more likely to require me to take an extra day off of work than with an airline like SWA or DL where flights leave about every hour. So, in the end, Southwest or Delta usually ends up being the lowest actual cost to me and both earn points/miles on every flight that don't expire (Delta miles don't expire at all and Southwest points remain active as long as you have at least some account activity once every two years.)
As far as redemption value, this again varies widely and will depend very largely on your use case. For me, these two programs are my primary options due to where I live:
Southwest
In the case of Southwest, you typically earn 6 points per dollar spent on the cheap fares and can redeem points at a rate of 70 points per dollar of airfare. If you purchase the ticket with a Southwest co-branded credit card, you earn 8 points per dollar instead of 6. So, without their card, you get a dollar worth of airfare for every $11.67 spent on airfare and, with their card, you get a dollar of airfare for every $8.75 spent.
Delta
Delta's scheme is much more complicated and can range from much more valuable to much less valuable depending on your situation. For my situation, it's more valuable.
Earning miles
With Delta, you earn 5 points per dollar of airfare as the base rate. If you buy the ticket with a Delta co-branded credit card, that goes up to 7 points per dollar. If you additionally have status with Delta, you get an additional 2, 3, 4, or 6 points per dollar based on your level of status. In my case, I get 10 points per dollar total at my current status level (which came entirely from personal travel, not business travel.) Note that there is no bonus or penalty to this for different fare or cabin classes, but you inherently earn more points on more expensive fares since it's based on dollars spent. So, if I buy a $200 round-trip fare, I earn 2,000 'miles' regardless of fare class, distance actually traveled, etc.
Redeeming miles
While miles are earned on a dollar-cost basis, mileage redemption with Delta is (loosely) based on an award chart, with mileage cost depending on region traveled to/from and demand for that particular route (i.e. usually only a few seats are available at the lowest mileage costs on a given flight.) If you book early, domestic round trips usually start at 25,000 miles (sometimes 20,000, but this is less common.) If you book a $200 round trip for 25,000 miles, that's obviously not a great value (less than 0.8 cents/mile value.) However, the 'regions' are quite large, with the entire U.S. and Canada (excluding Hawaii) being a single 'region.' So, if you book a $500 trans-continental round-trip for 25,000 miles, that's obviously a much better value (2 cents/mile, which is equivalent to $1 of airfare earned for every $5 of airfare purchased at my earning rate of 10 miles/dollar.)
The best redemption values I've seen on Delta are for long-haul business class flights with 140,000 miles getting you a business class ticket to the other side of the planet that would normally cost $7k if bought with cash, a 5 cent/mile value. So, if I'm earning 10 points per dollar of airfare and redeeming for 5 cents/mile of value, that's 50 cents per dollar originally spent (i.e. $1 of airfare earned for every $2 of airfare purchased!) In my case, this is exactly how I prefer to use the points, as I'm not really a fan of flying economy (and, thus, getting no sleep) on routes that involve 30-36 hours of travel time (20-22 of that actually airborne) each direction.
Conclusion for a non-business traveler
If you only ever travel short distances, always buy the cheapest tickets, and don't earn miles separately from credit cards, then you're probably never going to get up to 140,000 miles. However, 25,000 miles is certainly very attainable for the non-business traveler, since the points don't expire ($2,500 of total airfare purchased at my earning rate.) An extra $500 of free airfare for every $2,500 spent isn't a bad deal at all, even if it takes you a few years to buy that much airfare.
An additional aid to those who don't fly as often is that, with both Delta and Southwest, there's no penalty for booking just one way with points vs. booking a round-trip with points. So, using the trans-continental Delta flight example, you could get one way for 12,500 points and just pay cash for the other way and be able to get your first redemption after spending only $1,250 while getting the same value per dollar spent.
So, yes, there are definitely cases where it's useful for a non-business traveler to care about points. However, whether that's the case for you or not will be highly dependent on the flight options available where you live, the relative cost of those options, where and how often you tend to fly, and what the terms are of the frequent flier programs of the airline choices you have available. As you can see from just the two examples I've listed, the terms for different frequent flier programs and their relative usefulness to you can vary significantly from one airline to another (and that's before even considering elite status.)