ETL Orders and Returns Transfer Process

Pre-Integration Requirements

Please make sure to follow all pre-integration requirements mentioned at the top of the Storefront Widgets Integration Guide for ETL guide.

Orders Data Transfer Requirements

You will be transmitting batch order data utilizing your provided environment URI's.

a. ETL Summary Files Overview

b. ETL Order Files Overview

c. ETL Order Object Overview

d. Example Order Array of JSON Objects - No Product Details

e. Example Order Array of JSON Objects - Product Level Order

f. ETL Return File Overview

g. ETL Return Order Object Overview

h. Return Order Object - Partial Return with No Line Items

i. Return Order Object - Partial Return with Line Items

j. Return Order Object - Full Return/Canceled Order

With each file, please use the following naming convention:

.
└── sales/clientdirectory/
    β”œβ”€β”€ clientdomain.com-summary-1-2021-11-05T13:16:20Z
    β”œβ”€β”€ clientdomain.com-1-2021-11-05T13:16:20Z
└── returns/clientdirectory/
  β”œβ”€β”€ clientdomain.com-summary-1-2021-11-05T13:19:10Z
  └── clientdomain.com-1-2021-11-05T13:19:10Z

ETL Summary Files Overview

Each order and return file should have an associated summary file. Summary files indicate the number of records that are conveyed in the associated order or return file.

  • Summary files associated to orders files should be placed in the root directory of the S3 bucket.
  • Summary files associated to returns files should be placed in the sales-return folder.

The following is an overview of the expected structure of summary files:

{
     "fileName": {{salesFileName}},
     "totalRecords": {{numberOfRecords}}
}
{
     "fileName": "clientdomain.com-1-2021-11-05T13/19/10Z",
     "totalRecords": 435
}

ETL Order Files Overview

ETL order files (e.g. clientdomain.com-summary-0-2021-11-05T13:15:30Z placed in the root S3 directory) are expected to contain a JSON object with a single key ("object") containing an array of order objects. Specifically:

{
  "orders": [ {{orderObjects}} ]
}

ETL Order Object Overview

The following is an overview of the order object that is expected from the merchant for ShoppingGives to create a donation.

{
    // (Required) Your Store ID Guid that is provided by ShoppingGives.
    "storeId": "string",
    // (Required) Must match the user facing order ID generated by your CMS.
    "orderId": "string",
    // A Guid created by the Global Tracking Script.
    // Associates widget state with the order during a customer's shopping session.  
    // Stored in the browser's sessionStorage object as a base-64 encoded string.
    "trackingId": "string",
    // The ISO-4217 alpha-numeric code for the currency that the order is processed in.
    // Examples: 'USD', 'CAD'.
    "currency": "string",
    // (Optional) An array of Line Item objects detailing the order's items
    "lineItems": [{{lineItemObjects}}],
    // (Required) Total amount paid by the customer, including Tax, Shipping, and Discounts
    "total": "decimal",
    // (Required) Total amount paid by the customer, excluding tax, Shipping, and Discounts.                
    "subTotal": "decimal",
    // (Required) Sum of all line items or order total, excluding tax and shipping, including all discounts.
    "amount": "decimal",
    // (Optional) Source of the order, e.g. Retail.
    // 0 = Online, 1 = In Store
    "source": "integer",
    // (Optional) Customer ID.
    // Merchant unique Customer ID assigned to customer by merchant,
    "userCustomerId": "string",
    // (Optional) Customer Email.
    // Customer Email Address,
    "customerEmail": "string", 
    // Custom, freeform JSON object that defines key-value pairs which
    "userExtensions": "object",
    // (Required) Timestamp of when order was created in merchant’s CMS.
    "orderCreatedAtUtc": "ISO 8601 Date Time",
    // (Optional) Array of promo code strings applied to purchase.                        
    "promoCodes": []
}

Line item objects are expected as follows:

{
    // (Required) Unique ID used to identify line item within Merchant CMS.
    // This value should not change.
    "id": "string",
    // (Required) The quantity of the line item purchased.
    "quantity": "integer",
    // (Optional) Unique string related to the product.
    "sku": "string",
    // (Optional) Description of the line item.
    "description": "string",
    // (Optional) Cost of line item.
    "costPerItem": "decimal",
    // (Optional) Discount value applied to line item.
    "discount": "decimal"
}

Line items are only required if the merchant wants to be able to process returns of individual line items.

Example Order Array of JSON Objects - No Product Details

If the merchant opts to only provide order-level detail and not include product-level detail, then no line items array is required. The merchant should use this approach if either not processing returns or providing returns with new totals or providing fully refunded/canceled orders only. What follows is an example of a complete orders file containing 2 orders without line item specification:

