Google Ads OCI: wartości konwersji z BigQuery

Google Ads może optymalizować stawki na podstawie przekazanych wartości konwersji. BigQuery może przygotować te wartości z danych zamówień, na przykład jako marżę pokrycia po określonych kosztach zmiennych. Przykładowo 100 euro przychodu bez podatku minus 60 euro kosztów towaru, wysyłki i płatności daje 40 euro marży pokrycia. Koszty stałe nie zostały jeszcze odjęte. Nie oznacza to automatycznie zysku netto, przepływu pieniężnego ani wartości klienta w całym okresie relacji. Potrzebne są udokumentowana definicja wartości, dozwolone wykorzystanie danych, wiarygodne identyfikatory i oddzielny proces importu.

OCI oznacza Offline Conversion Import: zapisany wynik biznesowy jest przekazywany do Google Ads po wystąpieniu zdarzenia.

Zadanie potoku danych

Poniższy przykład przygotowuje klasyczne konwersje offline oparte na GCLID. Łączy zamówienie z zapisanym kliknięciem reklamy, ale nie tworzy modelu atrybucji wielopunktowej. GA4 jest w tym układzie opcjonalne. Standardowy eksport zawiera ecommerce.transaction_id oraz pole zdarzenia collected_traffic_source.gclid, lecz zdarzenie purchase nie musi zawierać własnego parametru gclid.

Zapis identyfikatorów kliknięć i przesyłanie danych reklamowych wymagają sprawdzenia odpowiednich zgód i zasad. Własne ciasteczko lub rekord ERP nie omija tych wymagań. Przykład używa wcześniej zweryfikowanej flagi marketing_upload_allowed. To wewnętrzna kontrola, a nie zamiennik dowodów zgody ani pól zgody w API importu.

Wymagane pola i ich znaczenie

• orders_for_ads zawiera jeden aktualny wiersz na order_id. Identyfikator jest stały i niepusty; duplikaty otrzymują status błędu zamiast cichego wyboru jednej wersji.
• gclid zawiera pierwotnie zapisany identyfikator. click_time i conversion_time mają typ TIMESTAMP. Czas konwersji opisuje rzeczywiste, niezmienne zdarzenie biznesowe, a nie chwilę ponownej wysyłki.
• net_revenue_ex_tax, cogs_amount, shipping_cost i payment_fee to kwoty NUMERIC w tej samej walucie. Przychód uwzględnia rabaty i znane zwroty. Znaki kwot oraz rozliczanie zwracanych kosztów muszą być spójne.
• currency_code zawiera sprawdzony kod waluty ISO 4217. Zapytanie kontroluje tylko format trzech wielkich liter, nie przynależność do listy ISO.
• marketing_upload_allowed ma typ BOOL. order_status wynika z udokumentowanego cyklu zamówienia. ads_upload_log zapisuje zaakceptowane importy z numerem zamówienia, akcją i polem BOOL accepted; odrzucone żądania nie są zaakceptowanymi importami.

Przykład zakłada jedno konto konwersji Google Ads i jedno mapowanie akcji. Wiele kont wymaga dodania identyfikatora konta do klucza rejestru importów. Kwoty muszą być skończonymi wartościami NUMERIC. Równoległe procesy wysyłające wymagają koordynacji lub idempotentnej wysyłki, czyli ponowień bez podwójnego skutku; sprawdzenie rejestru nie jest blokadą.

Przykład przygotowania danych, nie program wysyłający

Poniższy przykład GoogleSQL zachowuje również starsze oczekujące zamówienia. Wysyłka towaru ósmego dnia nie powoduje utraty zamówienia przez siedmiodniowe okno zakupów. W produkcji tabele wymagają odpowiedniego podziału, na przykład według daty, aby każdy przebieg nie odczytywał całej historii. Uzgadnianie przyrostowe przetwarza nowe lub zmienione zamówienia, a jednocześnie śledzi starsze oczekujące pozycje. Okno akcji wynoszące 30 dni jest przykładowe i musi odpowiadać ustawieniu akcji konwersji.

-- GoogleSQL teaching example: replace project/dataset identifiers.
-- orders_for_ads: one current row per order; timestamps remain immutable.
-- ads_upload_log: accepted imports only, keyed by action + order_id.
DECLARE run_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP();
DECLARE action_name STRING DEFAULT 'Contribution_Purchase';
-- Example only: match the configured conversion action's click window.
DECLARE click_window_days INT64 DEFAULT 30;

