Return-Inbound Sync in Practice: A Single-Strategy Guide from Marketing Cloud to SQL Server

What this strategy solves

Writing return-inbound documents back from the marketing cloud to the downstream SQL Server looks like a simple query-plus-insert, but in real customer environments the real issues tend to hide behind "inconsistent codes, wrong document status, misaligned incremental start points." In one real project, a retail enterprise did not design the incremental start point properly, and three months later the return quantities on both sides did not match, forcing the operations team to spend a week on manual reconciliation. This article breaks down that strategy: how to configure it, how to schedule it, and where the common pitfalls lie.

Data flow and field mapping

The data flow direction is B → A: marketing cloud (B) pulls return documents, and they are written into SQL Server (A). The middle layer is handled by the Qeasy data integration platform (轻易云数据集成平台), which is responsible for pulling, transforming, and writing.

The source side is the marketing cloud's POST /erp/api/order/query/saleReturnOrder, which pulls incrementally by dealer id + approved status (1) + time window, using number as the primary key and id for idempotency.

The target side is SQL Server's insert, which uses a phased write of master table + extension table: the master table writes the document header (gxkphz), the extension table writes the document body (gxkpmx), linked through djbh.

Key field mapping:

There is one common pattern among Qeasy customers: centralized code mapping management. Master data such as business partners and product ids is not hard-coded in the sync script but maintained in a separate master data mapping table, uniformly maintained through a "data preparation" node in Qeasy, with the field mapping node only performing reads. This way, when a new dealer is added on the business side, the sync script does not need to be touched.

How to configure on Qeasy

Go to the Qeasy data integration platform, create a new integration flow, and configure it according to the points below.

Source configuration:

• Select API POST /erp/api/order/query/saleReturnOrder, fill the request body with tenantId (dealer id), status=1 (only approved), beginTime, endTime.
• beginTime should use the platform's built-in variable {{LAST_SYNC_TIME|datetime}}. Never hard-code it to midnight today.
• Pagination: the marketing cloud generally paginates by pageSize + pageNum. We recommend pageSize=100 to avoid oversized single pages.

Target configuration:

• Select the SQL Server platform, operation type insert.
• In "Other Request", fill in two SQL statements: master table gxkphz write, extension table gxkpmx write.
• Key switches: idCheck=true, autoFillResponse=true. The former ensures that documents already written will not be duplicated, and the latter lets the target return the primary key id for log tracing.

Middle layer configuration:

• Header and body written in phases: first process the header, then expand itemList to write the body. The field mapping node in Qeasy supports a "master table → extension table" chain, so simply put itemList under extend_params_1.
• Code mapping: use a "lookup" node to read the master data mapping table, converting customerId and skuId into the wldwid and spid recognizable downstream.

Implementation steps

We split this strategy into three phases for steady rollout.

Phase one: initialize the incremental start point. Before running for the first time, manually trigger a "full backfill" in Qeasy, set beginTime to the business start date, pull a batch of historical data, and verify that code mapping and header/body writes all work. Once done, persist LAST_SYNC_TIME as the end time of this run.

Phase two: dual-track incremental and full. Run incremental daily (every 5–6 minutes, source */5, target */6, deliberately staggered to avoid timestamp collisions); run a "compensation full" at a fixed weekly time window, deduplicating by number to recover edge cases missed by the incremental. This is another common pattern among Qeasy customers, capable of absorbing data loss caused by network jitter or short upstream unavailability.

Phase three: converge scheduling frequency. Observe for 2–4 weeks. If the incremental is stable and the compensation full has essentially no new records, the compensation full can be reduced from weekly to monthly, or even retired. This step depends on the business volume and does not need to be one-size-fits-all.

Lessons learned

1. Misaligned incremental start point is the number-one pitfall. Typical mistake: writing beginTime as "today 00:00", causing today's data to overwrite yesterday's unapproved batch. The safe approach is to use LAST_SYNC_TIME and manually initialize it once before the first run.
2. Header and body not written in phases. If the header and body are placed in the same transaction, a failure in parsing one line of product will roll back the entire document, causing "whole document loss". We recommend committing the header separately, then committing the body line by line, with failed lines going into a retry queue.
3. Code mapping hard-coded in the script. In one customer environment, a new SKU was introduced but the hard-coded mapping in the sync script could not find the value, breaking the entire chain. We later switched to a unified master data mapping table, where the business side only maintains one table and engineers do not touch the script.
4. Wrong idempotency key chosen. The source id looks the most stable, but in some scenarios resending causes primary key conflicts downstream. We recommend using the business document number number as the idempotency key, with id only used for logging and troubleshooting.
5. Schedules colliding. Source */5 and target */6 are deliberately staggered. If both ends use */5, peak hours easily produce the strange phenomenon of "source not finished pulling while target is already writing".

Suitable and unsuitable scenarios

Suitable: Retail/distribution scenarios where the marketing cloud acts as the front-end order entry and the back-end ERP uses SQL Server for storage, requiring return documents to be written back based on approved status for inventory write-off and reconciliation.

Not suitable: Scenarios requiring extreme real-time performance (second-level), bidirectional synchronization (where the target also pushes status back to the source), or scenarios where the line items exceed the pagination limit and require streaming processing.