Jak zbudować stabilny potok danych e-commerce i uniknąć przekłamań w raportach ROI? Integracja rozproszonych danych z Google Analytics (GA4), systemów CRM oraz platform reklamowych (Google Ads, Meta Ads) w jednej bazie to potężne wyzwanie architektoniczne. Najczęstsze potknięcia to błędy wielokrotnej agregacji (fan-out effect) oraz transakcje gubione przez AdBlocka. W tym przewodniku technicznym krok po kroku wdrożymy nowoczesną strukturę ELT w Google Cloud Platform. Zobacz gotowy kod SQL dla BigQuery, który normalizuje asynchroniczne źródła i tworzy bezbłędny datamart pod Looker Studio.
Ręczne pobieranie plików CSV i sklejanie ich za pomocą funkcji VLOOKUP czy XLOOKUP w Excelu to dług technologiczny, który paraliżuje skalowanie e-commerce. Kiedy biznes mówi „chcę spójnego raportu ERS”, inżynier danych słyszy: integracja API, mapowanie asynchronicznych źródeł, czyszczenie danych (data cleansing) i unikanie błędów wielokrotnej agregacji (fan-out effect).
W tym artykule przejdziemy przez techniczną architekturę budowy nowoczesnej hurtowni danych (Data Warehouse) w ekosystemie Google Cloud Platform (GCP) z wykorzystaniem BigQuery jako silnika analitycznego oraz czystego SQL do transformacji danych.
1. Architektura referencyjna (ELT)
W nowoczesnych hurtowniach danych odchodzi się od klasycznego ETL (Extract, Transform, Load) na rzecz ELT (Extract, Load, Transform). Najpierw zrzucamy surowe dane (Raw Data) do chmury w niezmienionej formie, a całą logikę biznesową i transformacje wykonujemy bezpośrednio w hurtowni, wykorzystując moc obliczeniową BigQuery.
+-----------------------------------+
| ŹRÓDŁA DANYCH |
| (CRM API, Google Ads, Meta Ads) |
+-------------------+---------------+
|
| (Ingest: Fivetran / Airbyte / Cloud Functions)
v
+-----------------------------------+
| STAGE / RAW DATASET | -> Surowe, niezmienione tabeleappend-only
| (bigquery.raw_shop_orders) |
+-------------------+---------------+
|
| (Transformacja: SQL / dbt)
v
+-----------------------------------+
| CORE / ANALYTICS DATASET | -> Oczyszczone, zagregowane widoki datamart
| (bigquery.mart_daily_marketing) |
+-------------------+---------------+
|
| (BI Connector)
v
+-----------------------------------+
| LOOKER STUDIO / BI | -> Warstwa prezentacji i dashboardów
+-----------------------------------+
2. Ingest, czyli pobieranie danych (Extract & Load)
Aby nie wyważać otwartych drzwi, do przesyłania danych z systemów SaaS (Meta Ads, Google Ads, Shoper) do BigQuery wykorzystujemy gotowe narzędzia klasy data pipeline (np. Fivetran, Airbyte) lub natywny BigQuery Data Transfer Service (dla ekosystemu Google).
Dane lądują w warstwie Stage (Raw). Załóżmy, że dysponujemy trzema surowymi tabelami o następujących schematach:
raw_crm.orders(Dane ze sklepu):order_id(STRING),created_at(TIMESTAMP),total_revenue(NUMERIC),payment_status(STRING)raw_ga4.transactions(Dane z analityki):transaction_id(STRING),date(DATE),source_medium(STRING)raw_marketing.costs(Zbiorcza tabela kosztów z łączników reklamowych):date(DATE),platform(STRING),ad_cost(NUMERIC)
3. Warstwa Transformacji (SQL w BigQuery)
Tutaj rozwiązujemy dwa kluczowe problemy techniczne: obsługę AdBlocka oraz eliminację błędu agregacji (fan-out effect) przy łączeniu danych o różnej ziarnistości (order-level vs daily-level).
Tworzymy zapytanie SQL, które buduje zoptymalizowaną tabelę docelową (Datamart).
WITH
-- 1. Przygotowanie i oczyszczenie danych z CRM (tylko opłacone zamówienia)
clean_crm_orders AS (
SELECT
EXTRACT(DATE FROM created_at) AS order_date,
order_id,
total_revenue
FROM
`your_project.raw_crm.orders`
WHERE
payment_status = 'PAID' -- Kluczowe wymaganie biznesowe: tylko opłacone transakcje
),
-- 2. Przygotowanie danych z GA4 i mapowanie kanałów
ga4_attribution AS (
SELECT
transaction_id,
-- Normalizacja nazw kanałów do spójnego standardu
CASE
WHEN LOWER(source_medium) LIKE '%google / cpc%' THEN 'Google Ads'
WHEN LOWER(source_medium) LIKE '%facebook / cpc%' OR LOWER(source_medium) LIKE '%instagram%' THEN 'Meta Ads'
WHEN LOWER(source_medium) LIKE '%ceneo%' THEN 'Afiliacja'
ELSE 'Organic / Inne'
END AS marketing_channel
FROM
`your_project.raw_ga4.transactions`
),
-- 3. Złączenie CRM i GA4 (Rozwiązanie problemu AdBlocka poprzez LEFT JOIN)
joined_orders_attribution AS (
SELECT
crm.order_date,
crm.order_id,
crm.total_revenue,
-- Jeśli GA4 nie zarejestrował transakcji przez AdBlocka, przypisujemy ruch do 'AdBlock / Brak danych'
COALESCE(ga4.marketing_channel, 'AdBlock / Brak danych') AS channel
FROM
clean_crm_orders crm
LEFT JOIN
ga4_attribution ga4 ON crm.order_id = ga4.transaction_id
),
-- 4. Agregacja przychodów do poziomu Dnia i Kanału (Zapobieganie multiplikacji kosztów)
daily_revenue_aggregated AS (
SELECT
order_date AS date,
channel,
SUM(total_revenue) AS revenue_paid
FROM
joined_orders_attribution
GROUP BY
1, 2
),
-- 5. Przygotowanie i agregacja dziennych kosztów marketingowych
daily_costs_aggregated AS (
SELECT
date,
CASE
WHEN platform = 'google' THEN 'Google Ads'
WHEN platform = 'meta' THEN 'Meta Ads'
WHEN platform = 'ceneo' THEN 'Afiliacja'
ELSE 'Inne koszty'
END AS channel,
SUM(ad_cost) AS total_cost
FROM
`your_project.raw_marketing.costs`
GROUP BY
1, 2
)
-- 6. Finalne złączenie (FULL JOIN, aby uwzględnić dni z kosztem, ale bez przychodu i odwrotnie)
SELECT
COALESCE(r.date, c.date) AS date,
COALESCE(r.channel, c.channel) AS channel,
COALESCE(r.revenue_paid, 0) AS revenue_paid,
COALESCE(c.total_cost, 0) AS marketing_cost,
-- Bezpieczne wyliczanie wskaźnika ERS (zabezpieczenie przed podziałem przez zero za pomocą SAFE_DIVIDE)
ROUND(SAFE_DIVIDE(COALESCE(c.total_cost, 0), COALESCE(r.revenue_paid, 0)) * 100, 2) AS ers_percentage
FROM
daily_revenue_aggregated r
FULL JOIN
daily_costs_aggregated c ON r.date = c.date AND r.channel = c.channel
ORDER BY
date DESC,
ers_percentage DESC;
4. Dlaczego ten kod rozwiązuje problemy biznesowe? (Deep Dive)
Bezpieczeństwo przed Fan-Out Effect
Gdybyśmy połączyli surową tabelę kosztów marketingu bezpośrednio z tabelą zamówień (LEFT JOIN raw_crm.orders ON date), koszty dzienne zostałyby powielone dla każdego zamówienia z danego dnia. Wykorzystanie wspólnych wyrażeń tablicowych (CTE) daily_revenue_aggregated oraz daily_costs_aggregated sprowadza oba zbiory danych do tej samej ziarnistości (granulacji): jeden wiersz = jeden dzień = jeden kanał.
COALESCE i Obsługa AdBlocka
Użycie LEFT JOIN pomiędzy CRM a GA4 gwarantuje, że dane finansowe z systemu ERP/sklepu są nadrzędne. Funkcja COALESCE(ga4.marketing_channel, 'AdBlock / Brak danych') wyłapuje wszystkie zamówienia, przy których skrypt analityczny nie odpalił się na podsumowaniu zamówienia (Thank You Page) i wrzuca je do bezpiecznego „worka”, nie psując sumy przychodów.
SAFE_DIVIDE jako standard produkcyjny
W systemach produkcyjnych dni bez sprzedaży w konkretnym kanale (np. nowo uruchomione kampanie) wygenerują koszt, ale przychód wyniesie 0. Zwykły znak dzielenia / wywaliłby cały potok danych błędem Division by zero. Funkcja SAFE_DIVIDE zwraca w takiej sytuacji NULL, co Looker Studio bez problemu interpretuje jako brak danych lub wartość zerową.
5. Automatyzacja i Warstwa BI
Aby zapytanie nie było uruchamiane ręcznie, w BigQuery zapisujemy je jako Scheduled Query (zaplanowane zapytanie), które uruchamia się automatycznie np. codziennie o godzinie 02:00, nadpisując lub dopisując dane do tabeli produkcyjnej mart_daily_marketing.
Ostatni krok to podpięcie tabeli wynikowej do Looker Studio lub PowerBI. Narzędzia te nie przetwarzają już logiki biznesowej – pobierają gotowe, idealnie przeliczone rekordy, co skraca czas ładowania dashboardu z kilku minut do ułamka sekundy.