references/analyzy.md
Uložte jako dance/references/analyzy.md. Soubor je rozdělený do dvou bloků – spojte je za sebe bez úprav.
# Analýzy nad Dance daty – postup, recepty, chyby
## Obsah
1. Postup analýzy
2. Výběr tabulky
3. Recepty (SQL)
4. Channel grouping v L3
5. Kontroly konzistence
6. Srovnání s GA4 UI – proč se čísla liší
7. Časté chyby
8. Jak prezentovat výsledek
---
## 1. Postup analýzy
1. **Upřesni otázku na metriku + grain + období + segment.** „Kolik máme návštěv z Googlu?" → sessions (Cookies i Anonymous?), per den nebo za období, source = google (cpc i organic?), který dataset. Když je víc čtení, napiš je a vyber, nebo se zeptej – ale nejdřív dodej výsledek pro nejpravděpodobnější čtení.
2. **Zjisti kontext repa** (SKILL.md §1): projekt, dataset_id, měny, moduly.
3. **Vyber tabulku** podle §2 níže. Nejnižší vrstva, která odpoví, je správná; L3 default.
4. **Napiš SQL** s partition filtrem, `dataset_id`, `privacy`. Před spuštěním velkých dotazů dry run.
5. **Zkontroluj** (§5): součty proti `L3_acquisition_traffic`, NULL v `reporting_*`, poslední 3 dny.
6. **Prezentuj** (§8): číslo + tabulka + privacy + období + měna + co je odhad.
## 2. Výběr tabulky
| Potřebuji | Tabulka | Poznámka |
|---|---|---|
| sessions/uživatelé/tržby per zdroj, kampaň, landing page, den | `L3_acquisition_traffic` | user_count jen Cookies |
| bounce rate | `L3_bounce_rate_daily(_sources)` | Anonymous = odhad |
| objednávky, AOV, tržby per zdroj, refundy | `L3_monetization_transactions` | 1 řádek = transakce, dedup hotový |
| produkty (view → cart → purchase) | `L3_monetization_items` | kusově |
| stránky (PV, vstupy, výstupy) | `L3_pages_performance` | Cookies; distinct metriky nesčítej |
| funnel košíku | `L3_session_funnels` | otevřený funnel |
| počty eventů | `L3_engagement_events` | session_count neaditivní |
| vstupní produkt / cross-sell | `L3_initiator_product` | Cookies |
| CMP consent rate | `L3_cmp_stats_pivot` | jen s CMP trackingem |
| náklady Google Ads, ROAS | `L3_campaign_costs` + `L3_acquisition_traffic` (join campaign) nebo `L3_click_costs` + `L2_sessions_vw.session_gclid` | jen s Ads transferem |
| session duration, new vs. returning, device × zdroj × landing | `L2_sessions_vw` | |
| cokoliv per event s vlastní podmínkou (URL regex, parametr) | `L2_events_vw` | |
| košík/položky bez nákupu, item lists, promotions | `L2_items_vw` | |
| cross-device zákazníci | `L2_*_vw.master_customer_id` | jen s identity modulem |
| debugging atribuce | `L1_model_base_vw`, `L1_user_sources_vw` | |
## 3. Recept
## 3. Recepty
## 3. Recepty
Nahraď projekt a dataset_id podle repa. Období vždy uzavřené dny (`< CURRENT_DATE() - 2` je bezpečné).
### 3.1 Návštěvnost a tržby podle kanálu za období
```sql
WITH t AS (
SELECT event_date, privacy, session_source, session_medium, user_pseudo_id, purchaser_pseudo_id,
session_count, transactions, reporting_transaction_revenue
FROM `signalscz-dance.L3_dance.L3_acquisition_traffic`
WHERE event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31'
AND dataset_id = 'analytics_243381983'
)
SELECT
privacy,
CASE
WHEN session_source IN ('(direct)', 'direct') AND session_medium IN ('(none)', '(not set)') THEN 'direct'
WHEN session_medium IN ('cpc', 'ppc') OR REGEXP_CONTAINS(session_medium, r'paid') THEN 'paid'
WHEN session_medium = 'organic' THEN 'search_organic'
WHEN session_medium = 'referral' THEN 'referral'
WHEN REGEXP_CONTAINS(session_medium, r'^(email|e-mail|newsletter)$') THEN 'email'
WHEN REGEXP_CONTAINS(session_medium, r'social') THEN 'social'
ELSE 'other' END AS channel,
SUM(session_count) AS session_count,
COUNT(DISTINCT user_pseudo_id) AS user_count, -- Anonymous → 0, správně
SUM(transactions) AS transactions,
ROUND(SUM(reporting_transaction_revenue)) AS revenue_czk,
ROUND(SAFE_DIVIDE(SUM(transactions), SUM(session_count)) * 100, 2) AS cr_pct -- jen pro Cookies má smysl
FROM t
GROUP BY ALL
ORDER BY privacy, session_count DESC
```
Plný channel grouping modelu je v §4.
### 3.2 Bounce rate podle zdroje a zařízení
```sql
SELECT privacy, session_source, session_medium, device_category,
SUM(session_count) AS session_count,
ROUND(SAFE_DIVIDE(SUM(bounced_session_count), SUM(session_count)) * 100, 1) AS bounce_rate_pct
FROM `signalscz-dance.L3_dance.L3_bounce_rate_daily_sources`
WHERE event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31'
AND dataset_id = 'analytics_243381983'
GROUP BY ALL
HAVING session_count >= 100
ORDER BY session_count DESC
```
### 3.3 Objednávky, AOV, podíl anonymních
```sql
SELECT
DATE_TRUNC(event_date, WEEK(MONDAY)) AS week,
COUNT(*) AS orders,
COUNTIF(privacy = 'Anonymous') AS orders_anonymous,
ROUND(SUM(reporting_transaction_revenue)) AS revenue_czk,
ROUND(SAFE_DIVIDE(SUM(reporting_transaction_revenue), COUNT(*))) AS aov_czk,
COUNTIF(reporting_transaction_revenue IS NULL) AS missing_fx_rows -- musí být 0
FROM `signalscz-dance.L3_dance.L3_monetization_transactions`
WHERE event_date BETWEEN DATE '2026-06-01' AND DATE '2026-08-31'
GROUP BY ALL ORDER BY week
```
### 3.4 Produktový funnel (view → cart → purchase)
```sql
SELECT item_id, ANY_VALUE(item_name) AS item_name, ANY_VALUE(item_category) AS category,
SUM(view_item_quantity) AS views, SUM(add_to_cart_quantity) AS carts, SUM(purchase_quantity) AS purchases,
ROUND(SAFE_DIVIDE(SUM(purchase_quantity), SUM(view_item_quantity)) * 100, 2) AS view_to_purchase_pct,
ROUND(SUM(reporting_purchase_item_revenue)) AS revenue_czk
FROM `signalscz-dance.L3_dance.L3_monetization_items`
WHERE event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31'
GROUP BY item_id
HAVING views >= 50
ORDER BY revenue_czk DESC LIMIT 50
```
### 3.5 Konverzní funnel košíku (Cookies)
```sql
SELECT funnel_steps_order, funnel_steps,
SUM(unique_sessions_ctn) AS sessions,
ROUND(SAFE_DIVIDE(SUM(unique_sessions_ctn),
FIRST_VALUE(SUM(unique_sessions_ctn)) OVER (ORDER BY funnel_steps_order)) * 100, 1) AS pct_of_start
FROM `signalscz-dance.L3_dance.L3_session_funnels`
WHERE funnel_type = 'Průchod košíkem'
AND event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31'
AND funnel_start = 'session_start' -- quasi-closed: jen sessions, které začaly vstupem
GROUP BY ALL ORDER BY funnel_steps_order
```
### 3.6 Landing pages s nejhorším bounce rate
```sql
SELECT session_landing_url_reporting,
SUM(session_count) AS sessions,
ROUND(SAFE_DIVIDE(SUM(bounced_session_count), SUM(session_count)) * 100, 1) AS bounce_pct
FROM `signalscz-dance.L3_dance.L3_bounce_rate_daily`
WHERE event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31' AND privacy = 'Cookies'
GROUP BY ALL HAVING sessions >= 200 ORDER BY bounce_pct DESC LIMIT 30
```
### 3.7 Denní trend a srovnání s minulým rokem
```sql
SELECT event_date, SUM(session_count) AS sessions,
SUM(SUM(session_count)) OVER (ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) / 7 AS sessions_7d_avg
FROM `signalscz-dance.L3_dance.L3_acquisition_traffic`
WHERE (event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31'
OR event_date BETWEEN DATE '2025-08-01' AND DATE '2025-08-31')
AND privacy = 'Cookies'
GROUP BY event_date ORDER BY event_date
```
Při meziročním srovnání zkontroluj, jestli se mezi tím neměnil consent banner (podíl Anonymous) – jinak srovnáváš jinou populaci.
### 3.8 ROAS kampaní Google Ads (jen s Ads transferem)
```sql
WITH rev AS (
SELECT event_date, session_campaign, SUM(transactions) AS transactions, SUM(reporting_transaction_revenue) AS revenue
FROM `signalscz-dance.L3_dance.L3_acquisition_traffic`
WHERE event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31'
AND session_source = 'google' AND session_medium = 'cpc'
GROUP BY ALL
), cost AS (
SELECT event_date, LOWER(campaign_name) AS session_campaign, SUM(cost_amount) AS cost
FROM `signalscz-dance.L3_dance.L3_campaign_costs`
WHERE event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31'
GROUP BY ALL
)
SELECT session_campaign, SUM(cost) AS cost, SUM(revenue) AS revenue, SUM(transactions) AS transactions,
ROUND(SAFE_DIVIDE(SUM(revenue), SUM(cost)), 2) AS roas
FROM cost FULL JOIN rev USING (event_date, session_campaign)
GROUP BY ALL ORDER BY cost DESC
```
Pozor na měnu nákladů (účet Google Ads) vs. reporting měna, a na to, že kampaň bez gclid v datech se páruje jen přes název (`campaign_name` z Ads vs. `session_campaign` z gclid joinu v L1 – obě jsou název kampaně z Ads, takže se obvykle shodnou).
## 4. Channel grouping v L3
L3 tabulky `session_channel_grouping` nemají. Buď jdi do `L2_sessions_vw` (má ho), nebo replikuj logiku ze `includes/snippets.js` (`marketing.channel_grouping`) jako CASE nad `session_source`, `session_medium`, `session_campaign` v pořadí: direct → shopping_paid (campaign ~ shop & medium ~ cp/ppc/paid) → search_paid (google|bing|sklik & paid) → social_paid → video_paid (youtube & paid) → display → other_paid → shopping_organic → social_organic → video_organic → search_organic → email → affiliate → referral → audio → sms → mobile_push → (other). Přečti aktuální snippet v repu – klienti si ho upravují.
## 5. Kontroly konzistence
- **Součet sessions** za období z `L3_acquisition_traffic` = součet z `L3_bounce_rate_daily` = `SUM(totals.visits)` z `L2_sessions_vw`. Když se liší, někde je jiný checkpoint stav (tabulky neběžely stejný den) – porovnej `MAX(event_date)` v každé.
- **Transakce**: `L3_monetization_transactions` `COUNT(*)` vs. `SUM(transactions)` z `L3_acquisition_traffic` (obě dedupují) – malé rozdíly vznikají u transakcí s více `purchase` eventy v různých sessions.
- **`reporting_*` NULL**: `COUNTIF(reporting_x IS NULL AND x IS NOT NULL)` → chybí kurz v `L1_exchange_rates` pro daný den/pár.
- **Poslední dny**: `MAX(event_date)` tabulky a fakt, že poslední ~3 dny se přepočítávají; intraday běh přidává dnešek částečně.
- **Podíl Anonymous** (`session_count` Anonymous / celkem) – když skokově roste, změnil se consent banner nebo tracking, ne chování uživatelů.
## 6. Srovnání s GA4 UI – proč se čísla liší
- GA4 UI modeluje chování uživatelů bez consentu (behavioral modeling) nebo je vůbec nezobrazuje; Dance je má jako `Anonymous` s vlastní definicí session. Součet Cookies + Anonymous v Dance ≠ sessions v GA4 UI.
- GA4 „session default channel group" používá vlastní pravidla a last-non-direct fallback přes atribuční okno; Dance používá první zdroj v session + 91denní fallback na poslední zdroj uživatele + gclid/srsltid přepis. Podobné, ne identické.
- GA4 UI aplikuje thresholding, (other) řádky a sampling v explore; BigQuery export ne.
- Tržby v GA4 UI jsou v měně property; Dance `reporting_*` přepočítává denním kurzem.
- Bounce rate v GA4 UI = 1 − engagement rate; Dance Cookies to kopíruje (`engaged_session_event`), Anonymous rekonstruuje.
## 7. Časté chyby
| Chyba | Správně |
