Why it happens
An invitation to revise is not a commitment to publish. It means the editor thought the paper could potentially reach the bar, and asked you to try. Journals state this in the decision letter, though it is easy to read past when the news is good.
The most common cause is that the revision did not address the substance. Authors under time pressure sometimes answer the easy comments thoroughly and the hard one briefly. Reviewers notice, and a response that argues with a central criticism rather than resolving it often converts a revise into a reject.
Second, new reviewers. If an original reviewer was unavailable, a replacement reads the revised manuscript without having agreed it was promising, and may raise issues nobody raised in round one. This feels unfair. It is also common and, from the editor's side, unavoidable.
Third, the revision genuinely revealed a problem — an additional analysis the reviewers asked for produced a result that undermined the original claim. That is science working as intended, however unwelcome.
An invitation to revise is an invitation to try, not a provisional acceptance. Journals say so explicitly in the decision letter.
Is this worth appealing?
This is one of the situations where an appeal is more often justified than usual, because you invested substantial work at the journal's invitation. The strong grounds are the same as always: demonstrable factual error, or a new reviewer raising a point that contradicts what the first round explicitly accepted.
That second scenario is worth documenting carefully. If round one told you a particular design choice was fine and round three rejected the paper for that choice, the editor should hear about it, quoted precisely from both reports.
What will not work is arguing that the effort you invested entitles you to publication. Editors are sympathetic to it and cannot act on it.
Salvaging the work
The revised manuscript is substantially better than the one you first submitted. It has been through two rounds of specialist scrutiny, gained analyses, and tightened its claims. That is a real asset and it moves with you.
Take the full record — original reports, your responses, the second-round reports — and build the strongest version of the paper from it, then choose a journal that fits what the paper turned out to be rather than what you originally hoped it was. Revisions often change a paper's natural home, and the venue you first targeted may no longer be the right one.
One practical note: keep every version and every report. If you later transfer within a publisher, or a journal invites prior review history, that documentation shortens the next process considerably.