{
    "orders": [
        {
            "storeId": "59f5ff9c-3394-4974-b9d8-bf12b3172817",
            "orderId": "1234aBcD",
            "total": "10.00",
            "subTotal": "20.00",
            "amount": "10.00",
            "promoCodes": ["example-promo-code-50percentoff"],
            "discount": "10.00",
            "trackingId": "a6100aaa-f410-4508-93ed-695d511837ce",
            "userCustomerId": "123CustomerId",
            "orderCreatedAtUtc": "2021-08-31T01:01:01+00:00"
        },
        // Second order
        {
            "storeId": "59f5ff9c-3394-4974-b9d8-bf12b3172817",
            "orderId": "456eFgH",
            "total": "40.00",
            "subTotal": "30.00",
            "amount": "30.00",
            "promoCodes": [],
            "discount": "0.00",
            "trackingId": "432f6b2e-a356-492b-afe8-e63a7e75de25",
            "userCustomerId": "456CustomerId",
            "orderCreatedAtUtc": "2021-09-01T01:01:01+00:00"
        }
    ]
}

Example Order Array of JSON Objects - Product Level Order

If the merchant opts to provide product-level details, then a line items array is required. The merchant should use this approach if processing partial returns based on line items. What follows is an example of a complete orders file containing 2 orders with line item specification.

{
    "orders": [
        {
            "storeId": "59f5ff9c-3394-4974-b9d8-bf12b3172817",
            "orderId": "000001",
            "total": 1100.00,
            "subTotal": 1000.00,
            "amount": 1000.00,
            "promoCodes": ["example-promo-code-50percentoff"],
            "discount": 10.00,
            "trackingId": "a6100aaa-f410-4508-93ed-695d511837ce",
            "userCustomerId": "123CustomerId",
            "orderCreatedAtUtc": "2021-08-31T01:01:01+00:00",
            "lineItems": [
                {
                    "id": "100001",
                    "quantity": "1",
                    "sku": "product-1",
                    "description": "A product description.",
                    "costPerItem": 899.00,
                    "discount": 0.00
                },
                {
                    "id": "100002",
                    "quantity": "4",
                    "sku": "product-2",
                    "description": "A product description.",
                    "costPerItem": 99.00,
                    "discount": 10.00
                }
            ]
        },
        // Second order
        {
            "storeId": "59f5ff9c-3394-4974-b9d8-bf12b3172817",
            "orderId": "000002",
            "total": 100.00,
            "subTotal": 1000.00,
            "amount": 100.00,
            "promoCodes": [],
            "discount": 0,
            "trackingId": "a6100aaa-f410-4508-93ed-695d511837ce",
            "userCustomerId": "124CustomerId",
            "orderCreatedAtUtc": "2021-08-31T01:01:01+00:00",
            "lineItems": [
                {
                    "id": "100003",
                    "quantity": "10",
                    "sku": "product-1",
                    "description": "A product description.",
                    "costPerItem": 100.00,
                    "discount": 0.00
                }
            ]
        }
    ]
}

ETL Return File Overview

ETL return files (e.g. clientdomain.com-summary-0-2021-11-05T13:15:30Z placed in the ./sales-return S3 bucket) are expected to contain a JSON object with a single key ("object") containing an array of return order objects. Specifically:

{
  "orders": [ {{returnOrderObjects}} ]
}

ETL Return Order Object Overview

The following is an overview of requirements in order to process a return or cancellation of an order. Returns are required to be provided within the lock period specified in your Technology Agreement. These objects should be placed in the sales-return folder of the provided Amazon s3 bucket.

Return Order Object - Partial Return with No Line Items

If you do not provide line item details with an order, the return should provide the updated amount after return.

{
    // Your Store ID Guid that is provided by ShoppingGives.
    "storeId": "string",
    // (Required) Must match the user facing order ID generated by your CMS.
    "orderId": "string",
    // The new sale amount after refund.
    "newAmount": "decimal"
}
{
    "storeId": "503350aa-639a-47cf-893c-2d217709359c",
    "orderId": "000001",
    "newAmount": 100.00
}

Return Order Object - Partial Return with Line Items

If you provide line item details with an order, the return should provide an array containing the line items remaining after the return.

{
    // Your Store ID Guid that is provided by ShoppingGives.
    "storeId": "string",
    // (Required) Must match the user facing order ID generated by your CMS.
    "orderId": "string",
    // (Required) Array of objects detailing line items and their final   quantities after refund.
    // Example: If 3 quantities of a line item was purchased, and 1 returned, the final quantity would be 2.
    "lineItemQuantities": [ {{lineItemQuantityObjects}} ] // Array of LineItemQuantity objects
}
{
    "storeId": "503350aa-639a-47cf-893c-2d217709359c",
    "orderId": "000002",
    "lineItemQuantities": [
        {
            "id": "100003",
            "quantity": 8
        }
      {
            "id": "100004",
            "quantity": 2
        }   
    ]
}

Line item Quantity objects are specified as follows:

{
    // Unique ID used to identify line item within merchant’s CMS.
    // This value should not change.
    "id": "string",
    // Remaining quantity of the line after returns.
    "quantity": "integer"
}

Return Order Object - Full Return/Canceled Order

If you would like to cancel an order and issue a full refund, regardless of line items, the following should be provided.

{
    // Your Store ID Guid that is provided by ShoppingGives.
    "storeId": "string",
    // (Required) Must match the user facing order ID generated by your CMS.
    "orderId": "string"
}
{
    "storeId": "503350aa-639a-47cf-893c-2d217709359c",
    "orderId": "000002"
}