Analytics Playbook
# 1\. Executive Summary for Investors and Partners
This page is written for investors, partners, shareholders, and new team members. It summarizes what Blackdove is implementing, why it matters, and how leadership will use it.
- One unified measurement framework across: blackdove.com, app.blackdove.com, shop.blackdove.com, Android mobile app, and all display/receiver apps (webOS SCAP, Tizen SSSP, Android TV, etc.).
- A standardized event taxonomy that measures the complete customer journey: Visit → Sign up → Account created → Pair/Claim a display → Browse & select content → Start playback → Subscription/purchase → Retention.
- A single reporting layer using GA4 native export to BigQuery and a curated semantic layer (views) powering Metabase dashboards embedded in the Staff Portal.
What leadership gets (examples of headline KPIs):
- Acquisition: traffic sources, UTM campaign performance, visit→signup conversion, signup→activation conversion.
- Activation: pairing success rate, time-to-first-play after signup, drop-offs at each step.
- Engagement: daily/weekly active users & active devices, content views, playback starts, watch time, completion rate.
- Monetization: checkout starts, purchases/subscriptions, renewals/cancellations, revenue (where applicable), conversion by channel.
- Operations/QoE: buffering rate, error rate by platform/device model/app version, cache hit rate, download success and latency, fleet health and last-seen.
Governance & trust (important for investors):
- No personal data is intentionally collected in analytics events; user identifiers are set only after authentication and should be hashed/opaque where required.
- Consent Mode defaults are configured for analytics; ad-related signals are denied by default for signage contexts.
- Data is stored in BigQuery (exported from GA4). Dashboards read from curated views to control cost, consistency, and access.
- Instrumentation health checks report missing events/parameter drift so leadership metrics remain reliable as apps evolve.
How to read the rest of this playbook:
- Sections 1-3: how we instrument each app (web, mobile, receivers) and validate events.
- Appendix A: BigQuery SQL views powering reporting.
- Appendix B: Metabase dashboard setup and embedding in Staff Portal.
- Appendix C: full-funnel views for acquisition, engagement, monetization, and retention.
Scope note: this document is intentionally explicit and step-by-step so that new developers can implement changes safely, and partners/investors can understand the measurement system end-to-end.
**Blackdove Analytics Implementation & Reporting Playbook**
GA4 Instrumentation for Web, Mobile, TV/Signage Receivers + BigQuery, Metabase, and Staff Portal Dashboards
Date: January 31, 2026
# 2\. Measurement Coverage Matrix (Today vs Target)
This matrix is included to set clear expectations for owners/investors: what is measurable immediately after GA4 instrumentation, and what becomes measurable only after identity stitching, ecommerce event standardization, and receiver linkage are fully rolled out.
| Area | Measurable today | Requires additional rollout | Notes |
| --- | --- | --- | --- |
| Acquisition (traffic → visits) | Yes (web) | - | Requires UTM discipline on campaigns. |
| Visit → Sign-up conversion | Partial | Cross-domain linking + standardized sign_up events | If signup spans multiple domains, ensure linker config. |
| App activation (onboarding_complete) | Partial | Mobile event standardization | Depends on Android instrumentation completeness. |
| Pair/Claim success rate | Partial | Standard pair_start/pair_success/pair_fail + consistent device_id | Receiver + mobile must both emit pairing events. |
| Payment/Subscription conversion | Partial | GA4 ecommerce + subscription_\* mapping | Shop + billing source-of-truth needed. |
| First playback after signup | Partial | Identity stitching + device mapping | Requires connecting user_id_hash ↔ device_id. |
| Receiver QoE (buffers/errors/cache) | Yes (receivers) | - | Already covered by receiver event taxonomy and views. |
| Retention cohorts | Partial | Define cohort anchor + unified actor_id | Cohorts depend on stable identifiers. |
# 3\. Ownership and RACI
Clear ownership prevents dashboards from drifting or becoming untrusted. The roles below can be mapped to actual names internally.
| Workstream | Responsible | Accountable | Consulted | Informed |
| --- | --- | --- | --- | --- |
| Event taxonomy & schema changes | Analytics Lead | CTO/Eng Director | COO, Product | Leadership |
| Web instrumentation (gtag.js/GTM) | Web Lead | CTO/Eng Director | Analytics Lead | COO |
| Android instrumentation (Firebase Analytics) | Mobile Lead | CTO/Eng Director | Analytics Lead | COO |
| Receiver instrumentation (TV/Signage) | TV/Receiver Lead | CTO/Eng Director | Analytics Lead | COO |
| BigQuery export + datasets | Data/Platform Eng | CTO/Eng Director | Analytics Lead | Leadership |
| Semantic views + DQ checks | Data/Analytics Eng | Analytics Lead | Support/TV leads | Leadership |
| Metabase dashboards | Data/Analytics Eng | Analytics Lead | COO, Support | Leadership |
| Metabase embedding + portal | Portal/Backend Lead | CTO/Eng Director | Security | COO |
| Cost monitoring & budgets | Platform Eng | Finance/CTO | Analytics Lead | Leadership |
# 4\. KPI Governance and Definitions (Single Source of Truth)
Leadership dashboards are only useful when every metric has one agreed definition. This section defines core KPIs, the data source view, and the exact formula.
| KPI | Definition / Formula | Primary view | Default filters |
| --- | --- | --- | --- |
| Active devices (DAU) | COUNT(DISTINCT device_id) over day | kpi_daily_overview | build_channel='prod' |
| Active users (DAU) | COUNT(DISTINCT user_id) over day (authenticated) | kpi_daily_overview | build_channel='prod' |
| Playback starts | COUNTIF(event_name='playback_start') | kpi_daily_overview | build_channel='prod' |
| Completion rate | playback_completes / playback_starts | kpi_daily_overview | build_channel='prod' |
| Watch time (minutes) | SUM(watched_ms)/60000 | kpi_daily_overview | build_channel='prod' |
| Buffer rate | buffers_per_100_starts | buffer_rate_daily | build_channel='prod' |
| Error rate (receiver) | errors per 100 starts (derived) | errors_by_code_daily + kpi_daily_overview | build_channel='prod' |
| Cache hit rate | cache_hits / (cache_hits + cache_misses) | kpi_qoe_daily | build_channel='prod' |
| Pair success rate | pair_success / pair_start | journey_events_base | build_channel='prod' |
| Visit→Signup | signups / page_views | journey_events_base | web only |
| Paid conversion | (purchase OR subscription_start) / paired_display | funnel_daily_web_to_first_play | build_channel='prod' |
| Retention (weekly) | active_users_in_week / cohort_size | retention_weekly_by_first_play | build_channel='prod' |
# 5\. Ecommerce and Subscription Event Contract (Shop + Billing)
To report monetization credibly, the shop and billing flows must emit GA4-recommended ecommerce events with required parameters. This section defines the required events and the minimum required fields.
| Event | When fired | Required params | Notes |
| --- | --- | --- | --- |
| view_item | User views a product/subscription plan | items\[\] (item_id, item_name), currency | Use stable item_id. |
| add_to_cart | User adds plan/product to cart | items\[\], currency, value | value = price. |
| begin_checkout | Checkout begins | items\[\], currency, value | Track checkout funnel. |
| add_payment_info | Payment method entered | currency, value | Avoid PII; no card details. |
| purchase | Successful payment | transaction_id, currency, value, items\[\] | transaction_id must match billing gateway/DB. |
| subscription_start | Subscription activated (if separate from purchase) | subscription_id, value, currency, plan_id | Emit from backend as source-of-truth if possible. |
| subscription_renewal | Renewal | subscription_id, value, currency, plan_id | Prefer backend. |
| subscription_cancel | Cancellation | subscription_id, cancel_reason? | Reason optional; avoid PII. |
Example GA4 ecommerce payload shape (items array):
{
"event": "purchase",
"transaction_id": "ch_1Pxxxxxx",
"currency": "USD",
"value": 149.00,
"items": \[
{
"item_id": "plan_pro_annual",
"item_name": "Pro Annual Subscription",
"item_category": "subscription",
"price": 149.00,
"quantity": 1
}
\]
}
## 5.1. Monetization source of truth and deduplication
**Decision record (fill before rollout; do not assume):**
- Chosen source of truth for purchase/subscription events: \[Backend\] / \[Frontend\] / \[Hybrid\].
- Deduplication key(s): transaction_id = \____; subscription_id = \____ .
- Refund/cancel handling: \[emit refund event\] / \[emit cancel event\] / \[dashboard uses billing DB\] (choose).
- Owner role accountable for monetization correctness: \____; target date: \____ .
Decision required (do not assume): choose exactly one source of truth for purchase/subscription events to prevent double counting.
Option A (recommended): Backend is source of truth for purchase/subscription events. Frontend emits only begin_checkout / add_payment_info.
Option B: Frontend is source of truth. Implement strict idempotency so purchase fires once per transaction_id.
In both cases, dashboards and BigQuery views must treat transaction_id (and subscription_id) as the dedupe key.
# 6\. Identity, Correlation, and Cross-Domain Stitching (Required for Full Funnel)
**Decision record: cross-domain measurement strategy (fill before rollout):**
- Chosen approach: \[Cross-domain linker across blackdove.com ↔ app.blackdove.com ↔ shop.blackdove.com\] OR \[Stitch at login only via user_id_hash\].
- Domains in scope: \____ .
- Validation criteria: (a) same user_pseudo_id across domains OR (b) user_id set post-login and funnels built from authenticated journey. Choose and verify.
- Owner role accountable: \____; target date: \____ .
Full-funnel reporting requires stitching anonymous web activity, authenticated user activity, device pairing, receiver playback, and billing events. This section specifies the minimum identifiers and where they must be produced and stored.
| Journey step | Surface | Identifier(s) | Generated by | Stored in | Must be sent in events |
| --- | --- | --- | --- | --- | --- |
| Anonymous web visit | blackdove.com/app/shop | user_pseudo_id (GA), utm_\* | GA4 | browser cookies | page_view + acquisition params |
| Authentication | web/mobile | user_id (opaque) OR user_id_hash | backend auth | user profile DB | login/sign_up + set_user_id |
| Account created | backend | account_id (internal) | backend | account DB | account_created (as account_id_hash if needed) |
| Pair/claim display | mobile + backend + receiver | device_id + account_id_hash + user_id_hash | device hardware/app | device local + backend mapping | pair_start/pair_success/account_link |
| Playback start | receiver | device_id + content_id (+ account_id_hash) | receiver app | device local | playback_start/playback_complete |
| Payment/subscription | shop + backend | transaction_id + subscription_id | billing gateway/backend | billing DB | purchase/subscription_\* |
Cross-domain note: if signup spans blackdove.com → app.blackdove.com or shop.blackdove.com, enable cross-domain measurement linking so the same user_pseudo_id can be recognized across domains. If cross-domain linking is not feasible, treat domains as separate acquisition sources and stitch only after login via user_id_hash.
# 7\. Platform Policy: Firebase Usage
This policy is explicit to avoid confusion for investors and developers:
- Android mobile apps: Firebase is allowed (already used for Crashlytics). GA4 analytics may be implemented via Firebase Analytics SDK.
- Web apps (blackdove.com, app.blackdove.com, shop.blackdove.com): Firebase must NOT be used. Use gtag.js or GTM only.
- Receiver apps: do not use Firebase. Use gtag.js (where supported) and the offline-safe queue approach.
# 8\. Metabase Embedding Overview (Staff Portal)
This section provides a high-level embedding approach. For step-by-step implementation details, see the later section titled "Embedding Dashboards in Staff Portal (Implementation Details)".
Metabase dashboards should be accessible from the Staff Portal without requiring staff to navigate GA4. Recommended approach is Signed Embedding with role-based permissions.
| Step | Action | Output |
| --- | --- | --- |
| 1 | Create Metabase 'Analytics' collection and dashboards; restrict access to internal roles. | Dashboards organized and permissioned. |
| 2 | Create a Metabase service account / SSO integration (as applicable). | Controlled authentication. |
| 3 | Enable Signed Embedding in Metabase Admin; define secret key rotation policy. | Embedding enabled securely. |
| 4 | Implement Staff Portal backend endpoint to mint signed embed tokens (JWT) per user role. | Short-lived embed token. |
| 5 | Embed dashboards via iframe using the signed URL; pass filters (date/platform/build_channel) as parameters. | Embedded dashboard in portal. |
| 6 | Audit logging: log which staff viewed which dashboard and filters used (optional). | Governance trail. |
Security checklist:
- Use short-lived tokens (e.g., 5-15 minutes) and rotate embedding secrets periodically.
- Never allow public sharing for dashboards containing device_id or customer/site details.
- Default dashboard filters to build_channel='prod' and exclude test devices unless explicitly selected.
- Restrict BigQuery datasets so Metabase can read only semantic views, not raw exports (least privilege).
# 9\. Program Summary for Engineering Leadership
This document describes, step-by-step, how Blackdove implements Google Analytics 4 (GA4) across all products (web apps, Android mobile app, and TV/signage receiver apps), and how the resulting data is exported to BigQuery, modeled into leadership-ready metrics, visualized in Metabase, and embedded into the Staff Portal as a single, simplified analytics console.
It is written to be usable by: leadership, investors, partners, new developers, QA/support teams, and product owners. It includes the required event taxonomy, implementation patterns per platform, verification steps, and a dashboard blueprint.
# 10\. Scope and Non-Goals
In scope:
- GA4 tracking for Blackdove websites and web apps (e.g., app.blackdove.com, blackdove.com, shop.blackdove.com).
- GA4 tracking for Android mobile app (onboarding, browse/search, subscriptions, streaming control).
- GA4 tracking for display/receiver apps across platforms (webOS Signage/SCAP, Tizen Signage/SSSP, webOS TV, Android TV, etc.).
- A unified reporting stack: GA4 -> BigQuery export -> curated BigQuery views -> Metabase dashboards -> embedded dashboards in Staff Portal.
- Governance: naming conventions, consent, data quality checks, and release/rollback practices for analytics.
Out of scope (explicitly not covered here):
# 11\. Audience and Roles
| Role | Primary needs |
| --- | --- |
| COO / Leadership | High-level KPI dashboards, weekly/monthly reporting, cohort and growth metrics, reliability/QoE overview. |
| Engineering Leads | Event schema, instrumentation patterns, build-time configuration, debugging and validation. |
| Support / Ops | Device-level drilldowns, error codes, buffering rates, last-seen heartbeats, incident detection. |
| Data / Analytics | BigQuery modeling, Looker Studio construction, data quality checks, governance. |
| Investors / Partners | What is measured, how it is measured, trustworthiness of metrics, privacy and compliance posture. |
# 12\. Product Inventory and Tracking IDs
Blackdove uses multiple GA4 data streams. Each stream has a Measurement ID (format: G-XXXXXXXXXX). Some web experiences may also use Google Tag Manager (GTM) containers. The IDs below are provided from internal tracking inventory.
**Blocking inputs (must be confirmed in GA4/GTM Admin before rollout):**
Owner action required: Analytics/GTM admin must populate the website IDs above before Sprint 1 implementation begins.
- blackdove.com - GA4 web stream Measurement ID (G-...) and tagging approach (gtag.js vs GTM).
- shop.blackdove.com - GA4 web stream Measurement ID (G-...) and ecommerce event coverage confirmation.
- If GTM is used for any website: confirm GTM container ID (GTM-...) in GTM Admin (not a GT-... Google tag ID).
Action item: For any row where the GTM container is missing but required, create a GTM container in Google Tag Manager and deploy it to the website/app. Note that GA4 Measurement IDs and GTM container IDs are separate; one does not automatically create the other.
# 13\. Architecture Overview
End-to-end data pipeline (instrumentation to leadership dashboards):
Key idea: engineers instrument each product to emit a consistent event schema into the correct GA4 stream. GA4 exports raw events to BigQuery. We then create a curated semantic layer (views/rollups) that is easy and safe for dashboards. Metabase consumes curated views and the Staff Portal embeds the dashboards for leadership and operations.
# 14\. Receiver Offline-first Event Flow
## 14.1. GA4 / GTM Inventory Table (Authoritative)
This table is the single source of truth for which Measurement ID (and, where applicable, GTM container) is used in each product surface. Items marked TBD must be filled by a GA4/GTM admin before implementation begins. No IDs should be copied from memory; always confirm in GA4 Admin or GTM Admin.
| Surface / App | GA4 Stream / Property label | Measurement ID | Google tag ID (GT-...) or GTM container (GTM-...) | Notes (env / remarks) |
| --- | --- | --- | --- | --- |
| app.blackdove.com (web) | Artist-portal - GA4 | G-16N2JKC2CX | GT-NNVJWPP | Website priority |
| blackdove.com (web) | TBD | TBD | TBD | Website priority |
| shop.blackdove.com (web) | TBD | TBD | TBD | Website priority; ecommerce events required |
| BD Receiver App (unknown surface) | Receiver App | G-7ZYPZGCB4K | - | Measurement ID provided; confirm stream mapping |
| BD Receiver App | BD Receiver App | G-XM4VCJPHZV | - | Used in examples; confirm which receiver surface |
| Blackdove-Webapp (web) | Blackdove-Webapp - GA4 | G-P0QTQBM3EF | GT-PL95SSJ | Web app repository: bd-web-app-remix |
| Blackdove-AndroidTV | Blackdove-AndroidTV - GA4 | G-SYE350V3N6 | GT-WRF4TRN | Receiver / display app |
| Blackdove-FireTV | Blackdove-FireTV - GA4 | G-96PKZ4YJE7 | GT-PLVV4DH | Receiver / display app |
| Blackdove-Receiver | Blackdove-Receiver - GA4 | G-BHJXNB7NY0 | GT-5TGJT6L | Receiver / display app |
| Blackdove-SCAP (webOS signage) | Blackdove-SCAP - GA4 | G-FTBPGCVGMC | GT-PHRZQ72 | WebOS SCAP hosted app (Chromium M69) |
| Blackdove-SSSP (Tizen signage) | Blackdove-SSSP - GA4 | G-8QJSSYCGZ4 | GT-5RFLDJK | Tizen SSSP packaged app (Chromium M69) |
| Blackdove-TizenTV | Blackdove-TizenTV - GA4 | G-4JZR45DHCP | GT-P36LJQC | Tizen TV receiver |
| Blackdove-tvOS | Blackdove-tvOS - GA4 | G-TMZ0QBCG37 | GT-PBNRX97 | Apple tvOS receiver |
| Blackdove-VizioTV | Blackdove-VizioTV - GA4 | G-DGEZFMMHJ4 | GT-T5JN9LF | Vizio receiver |
| Blackdove-WebosTV | Blackdove-WebosTV - GA4 | G-DRCYHM7WPL | GT-K4Z76HZ | LG webOS TV receiver |
| Untitled tag | Untitled tag | G-2TQG1E5LR7 | - | Needs cleanup: identify surface and rename stream |
| Untitled tag | Untitled tag | G-18M712BSW6 | - | Needs cleanup: identify surface and rename stream |
| app.blackdove.com (alt stream) | Artist-portal - GA4 (alt) | G-KF3HX0BR4Z | - | Provided; verify whether deprecated or separate stream |
Note: Some surfaces use only a GA4 Measurement ID (direct gtag.js) and do not use Tag Manager. If you are using Google Tag Manager, the container typically looks like GTM-XXXXXXX. IDs that look like GT-XXXXXXX are Google tag IDs (not GTM containers). Always confirm in GA4/GTM Admin.
## 14.2. How to fill TBD rows (where to find IDs)
Do not guess IDs. Use the admin consoles and record the result in Section 4.1.
- GA4 Measurement ID: GA4 → Admin → Data Streams → select Web stream → Measurement ID (G-…).
- Google tag ID (if using Google tag directly): GA4 → Admin → Data Streams → select stream → Tagging Instructions; confirm GT-… references.
- GTM container ID (if using Tag Manager): Google Tag Manager → Admin → Container settings → Container ID (GTM-…).
- Confirm environment: ensure prod vs stage streams are clearly labeled; dashboards default to prod only.
- Paid media attribution and advanced marketing tagging beyond GA4 basics (UTMs, ad platform integrations).
- Server-side event collection proxies (this playbook focuses on direct gtag.js for web/Chromium-based receivers).
- PII ingestion into GA4 (this is explicitly forbidden; only hashed identifiers may be used where required).
Receiver apps often operate in constrained environments. Even when using direct gtag.js, receivers should include an offline queue so events are buffered in localStorage when network connectivity is unavailable and flushed when online.
## 14.3. Stream cleanup plan (naming, deprecation, ownership)
Some GA4 streams in the inventory are ambiguous (e.g., "Untitled tag") or have unclear surface ownership. This plan ensures the inventory remains auditable and dashboards remain trustworthy.
Actions (no assumptions; fill with outcomes as you confirm in GA4 Admin):
- Identify each ambiguous stream (G-2TQG1E5LR7, G-18M712BSW6, and any others) and document which product surface emits events to it.
- Rename streams and properties to match the standard: Blackdove-<platform/surface> - GA4 (e.g., Blackdove-WebosTV - GA4).
- Decide: keep vs deprecate vs merge. If deprecating, freeze tag deployment and mark as deprecated in the inventory table.
- Confirm whether IDs recorded as GT-… are Google tag IDs (not GTM containers). Record GTM-… containers only where GTM is actually used.
- Assign an owner role for the inventory table updates and a cadence (e.g., monthly review or per release).
Definition of done: all streams are renamed, mapped to a surface, deprecated streams are frozen, and the inventory table is updated; leadership dashboards use only approved production streams.
Owner role: \____| Target completion date: \____
# 15\. Standard Event Taxonomy (Mandatory for all Display/Receiver Apps)
All display/receiver apps must implement the following event set. Event names and parameter keys must use lower_snake_case. All events should include global context and consent flags (either explicitly in parameters or attached via a wrapper).
## 15.1. Global context fields (attach to every event)
- platform (e.g., webos_scap, tizen_sssp, android_tv, webos_tv)
- app_version (semantic version from build metadata)
- build_channel (prod/stage/dev)
- device_id (stable UUID generated once and stored locally; never a raw MAC address)
- device_model (best-effort from user agent / platform APIs)
- network_type (wifi/ethernet/cellular/unknown)
- analytics_storage, ad_user_data, ad_personalization (Consent Mode v2 signals)
## 15.2. Mandatory events for display/receiver apps
| Event name | When to send | Required params |
| --- | --- | --- |
| app_launch | Sent once per app start/boot. | - |
| account_link | Device linked/claimed to a user/account. | link_method |
| content_view | Artwork selected for viewing. | content_id, content_type |
| download_start | Start downloading MP4 to local storage. | content_id |
| download_complete | Download completed. | content_id, bytes_downloaded, duration_ms |
| cache_hit | Content played from local cache. | content_id |
| cache_miss | Cache miss; download needed. | content_id |
| playback_start | First frame / playback begins. | content_id, position_ms |
| playback_pause | Playback paused. | content_id, position_ms |
| playback_resume | Playback resumed. | content_id, position_ms |
| seek | Seek occurred. | content_id, from_ms, to_ms |
| buffer_start | Buffering started. | content_id, position_ms |
| buffer_end | Buffering ended. | content_id, position_ms, buffer_duration_ms |
| playback_complete | Playback completed. | content_id, duration_ms |
| playback_error | Playback error. | error_code (plus optional content_id) |
| heartbeat | Periodic while playing; low frequency. | interval_ms (plus optional content_id) |
| receiver_health | Low-frequency health snapshot. | disk_free_pct?, uptime_min? |
| receiver_watchdog_restart | Auto-restart event. | reason? |
Note: The above list is the minimum required set. Additional events may be defined for platform-specific diagnostics, but must not break naming conventions or introduce PII.
# 16\. Web Tracking Implementation (Websites and Web Apps)
Web experiences should generally use either (a) Google Tag (gtag.js) directly, or (b) Google Tag Manager (GTM) if non-developers need to manage tags. For engineering-controlled apps, gtag.js is the simplest starting point. For Shopify-like or marketing-heavy sites, GTM is typically preferred.
## 16.1. Choose the correct stream
Do not reuse a receiver stream Measurement ID on a website. Each website/web-app should use its dedicated web stream (Measurement ID from the tracking inventory table).
## 16.2. Add gtag.js to the base HTML template
Add the following snippet to the base HTML template (one time). Replace G-XXXXXXXXXX with the correct Measurement ID:
<!-- Google tag (gtag.js) -->
<script>
window.dataLayer = window.dataLayer || \[\];
function gtag(){dataLayer.push(arguments);}
gtag('consent', 'default', {
analytics_storage: 'granted',
ad_storage: 'denied',
ad_user_data: 'denied',
ad_personalization: 'denied'
});
</script>
<script async src="<https://www.googletagmanager.com/gtag/js?id=G-XXXXXXXXXX"></script>>
<script>
gtag('js', new Date());
gtag('config', 'G-XXXXXXXXXX');
</script>
For single-page applications (SPA) or Remix routes, also send page_view on route changes if required. If the framework already handles pageviews via the base tag, do not double-count.
# 17\. Receiver Tracking Implementation (Chromium-based Signage/TV Apps)
For Chromium-based receiver apps (webOS Signage SCAP, Tizen Signage SSSP, webOS TV HTML apps), we use gtag.js directly and a small ES5-compatible wrapper. The wrapper provides: consistent global context, event naming, and an offline queue.
## 17.1. Add GA tag to the receiver base HTML
Add a single GA4 loader to the receiver's root HTML and disable automatic page_view (kiosk apps should emit exactly one explicit page_view on boot). Example for SCAP stream:
<script>
window.dataLayer = window.dataLayer || \[\];
function gtag(){dataLayer.push(arguments);}
gtag('consent', 'default', {
analytics_storage: 'granted',
ad_storage: 'denied',
ad_user_data: 'denied',
ad_personalization: 'denied'
});
</script>
<script async src="<https://www.googletagmanager.com/gtag/js?id=G-FTBPGCVGMC"></script>>
<script>
gtag('js', new Date());
gtag('config', 'G-FTBPGCVGMC', { send_page_view: false });
</script>
## 17.2. Implement an ES5 analytics wrapper (offline queue)
Implement a small wrapper (example: window.BDGA) that queues events in localStorage when offline and flushes them when online. The wrapper must be ES5-compatible for Chromium M69 and must never block video playback if analytics fails.
# 18\. GA4 to BigQuery Export
GA4 offers a native integration to export raw events to BigQuery. In the GA4 UI, you link a GA4 property to a Google Cloud project. GA4 creates a dataset automatically (commonly named analytics_<property_id>). Dataset renaming is not supported by BigQuery, so build curated views in a separate dataset if you want clean naming.
## 18.1. Step-by-step
- Create or select a Google Cloud project with billing enabled.
- Ensure the GA4 property admin has permission to create a BigQuery link and has the required IAM access to the Google Cloud project.
- In BigQuery, confirm dataset location/region planning (choose once, keep it consistent).
- GA4 Admin -> Product Links -> BigQuery Links -> Link.
- Select project and complete linking. Wait for first daily export tables (events_YYYYMMDD) to appear.
- Optionally enable streaming export if near real-time dashboards are required (operations/QoE monitoring).
# 19\. BigQuery Semantic Layer (Views and Rollups)
Dashboards should not query raw GA4 export tables directly. Instead, create a semantic layer in BigQuery: (a) normalized events_base view, (b) daily rollups by platform/app, and (c) fleet health views for receivers.
## 19.1. Recommended datasets
Recommended structure:
- analytics_<property_id>: raw GA4 export dataset (do not edit).
- bd_analytics_prod: curated views and rollups for production dashboards.
- bd_analytics_stage: curated views and rollups for staging dashboards.
# 20\. Metabase Dashboards
Metabase should connect to the curated BigQuery views. The goal is to present GA4 capabilities in a simplified, Blackdove-specific way. The minimum dashboard pack for leadership includes: Executive Overview, Funnels, Content Performance, and Receiver QoE & Health.
Canonical step-by-step implementation instructions and the dashboard catalog are in Appendix B (and Appendix B Extended).
## 20.1. Dashboard pack blueprint
| Dashboard | Purpose and key visuals |
| --- | --- |
| Executive Overview | DAU/WAU/MAU, streams started/completed, completion rate, top platforms, top content, error/buffer rate trends. |
| Funnels & Journey | Account link funnel, content view -> playback start -> complete funnel, subscription funnel (if available). |
| Content Performance | Top artworks/artists/collections by views, watch time, completion, repeat rate. |
| Receiver QoE & Health | Cache hit rate, downloads, buffering metrics, error codes, worst devices, last seen heartbeats. |
# 21\. Embedding Dashboards in Staff Portal (Implementation Details)
Embed Metabase dashboards into the Staff Portal so leadership and teams can access analytics without navigating GA4 directly. Implement role-based access (exec vs support vs engineering) and provide deep links for device-level drilldowns (e.g., device_id filter).
# 22\. Verification and Validation
Instrumentation verification (per app):
- Confirm GA4 script loads (Network tab: googletagmanager.com/gtag/js).
- Confirm event hits are sent (Network tab: google-analytics.com/g/collect or /collect endpoints used by GA4).
- Confirm Realtime shows events in the correct stream (do not validate against a different stream).
- Test offline mode: generate events while offline; confirm queued events flush after reconnect.
- Confirm no PII in event parameters; user_id must be hashed.
Data pipeline verification:
- Confirm BigQuery daily export tables appear.
- Confirm curated views return data and match GA4 counts.
- Confirm Metabase charts match rollups and are filterable by date/platform/app.
- Confirm Staff Portal embed loads dashboards and respects access control.
# 23\. Privacy, Security, and Compliance
Do not send personally identifiable information (PII) to GA4. Avoid raw emails, phone numbers, access tokens, IP addresses, or full device serial numbers. Where user/device correlation is needed, use stable pseudonymous identifiers (e.g., device_id) and hashed user identifiers. Use Consent Mode v2 signals to reflect advertising consent (typically denied for signage receivers).
# 24\. Open Items and Required Inputs (No assumptions)
The following items must be confirmed by the business owner / COO / analytics owner before final rollout. These are intentionally listed to avoid assumptions.
- Confirm the authoritative GA4 property (single property vs multiple properties) and which streams belong to which property.
- Confirm which experiences must use GTM (non-developer tag management) vs gtag.js (developer-managed).
- Confirm consent policy by region and whether analytics_storage should default to granted or denied on public websites.
- Confirm the official KPI definitions (e.g., what counts as a 'stream started' vs 'playback_start').
- Confirm who owns BigQuery and Metabase permissions, and who can publish dashboards to leadership.
- Confirm whether near real-time streaming export is required for operations dashboards.
# 25\. GA4 Administration Checklist (Property, Streams, Custom Definitions)
This section documents the GA4-side steps that must be completed so that instrumentation and dashboards work end-to-end. Where the exact setting depends on business decisions, it is explicitly marked as a decision point.
## 25.1. Create or confirm the GA4 property
- GA4 Admin -> Create Property (or confirm the existing production property).
- Set the property time zone and reporting currency (decision: must match finance reporting).
- Enable BigQuery linking once property is stable (see Section 10).
## 25.2. Create data streams
Create one data stream per surface where isolation is required (typically per major app/platform). For each stream record: stream name, stream URL (web), stream ID, and Measurement ID.
- GA4 Admin -> Data Streams -> Add stream.
- For websites: Web stream + add the exact domain(s).
- For app-like environments that still use gtag.js (Chromium receivers): use a Web stream with an appropriate stream URL label (informational).
- For Android mobile (if using Firebase/SDK): use an Android app stream; if using direct GA4 measurement protocol you must provision API secrets (not recommended for this playbook).
## 25.3. Register Custom Definitions
GA4 will ingest custom event parameters, but to use them in standard reports you must register them as Custom Definitions (custom dimensions / metrics). Register only the parameters you will report on.
Recommended custom dimensions (examples):
- platform
- build_channel
- app_version
- device_model
- network_type
- subscription_tier
- content_type
Recommended custom metrics (examples):
- position_ms
- duration_ms
- watched_ms
- buffer_duration_ms
- bytes_downloaded
- disk_free_pct
- uptime_min
# 26\. Implementation Guides by Product Type
## 26.1. Websites and web apps (blackdove.com, app.blackdove.com, shop.blackdove.com)
Step-by-step (developer-managed gtag.js):
- Identify the correct Measurement ID for the website/web app from the inventory table (Section 4).
- Add the GA snippet to the global HTML template (Remix: root document or entry HTML).
- Ensure Consent Mode default is set BEFORE loading gtag.js.
- Verify in GA4 Realtime that page_view events appear under the correct stream.
- Add critical business events (login, signup, purchase, subscription upgrade) using gtag('event', ...).
- If routing is client-side, send page_view on route transitions (avoid double-counting).
## 26.2. Chromium-based receiver apps (SCAP, SSSP, webOS TV HTML)
Step-by-step:
- Add gtag.js snippet with send_page_view:false in the receiver root HTML.
- Initialize an ES5 wrapper (BDGA) on boot; emit one explicit page_view and app_launch.
- Emit mandatory playback/cache/download/QoE events at existing lifecycle hooks.
- Add localStorage queue; flush on online/visibilitychange.
- Verify Realtime events while online; verify queue flush after offline playback.
- Ship behind a runtime flag so analytics can be disabled in emergency without redeploying the app (decision point).
# 27\. BigQuery Modeling: Example SQL Patterns
The GA4 export schema is nested. The following example SQL snippets demonstrate how to extract common parameters into a flat view for dashboards. These snippets are templates; adjust dataset/table names to your environment.
## 27.1. Flatten event_params into columns
\-- Example: events_base view (template)
CREATE OR REPLACE VIEW bd_analytics_prod.events_base AS
SELECT
PARSE_DATE('%Y%m%d', event_date) AS event_day,
TIMESTAMP_MICROS(event_timestamp) AS event_ts,
event_name,
user_pseudo_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key='platform') AS platform,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key='app_version') AS app_version,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key='build_channel') AS build_channel,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key='device_id') AS device_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key='device_model') AS device_model,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key='content_id') AS content_id,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key='position_ms') AS position_ms,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key='duration_ms') AS duration_ms,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key='buffer_duration_ms') AS buffer_duration_ms
FROM \`analytics_<property_id>.events_\*\`;
## 27.2. Daily QoE rollup example
\-- Example: playback QoE rollup (template)
CREATE OR REPLACE VIEW bd_analytics_prod.kpi_qoe_daily AS
SELECT
event_day,
platform,
COUNTIF(event_name='playback_start') AS playback_starts,
COUNTIF(event_name='playback_complete') AS playback_completes,
SAFE_DIVIDE(COUNTIF(event_name='playback_complete'),
NULLIF(COUNTIF(event_name='playback_start'),0)) AS completion_rate,
COUNTIF(event_name='buffer_start') AS buffer_events,
AVG(IF(event_name='buffer_end', buffer_duration_ms, NULL)) AS avg_buffer_duration_ms,
COUNTIF(event_name='playback_error') AS playback_errors
FROM bd_analytics_prod.events_base
GROUP BY 1,2;
# 28\. Metabase: Building Leadership Dashboards
Use BigQuery views as Metabase data sources. Prefer one data source per dashboard page (or per KPI family) to keep query cost predictable. Standardize filters across all pages: date range, platform, app/stream, build_channel, subscription_tier.
Canonical step-by-step implementation instructions and the dashboard catalog are in Appendix B (and Appendix B Extended).
## 28.1. Dashboard design principles (leadership friendly)
- Start each page with 6-12 KPI tiles (numbers) and then 3-8 charts (trends and breakdowns).
- Use clear definitions and tooltips for KPIs (completion rate, buffer rate, cache hit rate).
- Provide a 'Drilldown table' below every chart with export enabled (CSV).
- Add an 'Incidents' table for spikes: error rate up vs baseline, buffering spikes, watchdog restarts.
# 29\. Staff Portal Embedding
Embed Metabase dashboards in the Staff Portal to provide a single analytics entrypoint. Enforce access control using Google Groups / Metabase sharing and portal-level role checks.
## 29.1. Embed checklist
- Create a staff-only Google group (e.g., analytics-viewers) and share the Metabase report with this group.
- Embed using an iframe and ensure the portal session requires authentication.
- Implement a report selector and consistent filters. Prefer URL parameter-based filters for device_id deep links.
- Add a support search box for device_id to jump into a filtered receiver view.
# 30\. Appendix A: BigQuery SQL views (core GA4 export)
This appendix provides ready-to-run SQL for the core BigQuery views referenced in the playbook. These are written against the GA4 native export schema in BigQuery (dataset typically named analytics_<GA4_PROPERTY_ID>). Do not hard-code names: replace placeholders like \${PROJECT_ID}, \${GA_EXPORT_DATASET}, and \${BD_SEMANTIC_DATASET} with your actual values.
## 30.1. Placeholders used in SQL
| Placeholder | Meaning / example |
| --- | --- |
| \${PROJECT_ID} | Google Cloud project, e.g., blackdove-prod |
| \${GA_EXPORT_DATASET} | GA4 export dataset, usually analytics_<property_id>, e.g., analytics_123456789 |
| \${BD_SEMANTIC_DATASET} | Your semantic dataset that contains views, e.g., bd_analytics_prod |
| \${START_DATE} | Optional filter lower bound in YYYYMMDD, e.g., 20260101 |
## 30.2. View: events_base
Purpose: Normalize GA4 exported events into a flat schema with commonly used parameters extracted (platform/app_version/build_channel/device_id/content_id, etc.). Dashboards should use this view instead of querying raw events_\* tables.
\-- Create in your semantic dataset:
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.events_base\` AS
WITH
src AS (
SELECT
PARSE_DATE('%Y%m%d', event_date) AS event_dt,
event_timestamp,
event_name,
user_pseudo_id,
user_id,
stream_id,
platform,
geo.country AS geo_country,
geo.region AS geo_region,
geo.city AS geo_city,
device.category AS device_category,
device.operating_system AS device_os,
device.web_info.browser AS device_browser,
traffic_source.source AS traffic_source,
traffic_source.medium AS traffic_medium,
traffic_source.name AS traffic_campaign,
event_params
FROM \`\${PROJECT_ID}.\${GA_EXPORT_DATASET}.events_\*\`
\-- Optional: uncomment to reduce scan
\-- WHERE \_TABLE_SUFFIX >= '\${START_DATE}'
),
<br/>ep AS (
SELECT
s.\*,
\-- Common helpers to read event_params
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'platform') AS param_platform,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'app_version') AS app_version,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'build_channel') AS build_channel,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'device_id') AS device_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'device_model') AS device_model,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'network_type') AS network_type,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'subscription_tier') AS subscription_tier,
<br/>(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_id') AS content_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_type') AS content_type,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_title') AS content_title,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'artist_id') AS artist_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'collection_id') AS collection_id,
<br/>(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'position_ms') AS position_ms,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'duration_ms') AS duration_ms,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'watched_ms') AS watched_ms,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'buffer_duration_ms') AS buffer_duration_ms,
<br/>(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'bytes_total') AS bytes_total,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'bytes_downloaded') AS bytes_downloaded,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'freed_mb') AS freed_mb,
<br/>(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'error_code') AS error_code,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'error_msg') AS error_msg,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'stage') AS stage,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'http_status') AS http_status,
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'retry_count') AS retry_count,
<br/>(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'analytics_storage') AS analytics_storage,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ad_user_data') AS ad_user_data,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ad_personalization') AS ad_personalization,
<br/>(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'req_id') AS req_id
FROM src s
)
<br/>SELECT
event_dt,
TIMESTAMP_MICROS(event_timestamp) AS event_ts,
event_name,
user_pseudo_id,
user_id,
stream_id,
<br/>\-- prefer explicit param_platform if you set it; fall back to GA's platform field
COALESCE(NULLIF(param_platform, ''), platform) AS platform,
app_version,
build_channel,
device_id,
device_model,
network_type,
subscription_tier,
<br/>geo_country,
geo_region,
geo_city,
device_category,
device_os,
device_browser,
<br/>traffic_source,
traffic_medium,
traffic_campaign,
<br/>content_id,
content_type,
content_title,
artist_id,
collection_id,
<br/>position_ms,
duration_ms,
watched_ms,
buffer_duration_ms,
<br/>bytes_total,
bytes_downloaded,
freed_mb,
<br/>error_code,
error_msg,
stage,
http_status,
retry_count,
<br/>analytics_storage,
ad_user_data,
ad_personalization,
<br/>req_id
FROM ep;
## 30.3. View: kpi_daily_overview
Purpose: Daily product overview metrics for leadership dashboards.
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.kpi_daily_overview\` AS
WITH e AS (
SELECT \* FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.events_base\`
)
SELECT
event_dt,
platform,
stream_id,
build_channel,
COUNTIF(event_name = 'app_launch') AS app_launches,
COUNT(DISTINCT device_id) AS active_devices,
COUNT(DISTINCT user_id) AS active_users,
COUNTIF(event_name = 'content_view') AS content_views,
COUNTIF(event_name = 'playback_start') AS playback_starts,
COUNTIF(event_name = 'playback_complete') AS playback_completes,
SAFE_DIVIDE(COUNTIF(event_name = 'playback_complete'), NULLIF(COUNTIF(event_name = 'playback_start'),0)) AS completion_rate,
SUM(IFNULL(watched_ms,0)) / 60000.0 AS watch_minutes
FROM e
GROUP BY 1,2,3,4;
## 30.4. View: kpi_qoe_daily
Purpose: Daily QoE metrics (buffering, errors, cache, downloads).
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.kpi_qoe_daily\` AS
WITH e AS (
SELECT \* FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.events_base\`
)
SELECT
event_dt,
platform,
stream_id,
build_channel,
<br/>COUNTIF(event_name = 'buffer_start') AS buffer_events,
AVG(IF(event_name = 'buffer_end', buffer_duration_ms, NULL)) AS avg_buffer_duration_ms,
<br/>COUNTIF(event_name = 'playback_error') AS playback_errors,
COUNTIF(event_name = 'network_error') AS network_errors,
COUNTIF(event_name = 'storage_error') AS storage_errors,
<br/>COUNTIF(event_name = 'cache_hit') AS cache_hits,
COUNTIF(event_name = 'cache_miss') AS cache_misses,
SAFE_DIVIDE(COUNTIF(event_name='cache_hit'), NULLIF(COUNTIF(event_name IN ('cache_hit','cache_miss')),0)) AS cache_hit_rate,
<br/>COUNTIF(event_name = 'download_start') AS downloads_started,
COUNTIF(event_name = 'download_complete') AS downloads_completed,
SAFE_DIVIDE(COUNTIF(event_name='download_complete'), NULLIF(COUNTIF(event_name='download_start'),0)) AS download_success_rate,
AVG(IF(event_name='download_complete', bytes_downloaded, NULL)) AS avg_bytes_downloaded
FROM e
GROUP BY 1,2,3,4;
## 30.5. View: receiver_fleet_health
Purpose: Latest status per receiver device for support and ops. Uses heartbeat/receiver_health events.
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.receiver_fleet_health\` AS
WITH e AS (
SELECT \* FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.events_base\`
),
last_seen AS (
SELECT
device_id,
ANY_VALUE(platform) AS platform,
ANY_VALUE(stream_id) AS stream_id,
ANY_VALUE(build_channel) AS build_channel,
MAX(event_ts) AS last_seen_ts,
MAX(IF(event_name = 'receiver_watchdog_restart', event_ts, NULL)) AS last_watchdog_ts,
COUNTIF(event_dt >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND event_name IN ('playback_error','network_error','storage_error')) AS errors_7d,
COUNTIF(event_dt >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AND event_name = 'buffer_start') AS buffers_7d
FROM e
WHERE device_id IS NOT NULL
GROUP BY device_id
)
SELECT
device_id,
platform,
stream_id,
build_channel,
last_seen_ts,
TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), last_seen_ts, MINUTE) AS minutes_since_last_seen,
last_watchdog_ts,
errors_7d,
buffers_7d
FROM last_seen;
## 30.6. View: playback_funnel_daily
Purpose: Daily conversion from content_view -> playback_start -> playback_complete.
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.playback_funnel_daily\` AS
WITH e AS (
SELECT
event_dt,
platform,
stream_id,
build_channel,
device_id,
content_id,
event_name
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.events_base\`
WHERE event_name IN ('content_view','playback_start','playback_complete')
)
SELECT
event_dt,
platform,
stream_id,
build_channel,
COUNT(DISTINCT IF(event_name='content_view', CONCAT(device_id,'|',content_id), NULL)) AS viewed,
COUNT(DISTINCT IF(event_name='playback_start', CONCAT(device_id,'|',content_id), NULL)) AS started,
COUNT(DISTINCT IF(event_name='playback_complete', CONCAT(device_id,'|',content_id), NULL)) AS completed,
SAFE_DIVIDE(COUNT(DISTINCT IF(event_name='playback_start', CONCAT(device_id,'|',content_id), NULL)),
NULLIF(COUNT(DISTINCT IF(event_name='content_view', CONCAT(device_id,'|',content_id), NULL)),0)) AS view_to_start,
SAFE_DIVIDE(COUNT(DISTINCT IF(event_name='playback_complete', CONCAT(device_id,'|',content_id), NULL)),
NULLIF(COUNT(DISTINCT IF(event_name='playback_start', CONCAT(device_id,'|',content_id), NULL)),0)) AS start_to_complete
FROM e
GROUP BY 1,2,3,4;
# 31\. Appendix A (Extended): Additional BigQuery views for dashboards
This section extends Appendix A with additional views that make Metabase dashboards faster, cheaper, and easier to maintain. All views read from events_base (Appendix A.1). Create these in the same semantic dataset.
## 32.1. View: errors_by_code_daily
Purpose: Track top error codes over time and by platform/app version/build channel.
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.errors_by_code_daily\` AS
WITH e AS (
SELECT \* FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.events_base\`
WHERE event_name IN ('playback_error','network_error','storage_error')
)
SELECT
event_dt,
platform,
stream_id,
build_channel,
app_version,
event_name AS error_type,
COALESCE(NULLIF(error_code,''), 'unknown') AS error_code,
COUNT(\*) AS error_count,
COUNT(DISTINCT device_id) AS affected_devices
FROM e
GROUP BY 1,2,3,4,5,6,7;
## 32.2. View: top_content_daily
Purpose: Rank content by starts, completes, and watch time (minutes) per day.
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.top_content_daily\` AS
WITH e AS (
SELECT \* FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.events_base\`
WHERE content_id IS NOT NULL
)
SELECT
event_dt,
platform,
stream_id,
build_channel,
content_id,
ANY_VALUE(content_title) AS content_title,
ANY_VALUE(artist_id) AS artist_id,
ANY_VALUE(collection_id) AS collection_id,
COUNTIF(event_name='content_view') AS views,
COUNTIF(event_name='playback_start') AS starts,
COUNTIF(event_name='playback_complete') AS completes,
SAFE_DIVIDE(COUNTIF(event_name='playback_complete'), NULLIF(COUNTIF(event_name='playback_start'),0)) AS completion_rate,
SUM(IFNULL(watched_ms,0)) / 60000.0 AS watch_minutes
FROM e
GROUP BY 1,2,3,4,5;
## 32.3. View: buffer_rate_daily
Purpose: Buffering rate normalized by playback starts (buffers per 100 starts) and average buffer duration. Useful for 'QoE score' trending.
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.buffer_rate_daily\` AS
WITH e AS (
SELECT \* FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.events_base\`
WHERE event_name IN ('playback_start','buffer_start','buffer_end')
)
SELECT
event_dt,
platform,
stream_id,
build_channel,
COUNTIF(event_name='playback_start') AS starts,
COUNTIF(event_name='buffer_start') AS buffer_events,
SAFE_MULTIPLY(
SAFE_DIVIDE(COUNTIF(event_name='buffer_start'), NULLIF(COUNTIF(event_name='playback_start'),0)),
100.0
) AS buffers_per_100_starts,
AVG(IF(event_name='buffer_end', buffer_duration_ms, NULL)) AS avg_buffer_duration_ms
FROM e
GROUP BY 1,2,3,4;
## 32.4. View: download_latency_daily
Purpose: Download performance and success rate (offline-first receivers).
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.download_latency_daily\` AS
WITH e AS (
SELECT \* FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.events_base\`
WHERE event_name IN ('download_start','download_complete','network_error')
)
SELECT
event_dt,
platform,
stream_id,
build_channel,
COUNTIF(event_name='download_start') AS downloads_started,
COUNTIF(event_name='download_complete') AS downloads_completed,
SAFE_DIVIDE(COUNTIF(event_name='download_complete'), NULLIF(COUNTIF(event_name='download_start'),0)) AS download_success_rate,
AVG(IF(event_name='download_complete', duration_ms, NULL)) AS avg_download_duration_ms,
AVG(IF(event_name='download_complete', SAFE_DIVIDE(bytes_downloaded, NULLIF(duration_ms,0)), NULL)) AS avg_bytes_per_ms
FROM e
GROUP BY 1,2,3,4;
## 32.5. View: worst_devices_daily
Purpose: Daily ranking of worst devices by errors and buffering, for support escalation. This view is designed for Metabase tables with drill-through.
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.worst_devices_daily\` AS
WITH e AS (
SELECT
event_dt, platform, stream_id, build_channel, app_version, device_id, event_name,
buffer_duration_ms
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.events_base\`
WHERE device_id IS NOT NULL
),
agg AS (
SELECT
event_dt,
platform,
stream_id,
build_channel,
app_version,
device_id,
COUNTIF(event_name IN ('playback_error','network_error','storage_error')) AS errors,
COUNTIF(event_name='buffer_start') AS buffers,
AVG(IF(event_name='buffer_end', buffer_duration_ms, NULL)) AS avg_buffer_duration_ms,
COUNTIF(event_name='playback_start') AS starts
FROM e
GROUP BY 1,2,3,4,5,6
)
SELECT
\*,
SAFE_MULTIPLY(SAFE_DIVIDE(buffers, NULLIF(starts,0)), 100.0) AS buffers_per_100_starts
FROM agg;
# 32\. Appendix B: Metabase dashboards (core leadership views)
Blackdove will use Metabase as the BI layer on top of BigQuery. This appendix describes how to connect Metabase to BigQuery, create Questions from the BigQuery views in Appendix A, assemble dashboards for leadership, and embed dashboards into the staff portal.
## 31.1. Connect Metabase to BigQuery
**BigQuery IAM checklist for Metabase (least privilege):**
- Create/identify the Metabase access identity (service account for self-hosted; managed identity for Metabase Cloud) - record it here: \____ .
- Grant read-only access (e.g., BigQuery Data Viewer) to the semantic views dataset only (BD_SEMANTIC_DATASET).
- Do NOT grant Metabase access to raw GA export datasets unless explicitly required; if needed, prefer authorized views.
- If using authorized views, document the view dataset, authorized view permissions, and review cadence.
- Log and review BigQuery query costs monthly; set budgets/alerts as needed.
Create a dedicated GCP service account for Metabase with least-privilege access to the semantic dataset (read-only). In Metabase: Admin -> Databases -> Add database -> BigQuery. Provide the service account JSON key, project id, and dataset access. Prefer connecting to the semantic dataset (e.g., bd_analytics_prod) rather than the raw GA export dataset.
## 31.2. Create Metabase Questions from views
Create one Metabase Question per metric and per chart. Use either the GUI query builder (for simple aggregations) or Native SQL (for advanced slices). Start from these views: events_base, kpi_daily_overview, kpi_qoe_daily, receiver_fleet_health, playback_funnel_daily.
- Executive Overview: active_devices, playback_starts, completion_rate, watch_minutes (from kpi_daily_overview).
- Receiver QoE: cache_hit_rate, avg_buffer_duration_ms, error counts (from kpi_qoe_daily).
- Support: worst devices list by errors_7d, buffers_7d, minutes_since_last_seen (from receiver_fleet_health).
- Funnel: view_to_start and start_to_complete (from playback_funnel_daily).
## 31.3. Dashboard assembly (Leadership pack)
Create a Metabase Dashboard named 'Blackdove Analytics - Leadership'. Add filters: date range, platform, build_channel. Pin the following cards: (1) Key tiles (DAU devices, starts, completes, completion rate, errors, cache hit rate), (2) Trends (starts/completes by day), (3) QoE trend (buffer events and avg buffer duration), (4) Top issues (top error_code), (5) Platform comparison.
## 31.4. Embedding into Staff Portal
Metabase supports multiple sharing modes. Choose based on your security posture and Metabase edition:
- Signed embedding (recommended): Staff portal backend generates a signed JWT for the Metabase dashboard and embeds it in an iframe. Supports row-level permissions and requires Metabase embedding configuration.
- Public sharing (not recommended for sensitive data): Metabase provides a public link. Use only for non-sensitive, aggregate-only dashboards.
- SSO + restricted access: If your staff portal uses SSO and Metabase supports SSO, restrict dashboards to internal users and embed using authenticated sessions.
## 31.5. Operational considerations
For performance and predictable BigQuery costs, point Metabase primarily at the daily KPI views. Avoid running broad ad-hoc queries against events_base over long date ranges. If needed, materialize rollups into partitioned tables and schedule refresh.
# 33\. Appendix B (Extended): Additional Metabase dashboards and drill-downs
This appendix provides copy/paste SQL for Metabase 'Native query' questions. Replace dataset placeholders as needed. Prefer querying the KPI views to reduce BigQuery costs.
## 33.1. Executive tiles (daily)
\-- Metabase: Executive tiles for a selected date range
SELECT
event_dt,
SUM(active_devices) AS active_devices,
SUM(playback_starts) AS playback_starts,
SUM(playback_completes) AS playback_completes,
SAFE_DIVIDE(SUM(playback_completes), NULLIF(SUM(playback_starts),0)) AS completion_rate,
SUM(watch_minutes) AS watch_minutes
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.kpi_daily_overview\`
WHERE event_dt BETWEEN {{start_date}} AND {{end_date}}
\[\[AND platform = {{platform}}\]\]
\[\[AND build_channel = {{build_channel}}\]\]
GROUP BY event_dt
ORDER BY event_dt;
## 33.2. QoE trend (buffers per 100 starts)
SELECT
event_dt,
AVG(buffers_per_100_starts) AS buffers_per_100_starts,
AVG(avg_buffer_duration_ms) AS avg_buffer_duration_ms
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.buffer_rate_daily\`
WHERE event_dt BETWEEN {{start_date}} AND {{end_date}}
\[\[AND platform = {{platform}}\]\]
\[\[AND build_channel = {{build_channel}}\]\]
GROUP BY event_dt
ORDER BY event_dt;
## 33.3. Top error codes (table)
SELECT
error_type,
error_code,
SUM(error_count) AS error_count,
SUM(affected_devices) AS affected_devices
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.errors_by_code_daily\`
WHERE event_dt BETWEEN {{start_date}} AND {{end_date}}
\[\[AND platform = {{platform}}\]\]
\[\[AND build_channel = {{build_channel}}\]\]
GROUP BY error_type, error_code
ORDER BY error_count DESC
LIMIT 50;
## 33.4. Top content (starts, completes, watch time)
SELECT
content_id,
ANY_VALUE(content_title) AS content_title,
SUM(starts) AS starts,
SUM(completes) AS completes,
SAFE_DIVIDE(SUM(completes), NULLIF(SUM(starts),0)) AS completion_rate,
SUM(watch_minutes) AS watch_minutes
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.top_content_daily\`
WHERE event_dt BETWEEN {{start_date}} AND {{end_date}}
\[\[AND platform = {{platform}}\]\]
\[\[AND build_channel = {{build_channel}}\]\]
GROUP BY content_id
ORDER BY starts DESC
LIMIT 100;
## 33.5. Worst devices (support table)
SELECT
device_id,
platform,
app_version,
SUM(starts) AS starts,
SUM(buffers) AS buffers,
AVG(avg_buffer_duration_ms) AS avg_buffer_duration_ms,
SUM(errors) AS errors,
SAFE_MULTIPLY(SAFE_DIVIDE(SUM(buffers), NULLIF(SUM(starts),0)), 100.0) AS buffers_per_100_starts
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.worst_devices_daily\`
WHERE event_dt BETWEEN {{start_date}} AND {{end_date}}
\[\[AND platform = {{platform}}\]\]
\[\[AND build_channel = {{build_channel}}\]\]
GROUP BY device_id, platform, app_version
ORDER BY errors DESC, buffers_per_100_starts DESC
LIMIT 200;
## 33.6. Download performance (trend)
SELECT
event_dt,
SUM(downloads_started) AS downloads_started,
SUM(downloads_completed) AS downloads_completed,
SAFE_DIVIDE(SUM(downloads_completed), NULLIF(SUM(downloads_started),0)) AS download_success_rate,
AVG(avg_download_duration_ms) AS avg_download_duration_ms
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.download_latency_daily\`
WHERE event_dt BETWEEN {{start_date}} AND {{end_date}}
\[\[AND platform = {{platform}}\]\]
\[\[AND build_channel = {{build_channel}}\]\]
GROUP BY event_dt
ORDER BY event_dt;
# 34\. Appendix C: Full-funnel analytics (Acquisition, Engagement, Monetization, Retention)
This appendix extends the playbook beyond receiver telemetry to cover the complete end-to-end customer journey: Website visit → sign up → account created → mobile onboarding → pair/claim a display → browse content → start playback on receiver → subscription/purchase → ongoing retention. These sections are essential for leadership reporting and for investor-ready metrics.
## 34.1. What this playbook previously covered vs. what this adds
Previously emphasized: receiver-side playback QoE, caching, download health, and Metabase dashboards built on daily rollups.
This appendix adds: acquisition sources, engagement loops, monetization events, and retention/cohort reporting across web + mobile + receiver.
## 34.2. Recommended GA4 event map for the complete user flow
- Acquisition (web): page_view, view_item/content_view (marketing content), sign_up_start, sign_up_complete, login.
- Activation: account_created (or sign_up), email_verified (if applicable), first_app_open (mobile), onboarding_complete.
- Pairing/Claiming display: pair_start, pair_success, pair_fail (with error_code), device_claimed, device_linked.
- Engagement: search, view_item/content_view, create_collection, add_to_collection, start_stream (mobile), playback_start (receiver), playback_complete (receiver), favorite_artist/content.
- Monetization: begin_checkout (shop/web), add_payment_info, purchase, subscription_start, subscription_renewal, subscription_cancel, refund (if needed).
- Retention: app_open (mobile/web), receiver_heartbeat, playback_start, days_active, returning_user cohorts.
## 34.3. GA4 implementation notes (web + mobile + receivers)
Web (blackdove.com, app.blackdove.com, shop.blackdove.com): use gtag.js or GTM, include UTM parameters, and implement GA4 recommended events (sign_up, login, begin_checkout, purchase). For shop, implement GA4 ecommerce parameters (items array, value, currency, transaction_id).
Android mobile app: implement GA4 via the native Google Analytics for Firebase SDK (recommended by Google for Android). Ensure event names match the canonical map and that user_id is set only after authentication (hashed/opaque).
Receivers (SCAP, SSSP, Android TV, etc.): continue using gtag.js or Measurement Protocol as per platform constraints; for offline-first receivers, queue events locally and flush when online. Ensure receiver events include device_id and, when available, account_id_hash to connect receiver telemetry to user journey.
## 34.4. BigQuery views for end-to-end funnel and retention
These views assume that web/mobile events include a stable user identifier (user_id or user_id_hash) and that pairing events include device_id. Create them in the semantic dataset alongside other KPI views.
### 34.4.1. View: journey_events_base
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.journey_events_base\` AS
\-- Purpose: Normalize key web+mobile+receiver events into one table for funneling and retention.
SELECT
event_dt,
event_ts,
platform,
stream_id,
build_channel,
app_version,
COALESCE(NULLIF(user_id,''), user_pseudo_id) AS actor_id, -- fallback for unauthenticated web users
user_id,
user_pseudo_id,
device_id,
event_name,
content_id,
collection_id,
artist_id,
traffic_source,
traffic_medium,
traffic_campaign,
geo_country,
geo_region,
geo_city,
error_code,
error_msg
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.events_base\`
WHERE event_name IN (
'page_view',
'sign_up_start','sign_up','signup','sign_up_complete','login',
'account_created','onboarding_complete',
'pair_start','pair_success','pair_fail','device_claimed','device_linked','account_link',
'begin_checkout','add_payment_info','purchase',
'subscription_start','subscription_renewal','subscription_cancel',
'content_view','search','create_collection','add_to_collection',
'start_stream','playback_start','playback_complete',
'app_open','heartbeat'
);
### 34.4.2. View: funnel_daily_web_to_first_play
Purpose: A daily funnel showing drop-offs from web visit → sign up → pairing → purchase/subscription → first receiver playback.
# 35\. Appendix D: Android Mobile Tracking Implementation (Firebase Analytics + GA4)
Scope: The Android mobile app is already using Firebase for Crashlytics. Firebase is allowed for Android and is the recommended implementation path for GA4 analytics on Android. This section is intentionally step-by-step and does not assume the app architecture (Compose vs XML, single-activity vs multi).
## 35.1. Preconditions and access
- Firebase project exists and Crashlytics is functioning in the Android app.
- GA4 property/stream for Android is confirmed in the inventory table (Section 4.1) OR created by GA4 Admin.
- A release channel strategy exists: prod vs stage builds must be distinguishable (e.g., build_channel param).
## 35.2. Add/verify Firebase Analytics dependency
If Crashlytics is already integrated, Analytics may or may not be enabled. Verify Gradle dependencies and google-services setup. Do not add Firebase to any web app-this is Android-only.
// app/build.gradle (or build.gradle.kts): ensure Google Services plugin and Analytics dependency exist
// Groovy example:
plugins {
id 'com.android.application'
id 'com.google.gms.google-services' // required
id 'com.google.firebase.crashlytics' // already present
}
<br/>dependencies {
implementation platform('com.google.firebase:firebase-bom:33.5.1 // example; pin a version')
implementation 'com.google.firebase:firebase-analytics'
implementation 'com.google.firebase:firebase-crashlytics'
}
## 35.3. Initialize and validate Analytics collection
Analytics initializes automatically if google-services.json is present. Confirm collection is enabled in the app and verify events in Firebase DebugView / GA4 DebugView.
// Example: enabling debug mode on a test device (ADB)
adb shell setprop debug.firebase.analytics.app com.blackdove.app
Replace com.blackdove.app with the app's actual applicationId from Gradle.
## 35.4. Set user identity safely (post-auth only)
Set user_id only after the user is authenticated. Use an opaque identifier or a hashed user id (no email/phone). Also set user properties needed for segmentation (subscription_tier, build_channel, app_version).
// Kotlin example
val analytics = FirebaseAnalytics.getInstance(context)
<br/>// After login succeeds:
analytics.setUserId(userIdHash) // opaque/hashed identifier
<br/>analytics.setUserProperty("subscription_tier", tier) // e.g., free/pro
analytics.setUserProperty("build_channel", buildChannel) // prod/stage
analytics.setUserProperty("app_version", versionName)
## 35.5. Log required journey events (mobile side)
Implement the canonical event taxonomy for mobile journeys. Use consistent names/parameters so the BigQuery views in Appendix C work without modification.
- onboarding_complete (when onboarding is finished)
- pair_start / pair_success / pair_fail (with error_code) when the user pairs or claims a display
- search (when searching artists/collections/artworks)
- content_view (when user opens a content detail page)
- create_collection / add_to_collection
- begin_checkout / purchase / subscription_start (if mobile handles billing; otherwise emit from backend source-of-truth)
// Kotlin event example (Firebase Analytics)
val bundle = Bundle().apply {
putString("device_id", deviceId)
putString("content_id", contentId)
putString("artist_id", artistId)
putString("collection_id", collectionId)
putString("build_channel", buildChannel)
putString("app_version", versionName)
}
analytics.logEvent("content_view", bundle)
## 35.6. Verification checklist (mobile)
- In DebugView, confirm: app_open, onboarding_complete, login, content_view, pair_success events appear with expected params.
- Confirm user_id is set only after login (events before login should not contain user_id).
- Confirm build_channel is present for segmentation (prod vs stage).
- Confirm events also appear in BigQuery export after GA4 export is enabled.
## 35.7. End-to-End Validation Journeys (No Assumptions)
These scripted journeys are used to prove that the funnel is measurable end-to-end. Each journey should be executed on a staging environment first, then production. The expected events are listed; if any are missing, fix instrumentation before dashboards are used by leadership.
- Journey A: Web visit → Signup → Pair display → First playback
• page_view (web)
• sign_up_start / sign_up / account_created (web/backend as implemented)
• pair_start / pair_success (mobile)
• account_link/device_linked (mobile/backend/receiver as implemented)
• playback_start (receiver)
- Journey B: Returning user → Browse content → Start playback → Complete
• app_open (mobile or web)
• search (optional)
• content_view (mobile/web)
• playback_start (receiver)
• playback_complete (receiver)
- Journey C: Monetization → Purchase/Subscription → First play after pay
• begin_checkout (web/shop/mobile)
• add_payment_info (optional)
• purchase (with transaction_id, currency, value, items\[\]) OR subscription_start
• playback_start (receiver)
## 35.8. Sample BigQuery verification queries
Use these queries to confirm the journeys. Replace placeholders for project/datasets and supply a known user_id_hash or device_id from your test run.
\-- Q1: Timeline for a specific device_id (receiver + pairing)
SELECT
event_ts, platform, event_name, device_id, user_id, content_id, error_code
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.journey_events_base\`
WHERE device_id = @device_id
AND event_dt BETWEEN @start_date AND @end_date
ORDER BY event_ts;
\-- Q2: Funnel drop-offs for a specific day (visit → signup → pair → pay → first_play)
SELECT \*
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.funnel_daily_web_to_first_play\`
WHERE event_dt BETWEEN @start_date AND @end_date
ORDER BY event_dt;
\-- Q3: Verify purchases include transaction_id and items
SELECT
event_ts, user_id, device_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key='transaction_id') AS transaction_id,
(SELECT value.double_value FROM UNNEST(event_params) WHERE key='value') AS value,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key='currency') AS currency
FROM \`\${PROJECT_ID}.\${GA_EXPORT_DATASET}.events_\*\`
WHERE event_name='purchase'
AND \_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', @start_date) AND FORMAT_DATE('%Y%m%d', @end_date)
LIMIT 200;
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.funnel_daily_web_to_first_play\` AS
WITH j AS (
SELECT \* FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.journey_events_base\`
),
steps AS (
SELECT
event_dt,
actor_id,
MIN(IF(event_name='page_view', event_ts, NULL)) AS ts_visit,
MIN(IF(event_name IN ('sign_up','signup','sign_up_complete','account_created'), event_ts, NULL)) AS ts_signup,
MIN(IF(event_name IN ('pair_success','device_linked','account_link','device_claimed'), event_ts, NULL)) AS ts_pair,
MIN(IF(event_name IN ('purchase','subscription_start'), event_ts, NULL)) AS ts_pay,
MIN(IF(event_name='playback_start', event_ts, NULL)) AS ts_first_play
FROM j
GROUP BY event_dt, actor_id
)
SELECT
event_dt,
COUNTIF(ts_visit IS NOT NULL) AS visited,
COUNTIF(ts_signup IS NOT NULL) AS signed_up,
COUNTIF(ts_pair IS NOT NULL) AS paired_display,
COUNTIF(ts_pay IS NOT NULL) AS paid,
COUNTIF(ts_first_play IS NOT NULL) AS first_play,
SAFE_DIVIDE(COUNTIF(ts_signup IS NOT NULL), NULLIF(COUNTIF(ts_visit IS NOT NULL),0)) AS visit_to_signup,
SAFE_DIVIDE(COUNTIF(ts_pair IS NOT NULL), NULLIF(COUNTIF(ts_signup IS NOT NULL),0)) AS signup_to_pair,
SAFE_DIVIDE(COUNTIF(ts_pay IS NOT NULL), NULLIF(COUNTIF(ts_pair IS NOT NULL),0)) AS pair_to_pay,
SAFE_DIVIDE(COUNTIF(ts_first_play IS NOT NULL), NULLIF(COUNTIF(ts_pay IS NOT NULL),0)) AS pay_to_first_play
FROM steps
GROUP BY event_dt
ORDER BY event_dt;
### 35.8.1. View: retention_weekly_by_first_play
Purpose: Cohort retention based on the week a user first starts playback on any receiver.
\-- CREATE OR REPLACE VIEW \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.retention_weekly_by_first_play\` AS
WITH j AS (
SELECT
COALESCE(NULLIF(user_id,''), user_pseudo_id) AS actor_id,
event_dt,
event_name
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.journey_events_base\`
WHERE event_name IN ('playback_start','app_open','page_view')
),
first_play AS (
SELECT
actor_id,
DATE_TRUNC(MIN(event_dt), WEEK(MONDAY)) AS cohort_week
FROM j
WHERE event_name='playback_start'
GROUP BY actor_id
),
activity AS (
SELECT
f.cohort_week,
DATE_TRUNC(j.event_dt, WEEK(MONDAY)) AS activity_week,
j.actor_id
FROM first_play f
JOIN j ON j.actor_id = f.actor_id
WHERE j.event_name IN ('playback_start','app_open','page_view')
),
cohort_sizes AS (
SELECT cohort_week, COUNT(DISTINCT actor_id) AS cohort_size
FROM first_play
GROUP BY cohort_week
)
SELECT
a.cohort_week,
a.activity_week,
DATE_DIFF(a.activity_week, a.cohort_week, WEEK) AS weeks_since_cohort,
COUNT(DISTINCT a.actor_id) AS active_users,
c.cohort_size,
SAFE_DIVIDE(COUNT(DISTINCT a.actor_id), NULLIF(c.cohort_size,0)) AS retention_rate
FROM activity a
JOIN cohort_sizes c USING (cohort_week)
GROUP BY 1,2,3,5
ORDER BY cohort_week, weeks_since_cohort;
## 35.9. Metabase questions for leadership (Acquisition/Engagement/Monetization/Retention)
Create these Metabase questions from the views above (native SQL or builder).
### 35.9.1. Acquisition: traffic sources → signups
SELECT
traffic_source,
traffic_medium,
traffic_campaign,
COUNTIF(event_name='page_view') AS visits,
COUNTIF(event_name IN ('sign_up','signup','sign_up_complete','account_created')) AS signups,
SAFE_DIVIDE(COUNTIF(event_name IN ('sign_up','signup','sign_up_complete','account_created')), NULLIF(COUNTIF(event_name='page_view'),0)) AS visit_to_signup
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.journey_events_base\`
WHERE event_dt BETWEEN {{start_date}} AND {{end_date}}
GROUP BY 1,2,3
ORDER BY visits DESC
LIMIT 100;
### 35.9.2. Full funnel: visit → signup → pair → pay → first_play
SELECT
event_dt,
visited,
signed_up,
paired_display,
paid,
first_play,
visit_to_signup,
signup_to_pair,
pair_to_pay,
pay_to_first_play
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.funnel_daily_web_to_first_play\`
WHERE event_dt BETWEEN {{start_date}} AND {{end_date}}
ORDER BY event_dt;
### 35.9.3. Retention cohorts (weekly)
SELECT
cohort_week,
weeks_since_cohort,
retention_rate,
active_users,
cohort_size
FROM \`\${PROJECT_ID}.\${BD_SEMANTIC_DATASET}.retention_weekly_by_first_play\`
WHERE cohort_week BETWEEN {{start_date}} AND {{end_date}}
ORDER BY cohort_week, weeks_since_cohort;Last updated