If the question was "why wasn't it a mega hit back in 2008" then the answer is the PS3's price and install base. The console hadn't had its resurgence yet.
On a broader level, I think the game's art style is attractive, but the tactile texture-heavy look can be less inviting and harder to communicate than Mario's cartoon art style.
On the point of it not being a hit back in 2008, I'd like to add that console 2D platformers were still kinda dead in the water at that time. It would take NSMB Wii a year later to really bring console 2D platformers back into the mainstream via its "psuedo-retro" approach.
I think the reason it was never massive is that if you are not into creation, the gameplay and content isn't enough to really grab you and user created stuff is just not worth the trouble to sift through when there are plenty of solid ready made games to play. On the creation front seems like 3D building stuff like Minecraft has really been the preferred avenue for a lot of people who pour time into that stuff.
As a big fan of the series, I think this is also true. I do remember revisiting LBP largely for completing the story and playing some of the user-created levels (usually the popular ones that were mostly clones of a specific type of game, like others have said). As for making the levels themselves, I didn't put much time into that either (and the levels I did make were nowhere near as elaborate as the showcased user-created levels); and that too unfortunately shows in the shared levels-Sturgeon's Law really does come into effect when you truly look into it (though MM Staff Picks does help alleviate that for a bit). And the playtime I put into LBP2 was nowhere near as much as I did to LBP1-compared to completing the story mode 100%, I've only done what, one playthrough of LBP2's mode, and I've spent much less time creating and playing user-generated levels. I've been thinking about finding time to revisit it, but even then I'd also have to stomach sitting through the hours of continuous patches MM usually deals out every now and then.
3 came out, that's what went wrong. The series was excellent prior to that.
I do agree with this part though, when looking at LBP from a critical reception standpoint. MM's first two games were fantastic, the PSP and Vita entries were only a few bars down the ladder, and even LBP Karting, while technically being a reskinned ModNation Racers (which I also love, but haven't really put much time into), was still a good spinoff.
Sumo Digital's take with LBP3, however, seems to be that its design brief was a mission pack sequel to LBP2, with its focus on platforming and refinement of the existing tools, rather than a big leap like LBP2 was (create a level that could effectively be of
any game genre), and even then its execution wasn't refined enough (bunch of bugs and glitches galore upon its initial release, and the game design --the implementation of the Sackimals being one of the more noticeable points-- not being up to par with the MM additions). From what I've heard still a good game (and I still plan to get it for PS3), but is really a step back as a sequel to the first two console games.
I'm really going to be disappointed if LBP3 is the note Sony/Media Molecule is going to leave this series on, because I really, really do love this series. Presumably (and preferably in my view), a PS4-exclusive third MM LBP title would give it a genuine leap into 3D movement and controls; it is in my view the obvious (final?) frontier the LBP formula has yet to expand into.
EDIT: As for the bits on the game's physics, it personally never bothered me in most cases (I only remember getting frustrated with how the game could sometimes lag over online connections, upon which the floaty jump physics were REALLY noticeable), but I do remember critics listing it as an inconvenience to the experience. So I take people's word for it that it turned a lot of people off from the game.