Hmm, the problem is that you've played it at the wrong time, I think. If you play it now (especially the real version on the 64) you'll think "funny framerate, some slightly blurred graphics, a bit of a daft story...some other things."
At the time, it was a great step up from Goldeneye. You had a good singleplayer with challenges to do, such as the firing range, where you could unlock awesome weapons. The game had awesome cut-scenes, voice-acting, and a nice futuristic style to it. Some of the early levels were brilliant, although the story did trail off towards the end.
The best part of the whole thing is the multiplayer. It absolutely obliterates Goldeneye in pretty much every way. Notably, you had up to 8 bots which you could play with, as well as 4 friends. The best fun I had was having team matches with the bots. Another great thing is that they took the Goldeneye control scheme and ramped it up, so you had secondary fire modes and you could even give instructions to the bots, such as telling them to guard the case (In capture the case, like capture the flag), telling them to target certain players (awesome if you're playing against friends, haha) and other things.
At the time, it was a brilliant experience. It pushed what the N64 could do to the max. I remember just putting the game on and thinking fuuuuck, this is a huge step up graphically and audio-wise to most other games I had played on the console.
Like I said, if you play it now, it'll seem a tad out-dated. The multiplayer still rocks in my opinion, but first person shooters have kept evolving, and you must remember that this game is two generations old.