I'm trying to think of the opinion of E.T from back in the day. My memory tells me it got mostly "ok, but not great" reviews, which is fair. It was a little over-hyped by Atari. And most reviewers pointed out how important it was to read the manual on the game or it won't make much sense.
Me personally -- I remember buying it on clearance at Kay-Bee toys for under $5, and I had a lot of fun with it, especially given the price paid. I remember being blown away by the opening screen. That alone made me really give the game a chance. It was fun to try and complete the game, and it was cool to have another game that had an ending. I sure did like E.T. a lot more than "Raiders of the Lost Ark" (which I also liked).
It's mostly a case of Atari paying way too much for the license, giving the developer too little time, and then finally overproducing and over-hyping the game. Are these part of the thought process when saying "Worst game of all time"? Probably not. But maybe they should be.