[Dashboard] Track dashboard usage via a query-attributable marker (read-path analog of the plugin's Write API trace_id)

[caohy1988]

Part of #404.

Request

We have no signal on whether a published dashboard copy is actually used —
how often it's opened, by how many people, or what it costs its owner. The
only cost evidence today is point-in-time bytes-billed measurements (#401,
#377 section H). This issue adds user-side usage tracking for the
dashboard's read path, modeled on how the BQAA write path already solves
attribution.

The pattern to reuse: the plugin's Write API trace_id

The ADK BQAA plugin stamps every Storage Write API append with a
client-identity trace ID
(bigquery_agent_analytics_plugin.py:2592-2601 at 74e7167,
class at L4134):

req = bq_storage_types.AppendRowsRequest(
    write_stream=self.write_stream,
    trace_id=f"{trace_id_prefix}/{__version__}",   # e.g. google-adk-bq-logger/1.x
)

Every write is attributable to the producer and its version, without any
side channel. The dashboard's read path has no equivalent marker: its
jobs run with the viewer's credentials in the viewer's billing project, and
nothing distinguishes them from any other query against the same table.

Proposed mechanism (read-path analog)

• Version-stamped marker in the template query. Add a stable,
  distinctive marker to sql/events_v1.template.sql — e.g. a leading
  comment /* bqaa-dashboard/<events-schema-version> */ — so every job
  the dashboard issues carries it in the job's query text.
  Verify first that Looker Studio preserves SQL comments in the
  jobs it issues (check INFORMATION_SCHEMA.JOBS.query against a live
  copy); if comments are stripped, fall back to a no-op literal the
  optimizer keeps (to be prototyped). The marker must not touch the
  sentinel bindings (sqlReplace list unchanged) and gets pinned by
  the existing generated-artifact drift tests.
• Corroborating signal, no template change needed: Looker Studio
  stamps its jobs with the requestor: looker_studio job label —
  usable today, but it cannot distinguish this dashboard from any
  other Looker Studio report on the same table, which is why the
  marker is the primary key and the label the cross-check. Verify the
  label's presence/name against a live copy rather than assuming it.
• A documented usage query (docs + an oracle-style SQL file) over
  region-<X>.INFORMATION_SCHEMA.JOBS_BY_PROJECT, filtered on the
  marker (and/or label + referenced table): queries/day, distinct
  principals (user_email), total/percentile total_bytes_billed,
  error rate, cache-hit rate. This is the read-path counterpart of the
  [Dashboard] Date range: default to 90 days and make one control apply to every page #401 bytes-billed measurement, repeatable by any installation owner.
• Docs: a "Measure your dashboard's usage and cost" section in the
  dashboard README, linking Looker Studio dashboard: UX/design/analytics improvement backlog #377 section H's operating guidance.

Privacy boundary — explicit non-goals

• No phone-home. All usage data stays in the installation owner's own
  project; nothing reports to Google or this repository. The configurator
  page keeps its no-backend property ([Dashboard/configurator] "Create my dashboard" flashes a "page not found" error before the Looker Studio report loads #398/[Dashboard/configurator] Pre-empt the Looker Studio authorization prompt — users cannot tell the page is legitimate #399/[Dashboard/configurator] Align the configurator page with Google Cloud brand style #400) untouched.
• No new collection. The mechanism only makes already-existing job
  metadata attributable; it adds no fields about viewers beyond what
  BigQuery job history already records in the owner's project.
• Repo-side adoption metrics (how many installations exist) are explicitly
  out of scope for this issue; if ever wanted, that is a separate
  opt-in discussion.

Coordination

Changing the template's custom query requires an editor session and a
republish. #401 is currently the batch's only planned republish — decide
whether this marker rides the same session (one republish event, per the
#404 plan's sequencing preference) or waits for the next scheduled one.
Either way the full #404 publication gate applies (all nine
live_template_verification methods, the 3×3 protocol, propagation probe,
attestation re-dating last).

Acceptance criteria

• An installation owner can answer, with one documented query in their
  own project: how often their copy was queried, by how many distinct
  principals, at what bytes billed, over the last N days.
• The marker survives sqlReplace hydration unchanged and is pinned
  by the generated-SQL drift tests.
• Comment-survival (or fallback-literal) behavior is verified against
  a live copy and recorded here before the template changes.
• No data leaves the user's project; the configurator remains
  backend-free; the docs state both explicitly.
• If the template changes, the republish passes the full [Tracking] Looker Studio dashboard — first-run field feedback (Aug 2026) #404
  publication gate.

Related

• [Tracking] Looker Studio dashboard — first-run field feedback (Aug 2026) #404 — tracking issue (this is new backlog, not in the current
  execution ledger).
• [Dashboard] Date range: default to 90 days and make one control apply to every page #401 — republish coordination and the bytes-billed measurement this
  generalizes.
• Looker Studio dashboard: UX/design/analytics improvement backlog #377 section H — query-cost operating guidance.
• google/adk-python bigquery_agent_analytics_plugin.py — the write-path
  precedent (pinned links above).

[caohy1988]

Addendum: service-side (Google-internal) tracking channels

The mechanism above is deliberately user-side. For Google teams, three service-side complements exist, ordered by governance weight — recorded here so the public issue reflects the full design space:

1. Write-path adoption — available today, no repo change. Every BQAA append already self-declares AppendRowsRequest.trace_id = "google-adk-bq-logger/<version>". Aggregating Write API traffic by that prefix (projects, streams, volume, version mix) measures plugin adoption from request metadata alone — no customer content involved. It does not measure dashboard usage.
2. Copies created — the cleanest dashboard-adoption number, and it is Looker Studio-side, not BigQuery-side. Every configurator click invokes /reporting/create?c.reportId=<our fixed template id>; a count of Linking API creations referencing that report ID yields copies-created without touching any customer query content. Internal ask to the Looker Studio team.
3. Read-path usage at aggregate — requires this issue's marker first, then internal privacy review. Query text is customer content; any internal aggregation over the bqaa-dashboard/<version> marker would be aggregate-only and privacy-approved, never per-customer inspection. This is the only channel that measures actual dashboard usage service-side, and it is the slowest path.

Explicitly cautioned against without disclosure: Looker Studio reports can carry a Google Analytics measurement ID, and copies can inherit report settings — meaning views of user copies would report to the template owner's GA property. That would contradict the no-backend/no-tracking posture the configurator just shipped (#398–#400). If that route is ever considered, it requires prominent disclosure on the configurator page and in the report; it must not ship silently.

None of these replace the user-side mechanism above, which stands alone and keeps the privacy boundary: installation owners measure their own usage in their own project.

[caohy1988]

Review — verified against both repos; endorse with one metric-semantics note

Checked the load-bearing claims directly:

• Write-path precedent is exactly as described. bigquery_agent_analytics_plugin.py at the pinned commit stamps trace_id=f"{trace_id_prefix}/{__version__}" on every AppendRowsRequest, with trace_id_prefix = google-adk-bq-logger (plus a -visual-builder variant — prefix aggregation should match both). Request metadata, not customer content — the addendum's governance framing is accurate.
• The fixed template ID is real and wired: report-config.mjs pins reportId: 5a3f85ef-fc9c-4730-8ef2-8ef9129ddb40, emitted as c.reportId on every lookerstudio.google.com/reporting/create URL the configurator builds (configurator.mjs:81,90). The copies-created count against that ID is structurally sound.
• The verify-first caveats are the right ones: whether Looker Studio preserves SQL comments in issued jobs is genuinely uncertain and must be checked against a live copy before the template changes; the requestor: looker_studio label presence likewise. Good that both are acceptance-gated.

One metric-semantics note for the docs section: Looker Studio caches query results at the report level, so BigQuery job counts measure queries issued, not dashboard opens — a widely-viewed copy with warm caches can show few jobs. The acceptance criterion is worded correctly ("how often their copy was queried"), and including cache-hit rate in the usage query helps, but the README section should state the distinction explicitly so installation owners don't read the number as viewership.

On the addendum's channel ranking: agreed with the ordering. One small addition for channel 3's eventual design: the marker string should be chosen so a substring match can't false-positive on unrelated user SQL (e.g. bqaa-dashboard/v2 inside a quoted literal in someone's ad hoc query on the same table) — a distinctive prefix plus an exact INFORMATION_SCHEMA.JOBS.query LIKE '/* bqaa-dashboard/%' prefix match, not a substring LIKE '%...%', keeps the aggregation honest.

The privacy boundary (non-goals) and the GA-disclosure caution are correctly stated; nothing here touches the configurator's no-backend property.