WITH prepared AS (
  SELECT o.*,
    COUNT(*) OVER (PARTITION BY order_id) AS rows_per_order,
    ROUND(net_revenue_ex_tax - cogs_amount - shipping_cost - payment_fee, 2)
      AS contribution_value,
    EXISTS (
      SELECT 1 FROM `project_id.erp_data.ads_upload_log` AS l
      WHERE l.order_id = o.order_id AND l.conversion_action = action_name
        AND l.accepted = TRUE
    ) AS already_imported
  FROM `project_id.erp_data.orders_for_ads` AS o
),
classified AS (
  SELECT *,
    CASE
      WHEN order_id IS NULL OR TRIM(order_id) = '' OR rows_per_order != 1
        THEN 'INVALID_ORDER_KEY'
      WHEN already_imported THEN 'ALREADY_IMPORTED_CHECK_ADJUSTMENTS'
      WHEN marketing_upload_allowed IS NOT TRUE THEN 'CONSENT_NOT_CLEARED'
      WHEN order_status != 'PAID_AND_SHIPPED' OR order_status IS NULL
        THEN 'NOT_READY'
      WHEN gclid IS NULL OR TRIM(gclid) = '' THEN 'MISSING_GCLID'
      WHEN conversion_time IS NULL OR click_time IS NULL
        OR conversion_time <= click_time OR conversion_time > run_time
        THEN 'INVALID_TIME'
      WHEN click_time < TIMESTAMP_SUB(run_time, INTERVAL 90 DAY)
        THEN 'IMPORT_TOO_LATE'
      WHEN conversion_time > TIMESTAMP_ADD(click_time, INTERVAL click_window_days DAY)
        THEN 'OUTSIDE_ACTION_WINDOW'
      WHEN contribution_value IS NULL THEN 'MISSING_FINANCIALS'
      WHEN currency_code IS NULL OR NOT REGEXP_CONTAINS(currency_code, r'^[A-Z]{3}$')
        THEN 'INVALID_CURRENCY_FORMAT'
      ELSE 'READY'
    END AS staging_status
  FROM prepared
)
SELECT order_id, gclid, action_name AS conversion_action,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S+00:00', conversion_time, 'UTC')
    AS conversion_date_time,
  contribution_value,
  -- Explicit business policy: losses become zero bidding value, not omitted orders.
  GREATEST(contribution_value, 0) AS conversion_value,
  currency_code, staging_status
FROM classified;

Przykład zaokrągla kwoty do dwóch miejsc po przecinku, zgodnie z konwencją kwot taką jak dla EUR lub USD; inna dokładność waluty wymaga dostosowania.

Tylko wiersze ze statusem staging_status = 'READY' są kandydatami do wysłania. Pozostałe statusy wymagają monitorowania oraz, zależnie od przyczyny, korekty lub ponowienia później. Nawet READY nie potwierdza przyjęcia ani przypisania konwersji przez Google Ads. Brakujące kwoty pozostają błędem zamiast cicho zamieniać się w zero.

Przykład świadomie ogranicza ujemną marżę pokrycia do zerowej wartości dla licytacji, zachowując kwotę ze znakiem do raportowania. To reguła biznesowa, a nie twierdzenie o znikaniu strat. Inna polityka wartości wymaga osobnego projektu. Nie ma arbitralnego mnożnika LTV; prognozy wymagają oddzielnie zweryfikowanego modelu.

Połączenie i obsługa importu

1. Wybór ścieżki: Google Ads Data Manager obsługuje bezpośrednie połączenie z BigQuery wraz z uprawnieniami i mapowaniem pól. Konektor Google Ads w BigQuery Data Transfer Service przenosi dane raportowe w przeciwną stronę, do BigQuery. Samo zaplanowane zapytanie nie wysyła konwersji.
2. Mapowanie akcji i danych: Ads API wymaga nazwy zasobu aktywnej akcji typu UPLOAD_CLICKS. Etykieta akcji z SQL wymaga więc mapowania. Numer zamówienia, GCLID, czas, wartość, waluta i właściwe pola zgody muszą odpowiadać wybranemu interfejsowi; nazwy pól CSV, Data Managera i API nie są zamienne.
3. Zapis wyników: potrzebne są identyfikator żądania, stałe zamówienie, akcja i czas, wynik przyjęcia, błędy pojedynczych rekordów oraz stan ponowień. Błędy częściowe wymagają obsługi; poprawna odpowiedź HTTP nie potwierdza sukcesu każdego wiersza. Okna czasowe trzeba ponownie sprawdzić przy faktycznej wysyłce.
4. Uzgadnianie zmian: przyjęte zamówienia trafiają do oddzielnego procesu korekt po zwrotach lub zmianach wartości. Ponowny pierwotny import nie aktualizuje wartości. Rejestr wysyłek wymaga uzgodnienia z systemem zamówień i diagnostyką Google Ads.
5. Świadome włączenie licytacji: początkowo dane służą obserwacji, a cele kampanii wymagają kontroli. Akcje dodatkowe mogą wpływać na stawki w celach niestandardowych. Walidacja zależy od ilości danych, uzgodnienia finansowego i opóźnień konwersji; czternaście dni nie jest uniwersalnym potwierdzeniem gotowości. Przychód i marża tego samego zamówienia nie powinny niezamierzenie podwójnie zasilać licytacji.

Okna czasowe i ograniczenia identyfikatorów

Google podaje maksymalnie 90 dni od powiązanego kliknięcia do importu dla klasycznych konwersji offline oraz 63 dni dla konwersji rozszerzonych dotyczących potencjalnych klientów. Obowiązuje również własne okno akcji konwersji. Nie wynika z tego powszechny obowiązek opóźniania każdego importu o 24 godziny. Przetwarzanie świeżego kliknięcia może powodować przejściowe błędy; FAQ zaleca w takim przypadku sześciogodzinny odstęp przy importach harmonogramowanych. Ponowienia nie zmieniają pierwotnego czasu konwersji.

Przykład celowo obsługuje wyłącznie GCLID. GBRAID i WBRAID wymagają właściwych pól i obsługiwanego procesu, a nie ogólnego COALESCE do kolumny GCLID. Aktualna dokumentacja Ads API obsługuje korekty dla GCLID i GBRAID, ale wyklucza korekty WBRAID. Wybór identyfikatora wpływa zatem także na obsługę zwrotów.