ETL Orders and Returns Transfer Process

Pre-Integration Requirements

Please 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 URIs.

a. ETL Order Files Overview

b. ETL Order Object Overview

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

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

e. ETL Return File Overview

f. ETL Return Order Object Overview

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

h. Return Order Object - Partial Return with Line Items

i. Return Order Object - Full Return/Canceled Order

With each file, please use the following naming convention:

.
└── /clientdirectory/orders
	├── clientdomain.com_{storeID}_2021-11-05T13:16:20Z
└── /clientdirectory/returns
	├── clientdomain.com_{storeID}_2021-11-05T13:16:20Z

ETL Order Files Overview

ETL order files (e.g.,clientdomain.com_{storeID}_2021-11-05T13:16:20Z placed in the S3 directory {storeID}/orders) 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 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",
    //(Optional) The causeIds is the ID(s) of the causes or nonprofits who are the recipient of the donation generated from the action. 
  	//The causeIds are a comma delimited string of NPO EINs without dashes or spaces. Up to 6 distinct EINs are supported. 
  	//Computed donation is spread evenly across the specified NPOs.	
  	// Example: ["521693387"] or ["521693387", "363673599"].
 	// Or one Cause Portfolio, in which the Cause Portfolio ID provided by your ShoppingGives representative should be used. 
  	//Computed donations are spread evenly across the NPOs included within a given Cause Portfolio.
  	// Example: ["ac933317-6ceb-44cb-868d-b41849887132"].
  	"causeIds": "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",
    // If the order source is “In Store” then ID of the location associated with the store where the purchase occurred.
    "locationId": "string",
    // (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 only to provide order-level detail and not include product-level detail, then no line item 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 two orders without line item specification:

{
    "orders": [
        {
            "storeId": "59f5ff9c-3394-4974-b9d8-bf12b3172817",
            "orderId": "1234aBcD",
            "total": "10.00",
            "subTotal": "20.00",
            "amount": "10.00",
          	"causeIds":["521693387", "363673599"],
            "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",
          	"causeIds":["ac933317-6ceb-44cb-868d-b41849887132"],
            "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

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

{
    "orders": [
        {
            "storeId": "59f5ff9c-3394-4974-b9d8-bf12b3172817",
            "orderId": "000001",
            "total": 1100.00,
            "subTotal": 1000.00,
            "amount": 1000.00,
          	"causeIds":["521693387", "363673599"],
            "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,
          	"causeIds":["ac933317-6ceb-44cb-868d-b41849887132"],
            "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_{storeID}_2021-11-05T13:16:20Z placed in the {storeId}/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 the requirements 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 a 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

The following should be provided if you want to cancel an order and issue a full refund, regardless of line items.

{
    // 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"
}