English
Deutsch
PolskiAlgorithmische ROAS-Optimierung: BigQuery Attribution Export zu Google Ads OCI
Inhalt
Klassisches Conversion-Tracking im Performance-Marketing optimiert Gebotsmodelle häufig auf oberflächliche Kennzahlen. Die Einspeisung von Bruttoumsätzen oder ungewichteten Transaktionszahlen in automatische Bidding-Systeme ignoriert entscheidende finanzielle Realitäten wie Produktmargen, Stornoquoten, Versandkosten und den langfristigen Customer Lifetime Value (LTV). Die Implementierung einer algorithmischen ROAS-Optimierungspipeline zwischen Google BigQuery und dem Google Ads Offline Conversion Import (OCI) ermöglicht es Data-Engineering-Teams, browserbasierte Conversion-Pixel durch bereinigte, reale Gewinnsignale zu ersetzen, die direkt an Werbeklick-Identifikatoren gekoppelt sind.
1. Architektur-Grundlagen: Value-Based Bidding (VBB) & OCI
Value-Based Bidding (VBB) basiert auf der Rückübermittlung präziser Gewinn- und LTV-Metriken an die Smart-Bidding-Algorithmen von Google Ads. Die OCI-Datenpipeline besteht aus drei strukturellen Komponenten:
- Klick-Identifikator-Erfassung: Jede eingehende Landingpage-URL mit Google-Klickparametern (
gclid,wbraidodergbraid) muss in First-Party-Cookies und backendseitigen ERP-/CRM-Datenbanken persistent gespeichert werden. - Finanzielle Attributionsmodellierung in BigQuery: Eine geplante SQL-Transformation verknüpft GA4-Webverkehrsströme mit Buchhaltungsdaten, um den realen Nettogewinn und den prognostizierten 90-Tage-LTV für jeden konvertierenden Kunden zu berechnen.
- Automatisierter OCI-Payload-Sync: Standardisierte Conversion-Payloads werden über geplante BigQuery-Datentransfers oder die Google Ads API automatisiert hochgeladen, wodurch Bidding-Modelle gezielt auf Kundenprofile mit echtem Gewinnbeitrag optimieren.
2. Schritt-für-Schritt-SQL-Modellierung in Google BigQuery
Zur Berechnung des realen Conversion-Wertes müssen GA4-Klick-Identifikatoren mit backendseitigen Transaktionsdaten und Margentabellen zusammengeführt werden. Das folgende produktionsreife SQL-Skript extrahiert Klick-IDs und berechnet Nettogewinne für den OCI-Import:
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,
-- Bruttoumsatz abzüglich Wareneneinsatz (COGS), Retouren und Versandkosten
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. Schritt-für-Schritt-Datenaufbereitung für Google Ads OCI
Google Ads OCI verlangt spezifische Schema-Regeln und Zeitfenster, damit Conversions fehlerfrei verarbeitet werden können:
- Deduplizierungs-Management: Die SQL-Ausgabe ist in einer Staging-Tabelle namens
google_ads_oci.profit_conversions_stagingzu speichern. Durch Gruppierung nachtransaction_idwird verhindert, dass nachträglich korrigierte Bestellungen mehrfach an Google Ads übertragen werden. - Zeitfenster-Puffer: Der Upload muss zwischen 24 Stunden und 90 Tagen nach dem ursprünglichen Klick erfolgen. Transaktionen, die schneller als 6 Stunden nach dem Klick hochgeladen werden, riskieren eine Ablehnung aufgrund von Indexierungsverzögerungen in den Google-Ads-Servern.
- Conversion-Aktions-Mapping: In Google Ads muss unter Zielvorhaben > Conversions > Uploads eine sekundäre Conversion-Aktion namens
Net_Profit_Purchaseerstellt werden. Nach einer 14-tägigen Validierungsphase kann diese Aktion als primäres Zielvorhaben aktiviert werden.
4. Schritt-für-Schritt-Einrichtung der automatischen Synchronisation
Um manuelle CSV-Exporte zu überflüssig zu machen, wird eine automatisierte Synchronisation zwischen BigQuery und Google Ads eingerichtet:
- BigQuery Scheduled Query: Einrichtung einer täglichen Scheduled Query um 04:00 UTC, welche die Staging-Tabelle mit den abgeglichenen Finanztransaktionen des Vortages überschreibt.
- Google-Ads-Upload-Zeitplan: In Google Ads zu Zielvorhaben > Conversions > Uploads > Zeitpläne navigieren. Als Quelle wird HTTPS / Google Cloud Storage oder eine direkte Verbindung zur BigQuery-Staging-Tabelle über ein Service-Account-Key-File konfiguriert.
- Upload-Frequenz & Alarmierung: Der tägliche Upload wird auf 06:00 UTC terminiert. Webhook-Benachrichtigungen in Google Ads sind zu aktivieren, um abgewiesene Klick-IDs oder Formatierungsfehler sofort zu erkennen.
5. Zusammenfassung & Mehrwert
Was sich damit erreichen lässt: Die erfolgreiche Bereitstellung einer automatisierten, durch die Buchhaltung verifizierten Offline-Conversion-Import-Pipeline (OCI) von Google BigQuery in die Smart-Bidding-Algorithmen von Google Ads.
Resultierender Mehrwert: Smart-Bidding-Algorithmen optimieren nicht länger auf umsatzstarke, aber unrentable oder stornierte Bestellungen. Werbebudgets werden automatisch auf Kundenkohorten verlagert, die reale Nettogewinne und einen hohen Customer Lifetime Value generieren. Dies maximiert die Gesamteffizienz der Kampagnen, sorgt für einen aussagekräftigen Return on Ad Spend (ROAS) und minimiert Streuverluste bei margenschwachen Retouren-Produkten dauerhaft.
Fragen und Antworten
Zählt Google Ads eine Bestellung doppelt, wenn sie an mehreren Tagen im Upload steht?
Nicht, solange der Conversion-Zeitpunkt stabil bleibt. Google Ads behandelt eine Conversion mit derselben Klick-ID, derselben Conversion-Aktion und demselben Zeitpunkt als Duplikat und nimmt sie kein zweites Mal an. Die Abfrage nimmt den Zeitpunkt aus dem GA4-Ereignis, und der ist bei jedem Lauf derselbe; eine Bestellung, die wegen des Abfragefensters von sieben Tagen in mehreren aufeinanderfolgenden Uploads steht, zählt deshalb einmal. Dieselbe Regel verhindert allerdings auch, dass ein korrigierter Wert über einen erneuten Upload ankommt; dafür sind Conversion-Anpassungen da.
Heikel ist der umgekehrte Fall: ein zweites purchase-Ereignis zur selben Bestellung, etwa weil die Bestätigungsseite neu geladen wurde. Es trägt einen eigenen Zeitstempel und ist für Google Ads damit eine eigene Conversion mit demselben Gewinn. Deshalb ist die Gruppierung nach transaction_id beim Schreiben der Staging-Tabelle nötig, und zwar so, dass je Bestellung immer derselbe Zeitpunkt gewählt wird, etwa der des frühesten Ereignisses. Nur dann erzeugt jeder Lauf für dieselbe Bestellung dieselbe Zeile.
Was geschieht mit Bestellungen, die nach dem Upload noch retourniert werden?
Die Pipeline erfährt davon nichts. Gemeldet wird eine Bestellung, sobald sie bezahlt, versandt und nicht retourniert ist, und zu diesem Zeitpunkt ist eine Retoure oft noch möglich: Im Fernabsatz an Verbraucher in der EU beginnt die Widerrufsfrist von 14 Tagen erst mit dem Erhalt der Ware. Eine spätere Retoure lässt den gemeldeten Gewinn stehen, und auch ein erneuter Upload derselben Conversion mit geändertem Wert kommt nicht an, weil Google Ads ihn als Duplikat verwirft.
Für diesen Fall gibt es in Google Ads Conversion-Anpassungen. Eine Rücknahme (RETRACTION) streicht die Conversion, eine Korrektur (RESTATEMENT) setzt einen neuen Wert, etwa nach einer Teilretoure. Gefunden wird die ursprüngliche Conversion über die Bestellnummer, sofern sie beim Upload mitgesendet wurde, oder über Klick-ID und Conversion-Zeitpunkt. Die Abfrage im Artikel sendet keine Bestellnummer; wer Anpassungen plant, nimmt die transaction_id als order_id in den Upload auf oder bewahrt zumindest den genauen Zeitpunkt jeder gemeldeten Conversion auf.
Die Alternative ist, mit dem Upload bis zum Ende der Retourenfrist zu warten. Das 90-Tage-Fenster ließe das zu, doch Smart Bidding lernt dann mit Wochen Verzug. Dazu kommt eine Eigenheit der Abfrage, die nur Käufe der letzten sieben Tage liest. Eine Bestellung, die erst am achten Tag versandt wird, fällt aus diesem Fenster, bevor sie die Bedingung PAID_AND_SHIPPED erfüllt, und wird nie gemeldet. Wer später melden will, muss also auch das Fenster der Abfrage verlängern.
Woher kommt die gclid im purchase-Ereignis des GA4-Exports?
Nicht von GA4 selbst. Der Standardexport führt die gclid nicht als Parameter des purchase-Ereignisses; neuere Exporte enthalten sie im Feld collected_traffic_source.gclid, und zwar bei dem Ereignis, dessen Seitenadresse sie trug, in der Regel also beim Landen und nicht beim Kauf. Die Abfrage im Artikel setzt deshalb voraus, dass der Shop die Klick-ID aus dem First-Party-Cookie aus Abschnitt 1 beim Kauf als eigenen Ereignisparameter mitsendet. Fehlt das, bleibt click_id leer, und die WHERE-Bedingung filtert die Zeile ohne Fehlermeldung heraus.
Robuster ist die zweite Quelle, die Abschnitt 1 nennt: die Klick-ID am Bestelldatensatz im ERP. Der GA4-Export enthält nur Käufe, bei denen das Tag ausgelöst hat; eine Bestellung ohne Einwilligung für Analytics oder hinter einem Blocker fehlt dort, auch wenn ihr Datensatz die Klick-ID trägt. Ob solche Bestellungen an Google Ads gehen dürfen, entscheidet die Werbe-Einwilligung und nicht die für Analytics; deshalb gehört auch sie an den Datensatz.
Woher kommt der Faktor 1,2 in der Gewinnberechnung?
Er ist ein Ersatzwert. COALESCE(ltv_multiplier, 1.2) greift überall dort, wo die Bestelltabelle keinen eigenen LTV-Faktor führt, und hebt den Nettogewinn dieser Bestellungen pauschal um 20 % an – eine Annahme, keine Messung. Ohne einen aus eigenen Daten abgeleiteten Faktor ist 1 die ehrlichere Wahl, denn sonst stehen gemessene und angenommene Werte in derselben Spalte, und der Ziel-ROAS setzt auf einer Zahl auf, die zum Teil nur angenommen ist.
Passend dazu
