As you've rightly identified choosing the right loyalty card can be a real earner to top up points or miles. But the word is loyalty card, typical sign up might give you enough points for a night at a top chain hotel. Or 2 nights at a lower demand destination. These cards top up the points from your loyalty activity. But to accumulate points you do need to "buy into" a scheme.
Take Marriott Rewards, as a base member you get 10 points per $ and a week at a Miami Beach Marriott will cost 270,000 points. If you stay away one week every year in other words 7 nights a year at a rate of $150/nt you'll need to stay 180 nights to get your free week, so it'll take 25 years to earn the points. Let me tell you, by that time the depreciation of points will mean you'll still be a years away from that free weeks holiday! But a 75 night per year Plat will get 10 points per $ and 5 extra per $ for being Plat, 5 extra per $ with his credit card, 500 welcome points each visit and 100,000 from his 2 megabonuses each year, so his $150 per night stays with 55 one night stays and 10 2-nighters will make 360,000 points, enough not merely for a week but actually for 10 nights. And all done in one year!
These loyalty programs work well for regular travelers, on their bosses dime, not those travelling only once a year looking to get a free holiday on points after a few years. As a once a year traveller your best bet for value is to get the best price for your stays as you take them and a good place to start searching for value is at the comparison sites, like Kayak or Trivago.