The movie itself is shit, IMHO. The experience of seeing it with a crowd that knows the responses, knows when to throw what, people in drag making out in the back row (fuck you back row! fuck you front row!)... that's what makes the movie great, not the movie itself.
For instance:
CROWD: DESCRIBE OPRAH WINFRY'S BREASTS!
NARRATOR: ...for it was thick, black, and pendulous.
Dr. Franken-furter is face down dead in the pool
CROWD: LOOK, IT'S THE COVER OF THE NEW NIRVANA ALBUM!!!
I think the appeal, at least for me, stemmed from my love for MST3k, where a bad movie can be made good by the wit of the people watching it. IMHO, of course.