Google Ads OCI: Conversion-Werte aus BigQuery

Google Ads kann Gebote anhand übermittelter Conversion-Werte optimieren. BigQuery kann diese Werte aus Bestelldaten vorbereiten, beispielsweise als Deckungsbeitrag nach festgelegten variablen Kosten. Bei beispielsweise 100 Euro Umsatz ohne Steuer und 60 Euro Waren-, Versand- und Zahlungskosten bleiben 40 Euro Deckungsbeitrag. Fixkosten sind dabei noch nicht abgezogen. Das ist nicht automatisch Nettogewinn, Zahlungsfluss oder Kundenlebenszeitwert. Eine brauchbare Strecke benötigt eine dokumentierte Wertdefinition, zulässige Datennutzung, verlässliche Kennungen und einen gesonderten Upload.

OCI steht für Offline Conversion Import: Ein erfasstes Geschäftsergebnis wird nach dem Ereignis an Google Ads übermittelt.

Was die Datenstrecke leistet

Das folgende Beispiel bereitet klassische Offline-Conversions mit GCLID vor. Eine Bestellung wird einem erfassten Werbeklick zugeordnet; ein Multi-Touch-Attributionsmodell entsteht dadurch nicht. GA4 ist für diesen Aufbau optional. Der Standardexport enthält ecommerce.transaction_id und das ereignisbezogene Feld collected_traffic_source.gclid. Ein purchase-Ereignis enthält jedoch nicht zwingend einen eigenen Parameter gclid.

Für Klick-ID-Erfassung und Werbeuploads sind die jeweils erforderlichen Einwilligungen und Richtlinien zu prüfen. Ein First-Party-Cookie oder ERP-Datensatz umgeht diese Anforderungen nicht. Das Beispiel verwendet das zuvor geprüfte Kennzeichen marketing_upload_allowed. Diese interne Freigabe ersetzt weder Einwilligungsnachweise noch die Consent-Felder der Upload-API.

Benötigte Felder und deren Bedeutung

• orders_for_ads enthält genau einen aktuellen Datensatz je order_id. Die Kennung bleibt stabil und ist nicht leer; doppelte Zeilen erhalten einen Fehlerstatus statt einer stillen Auswahl.
• gclid enthält die ursprünglich erfasste Kennung. click_time und conversion_time sind TIMESTAMP-Felder. Der Conversion-Zeitpunkt beschreibt das tatsächliche, unveränderliche Geschäftsereignis, nicht den Zeitpunkt eines Wiederholungsversuchs.
• net_revenue_ex_tax, cogs_amount, shipping_cost und payment_fee sind NUMERIC-Beträge in derselben Währung. Der Umsatz berücksichtigt Rabatte und bekannte Erstattungen. Vorzeichen und Behandlung erstatteter Kosten müssen zusammenpassen.
• currency_code enthält eine geprüfte ISO-4217-Währung. Die Abfrage prüft nur das Format aus drei Großbuchstaben, nicht die Zugehörigkeit zur ISO-Liste.
• marketing_upload_allowed hat den Typ BOOL. order_status folgt dem dokumentierten Bestellablauf. ads_upload_log protokolliert angenommene Importe mit Bestellnummer, Aktion und BOOL-Feld accepted; abgelehnte Anfragen gelten nicht als angenommen.

Das Beispiel setzt genau ein Google-Ads-Conversion-Konto und eine Aktionszuordnung voraus. Bei mehreren Konten gehört die Kontokennung zusätzlich in den Protokollschlüssel. Finanzwerte müssen endliche NUMERIC-Beträge sein. Parallele Uploadprozesse benötigen außerdem Koordination oder einen idempotenten Sender, der Wiederholungen ohne doppelte Wirkung verarbeitet; eine Protokollabfrage ist keine Sperre.

Bestellungen vor dem Upload vorbereiten und prüfen

Das folgende GoogleSQL-Beispiel behält auch ältere offene Bestellungen. Ein Versand am achten Tag fällt dadurch nicht aus einem Kaufzeitfenster von sieben Tagen. Im Produktivbetrieb sollten Tabellen passend aufgeteilt sein, etwa nach Datum. So muss nicht jeder Lauf den gesamten Bestand lesen. Ein schrittweiser Abgleich verarbeitet neue oder geänderte Bestellungen und behält zugleich ältere noch offene Vorgänge im Blick. Das Aktionsfenster von 30 Tagen ist eine Beispieleinstellung und muss zur Conversion-Aktion passen.

