What is so special about this tune?

The instant appeal of the music might be explained in the human's pursuit for balance. Speaking from a musicological point of view we are dealing with almost perfect symmetry and intricate harmonic layering

detail of engraving from Lambranzi's 'Nueva e curiosa scuolade balli theatrali' (1716)
thanks to Dell Hollingsworth, former artistic director of La Follia Austin Baroque for using the image

Out of a passacaglia over 8 bars, in which the eight bar connected to the first one, Lully developed the Folia to a sixteen bar structure. Now the variation no longer ended on its dominant but on its tonic. Because of this the final eight bars can be mirrored onto themselves; the first eight bars can also be seen as the reflection of the final
Every two bars carry a movement from a tonic to a dominant functional chord: namely from I (d-minor) or III (f) to V (a) or VII (c). For the III chord is the mediant of the tonic chord and is able to replace it in that capacity. The same applies to the VII chord (c) which is the mediant of the dominant chord (a).
The minor and major scales alternate every four bars. The listener experiences the first four bars as in a minor key. This mood changes into major with the introduction of the III chord (f). This constant fluctuation of chord functions, keys and cadenzas, results in a fascinating duality.

By far the most important reason is that it is very pleasant music to listen to. There is somehow an instant appeal for listeners and composers alike. It even sounds familiar when you have never heard the piece before. The theme is very recognizable too (at least in the variations before the 20th century) so the listener feels right at home and will be able to focus on the nuances in the technique of the variations after the theme.
Being captivated by the tune is of course a matter of personal taste, for there remain plenty of people who get bored by hearing only a couple of variations like Robert Greenberg who continued after an acoustic fragment of the Folia-variations by Vivaldi in a lecture about ground bass, where incidentally he is persistently pronouncing Folía as Fólia:

You will be very happy that we ended after six variations because Vivaldi does not create a larger argument out of these variations. Each one will sound isolated from the next, not a single variation seems to build up or lead to the next one. Yes they get a little faster as we got along, but there is no sense of cumulative variations of somehow developing the music in a singular direction.
Rather each of these variations seems like a self-standing miniature, essentially unconnected to the next in terms of either mood or momentum. And as a result, after six or seven of these small slices of musical life we will have our fill. Ok, I got the point, I have got to go take a shower, I have got to pluck my eyebrows. I have got things to do.

From 1672 up to now the theme has not lost its power. More than 50 leading composers have been inspired to write variations on it for various instrumentations.
Composers are always looking for something that will give a piece instant flavour and character. La Folia variations provide lots of freedom too because there are no fixed numbers of variations to complete nor has there to be any structure between the variations. Some composers seem to be addicted to write variations. Pasquini and Trovesi wrote three sets of variations for instance. And as Miles Hoffman explains that there is the aspect of challenge for composers:

Composers knew that everybody knew this tune. So there was the question of ok, let me show you what I can do with this tune. You know this tune but you have not heard what I can do with this tune!

So you can imagine that the Folia-variations evolved into a sort of a masterpiece for the guild of performers that Robert de Visée, one of the leading composers for the guitar felt obliged to defend himself in the preview of his Livre de Guitarre (1682) why he did not include any Folia.

piano roll of Corelli's 'La Folia' in an arrangement of Spalding accompaniment only played by Andre Benoist dated back to 1921

Because of the vast repertoire it is possible to deduce the course of musical history from the various styles of these variations. There is a world of difference between the variations by Sanz (1674) for rizzio five string guitar and the massive symphonic study in orchestration by Salieri (1815). The spirit of the age is undeniably present in these variations, despite the fact that the theme itself remains untouched by the wear and tear of time.
Besides the various styles and instrumentation there are also regional differences. Like the Finnish folk-fiddle tune using the Folia-theme in 'Lampaanpolska' which originated from the musical culture of popular fiddle playing in Finland. Miles Hoffman made use of a nice methaphor for these national or regional characteristics:

It actually reminds me of this new 'Euro-coin'. On one side it is the same, but on the other side, for each different country where it is minted it has a different reverse. I guess you could say the Folia is a little bit like that. It took on those different characteristics and different names.

Finally there is the interesting social phenomenon that the music had its roots in popular culture and developed into a stately court dance; from a fertility dance for the people into a courtly flirtation.

best viewed in 800 x 600