Concorde failed mainly due to economy of scale and legal issues. It was profitable on a per trip basis; people were willing to pay a large premium for the service, and Concordes flew for almost 30 years. A huge issue was that the program ended up costing over a billion dollars to develop them, and only about a dozen were built. Since only a few were made, maintenance costs got out of hand. Air and noise pollution limited them to overseas routes, limiting the appeal of developing new supersonic airliners.
Basically, the issue wasn't that people were unwilling to pay, just that the market for it was too niche.
I suspect the hypothetical story of Musk's dildo transit would be the same. There'd be enough people willing to pay to cover operational costs, but all the issues of regularly launching/landing fucking rockets in/near population centers would make it unattractive from a business standpoint.