Why was the Boeing 747SP unsuccessful?
It just was not a great fit for the use case it was built for at the time it was released:

The Boeing 747SP was hit by the same problem that many airlines have
suffered from throughout history… rising fuel costs. The aircraft was
heavy, didn’t carry as many passengers as a normal 747 and was
expensive to run (four engines after all). Boeing had expected to
build up to 200 of these aircraft, but their dreams were never
realized.
Additionally, by the early 1980’s, other aircraft started to arrive
that could easily perform the same route and provide a better
passenger experience. The last commercial flight of the Boeing 747SP
flew in 2006 for Iran Air. As the country is currently under embargo
and spare parts cannot be flown in from Boeing, it is likely that they
retired them out of necessity, rather than just replacing them with
something better.

It was a clunky 4 engine fuel hog, which was not great with rising oil prices

The aircraft, despite its long range, was not popular with airlines,
and only 45 were built. This was mainly due to its four-engine
configuration, which meant high fuel usage and higher costs for
airlines, making it difficult to break even on ultra-long-haul
flights. Shorter flights weren’t profitable either, as the 747SP’s
high fuel capacity added weight and increased fuel burn on short hops.
The aircraft’s failure was not due to a lack of effort on the part of
airlines.