Yes, you'd be treated as a new Ensign and take an O-1/2 billet out of training vice being treated like a lat transfer.
To expand a bit on the "not what you'd expect" stuff:
- It's more Jane's than James Bond. Lots of technical stuff about enemy capabilities and limitations, their behavior patterns, and so on. I think this stuff is really interesting, but I think some people believe that intel is all about thinking the big thoughts about political science. It isn't, and that is kind of disappointing for some.
- You will not do a tour with something badass like SEALs right out of the gate (or EOD or any exped stuff)
- You can very easily get a first tour in a squadron if you are passionate about staying close to the aviation community (but you'll spend a lot of time doing collateral duties like security manager)
I would have killed to take NFO if it had been an option when I NPQed from pilot. Go for that. Your profile says that you wanted helos, but do you really think that life would be terrible in a P-8 or the back of an -18 of some sort? I sure as hell don't. I love this stuff, and if NFO doesn't work out for you then I encourage you to go for intel. In all likelihood you'd be separated unless you had a scholarship or something.
But for now ask yourself, do you want to do cool stuff or make powerpoints about the cool stuff that other people do?