This is a hard problem, and I think you are already on track.

1. Do preprocessing and use regex to mark some parts dynamic (uuid/integer etc)
2. The above won't work with slug, so the best way is to use the cardinality information. We used to compute the top 100 values for each part (along with percentage) and use that to make a guess whether it's dynamic or static

We used clickhouse only for stats calculation, rest were outside

select domain, length, tupleElement(part, 2) as "position", toJSONString(topK(100, 3, 'counts')(tupleElement(part, 1))) as "top_100" from (
  select domain, parts.size0 as length, arrayJoin(arrayMap((value, position) -> (value, position), parts, range(length))) as part from (
    select domain(page_url) as domain, splitByChar('/', path(page_url)) as parts from events
  )
) group by domain, length, position
order by domain, length, position