Even if we can't rewrite everything up front, it's used to think about the problem at hand from first principles, ignoring the constraints of any particular language. From the vantage point "double dispatch" will never arise.
It's apparently not useful in languages with sum types, and it doesn't seem useful in an object-oriented language either (where you'd use the normal visitor pattern). So, when is it useful? The author doesn't have a practical example.
I guess there might be a language somewhere that has neither sum types nor methods? It's kind of a reach.
But, maybe it's useful educationally, as a good way to think about problems? That doesn't seem particularly likely either, considering that you can apply the visitor pattern just fine without knowing about Church encodings at all.
Disparaging "double dispatch" as "epicycles" while promoting Church encoding as "legit" just seems out of touch with how practical programmers think. I can't think of any reason why I'd want to use Church encoding to explain what's going on to a newbie.
Maybe there is some other situation where it's useful, but it's not explained.
I said it was useful theory. E.g. a way to shrink a programming language to remove data types so there's less drudgery in proofs about that language. That's not a regular programming task in the slightest.
"This post also explains how you can usefully employ the visitor pattern / Church encoding / Böhm-Berarducci encoding to expand your programming toolbox."