references/architektura.md
Uložte jako dance/references/architektura.md. Soubor je rozdělený do dvou bloků – spojte je za sebe bez úprav.
# Architektura Dance – jak vzniká L1, L2, L3
Čti, když potřebuješ vysvětlit, proč číslo vyšlo tak, jak vyšlo, když píšeš nebo upravuješ `.sqlx`, nebo když řešíš přepočet historie.
## Obsah
1. Tok dat
2. Zdroj: GA4 export a výběr shardů
3. L1_user_sources – historie zdrojů
4. L1_model_base – krok za krokem
5. Views `_vw`
6. L2 vrstva
7. L3 vrstva
8. Checkpoint, recalc tabulka, intraday, backfill
9. Tagy a plánování
10. Konvence (sloupce, názvy metrik, dokumentace, styl)
11. Jak napsat nový L3 model
---
## 1. Tok dat
```
GA4 export events_YYYYMMDD (+ intraday/fresh) Google Ads transfer (volitelné)
│ │
▼ ▼
L1_dance.L1_user_sources ──┐ L1_dance.L1_gads_campaigns / _adgroups
│ │ │
▼ ▼ ▼
L1_dance.L1_model_base ◄── L1_user_sources_vw ◄── L1_dance.L1_gads_gclid_clicks
│
▼ (+ domain, event_id, landing reporting, master_customer_id)
L1_dance_vw.L1_model_base_vw
│
├──► L2_dance.L2_sessions ──► L2_dance_vw.L2_sessions_vw ─┐
├──► L2_dance.L2_events ──► L2_dance_vw.L2_events_vw ─┼─► L3_dance.* (+ L1_exchange_rates pro reporting_*)
├──► L2_dance.L2_items ──► L2_dance_vw.L2_items_vw ─┘
└──► L2_dance.L2_anonym_* (atribuce anonymních transakcí) ──► L3_monetization_transactions
```
Fyzické tabulky (`L1_dance`, `L2_dance`) jsou inkrementální. Views (`*_vw`) přidávají věci, které se mají měnit bez přepočtu historie: doménu, přepočet měn, timezone, `master_customer_id`. **L3 čte z `_vw`, ne z fyzických tabulek** (výjimka: `L3_session_funnels` čte `L2_events`).
## 2. Zdroj: GA4 export a výběr shardů
`config.ga4.events[]` = seznam GA4 properties (`database` = projekt s exportem, `schema` = `analytics_<property_id>` = `dataset_id`, `name` = `events_*`, `currency`). Z něj se generuje `combinedQueryAll` (UNION ALL přes properties, každý řádek dostane `dataset_id`).
GA4 export má pro jeden den až tři shardy: `events_YYYYMMDD` (finální), `events_fresh_YYYYMMDD`, `events_intraday_YYYYMMDD`. `config.ga4_suffix_declarations` vybere přes `INFORMATION_SCHEMA.TABLES` **pro každý den jen jeden shard** s prioritou daily (1) > fresh (2) > intraday (3), a to jen v okně `date_checkpoint < den <= date_checkpoint_end`. Díky tomu se intraday data nikdy nesčítají s finálními a skenují se jen potřebné tabulky.
## 3. L1_user_sources – historie zdrojů
`L1_dance.L1_user_sources` (inkrementální, partition `event_date`, cluster `dataset_id, is_first_batch_page_id, is_first_event`). Bere jen eventy `page_view`, `session_start`, `firebase_campaign`, jen `privacy = Cookies` a jen ty, které **nesou zdroj** (`source`, nebo `gclid`/`srsltid`, nebo extrahované `utm_source` z `page_location`). Účel: 1 řádek = stránka (batch_page_id) v session, která přinesla nějaký zdroj.
Odvození zdroje (priorita):
1. `gclid` (param nebo z URL) → `source = google`, `medium = cpc`; `campaign/campaign_id/term/content` se dotahují joinem na `L1_gads_gclid_clicks` (campaign_name, campaign_id, keyword → term, ad_group_name → content).
2. `srsltid` → `google / organic / 'Shopping Free Listings'`.
3. event param `source/medium/campaign/...` (GA4 collected).
4. `utm_*` extrahované regexem z `page_location` (přes UDF `URLDECODE`).
5. referrer: `medium = referral`, `source` = host z `page_referrer`, `campaign = (referral)`. Interní referrer (stejný host) se nuluje.
Sloupce `manual_*` = totéž bez přepisu podle gclid (surové UTM). `custom_*` = varianta s klientskou logikou (dnes DV360: `source = dv360` → `custom_source = DV360`, medium rtb/cpm). `ignored = 'true'` = referral, který má `ignore_referrer = 'true'` (GA4 referral exclusion) – takový zdroj se do session nepropaguje.
`is_first_batch_page_id = 1` = první stránka v session; `is_first_event = 1` = první event na té stránce. **Zdroj session = řádek s `is_first_batch_page_id = 1 AND is_first_event = 1 AND ignored = 'false'`** (viz CTE `user_sources` v L1_model_base).
## 4. L1_model_base – krok za krokem
`L1_dance.L1_model_base`: inkrementální, partition `event_date`, cluster `dataset_id, privacy, platform, event_name`. 1 řádek = 1 GA4 event. CTE po pořádku:
### base_raw
- Pivot `event_params` do STRUCTu `event_params.<key>` – **jen klíče z `config.params.in_all` + `in_custom`**. Vše ostatní jde do `event_params_other` (ARRAY<STRUCT<key, value>>). Totéž `user_properties` / `user_properties_other` podle `config.properties`. Hodnota = COALESCE(string, int, float, double) jako STRING – proto se v L2 castuje (`CAST(event_params.engagement_time_msec AS NUMERIC)`).
- Rozbalení `device.*`, `geo.*`, `app_info.*`, `traffic_source.*` (→ `t_source/t_medium/t_name` = GA4 first-user source), `ecommerce.*`, `collected_traffic_source.*` (→ `cts_*`), `session_traffic_source_last_click.*` (→ `sts_ads_*`, `sts_mc_*`), `items` zůstává ARRAY.
### base_intra
Doplnění chybějících `user_pseudo_id`, `ga_session_id`, `analytics_storage`, `ads_storage` přes `FIRST_VALUE ... OVER (PARTITION BY dataset_id, stream_id, batch_page_id, device fingerprint, geo)`. Řeší situaci, kdy část eventů jedné stránky (batch) přijde bez consentu a část s ním – celá stránka se sjednotí.
### base
- **`privacy`**: `Cookies` když `analytics_storage = 'Yes'` **nebo** (`user_pseudo_id` i `ga_session_id` nejsou NULL); jinak `Anonymous`. Anonymous = event bez identifikátoru cookie (consent mode „denied" / server-side bez cookie).
- Extrakce `extracted_utm_*`, `extracted_gclid`, `extracted_dclid` z `page_location`.
### base_cookies (privacy = Cookies)
- `unique_session_id = stream_id|user_pseudo_id|ga_session_id`.
- `event_number` = pořadí eventu v session (podle timestamp, batch_ordering_id, bundle, batch_event_index).
- `session_start / session_end` = první / poslední event_timestamp session.
- Session atribuce (`session_source`, `session_medium`, `session_campaign`, `_campaign_id`, `_gclid`, `_dclid`, `_srsltid`, `_content`, `_term`, `_custom_*`, `_source_platform`, `_segments_ad_network_type`, `_search_term`, `_ab_test`, `_page_referrer`) = join na `L1_user_sources_vw` **první stránku session se zdrojem**. Pokud má `ignored = 'true'`, je NULL.
- `session_landing_page` = `page_location` z user_sources, jinak první `page_location` v session.
### base_cookies_final – fallback „last non-direct click"
Sessions bez zdroje (`session_source IS NULL`) dostanou **poslední neignorovaný zdroj téhož `user_pseudo_id` (a `dataset_id`) z `L1_user_sources_vw` v okně 91 dní před `session_start`**. Pokud ani ten není, `(direct)` / `(none)`. Důsledek: **Dance `session_source` odpovídá spíš GA4 „session default channel" s last-non-direct logikou, ne surovému referreru.** Pozor: hodnota 91 je v SQL natvrdo (`INTERVAL -91 DAY`), konstanta `config.lookback_window_last_click` na ni dnes není napojená.
### base_anonymous (privacy = Anonymous)
Bez cookie není session. Model proto definuje:
- `session_start` = timestamp eventu **jen** když `event_name = 'page_view'` **a** host `page_referrer` ≠ host `page_location` (externí příchod) **a** nejde o ignorovaný referral. Jinak NULL.
- Session atributy (`session_source` atd.) se plní **jen na tomto řádku**, ze stejných pravidel (gclid → google/cpc, srsltid → google/organic/Shopping Free Listings, jinak event param, jinak extracted utm, jinak `(direct)`/`(none)`), campaign z `L1_gads_gclid_clicks` podle gclid.
- `event_number = 1` pro všechny řádky, `session_end = NULL`, `unique_session_id` je většinou NULL (CONCAT s NULL).
- `base_anonymous_sessions`: v rámci `batch_page_id` + device fingerprint zůstane jen první `session_start` (víc page_view s externím referrerem na jedné stránce = jedna „session").
**Analytický důsledek:** pro Anonymous je počet sessions = `COUNT(session_start)` (tak to dělá `L2_sessions.totals.visits`), počet uživatelů neexistuje, a atribuce je na úrovni „vstupní stránky", ne uživatele. Anonymous transakce se atribuují zvlášť (L2_anonym_*).
### final
- Přepis `session_source/medium/campaign` podle `session_gclid` / `session_srsltid` (google/cpc, google/organic/Shopping Free Listings) i pro fallbackové zdroje.
