I can't remember how that pilot was advertised exactly but there was an anticipation there as the first sequel to TNG. I presume they plugged Locutus doing his thing in the promotion, promising a series with action-action-action but what happened? The pace turned out to be quite different, much slower, more nichey, it struggled to find its feet so no wonder the audience dropped off.
I think this "anticippointment" theory is probably on target.
DS9 was a tough pill to swallow coming in the midst of TNG. TNG had a strong, mostly grounded captain. He let his guard down for the audience occasionally and with a few choice characters at times, but for the most part he was commanding, strong and steadfast. He loved Starfleet and everything it stood for. Sisko was crying in the first 5 minutes of the pilot episode. This was a man who was emotionally messed up, raising a son on his own, and had mostly misguided resentment for Starfleet. Sisko is seen as a man who is deeply flawed and has no desire to command or captain. He wants to go home with his son. Picard was loyal to Starfleet. Sisko's loyalty was to his son.
The Enterprise-D was huge, clean, exciting and attractive. All of the cool people wanted to be there. DS9 was rickety, falling apart, dirty and alien in the armpit of space. Only the scum of the earth wanted to be there at first.
Enterprise-D could move super fast and rescue the universe in the blink of an eye. DS9 struggled to move a few feet without falling apart. Some fans came on board with the Defiant, but most probably thought it was too little too late, or obviously trying to be TNG-light at that point (which it definitely wasn't, but perception is what it is).
Then there's the Bajoran religion, which was given lots of time in the pilot. I think religiophobia had a lot to do with the dislike of the series...though I personally think they could have done even more with it.
The aliens were relatively unfamiliar. Cardassians? Bajorans? Who cares about them! Where are the Klingons? Vulcans? Romulans?
It felt dark and heavy. Not very uplifting. Total opposite of TNG. Though there were some glimmers of hope at the end of the pilot.
I think the biggest thing for me, at the time, was the whole concept of a "star trek" series being stationary. As a teen, I just found the whole concept incredibly boring. That, plus the wooden performances at the beginning really turned me off. Later when I tried to tune in from time to time, there were so many serial elements that I had no idea what was going on.
I'm really glad I gave it a second chance a few years ago. Boy was I wrong about this series. It's truly the best.
TNG had a very bland pilot, for me. The acting was equally wooden and/or extremely overacted. I found some of Crosby's performance in the pilot totally cringeworthy.
Voyager was the opposite to me. It had the most promising pilot (for me), but unraveled into a very technobabble-heavy series that should have been (to me) more like what nuBSG ended up being, but wasn't even close.
I thought Enterprise had an interesting pilot, but a mostly forgettable first two seasons. Season 3 and 4 were like different shows altogether. They were fantastic. I especially enjoyed season 3.