It seems like the ship was terminated due to being off-course (which couldn't be corrected once its propellant was burned through). It succeeded in reaching space, it seemed to reach almost-orbit (they deliberately made it slightly slower, to avoid becoming a giant piece of space-junk if they happened to lose contact/control), so they've almost got it working as a traditional rocket.
All of the crazy stuff they're planning, like returning and catching the booster, having the ship re-enter and land/get-caught, in-orbit refuelling, etc. will probably take a while to get right; but those are all "bonus features" that other rockets/companies don't bother with. I imagine it will be launching satellites pretty soon (next year?), at least in-house Starlinks, since refuelling, re-entering, landing, etc. can be done after the payload is safely deployed; and returning/catching the booster only takes place after safe separation (as we saw today, when the ship kept going after the booster's spectacular demise)
It will take a while to human-rate it. I imagine we'll start by seeing crews transferring to Starships that launch uncrewed (e.g. like the HLS for Nasa); eventually we'll probably see crew launch, once it's established a decent track record. I'm unsure if we'll ever see people in the landing flip; I'd rather eject beforehand and parachute down!