You need to really love where it is and want to spend much of your free time there.
To the extent the money becomes not relevant,
We had friends who had one and we didn’t understand it, just saw huge expense, but they loved it and saw it as a second home. It was lovely though, three bedrooms, two bathrooms, kitchen, living room, little dining area, decking, but was still very much a caravan, you’d never have though you were in an actual house. They fully kitted it out with sofa, tv,curtains, all kitchen stuff, lots of little knick knacks, furniture for the decking etc.
I think renting it out becomes bit like you own a time share. You can’t leave all your stuff there, personalise it, you just become like any other renter except it’s more expensive for you, so kinda defeats the purpose really, and you can’t impulsively decide to go or stay longer if you’ve rented it out.
So I’d say pick somewhere you love and want to spend your weekends for the next twenty years, and only do it if you can afford it and don’t need to get into renting it out to randoms, but can turn it into your own home from home.