{"id":471,"date":"2026-09-16T08:40:00","date_gmt":"2026-09-16T06:40:00","guid":{"rendered":"https:\/\/www.lukaswojcik.com\/?p=471"},"modified":"2026-09-10T10:51:47","modified_gmt":"2026-09-10T08:51:47","slug":"algorithmic-roas-optimization-bigquery-attribution-export-to-google-ads-oci","status":"publish","type":"post","link":"https:\/\/www.lukaswojcik.com\/blog\/en\/digital-marketing\/tutorials-en-digital-marketing\/algorithmic-roas-optimization-bigquery-attribution-export-to-google-ads-oci\/","title":{"rendered":"Algorithmic ROAS Optimization: BigQuery Attribution Export to Google Ads OCI"},"content":{"rendered":"\r\n
Standard conversion tracking in performance marketing frequently optimizes bidding models toward superficial metrics. Feeding automated bidding systems with gross order revenue or unweighted transaction counts ignores critical financial realities such as product profit margins, order cancellation rates, shipping overhead, and long-term Customer Lifetime Value (LTV). Implementing an algorithmic ROAS optimization pipeline between Google BigQuery and Google Ads Offline Conversion Import (OCI) allows data engineering teams to replace raw browser conversion pixels with enriched, real-profit signals tied directly to ad click identifiers.<\/p>\r\n\r\n\r\n\r\n
Value-Based Bidding (VBB) relies on transmitting accurate profit and LTV metrics back into Google Ads Smart Bidding algorithms. The OCI data pipeline consists of three structural components:<\/p>\r\n\r\n\r\n\r\n
\r\n
Click Identifier Capture:<\/strong> Every inbound landing page URL containing Google click parameters (gclid<\/code>, wbraid<\/code>, or gbraid<\/code>) must persist these identifiers within first-party cookies and backend ERP\/CRM transaction databases.<\/li>\r\n
Financial Attribution Modeling in BigQuery:<\/strong> A scheduled SQL transformation joins GA4 web traffic streams with backend accounting datasets, calculating the actual net profit and predicted 90-day LTV for each converting customer.<\/li>\r\n
Automated OCI Payload Sync:<\/strong> Formatted conversion payloads are uploaded automatically to Google Ads via scheduled BigQuery data transfers or the Google Ads API, allowing bidding models to bid higher on customer profiles generating true financial profit.<\/li>\r\n<\/ul>\r\n\r\n\r\n\r\n\n\nThe chain closes the loop from click to margin: the click identifier is stored with the lead, BigQuery joins it with the real profit, and only that value goes back into the bidding.<\/figcaption>\n<\/figure>\r\n\r\n\r\n\r\n
2. Step-by-Step SQL Modeling in Google BigQuery<\/h2>\r\n\r\n\r\n\r\n
To calculate true conversion value, GA4 click identifiers must be joined with backend transactional data and product margin tables. The following production-grade SQL script extracts click IDs and calculates net profit values for OCI ingestion:<\/p>\r\n\r\n\r\n\r\n
WITH ga4_click_params AS (\n SELECT\n (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaction_id,\n (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'gclid') AS gclid,\n (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'gbraid') AS gbraid,\n (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'wbraid') AS wbraid,\n TIMESTAMP_MICROS(event_timestamp) AS event_time\n FROM\n `project_id.analytics_XXXXXXXXX.events_*`\n WHERE\n _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))\n AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))\n AND event_name = 'purchase'\n),\nbackend_financials AS (\n SELECT\n order_id AS transaction_id,\n order_status,\n -- Gross Revenue minus Cost of Goods Sold (COGS), Returns, and Shipping Cost\n ROUND((gross_revenue - cogs_amount - shipping_cost) * COALESCE(ltv_multiplier, 1.2), 2) AS net_profit_value,\n currency_code\n FROM\n `project_id.erp_data.completed_orders`\n WHERE\n order_status = 'PAID_AND_SHIPPED'\n AND return_status = 'NO_RETURN'\n)\nSELECT\n COALESCE(g.gclid, g.gbraid, g.wbraid) AS click_id,\n CASE\n WHEN g.gclid IS NOT NULL THEN 'GCLID'\n WHEN g.gbraid IS NOT NULL THEN 'GBRAID'\n WHEN g.wbraid IS NOT NULL THEN 'WBRAID'\n END AS click_id_type,\n 'Net_Profit_Purchase' AS conversion_name,\n FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S%z', g.event_time) AS conversion_time,\n b.net_profit_value AS conversion_value,\n b.currency_code AS conversion_currency\nFROM\n ga4_click_params g\nINNER JOIN\n backend_financials b\n ON g.transaction_id = b.transaction_id\nWHERE\n COALESCE(g.gclid, g.gbraid, g.wbraid) IS NOT NULL\n AND b.net_profit_value > 0;<\/code><\/pre>\r\n\r\n\r\n\r\n
3. Step-by-Step Data Staging for Google Ads OCI<\/h2>\r\n\r\n\r\n\r\n
Google Ads OCI mandates specific schema rules and timing thresholds before conversions can be processed without rejection:<\/p>\r\n\r\n\r\n\r\n
\r\n
Deduplication Management:<\/strong> The SQL output must be saved into a staging table named google_ads_oci.profit_conversions_staging<\/code>. Deduplication is enforced by grouping on `transaction_id` to prevent double-uploading orders adjusted over multiple billing cycles.<\/li>\r\n
Conversion Timing Buffer:<\/strong> Uploads must occur between 24 hours and 90 days after the initial ad click. Orders processed faster than 6 hours after click timestamp risk rejection due to click identifier indexing delays within Google Ads servers.<\/li>\r\n
Conversion Action Mapping:<\/strong> A dedicated Secondary Conversion Action named Net_Profit_Purchase<\/code> must be created in Google Ads under Goals > Conversions > Uploads<\/em>. Once data import stability is verified over a 14-day window, this action can be promoted to a Primary Conversion Goal.<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n
To eliminate manual CSV exports, automated synchronization between BigQuery and Google Ads must be configured:<\/p>\r\n\r\n\r\n\r\n
\r\n
BigQuery Scheduled Query:<\/strong> Configure a daily Scheduled Query executing at 04:00 UTC, overwriting the staging table with newly reconciled financial transactions from the preceding day.<\/li>\r\n
Google Ads Scheduled Upload Connection:<\/strong> In Google Ads, navigate to Goals > Conversions > Uploads > Schedules<\/em>. Select HTTPS \/ Google Cloud Storage<\/strong> or link directly to the BigQuery staging table using a service account credentials key.<\/li>\r\n
Upload Frequency & Alerting:<\/strong> Schedule the upload daily for 06:00 UTC. Configure error notification webhooks in Google Ads to monitor rejected click identifiers or formatting syntax errors.<\/li>\r\n<\/ol>\r\n\r\n\r\n\r\n
What this tutorial achieves:<\/strong> The deployment of an automated, accounting-verified Offline Conversion Import (OCI) pipeline from Google BigQuery into Google Ads Smart Bidding algorithms.<\/p>\r\n\r\n\r\n\r\n
Resulting value:<\/strong> Smart Bidding algorithms cease optimizing for empty revenue volume or fraudulent orders. Ad budgets are automatically shifted toward customer cohorts that generate high net profit margins and strong Customer Lifetime Value. Consequently, overall advertising efficiency improves, Return on Ad Spend (ROAS) reflects real financial cash flow, and wasted ad spend on high-return, low-margin products is permanently minimized.<\/p>\r\n\n\n
![\"Chain](\"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/diagrams\/hand-roas-en.png\")
\n