![\"Order](\"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/diagrams\/dia-471-review-20261024.png\")
What the pipeline does<\/h2>\n
The example below prepares classic GCLID-based offline conversions. It links an order to a captured ad click; it does not calculate a multi-touch attribution model. GA4 is optional for this design. The standard GA4 export includes Click-ID capture and advertising uploads require the applicable consent and policy checks. A first-party cookie or an ERP record does not bypass those requirements. The example uses a previously validated The example assumes one Google Ads conversion customer and one action mapping. Multiple accounts require that account identity in the import ledger key. Financial values must be finite NUMERIC amounts. Parallel upload workers also need coordination or an idempotent sender that repeats requests without duplicate effects; checking the ledger is not a lock.<\/p>\n The following GoogleSQL example retains pending historical orders, so shipment on day eight does not lose an order from a seven-day purchase window. For production use, tables need suitable partitions, for example by date, so each run does not read the entire history. Incremental reconciliation processes new or changed orders while also keeping track of older pending orders. The 30-day action window is an example setting that must match the conversion action.<\/p>\n The example rounds values to two decimal places, as appropriate for its EUR\/USD-style amount convention; other currency precision rules require adaptation.<\/p>\n Only rows with The example deliberately maps negative contribution margins to a bidding value of zero while retaining the signed amount for reporting. This is a business rule, not a claim that losses disappear. A different value policy needs its own design. No arbitrary LTV multiplier is added; predictions require a separately validated model.<\/p>\n <\/p>\n Google documents a maximum upload age of 90 days after the associated click for classic offline imports, versus 63 days for enhanced conversions for leads. The conversion action\u2019s own window also applies. There is no universal rule requiring every upload to wait 24 hours. Recent-click processing can cause temporary errors; the scheduled-upload FAQ recommends allowing six hours in that case. The original conversion timestamp must remain unchanged during retries.<\/p>\n This example intentionally handles GCLID only. GBRAID and WBRAID require the appropriate fields and supported workflow, not a generic COALESCE into a GCLID column. The current Ads API adjustment documentation supports GCLID and GBRAID adjustments but excludes WBRAID adjustments. Identifier choice therefore also affects the refund design.<\/p>\n A stable order ID per action helps Google Ads deduplicate imports and reference adjustments. Internal uniqueness checks and an accepted-upload ledger are still necessary. A second conversion action can count the same order separately.<\/p>\n Supported conversions use No. Data quality, delays, campaign settings and the chosen value model affect results. Conversion value divided by ad spend expresses the uploaded metric; a margin-based ratio is not revenue ROAS and does not measure total business profit.<\/p>\n<\/div>\n A BigQuery example prepares contribution-margin values for GCLID-based offline conversion imports into Google Ads. The article explains data checks, separate uploads, retries and refunds; the query does not calculate profit or customer lifetime value.<\/p>\n","protected":false},"author":1,"featured_media":30579,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[127],"tags":[91383,91333,91261,91386,91288,91219],"class_list":["post-471","post","type-post","status-publish","format-standard","hentry","category-tutorials-en-digital-marketing","tag-attribution","tag-bigquery","tag-conversion-tracking","tag-marketing-cyfrowy-pl","tag-google-ads","tag-tutorial"],"yoast_head":"\necommerce.transaction_id<\/code> and event-level collected_traffic_source.gclid<\/code>, but a purchase event is not guaranteed to contain a custom gclid<\/code> parameter.<\/p>\nmarketing_upload_allowed<\/code> flag. That flag is an internal gate, not a replacement for consent evidence or the upload API\u2019s consent fields.<\/p>\nRequired fields and their meaning<\/h2>\n
\n
orders_for_ads<\/code> contains one current row per order_id<\/code>. The ID is stable and non-empty; duplicate rows are flagged rather than silently selected.<\/li>\ngclid<\/code> is the original captured identifier. click_time<\/code> and conversion_time<\/code> are TIMESTAMP fields; the conversion time is the actual, immutable business event, not the time of a retry.<\/li>\nnet_revenue_ex_tax<\/code>, cogs_amount<\/code>, shipping_cost<\/code> and payment_fee<\/code> are NUMERIC amounts in the same currency. Revenue reflects discounts and known refunds. Costs must use consistent signs and refund treatment.<\/li>\ncurrency_code<\/code> is a validated ISO 4217 currency. The query only checks the three-letter format, not membership in the ISO list.<\/li>\nmarketing_upload_allowed<\/code> is BOOL. order_status<\/code> follows the shop\u2019s documented lifecycle. ads_upload_log<\/code> records accepted imports with order ID, action and BOOL accepted<\/code>; rejected requests do not count as accepted.<\/li>\n<\/ul>\nPreparing and checking orders before upload<\/h2>\n
-- GoogleSQL teaching example: replace project\/dataset identifiers.\n-- orders_for_ads: one current row per order; timestamps remain immutable.\n-- ads_upload_log: accepted imports only, keyed by action + order_id.\nDECLARE run_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP();\nDECLARE action_name STRING DEFAULT 'Contribution_Purchase';\n-- Example only: match the configured conversion action's click window.\nDECLARE click_window_days INT64 DEFAULT 30;\n\nWITH prepared AS (\n SELECT o.*,\n COUNT(*) OVER (PARTITION BY order_id) AS rows_per_order,\n ROUND(net_revenue_ex_tax - cogs_amount - shipping_cost - payment_fee, 2)\n AS contribution_value,\n EXISTS (\n SELECT 1 FROM `project_id.erp_data.ads_upload_log` AS l\n WHERE l.order_id = o.order_id AND l.conversion_action = action_name\n AND l.accepted = TRUE\n ) AS already_imported\n FROM `project_id.erp_data.orders_for_ads` AS o\n),\nclassified AS (\n SELECT *,\n CASE\n WHEN order_id IS NULL OR TRIM(order_id) = '' OR rows_per_order != 1\n THEN 'INVALID_ORDER_KEY'\n WHEN already_imported THEN 'ALREADY_IMPORTED_CHECK_ADJUSTMENTS'\n WHEN marketing_upload_allowed IS NOT TRUE THEN 'CONSENT_NOT_CLEARED'\n WHEN order_status != 'PAID_AND_SHIPPED' OR order_status IS NULL\n THEN 'NOT_READY'\n WHEN gclid IS NULL OR TRIM(gclid) = '' THEN 'MISSING_GCLID'\n WHEN conversion_time IS NULL OR click_time IS NULL\n OR conversion_time <= click_time OR conversion_time > run_time\n THEN 'INVALID_TIME'\n WHEN click_time < TIMESTAMP_SUB(run_time, INTERVAL 90 DAY)\n THEN 'IMPORT_TOO_LATE'\n WHEN conversion_time > TIMESTAMP_ADD(click_time, INTERVAL click_window_days DAY)\n THEN 'OUTSIDE_ACTION_WINDOW'\n WHEN contribution_value IS NULL THEN 'MISSING_FINANCIALS'\n WHEN currency_code IS NULL OR NOT REGEXP_CONTAINS(currency_code, r'^[A-Z]{3}$')\n THEN 'INVALID_CURRENCY_FORMAT'\n ELSE 'READY'\n END AS staging_status\n FROM prepared\n)\nSELECT order_id, gclid, action_name AS conversion_action,\n FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S+00:00', conversion_time, 'UTC')\n AS conversion_date_time,\n contribution_value,\n -- Explicit business policy: losses become zero bidding value, not omitted orders.\n GREATEST(contribution_value, 0) AS conversion_value,\n currency_code, staging_status\nFROM classified;\n<\/code><\/pre>\nstaging_status = 'READY'<\/code> are upload candidates. Other statuses need monitoring and, where appropriate, correction or later retries. Even READY does not confirm that Google Ads will accept or attribute a conversion. Missing amounts remain errors instead of silently becoming zero.<\/p>\n![\"Today's](\"https:\/\/www.lukaswojcik.com\/blog\/wp-content\/uploads\/illustrationen\/flux-471-a3.webp\")
Connection and operation<\/h2>\n
\n
UPLOAD_CLICKS<\/code> action. The SQL action label therefore needs mapping. Order ID, GCLID, conversion time, value, currency and applicable consent fields need mapping to the chosen interface; CSV, Data Manager and API field names are not interchangeable.<\/li>\nTiming and identifier boundaries<\/h2>\n
Questions and answers<\/h2>\n
Does an order ID prevent every duplicate?<\/h3>\n
How are later refunds represented?<\/h3>\n
RESTATEMENT<\/code> for a revised value or RETRACTION<\/code> for removal, subject to the method\u2019s rules and deadlines. If the original import included an order ID, adjustments must use that ID. Restating to zero or retracting can prevent further adjustments, so the correction workflow needs explicit handling.<\/p>\nDoes margin-based bidding guarantee better ROAS?<\/h3>\n
Sources<\/h2>\n
\n