references/tabulky-L2-L1.md
Uložte jako dance/references/tabulky-L2-L1.md.
# L2 a L1 tabulky – kdy a jak
Do L2 jdi, když L3 nemá dimenzi nebo grain, který potřebuješ (např. distinct uživatelé per stránka, session duration, kombinace event_name × source × device, položky košíku bez nákupu, custom parametry). Do L1 jen pro debugging nápočtu nebo pro parametry, které v L2 nejsou (`event_params_other`, `user_properties_other`, `items` ARRAY na eventu).
Vždy čti **`_vw` varianty** (`L2_dance_vw`, `L1_dance_vw`) – mají `reporting_*` tržby, `domain`, `master_customer_id`, `event_custom_datetime`. Fyzické `L2_dance`/`L1_dance` tabulky jen když view záměrně obcházíš.
## L2_dance_vw.L2_sessions_vw
- **1 řádek = session (Cookies)** / agregát anonymních řádků se stejnými atributy (Anonymous, `unique_session_id` NULL).
- Klíče: `dataset_id, domain, stream_id, event_date, privacy, platform, unique_session_id, user_pseudo_id, ga_session_id`.
- Atribuce session: `session_source, session_medium, session_campaign, session_campaign_id, session_gclid, session_srsltid, session_dclid, session_content, session_term, session_custom_*, session_source_platform, session_segments_ad_network_type, session_channel_grouping, session_search_term, session_ab_test, session_landing_page, session_landing_page_reporting, session_landing_url_reporting, session_page_referrer`.
- `session_start, session_end` (TIMESTAMP), `user_type`, `user_id` (ARRAY<STRING>), `device_category`, `geo_country/region/city` (první hit), `t_source/t_medium/t_name` (GA4 first-user), `sts_ads_*`, `sts_mc_*` (GA4 session last click), `signals_dance_flag`.
- `totals` STRUCT: `hits, visits, pageviews, bounces, new_visits, transactions, transaction_revenue, reporting_transaction_revenue, items_quantity, unique_items, conversions, consented_session_duration_sec, consented_session_duration_active_sec`.
- `master_customer_id, master_source_type, is_stitched` (identity modul; Anonymous vždy NULL/FALSE).
```sql
-- session duration a engagement podle kanálu (jen Cookies mají duration)
SELECT session_channel_grouping,
COUNT(*) AS session_count,
AVG(totals.consented_session_duration_sec) AS avg_duration_sec,
SAFE_DIVIDE(SUM(totals.bounces), COUNT(*)) AS bounce_rate,
SUM(totals.reporting_transaction_revenue) AS revenue_czk
FROM `signalscz-dance.L2_dance_vw.L2_sessions_vw`
WHERE event_date BETWEEN '2026-08-01' AND '2026-08-31'
AND privacy = 'Cookies'
GROUP BY ALL ORDER BY session_count DESC
```
## L2_dance_vw.L2_events_vw
- **1 řádek = event.** Session atributy jako výše (bez `totals`), plus `session_engaged`, `engaged_session_event`.
- Event: `event_name, event_timestamp, event_custom_timezone, event_custom_datetime, event_id, event_number, page_referrer, page_location, previous_page_location, page_type, page_segment, batch_page_id, search_term, ab_test, user_type, user_unique_id, engagement_time_msec, is_conversion, signals_dance_flag, cmp_action_detail`.
- `event_details` STRUCT: `device_*` (17 sloupců), `geo_country/region/city`, `ecommerce_*` (`total_item_quantity, purchase_revenue, reporting_ecommerce_purchase_revenue, refund_value, shipping_value, tax_value, unique_items, transaction_id` + `_in_usd` varianty), `cts_*` (collected_traffic_source).
- `master_customer_id` atd. (identity).
- Cluster `dataset_id, privacy, event_name, session_source` → filtruj `event_name` co nejdřív.
```sql
-- distinct uživatelé, kteří viděli stránku a pak nakoupili ve stejné session (Cookies)
WITH pv AS (
SELECT DISTINCT unique_session_id, user_pseudo_id
FROM `signalscz-dance.L2_dance_vw.L2_events_vw`
WHERE event_date BETWEEN '2026-08-01' AND '2026-08-31' AND privacy = 'Cookies'
AND event_name = 'page_view' AND REGEXP_CONTAINS(page_location, r'/akce/')
), buy AS (
SELECT DISTINCT unique_session_id
FROM `signalscz-dance.L2_dance_vw.L2_events_vw`
WHERE event_date BETWEEN '2026-08-01' AND '2026-08-31' AND privacy = 'Cookies'
AND event_name = 'purchase' AND event_details.ecommerce_transaction_id <> '(not set)'
)
SELECT COUNT(DISTINCT pv.user_pseudo_id) AS users_seen,
COUNT(DISTINCT IF(buy.unique_session_id IS NOT NULL, pv.user_pseudo_id, NULL)) AS users_bought
FROM pv LEFT JOIN buy USING (unique_session_id)
```
## L2_dance_vw.L2_items_vw
- **1 řádek = event × položka** (`items` UNNEST, `item_id IS NOT NULL`). Session atributy + `event_name, event_id, event_number, batch_page_id, page_location, page_referrer` + item sloupce (`item_id, item_name, item_brand, item_variant, item_category…5, price, quantity, item_revenue, reporting_item_revenue, item_refund, coupon, affiliation, location_id, item_list_id/name/index, promotion_id/name, creative_name/slot`, `_in_usd` varianty) + `ecommerce_*` transakce + `cts_*`, `sts_*`.
- Pro tržbu za položku u `purchase` použij `item_revenue`/`reporting_item_revenue`; `ecommerce_purchase_revenue` je tržba celé transakce a na řádku položky se opakuje.
## L2_dance.L2_anonym_transaction_attribution
Atribuce anonymních transakcí: `dataset_id, domain, event_date, ecommerce_transaction_id, session_source, session_medium, session_campaign, device_category, attribution_type, attribution_confidence, attribution_quality (high/medium/low), baseline_source, campaign_source`. `attribution_type` říká, jak vznikla: `direct_match` / `direct_match_dupl` (spárováno s page_view/gclid), `weighted` (skórované podle podobnosti, s confidence), `calibrated` (post-hoc přerozděleno z přereprezentovaných zdrojů), `allocated_global` (alokace podle baseline rozložení Cookies nákupů – čistý odhad). Konzumuje ji `L3_monetization_transactions`; přímo ji čti jen při vysvětlování, „proč má anonymní objednávka zdroj X".
## L1_dance_vw.L1_model_base_vw
- **1 řádek = GA4 event**, všechny sloupce z architektura.md §4 + `domain`, `event_id`, landing reporting, identity.
- `event_params` STRUCT (jen pivotnuté klíče z configu, vše STRING), `event_params_other` ARRAY<STRUCT<key,value>>, `user_properties` / `user_properties_other`, `items` ARRAY.
- Cluster `dataset_id, privacy, platform, event_name`.
- Použij pro: parametry mimo pivot (`(SELECT value FROM UNNEST(event_params_other) WHERE key = 'x')`), `items` bez UNNESTu (např. počet položek v `add_to_cart`), ověření session atribuce konkrétní session (`WHERE unique_session_id = '...' ORDER BY event_number`), master id pro Anonymous přes user key fallback.
```sql
-- debug: proč má session tenhle zdroj
SELECT event_number, event_name, event_timestamp, session_source, session_medium, session_campaign,
session_gclid, session_landing_page, event_params.page_referrer, event_params.source, event_params.medium
FROM `signalscz-dance.L1_dance_vw.L1_model_base_vw`
WHERE event_date = '2026-08-15' AND unique_session_id = '1234567890|abc.def|1755250000'
ORDER BY event_number
```
## L1_dance_vw.L1_user_sources_vw
Historie stránek nesoucích zdroj (jen Cookies). Sloupce `source, medium, campaign, campaign_id, term, content, manual_*, custom_*, gclid, dclid, srsltid, page_location, page_referrer, ignored, is_first_batch_page_id, is_first_event, batch_page_number`. Použij pro audit atribuce („odkud model vzal `session_source` pro session X") a pro analýzu vícezdrojových session (druhý a další `batch_page_number` se zdrojem = mid-session kampaň, kterou GA4 nezapočítá).
## L1_dance.L1_exchange_rates
`date, from_currency, to_currency, exchange_rate, source, created_at` – 1 kurz na pár a den. Pro kontrolu „proč je reporting_revenue NULL": `SELECT * WHERE date = '…' AND from_currency = 'EUR' AND to_currency = 'CZK'`.
## L1_dance.L1_customer_map (jen s identity modulem)
Viz `moduly-volitelne.md` – mapa identit → `master_customer_id`. Tabulku vytváří operations, ne Dataform table; join vzor je tam.
## Tipy k nákladům
- L1_model_base je řádově největší tabulka (všechny eventy, `items` ARRAY, `event_params_other`). Vždy `event_date` filtr, vyber jen potřebné sloupce (BigQuery účtuje po sloupcích), a pokud jde o `items`, uvažuj `L2_items_vw`.
- Views skenují podkladovou tabulku – `L2_sessions_vw` stojí tolik jako `L2_sessions` + malý join na kurzy a mapu identit.
- Dry run před spuštěním: `bq query --use_legacy_sql=false --dry_run '<sql>'` a přepočet `total_bytes_processed` na cenu (6,25 USD/TiB v EU).
