Algorytmiczna optymalizacja ROAS: Eksport atrybucji z BigQuery do Google Ads OCI

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, wbraid lub gbraid) musi zapisywać te wartości w plikach cookie 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:

1. 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 po transaction_id zapobiega wielokrotnemu przesyłaniu zamówień korygowanych w kolejnych cyklach rozliczeniowych.
2. 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.
3. 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:

1. 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.
2. 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.
3. 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.