English
Deutsch
PolskiAlgorytmiczna optymalizacja ROAS: Eksport atrybucji z BigQuery do Google Ads OCI
Spis treści
Standardowe śledzenie konwersji w marketingu efektywnościowym często optymalizuje modele licytacji pod kątem powierzchownych wskaźników. Zasilanie automatycznych systemów licytacji przychodem brutto lub nieskalibrowaną liczbą transakcji pomija kluczowe realia finansowe, takie jak marża produktów, wskaźniki zwrotów, koszty logistyki oraz długoterminowa wartość klienta (Customer Lifetime Value – LTV). Wdrożenie algorytmicznego potoku optymalizacji ROAS pomiędzy Google BigQuery a importem konwersji offline w Google Ads (OCI) pozwala zespołom inżynierii danych zastąpić surowe piksele przeglądarkowe rzetelnymi sygnałami o rzeczywistym zysku, powiązanymi bezpośrednio z identyfikatorami kliknięć reklamowych.
1. Architektura rozwiązania: Value-Based Bidding (VBB) i OCI
Licytacja oparta na wartości (Value-Based Bidding) opiera się na przesyłaniu dokładnych wskaźników zysku i LTV z powrotem do algorytmów Smart Bidding w Google Ads. Potok danych OCI składa się z trzech kluczowych elementów:
- Przechwytywanie identyfikatorów kliknięć: Każdy adres URL strony docelowej zawierający parametry kliknięć Google (
gclid,wbraidlubgbraid) musi zapisywać te wartości w ciasteczkach First-Party oraz w bazach transakcyjnych systemów ERP/CRM. - Finansowe modelowanie atrybucji w BigQuery: Zaplanowana transformacja SQL łączy strumienie ruchu GA4 z danymi księgowymi, obliczając rzeczywisty zysk netto oraz prognozowany 90-dniowy wskaźnik LTV dla każdego konwertującego klienta.
- Zautomatyzowana synchronizacja ładunków OCI: Ustandaryzowane dane konwersji są automatycznie przesyłane do Google Ads za pomocą harmonogramów BigQuery lub API Google Ads, co umożliwia algorytmom wyższe licytowanie profili klientów generujących realny zysk.
2. Modelowanie SQL w Google BigQuery krok po kroku
W celu obliczenia prawdziwej wartości konwersji, identyfikatory kliknięć z GA4 muszą zostać połączone z danymi transakcyjnymi z backendu oraz tabelami marżowści. Poniższy produkcyjny skrypt SQL wyodrębnia identyfikatory kliknięć i wylicza zysk netto gotowy do importu OCI:
WITH ga4_click_params AS (
SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaction_id,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'gclid') AS gclid,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'gbraid') AS gbraid,
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'wbraid') AS wbraid,
TIMESTAMP_MICROS(event_timestamp) AS event_time
FROM
`project_id.analytics_XXXXXXXXX.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND event_name = 'purchase'
),
backend_financials AS (
SELECT
order_id AS transaction_id,
order_status,
-- Przychód brutto minus koszty wytworzenia (COGS), zwroty i koszty wysyłki
ROUND((gross_revenue - cogs_amount - shipping_cost) * COALESCE(ltv_multiplier, 1.2), 2) AS net_profit_value,
currency_code
FROM
`project_id.erp_data.completed_orders`
WHERE
order_status = 'PAID_AND_SHIPPED'
AND return_status = 'NO_RETURN'
)
SELECT
COALESCE(g.gclid, g.gbraid, g.wbraid) AS click_id,
CASE
WHEN g.gclid IS NOT NULL THEN 'GCLID'
WHEN g.gbraid IS NOT NULL THEN 'GBRAID'
WHEN g.wbraid IS NOT NULL THEN 'WBRAID'
END AS click_id_type,
'Net_Profit_Purchase' AS conversion_name,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S%z', g.event_time) AS conversion_time,
b.net_profit_value AS conversion_value,
b.currency_code AS conversion_currency
FROM
ga4_click_params g
INNER JOIN
backend_financials b
ON g.transaction_id = b.transaction_id
WHERE
COALESCE(g.gclid, g.gbraid, g.wbraid) IS NOT NULL
AND b.net_profit_value > 0;3. Przygotowanie danych dla Google Ads OCI krok po kroku
Usługa Google Ads OCI wymaga spełnienia określonych reguł schematu i okien czasowych, aby konwersje zostały przetworzone bez odrzucenia:
- Zarządzanie deduplikacją: Wynik zapytania SQL należy zapisać w tabeli przejściowej o nazwie
google_ads_oci.profit_conversions_staging. Deduplikacja oparta na grupowaniu potransaction_idzapobiega wielokrotnemu przesyłaniu zamówień korygowanych w kolejnych cyklach rozliczeniowych. - Bufor czasowy importu: Wysyłka danych musi odbyć się w przedziale od 24 godzin do 90 dni od kliknięcia w reklamę. Konwersje przesyłane szybciej niż 6 godzin po kliknięciu mogą zostać odrzucone ze względu na opóźnienia indeksowania identyfikatorów na serwerach Google Ads.
- Mapowanie akcji konwersji: W panelu Google Ads (w sekcji Cele > Konwersje > Przesyłanie) należy utworzyć wtórną akcję konwersji o nazwie
Net_Profit_Purchase. Po 14-dniowej weryfikacji stabilności importu, akcję tę da się awansować do roli głównego celu optymalizacyjnego.
4. Konfiguracja zautomatyzowanej synchronizacji krok po kroku
W celu wyeliminowania ręcznego eksportu plików CSV, konieczne jest skonfigurowanie automatycznego przepływu danych między BigQuery a Google Ads:
- Zapytanie harmonogramowane (Scheduled Query): Ustawienie codziennego zapytania SQL na godzinę 04:00 UTC, które nadpisuje tabelę przejściową zweryfikowanymi transakcjami finansowymi z poprzedniego dnia.
- Harmonogram przesyłania w Google Ads: W panelu Google Ads przejść do sekcji Cele > Konwersje > Przesyłanie > Harmonogramy. Jako źródło należy wybrać HTTPS / Google Cloud Storage lub bezpośrednie połączenie z tabelą BigQuery przy użyciu konta serwisowego.
- Częstotliwość i monitorowanie błędów: Skonfigurować codzienne pobieranie danych na godzinę 06:00 UTC. Warto aktywować powiadomienia e-mail o błędach w Google Ads, aby natychmiast wykrywać odrzucone identyfikatory kliknięć lub błędy składniowe.
5. Podsumowanie i wartość architektoniczna
Co da się osiągnąć dzięki temu poradnikowi: Wdrożenie w pełni zautomatyzowanego, zweryfikowanego księgowo potoku importu konwersji offline (OCI) z Google BigQuery do algorytmów Smart Bidding w Google Ads.
Wynikająca z tego wartość: Algorytmy Smart Bidding przestają optymalizować kampanie pod kątem pustych wolumenów przychodów, zwrotów czy nierentownych zamówień. Budżety reklamowe są automatycznie kierowane w stronę kohort klientów generujących realny zysk netto oraz wysoki wskaźnik Customer Lifetime Value. W efekcie rośnie ogólna efektywność reklamy, wskaźnik ROAS odzwierciedla faktyczne przepływy pieniężne, a przepalanie budżetu na produkty o niskiej marży zostaje trwale zminimalizowane.
Pytania i odpowiedzi
Czy Google Ads liczy zamówienie podwójnie, gdy pojawia się ono w importach z kilku dni?
Nie, dopóki czas konwersji pozostaje stały. Google Ads traktuje konwersję z tym samym identyfikatorem kliknięcia, tą samą akcją konwersji i tym samym czasem jako duplikat i nie przyjmuje jej po raz drugi. Zapytanie bierze czas ze zdarzenia GA4, a ten jest w każdym przebiegu taki sam, więc zamówienie, które z powodu siedmiodniowego okna zapytania trafia do kilku kolejnych importów, liczy się raz. Ta sama reguła sprawia jednak, że poprawiona wartość nie dotrze przez ponowny import; do tego służą korekty konwersji.
Groźny jest przypadek odwrotny: drugie zdarzenie purchase dla tego samego zamówienia, na przykład po odświeżeniu strony potwierdzenia. Ma ono własny znacznik czasu, więc dla Google Ads jest osobną konwersją z tym samym zyskiem. Dlatego przy zapisie do tabeli przejściowej potrzebne jest grupowanie po transaction_id, i to tak, by dla każdego zamówienia wybierany był zawsze ten sam czas, na przykład czas najwcześniejszego zdarzenia. Tylko wtedy każdy przebieg daje dla tego samego zamówienia ten sam wiersz.
Co dzieje się z zamówieniami zwróconymi już po przesłaniu konwersji?
Potok się o tym nie dowiaduje. Zgłasza zamówienie, gdy jest ono opłacone, wysłane i niezwrócone, a w tym momencie zwrot często jest jeszcze możliwy: przy sprzedaży na odległość konsumentom w UE 14-dniowy termin na odstąpienie od umowy biegnie dopiero od otrzymania towaru. Późniejszy zwrot pozostawia zgłoszony zysk bez zmian, a ponowne przesłanie tej samej konwersji ze zmienioną wartością też nie pomaga, bo Google Ads odrzuca je jako duplikat.
Na taki przypadek Google Ads przewiduje korekty konwersji. Wycofanie (RETRACTION) usuwa konwersję, korekta wartości (RESTATEMENT) ustawia nową wartość, na przykład po częściowym zwrocie. Pierwotną konwersję odnajduje się po numerze zamówienia, jeśli został przesłany razem z nią, albo po identyfikatorze kliknięcia i czasie konwersji. Zapytanie z artykułu nie wysyła numeru zamówienia; jeśli korekty są w planie, warto dodać transaction_id do przesyłanych danych jako order_id albo przynajmniej przechowywać dokładny czas każdej zgłoszonej konwersji.
Alternatywą jest wstrzymanie importu do końca okresu zwrotów. Okno 90 dni by na to pozwalało, ale Smart Bidding uczy się wtedy z kilkutygodniowym opóźnieniem. Do tego dochodzi właściwość samego zapytania: czyta ono tylko zakupy z ostatnich siedmiu dni. Zamówienie wysłane dopiero ósmego dnia wypada z tego okna, zanim spełni warunek PAID_AND_SHIPPED, i nigdy nie zostaje zgłoszone. Późniejsze zgłaszanie wymaga więc także poszerzenia okna zapytania.
Skąd bierze się gclid w zdarzeniu purchase w eksporcie GA4?
Nie z samego GA4. Standardowy eksport nie zapisuje gclid jako parametru zdarzenia purchase; nowsze eksporty przechowują go w polu collected_traffic_source.gclid, i to tylko przy zdarzeniu, którego adres strony go zawierał, czyli zwykle przy wejściu na stronę, a nie przy zakupie. Zapytanie z artykułu zakłada więc, że sklep przy zakupie wysyła identyfikator kliknięcia z ciasteczka First-Party opisanego w sekcji 1 jako własny parametr zdarzenia. Bez tego click_id pozostaje pusty, a warunek WHERE odrzuca wiersz bez żadnego komunikatu o błędzie.
Solidniejsze jest drugie źródło z sekcji 1: identyfikator kliknięcia zapisany przy rekordzie zamówienia w ERP. Eksport GA4 zawiera tylko zakupy, przy których uruchomił się tag; brakuje w nim zamówień złożonych bez zgody na analitykę albo przy aktywnym blokerze, nawet jeśli ich rekordy mają identyfikator kliknięcia. O tym, czy takie zamówienia mogą trafić do Google Ads, decyduje zgoda reklamowa, a nie analityczna, dlatego ona również powinna być zapisana przy rekordzie.
Skąd pochodzi współczynnik 1,2 w obliczeniu zysku?
To wartość zastępcza. COALESCE(ltv_multiplier, 1.2) działa wszędzie tam, gdzie tabela zamówień nie ma własnego współczynnika LTV, i ryczałtowo podnosi zysk netto takich zamówień o 20% – to założenie, a nie pomiar. Bez współczynnika wyprowadzonego z własnych danych uczciwszym wyborem jest 1; w przeciwnym razie w tej samej kolumnie leżą wartości zmierzone i założone, a docelowy ROAS opiera się na liczbie, która częściowo jest tylko założeniem.
Powiązane artykuły
