Read BigQuery and Dataform data

Follow the layers

1. Raw mempertahankan bentuk sumber dan provenance.
2. Staging memilih environment, merapikan tipe, memecah field penting, dan melakukan dedup sesuai kontrak sumber.
3. Core / base menyusun event, session, page visit, dan dimensi dasar.
4. Core / enrichment menambahkan konteks interaksi dan hubungan antarrecord.
5. Core / attribution menyiapkan konteks campaign/acquisition.
6. master_analytics menghubungkan kedua sumber dengan WhatsApp, customer profiling, dan main marketing.

Mart khusus diserahkan kepada tim pemakai. Jangan menjumlahkan event bmlytics dan mirror GA sebagai dua aksi.

Raw is not always a physical copy

Pada release production 18 September 2026, raw_ga.events adalah view atas native export analytics_546016493.events_*. Native GA tetap memiliki tabel daily/intraday; daily diprioritaskan pada proses staging. staging_ga.stg_events hanya mengambil hostname production bumame.com dan www.bumame.com.

raw_bmlytics.events dan raw_bmlytics.journeys membaca physical dataset raw_landing_analytics_production. Dataset staging lama tetap disimpan. Nama staging_bmlytics dan staging_ga menjelaskan lapisan transformasi, bukan environment aplikasi staging.

Workflow Dataform landing-analytics-production / production-15min berjalan setiap 15 menit. Release production mengompilasi branch main setiap jam. Jangan mengaktifkan kembali workflow staging lama yang menulis tabel canonical yang sama.

Raw bmlytics mempertahankan payload JSON. Di staging_bmlytics.stg_events, field penting menjadi kolom bertipe: occurred_at TIMESTAMP, position INT64, depth_percent INT64, serta identitas dan UTM STRING. Analyst tidak perlu melakukan JSON parsing berulang untuk field tersebut.

Raw payload path	Staging column
$.anonymous_id	anonymous_id
$.properties.position	position
$.properties.status	form_status
$.acquisition.utm_campaign	utm_campaign
$.whatsapp_click_id atau legacy $.bm_click_id	whatsapp_click_id

Table catalog

Daftar berikut diambil dari definisi SQLX lokal. Ini menjelaskan kontrak source; keberadaan, freshness, dan jumlah baris live harus diverifikasi saat UAT.

Dataset	Table / view	Contract
core_bmlytics	dim_base_components	Observed page/component identifiers, including CMS button Tracking IDs. New IDs have generated display labels, not captured button text. No historical tracking settings.
core_bmlytics	dim_base_pages	One installed page type. Individual content uses page_entity_id.
core_bmlytics	fact_attribution_session_campaigns	One session acquisition from earliest observed event. No invented attribution window.
core_bmlytics	fact_base_events	One website event. Browser identity is not customer identity.
core_bmlytics	fact_base_page_visits	One observed page visit, scoped to browser/session/environment.
core_bmlytics	fact_base_sessions	One browser session. Observed span is not active engagement time.
core_bmlytics	fact_base_whatsapp_references	One prepared CRM WhatsApp reference.
core_bmlytics	fact_enrichment_component_visits	One component/content/position per page visit. No impression means unobserved, not proof of disabled tracking.
core_bmlytics	fact_enrichment_page_transitions	One observed page visit with its next observed page; NULL destination is not proven exit.
core_bmlytics	fact_enrichment_page_visits	One page visit with measured interactions; no inferred reading time.
core_ga	dim_base_streams	One observed GA stream.
core_ga	fact_attribution_first_user_acquisition	One GA property/user with native first-user acquisition.
core_ga	fact_attribution_session_acquisition	One GA session with native last-click session acquisition; never substituted with first-user traffic.
core_ga	fact_base_events	One GA event; bm_event_id links mirrored website events.
core_ga	fact_base_sessions	One identifiable GA session scoped to property, stream and user.
core_ga	fact_enrichment_event_context	One event with extracted device, page, location and engagement context.
core_ga	fact_enrichment_session_context	One GA session with first observed page/device context.
master_analytics	fact_attribution_conversion_credits	One canonical main marketing conversion/touchpoint credit. Retains every credit for linked orders; no new attribution model.
master_analytics	fact_enrichment_customer_links	One confirmed non-ambiguous customer mapping and its evidence method. No raw phone or customer name exported.
master_analytics	fact_enrichment_customer_touchpoints	One directly evidenced customer event/reference. Anonymous history is not silently assigned to a person.
master_analytics	fact_enrichment_event_links	One observed event mirror pair; do not add mirror events to website totals.
master_analytics	fact_enrichment_identity_links	One observed browser/GA identity pair, not cross-device customer identity.
master_analytics	fact_enrichment_order_context	One company/transaction using main marketing values; multiple session links do not multiply revenue.
master_analytics	fact_enrichment_whatsapp_message_links	One exact reference/message link. Production Yellow is explicitly excluded for staging references.
raw_bmlytics	events	Production source records; raw at-least-once delivery. Physical source retained.
raw_bmlytics	journeys	Production source records; raw at-least-once delivery. Physical source retained.
raw_ga	events	Native GA export rows with hostname/environment labels, including daily and intraday provenance.
staging_bmlytics	stg_events	One consented event per event ID. Optional event-specific values remain NULL.
staging_bmlytics	stg_whatsapp_references	One prepared WhatsApp reference; does not prove a message was sent.
staging_ga	stg_events	One GA event after daily/intraday selection and mirror dedup. Legitimate standard event multiplicity retained.

Interpret the results

• Traffic dan perjalanan halaman: gunakan base events, sessions, page visits, lalu page transitions.
• Performa UI: component visits dan event klik; denominator harus sesuai exposure, bukan sekadar seluruh traffic.
• Campaign: session campaign/acquisition; pisahkan first-user acquisition dari session acquisition GA.
• WhatsApp: references → message links → customer links → order context.
• Revenue attribution: conversion credits dari main marketing; jangan menggandakan nilai order setelah join one-to-many.

Unique visitor/session tidak boleh dijumlahkan dari beberapa kelompok yang saling tumpang tindih. Hitung distinct kembali pada grain yang dibutuhkan. Null berarti nilai tidak tersedia, bukan otomatis nol.

Validate before publishing a dashboard

Periksa freshness, duplikasi event ID, environment, unmatched references, cardinality join, dan total revenue sebelum/sesudah join. Uji dengan synthetic fixture terlebih dahulu, lalu bukti end-to-end pada environment yang disepakati.