references/tabulky-L3.md
Uložte jako dance/references/tabulky-L3.md. Soubor je rozdělený do dvou bloků – spojte je za sebe bez úprav.
# Katalog L3 tabulek (`<projekt>.L3_dance.*`)
Zdrojové soubory: `definitions/core/L3_dance/*.sqlx`; insight tabulky (`L3_pages_performance`, `L3_session_funnels`, `L3_initiator_product`, `L3_cmp_stats*`) jsou v podsložce `definitions/core/L3_dance/insights/`. Všechny jsou partitionované po `event_date` (kromě `L3_campaign_costs`/`L3_click_costs`, které mají `event_date` = `segments_date` z Google Ads, a `L3_anomalies` po `week_start`). Sloupce začínají `event_date, dataset_id, domain, privacy`. Pokud tabulka v BigQuery chybí, klient buď modul nemá, nebo ještě neběžel – ověř přes `INFORMATION_SCHEMA.TABLES`.
Přehled – otázka → tabulka:
| Otázka | Tabulka |
|---|---|
| návštěvy, uživatelé, tržby podle zdroje / kampaně / landing page | `L3_acquisition_traffic` |
| bounce rate, engaged sessions (po landing page / po zdroji, geo, device) | `L3_bounce_rate_daily`, `L3_bounce_rate_daily_sources` |
| objednávky, tržby, AOV, refundy, doprava, atribuce transakcí | `L3_monetization_transactions` |
| produkty: zobrazení → košík → nákup, tržby za položky | `L3_monetization_items` |
| počty eventů, sessions s eventem | `L3_engagement_events` |
| výkon stránek: pageviews, vstupy, výstupy, předchozí/další stránka | `L3_pages_performance` |
| tržba per stránka v session (Cookies) | `L3_cookies_engagement_pages` |
| nákupní funnel (session_start → … → purchase) | `L3_session_funnels` |
| vstupní produkt (initiátor nákupu), cross-sell | `L3_initiator_product` |
| cookie lišta (CMP) | `L3_cmp_stats`, `L3_cmp_stats_pivot` |
| náklady Google Ads per kampaň / per klik | `L3_campaign_costs`, `L3_click_costs` |
--
---
---
## L3_acquisition_traffic
- **Typ:** incremental, cluster `privacy, session_source, session_medium`.
- **Grain:** `event_date × dataset_id × domain × privacy × user_pseudo_id × purchaser_pseudo_id × session_source × session_medium × session_campaign × session_content × session_term × session_landing_url_reporting × session_landing_page_reporting`. Source/medium/campaign/content/term jsou **LOWER()**.
- **Zdroj:** (1) `L2_sessions_vw` – všechny sessions (Cookies i Anonymous), transakce a tržby jen pro Cookies; (2) `L3_monetization_transactions` s `privacy = 'Anonymous'` – anonymní transakce s atribucí z transakce (`session_count = 0`, `event_count = 0`, content/term/landing/user_pseudo_id NULL).
- **Metriky:** `session_count` (= suma `totals.visits`), `event_count` (= `totals.hits`), `transactions`, `transaction_revenue`, `reporting_transaction_revenue`.
- **Poznámky:** `user_pseudo_id` v grainu → `COUNT(DISTINCT user_pseudo_id)` = uživatelé (jen Cookies), `COUNT(DISTINCT purchaser_pseudo_id)` = nakupující. Anonymous řádky mají `user_pseudo_id` NULL. Channel grouping tu není – dopočítej CASE nad source/medium (`analyzy.md`).
```sql
SELECT event_date, privacy, session_source, session_medium,
SUM(session_count) AS session_count,
COUNT(DISTINCT user_pseudo_id) AS user_count, -- jen Cookies dává smysl
SUM(transactions) AS transactions,
SUM(reporting_transaction_revenue) AS revenue_czk
FROM `signalscz-dance.L3_dance.L3_acquisition_traffic`
WHERE event_date BETWEEN '2026-08-01' AND '2026-08-31'
AND dataset_id = 'analytics_243381983'
GROUP BY ALL
ORDER BY event_date, session_count DESC
```
## L3_bounce_rate_daily
- **Typ:** incremental, cluster `dataset_id, privacy, platform`.
- **Grain:** `event_date × dataset_id × domain × privacy × platform × session_landing_page_reporting × session_landing_url_reporting`.
- **Zdroj:** Cookies z `L2_sessions_vw` (`totals.visits`, `totals.bounces`). Anonymous z `L2_events_vw`: „session" = `batch_page_id` s `session_start`; engaged = `session_engaged`/`engaged_session_event`/`user_engagement`/konverze/`engagement_time ≥ 10 s`/následná stránka do 7 s se stejným device fingerprintem a `page_referrer = page_location` první stránky.
- **Metriky:** `session_count`, `bounced_session_count`, `engaged_session_count`. Bounce rate = `SAFE_DIVIDE(SUM(bounced), SUM(session_count))`.
- **Poznámky:** Anonymous bounce je rekonstrukce – označ jako odhad. Cookies bounce = session bez `engaged_session_event = 1` (GA4 definice engaged session), ne „1 pageview".
## L3_bounce_rate_daily_sources
Totéž jako výše plus dimenze `session_source, session_medium, session_campaign, session_keyword (= session_term), geo_country, geo_city, device_category`. Cluster `privacy, session_source, session_medium, device_category`. Pro Anonymous jsou source/medium z page_view se `session_start` (MAX v rámci batch_page_id).
## L3_monetization_transactions
- **Typ:** incremental, cluster `privacy`.
- **Grain:** **1 řádek = transakce** (`dataset_id × transaction_id`, dedup `QUALIFY ROW_NUMBER() … ORDER BY event_timestamp` – první purchase vyhrává; `"(not set)"` vyřazeno).
- **Zdroj:** `L2_events_vw` s `event_name = 'purchase'`; pro Anonymous se `session_source/medium/campaign` dotahují z `L2_anonym_transaction_attribution`, fallback `(direct)/(none)/(not set)`.
- **Sloupce:** `session_source, session_medium, session_campaign, transaction_id, total_item_quantity, transaction_revenue, reporting_transaction_revenue, refund_value, shipping_value, tax_value`.
- **Použití:** počet objednávek = `COUNT(*)`, AOV = `SAFE_DIVIDE(SUM(reporting_transaction_revenue), COUNT(*))`. Jediné místo, kde má anonymní transakce atribuci na zdroj.
## L3_monetization_items
- **Grain:** `event_date × dataset_id × domain × privacy × item_id × item_name × item_brand × item_variant × item_category…5`.
- **Zdroj:** `L2_items_vw`.
- **Metriky:** `view_item_quantity`, `add_to_cart_quantity`, `purchase_quantity`, `purchase_item_revenue`, `reporting_purchase_item_revenue`. Quantity = suma `quantity` z items (u `view_item` typicky 1/položka).
- **Poznámky:** konverzní poměr produktu = `purchase_quantity / view_item_quantity` (kusově, ne per uživatel). Item dimenze se mění v čase (název, kategorie) → agreguj přes `item_id`.
## L3_engagement_events
- **Grain:** `event_date × dataset_id × domain × privacy × user_pseudo_id × event_name`.
- **Metriky:** `session_count` (Cookies: DISTINCT session s eventem; Anonymous: COUNT(session_start)), `event_count`, `transactions`, `transaction_revenue`, `reporting_transaction_revenue` (smysl jen pro `purchase`).
- **Pozor:** `session_count` **není aditivní přes event_name** (jedna session má víc eventů). Pro Anonymous je `user_pseudo_id` NULL = agregát všech anonymních. Uživatelé s eventem = `COUNT(DISTINCT user_pseudo_id) WHERE privacy = 'Cookies'`.
## L3_pages_performance
- **Grain:** `event_date × dataset_id × domain × privacy(Cookies) × platform × session_source × session_medium × session_campaign × session_keyword × geo_country × geo_city × device_category × page_path × page_location × page_hostname × page_clean × previous_page_* × next_page_* × entry_page* × exit_page*`.
- **Zdroj:** `L2_events_vw`, jen `page_view` a `privacy = 'Cookies'`.
- **Metriky:** `page_view_count`, `user_count`, `session_count`, `entry_count`, `exit_count`, `bounced_session_count` (session s 1 pageview, kde je stránka exitem).
- **Pozor:** velmi jemný grain (previous/next page) – pro report per stránka agreguj `SUM(page_view_count)`, ale `user_count`/`session_count` **nesčítej** přes řádky (jsou distinct jen v rámci řádku). Pro distinct uživatele stránky jdi do `L2_events_vw`.
## L3_cookies_engagement_pages
- **Grain:** `event_date × dataset_id × domain × privacy(Cookies) × user_pseudo_id × unique_session_id × page_path`.
- **Metriky:** `session_revenue`, `session_reporting_revenue` (tržba **celé session**, opakuje se na každém page_path), `page_revenue`, `page_reporting_revenue` (purchase na této page_path), `page_view_count`, `page_event_count`.
- **Použití:** „tržba sessions, které viděly stránku X" = `SUM(session_reporting_revenue)` **po dedupu na `unique_session_id`** (`SELECT DISTINCT unique_session_id, session_reporting_revenue …`). Řádek = session × stránka, ne agregát.
## L3_session_funnels
- **Grain:** `funnel_type × event_date × privacy × session_source × session_medium × channel_grouping × device_category × new_visitor × funnel_steps × funnel_steps_order × funnel_start`.
- **Zdroj:** `L2_events` (fyzická), eventy `session_start, view_item, add_to_cart, view_cart, begin_checkout, add_shipping_info, add_payment_info, add_personal_info, purchase`.
- **Funnely:** `"Průchod košíkem"` = **otevřený** session funnel jen Cookies (každý krok nezávisle: session krok vyvolala / nevyvolala, bez pořadí; `funnel_start` = skutečně první krok session → pro přísnější pohled filtruj `funnel_start = 'session_start'`). `"Event funnel"` = čisté počty eventů, Cookies i Anonymous, `unique_sessions_ctn`/`unique_users_ctn`/`new_visitor`/`funnel_start` NULL.
- **Metriky:** `unique_sessions_ctn`, `event_ctn`, `unique_users_ctn`. Konverze kroku = `sessions(krok N) / sessions(krok 1)`.
- **Pozor:** kroky a jejich pořadí jsou v SQL natvrdo; u klienta se liší (viz `SPECIFIKA PROJEKTU` v souboru). Nemá `dataset_id`/`domain` v grainu – pokud má klient víc properties, jsou sloučené.
## L3_initiator_product
- **Grain:** `dataset_id × domain × event_date × session_source_medium × session_medium (cpc/non-cpc) × initial_sku × item_* × sell_type`.
- **Logika (Cookies):** `initial_sku` = `item_id` z `view_item` na landing page session (`page_location = session_landing_page`), jinak `'No product'`. `sell_type`: `LEAD PRODUCT SALE` (koupen jen vstupní), `CROSS SELL` (koupeno něco jiného), `LEAD PRODUCT SALE AND CROSS SELL`, `NO SELL`.
- **Metriky:** `sell_count`, `session_count`, `sold_items` (ARRAY), `item_revenue`, `initial_item_revenue`, `other_item_revenue` (lokální měna, bez reporting varianty).
## L3_cmp_stats / L3_cmp_stats_pivot
- Denní počty eventů CMP lišty (`config.cmp_event_name`) podle `cmp_action_detail`, `page_location`, `session_landing_url_reporting`, `device_type`, source/medium/campaign, `cmp_page_type` (Landing/Other). Pro Anonymous se zdroj dotahuje přes `batch_page_id` z page_view. Pivot má sloupce `cnt_show, cnt_accept, cnt_deny, cnt_close, cnt_view_preferences, cnt_save_preferences, cnt_settings_open` – názvy akcí natvrdo, u klienta upravit. Consent rate = `cnt_accept / cnt_show`.
## L3_campaign_costs, L3_click_costs
- **Typ:** `table` (full refresh), zdroj Google Ads transfer z `config.tables.gads.*` (ne z `gads_config.js`).
- `L3_campaign_costs`: `event_date (= segments_date) × campaign_id × campaign_name → cost_amount` (poslední známý název kampaně).
- `L3_click_costs`: `event_date × click_view_gclid × campaign × ad_group × keyword × segments_device → cost_amount` – rozpočítání denních nákladů kampaně/sestavy/klíčového slova na jednotlivé gclid kliky (průměrné CPC + posuny tak, aby součet seděl na kampaň). Join na sessions přes `session_gclid`.
- Bez Google Ads transferu tabulky neexistují nebo selhávají – ověř.
## L3_anomalies
**DEMO.** Týdenní anomálie tržeb/marže/kusů na úrovních eshop → kategorie → produkt, ale data jsou **syntetická (deterministický FARM_FINGERPRINT)**, protože `shoptet.L2_shoptet_order_items` je v template prázdná. Nepoužívej pro reálný report; slouží k demu analytického modulu „Anomálie". Produkční verze modulu žije mimo tohle repo.
