Per-guide browser visit/click funnel with separately attributed installs.

Browser visits and CTA clickers share a visitor id, so visit-to-click is a meaningful rate. Installs are distinct machine ids attributed by guide ref in the same time window, NOT a cohort joined to browser clickers. Never divide installs by clicks. Direct-DMG installs carry no ref (R2 is ref-agnostic).

The guide identity is read from path FIRST and from ref only as a fallback, because ref is being handed over to acquisition channel on page_view in lmcp-web: a visit would start arriving as ref='web-direct' and this report would read visits=0 / clicks=N per guide — not a shifted number, a broken conversion rate that looks like the traffic died. Clicks keep ref='guide-<slug>' on purpose (content credit), so they resolve through the fallback and the same expression serves both.

Reading both shapes is what lets this ship BEFORE the emitter without a zero window: measured against production 2026-09-18, 30 days, 0 of 8176 guide page_views carried path at all (it is not even a key on them), while 8172 carried ref='guide-%'. Control for that zero: (none)-source page_views were 5875 of 5877 WITH path, so the field was visible to the query and the guide zero was real. Drop the fallback only once path coverage is measured to be complete. Checked against production the same day: the old query and this one return the same 216 rows with 0 rows differing. The totals themselves drift with the rolling window (5383 visits / 949 clicks on one run, 5382 / 948 an hour later) — the invariant is the zero, not the sums.

Both branches are guarded by an anchored match instead of a bare prefix strip: a path of /en or a ref of web-direct would otherwise survive the strip unchanged and be grouped as if it were a slug, inventing a guide out of a homepage visit — which is the very confusion the emitter change exists to end.

The fallback fires when path is ABSENT, never when it is present and does not match, and the difference is not stylistic. 73 of the 95 guides link to /download?ref=guide-<slug> in their body, so production already carries 173 page_views in 30 days whose ref says guide-* on a path that is not a guide (139 on /download, 16 on /en, 15 on /de/download, 2 on /enterprise, 1 on /es/download). A COALESCE would treat "did not match" as "try the ref" and count every one of them as a visit to the guide that sent the user — failing in the direction that inflates, and crediting a real slug, which is what makes it hard to see. The source='guide' equality also excludes them today, because the emitter only sets source when the PATH is content; this CASE means the report does not depend on that second, separately-owned guarantee staying true.

source='guide' still scopes this (the edge stream is guide-edge, see :1767); that exclusion is load-bearing at ~6.4x, so it must not be relaxed to guide%.