-- 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;

Das Beispiel rundet auf zwei Dezimalstellen entsprechend einer Betragskonvention wie bei EUR oder USD; andere Währungsgenauigkeiten benötigen Anpassungen.

Nur Zeilen mit staging_status = 'READY' sind Uploadkandidaten. Andere Status benötigen Überwachung und gegebenenfalls Korrektur oder spätere Wiederholung. Auch READY bestätigt keine Annahme oder Zuordnung durch Google Ads. Fehlende Beträge bleiben Fehler und werden nicht still auf null gesetzt.

Negative Deckungsbeiträge werden hier bewusst auf einen Gebotswert von null begrenzt; der vorzeichenbehaftete Betrag bleibt für Auswertungen erhalten. Das ist eine Geschäftsregel und keine Behauptung, Verluste verschwänden. Eine andere Wertpolitik benötigt einen eigenen Aufbau. Ein pauschaler LTV-Faktor entfällt; Prognosen benötigen ein gesondert validiertes Modell.

Import verbinden und betreiben

1. Uploadweg wählen: Google Ads Data Manager unterstützt die direkte BigQuery-Verbindung mit Berechtigungen und Feldzuordnung. Der Google-Ads-Konnektor im BigQuery Data Transfer Service transportiert Berichtsdaten in die Gegenrichtung nach BigQuery. Eine geplante Abfrage allein sendet keine Conversions.
2. Aktion und Nutzdaten zuordnen: Die Ads API erwartet den Ressourcennamen einer aktiven Aktion vom Typ UPLOAD_CLICKS. Dafür muss die Aktionsbezeichnung aus dem SQL zugeordnet werden. Bestellnummer, GCLID, Zeitpunkt, Wert, Währung und erforderliche Consent-Felder sind auf die gewählte Schnittstelle abzubilden; CSV-, Data-Manager- und API-Feldnamen sind nicht beliebig austauschbar.
3. Ergebnisse protokollieren: Anfragekennung, stabile Kombination aus Bestellung, Aktion und Zeitpunkt, Annahme, Einzelfehler und Wiederholungsstatus bleiben gespeichert. Teilfehler benötigen eigene Behandlung; ein erfolgreicher HTTP-Aufruf bestätigt nicht jede Zeile. Zeitfenster sind beim tatsächlichen Versand erneut zu prüfen.
4. Änderungen abgleichen: Bereits angenommene Bestellungen gelangen bei Retouren oder Wertkorrekturen in einen gesonderten Anpassungsprozess. Ein erneuter Originalimport aktualisiert den Wert nicht. Uploadprotokoll, Bestellsystem und Google-Ads-Diagnosen müssen zusammenpassen.
5. Gebotsnutzung bewusst einführen: Zunächst dienen die Daten der Beobachtung; Kampagnenziele benötigen eine Prüfung. Sekundäre Aktionen können innerhalb benutzerdefinierter Ziele trotzdem Gebote beeinflussen. Datenmenge, Buchhaltungsabgleich und Conversion-Verzögerungen bestimmen die Validierung. Vierzehn Tage sind keine allgemeine Freigabegarantie. Umsatz und Deckungsbeitrag derselben Bestellung sollten nicht unbeabsichtigt doppelt in Gebote eingehen.

Zeitfenster und Kennungsgrenzen

Google nennt für klassische Offline-Importe höchstens 90 Tage zwischen zugehörigem Klick und Upload, für erweiterte Conversions für Leads dagegen 63 Tage. Zusätzlich gilt das eigene Fenster der Conversion-Aktion. Eine allgemeine Pflicht, jeden Upload 24 Stunden zurückzuhalten, besteht daraus nicht. Noch verarbeitete Klicks können vorübergehende Fehler auslösen; die FAQ empfiehlt dafür bei geplanten Uploads sechs Stunden Abstand. Wiederholungen verändern den ursprünglichen Conversion-Zeitpunkt nicht.

Das Beispiel verarbeitet bewusst nur GCLID. GBRAID und WBRAID benötigen passende Felder und einen unterstützten Ablauf statt eines pauschalen COALESCE in eine GCLID-Spalte. Die aktuelle Ads-API-Dokumentation unterstützt Anpassungen für GCLID und GBRAID, jedoch nicht für WBRAID. Die Kennungswahl beeinflusst deshalb auch die Retourenverarbeitung.