But is a 'great' tune a tune that is publicly successful, or a tune that doesn't become a hit BECAUSE is is so of the wall and unusual (and great!)?

Perhaps you are better off asking what makes a tune a 'standard'?

There are so many different opinions on whether a tune is 'great' (some like simplicity, some like complexity, some like a bangin' beat!), but much more consensus on whether it is a 'standard' (there are many standards I don't consider 'great' tunes, but I agree they are 'standards'!).