First of all, theory doesn't explain "why" something sounds "good". That will be down to your experience, listening habits, cultural background, personal tastes, etc.
Theory is only about labelling and describing certain "common practices" in music, and attempting to lay out patterns and connections. It really is mostly about the "what", partly about the "how" and "when", but not about the "why" at all.
This is a typical example of a practice that's been part of rock since its beginnings back in the 1950s, and a fundamental defining element since heavy rock began in the 1960s: known as "borrowing from the parallel minor".
Standard "rock theory", IOW.
The principle works as follows: "When you are in a major key, you can also use any chords that belong to the parallel minor key (the minor key with the same tonic)."
In this case, the key is D major, and the F chord comes from the key of D minor. (If that "explains" it well enough for you, then that's good!)
It's actually a majority of rock songs that will follow this principle to some extent; a minority that adhere to "chapter 1" of the classical "key theory" book , where every chord is harmonised from the same major scale.
The only governing principle of "key" (in any kind of music) is that you should be able to tell which chord in a progression is "I". That "tonic" chord will then be either major or minor (that's its "tonality"). There is no rule that then determines what other chords or notes must be used; only some "common practices" in each case.
It's certainly common that - in a major key - you will also find major IV and V chords; because they are the main factors that actually make the "I" chord sound "tonic". (That, and/or a fairly consistent repetition of the I chord.) The 3 minor chords (ii, vi and iii) might also make an appearance.
But other chords - usually implying other (secondary) scales or keys - may well be introduced; they will add variety, interest or surprise, but usually without disturbing that sense of a central "tonality".
So it's a defining element of "rock theory" to borrow from the parallel minor. In classical and jazz theory, another defining element is "secondary dominants" - the principle that any chord in a key (not just the I) can have its own dominant chord before it. (And that's before we get into the jazz practice of chord substitution, or classical concepts like "augmented 6ths" or "neapolitan" chords.)
Getting back to this tune... You rightly spot that the melody notes in the verse suggest the G major scale.
But the "key" is not G: my ears at least tell me the keynote is obviously D. ("Key" is a matter of sound, not of pitch collection.)
So we have a "key of D", but one with a C natural in the scale, and (in the chords) a mix of F# and F natural.
What type of music do you know that has a b7 in a major key, and an occasionally flattened 3rd?
No prizes for the answer: blues.
We all know that rock music evolved from the blues, and this is one of the results of that: the habit of flattening the 7th and 3rd of a major key, even when the rest of the music (as here) doesn't sound particularly bluesy. (Of course the 3rd isn't always flattened, because then it would a minor key! )
Blues is now a basic part of western music culture, which is why these kind of alterations of a major key sound quite natural, even predictable. (In fact, although I'm no classical expert, I wouldn't be surprised to find similar practices in some classical music. It's certainly common in classical music to switch between parallel major and minor keys, but I don't know if the two would be combined at the same time, as they are in rock.)
The effect of these borrowed chords is to make the major key sound "heavier" or "darker". Rock taste is not too keen on the unrelenting brightness of the plain major key. It's OK for ballads, or for more folksy kinds of sounds, but when we want to "get down and dirty", we want those darker sounds from the parallel minor. We don't want to go all the way to minor - making the tonic minor - because we like the tonal strength of that major I. (We still like minor keys as much as anyone, for their moody intensity, but they generally have a "weaker" sound. The major key with borrowed chords has a useful "toughness": not too bright or happy, but not too introspective or sad either.
As for definitions or labels, if we just went by the melody (G major scale with D keynote) then we could describe the verse as being "in D mixolydian mode". But the F chord doesn't belong to that. So it's "D major with a borrowed bIII chord".
The prechorus confirms the key of D major by introducing the V chord (A) (and a passing Bm7 before the G), while still retaining that "heavy" F before going to the chorus.
The chorus is the same D-F-G chords, but with different phrasing (2 beats on D and 4 on G, rather than vice versa).
You could argue that the emphasis on G in the chorus makes G sound like the key chord; so it's a V-bVII-I in G, rather than a I-bIII-IV in D. It's really up to your ears to decide that. Mine tell me D is still key chord, because I think by this point D has been well established. But if you took the chorus on its own, you might well hear G as key chord.
At the end of the chorus we get a more unusual chord. There is a two-bar phrase (on bass and sax at least I think) that ends on a B. This is the sound that stands out of the track for me. The line is as follows:
The B note is of course diatonic to D major, but it sounds "off" because t follows so soon after the strong F note; so we get the effect of a tritone. (If we imagine a chord to fit the whole bar, it would have to be something odd like Bdim, Bm7b5, or a rootless G7: the last 4 notes are actually a G7 arpeggio.)
|1 . . . 2 . . . 3 . . . 4 . . . |
IOW, to my ears, it's the F that makes the B sound strange, rather than vice versa as we might expect (given the key of D major).