ETL Products Transfer Process

Suggest Edits

Pre-Integration Requirements

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

Products Data Transfer Requirements

You will be transmitting batch order data utilizing your provided environment URIs.

a. ETL Product Files Overview

b. Example file for a full sync of a catalog containing two (2) products

c. Example file for a partial sync that updates two (2) products and simultaneously deletes a third one

With each file, please use the following naming convention:

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

Note that the directory structure is reversed from the ETL for orders and returns.

Delivered files will be processed in chronological order of their receipt. Products within files will be processed in the provided array order, so if the same product is provided twice in the same file, the information in the second instance will be persisted since the second instance will be processed after the first instance, overwriting it.

ETL Products Files Overview

ShoppingGives allows using an ETL (Extract, Transform, Load) file with product definitions updates with a single JSON object per file of the following form.

Root Level Object: 

{
  "storeId": <string: Store uuid provided by SG>,
  "fullSync": (optional)<bool: default false>,
  "timestamp": <string: valid ISO 8601, w3c compatible datetime string>,
  "upsertProducts": (optional)[...<Product>],
  "deleteProducts": (optional)[...<cmsId>]
}

Regarding the "timestamp" date format, Information on the ISO 8601 date specification can be found here.

Where Product objects satisfy the following contract:

{
  "cmsId": <string: CMS identifier of product>,
  "sku": <string: Stock keeping identifier>,
  "name": <string>,
  "description": <string>,
  "images": (optional)[...<string: product image URIs>],
  "basePrice": (optional)<decimal: price per unit in configured store currency>,
  "associatedGroups": (optional)[...<ProductGroup>],
  "tags": (optional)[...<string: CMS product tags],
  "subItems": (optional)[...<Subitem>],
  "productUri": <string: URI of product display page>
}

Where ProductGroup objects satisfy the following contract:

{
  "cmsId": <string: SKU identifier of product group>,
  "name": <string>,
  "description": <string>
}

Where Subitem objects satisfy the following contract:

{
  "cmsId": <string: CMS identifier of subitem>,
  "sku": <string: Stock keeping identifier>,
  "name": <string>,
  "description": <string>,
  "images": (optional)[...<string: product image URIs>],
  "basePrice": (optional)<decimal: price per unit in configured store currency>
}

CMS ids and SKUs: CMS ids and SKUs may or may not be distinct in your system. If your SKUs and CMS IDs are identical, populate the same value for both fields. Note that SKUs are used to match order line items we receive in the Orders and Return ETL process. Please ensure that the SKUs provided in your product ETL are the same SKUs that will be communicated in your Orders ETL. This will ensure proper functionality of product-level donation settings.

Full vs Partial Syncs: In the root-level object, fullSync indicates whether the file contains all of the products in your catalog (true) or only a portion in your catalog (false). If fullSync==true, we will update all listed products and delete any products from our records not provided in the products file. This does not affect the products or their representation in your CRM. If fullSync==false, we will create records within the ShoppingGives Product Manager for any new products you are providing in the upsertProducts array and update our records of existing products using the provided data.

🚧

Upserting Products

If you upsert a product, the ETL expects a complete listing of all of the product's ProductGroups and Subitems. If any are omitted in the upsert, the upserted product will be assumed to no longer be associated with the omitted objects.

🚧

Deleting Products

If a product identifier is included in the optional deleteProducts array, the product will be deleted from our records, even if the same product was included in the upsertProducts array within the same file.

Example file for a full sync of a catalog containing two (2) products

{
  "storeId": "12345678-abcd-1234-abcd-1234567890ab",
  "fullSync": true,
  "timestamp": "2022-01-01T00:00:00Z",
  "upsertProducts": [
    {
      "cmsId": "product1",
      "sku": "32145",
      "name": "Product 1",
      "description": "This is the first product",
      "images": ["https://www.example.com/image1.jpg", "https://www.example.com/image2.jpg"],
      "basePrice": 100.00,
      "associatedGroups": [
        {
          "cmsId": "group1",
          "name": "Group 1",
          "description": "This is the first group"
        }
      ],
      "tags": ["tag1", "tag2"],
      "subItems": [
        {
          "cmsId": "subitem1",
          "sku": "123456",
          "name": "Subitem 1",
          "description": "This is the first subitem",
          "images": ["subimage1.jpg"],
          "basePrice": 50.00
        }
      ],
      "productUri": "https://www.example.com/products/product1"
    },
    {
      "cmsId": "product2",
      "sku": "352321",
      "name": "Product 2",
      "description": "This is the second product",
      "images": ["https://www.example.com/image3.jpg", "https://www.example.com/image4.jpg"],
      "basePrice": 200.00,
      "associatedGroups": [
        {
          "cmsId": "group2",
          "name": "Group 2",
          "description": "This is the second group"
        }
      ],
      "tags": ["tag3", "tag4"],
      "subItems": [
        {
          "cmsId": "subitem2",
          "sku": "654321",
          "name": "Subitem 2",
          "description": "This is the second subitem",
          "images": ["https://www.example.com/subimage2.jpg"],
          "basePrice": 100.00
        }
      ],
      "productUri": "https://www.example.com/products/product2"
    }
  ]
}

Partial sync example file that updates two (2) products and simultaneously deletes a third one

{
  "storeId": "12345678-abcd-1234-abcd-1234567890ab",
  "timestamp": "2022-01-01T00:00:00Z",
  "upsertProducts": [
    {
      "cmsId": "product1",
      "sku": "123456",
      "name": "Product 1",
      "description": "This is the first product",
      "images": ["https://www.example.com/image1.jpg", "https://www.example.com/image2.jpg"],
      "basePrice": 100.00,
      "associatedGroups": [
        {
          "cmsId": "group1",
          "name": "Group 1",
          "description": "This is the first group"
        }
      ],
      "tags": ["tag1", "tag2"],
      "subItems": [
        {
          "cmsId": "subitem1",
          "sku": "123456",
          "name": "Subitem 1",
          "description": "This is the first subitem",
          "images": ["https://www.example.com/subimage1.jpg"],
          "basePrice": 50.00
        }
      ],
      "productUri": "https://www.example.com/products/product1"
    },
    {
      "cmsId": "product2",
      "sku": "352321",
      "name": "Product 2",
      "description": "This is the second product",
      "images": ["https://www.example.com/image3.jpg", "https://www.example.com/image4.jpg"],
      "basePrice": 200.00,
      "associatedGroups": [
        {
          "cmsId": "group2",
          "name": "Group 2",
          "description": "This is the second group"
        },
        {
          "cmsId": "group3",
          "name": "Group 3",
          "description": "This is the third group"
        }
      ],
      "tags": ["tag3", "tag4"],
      "subItems": [
        {
          "cmsId": "subitem2",
          "sku": "654321",
          "name": "Subitem 2",
          "description": "This is the second subitem",
          "images": ["https://www.example.com/subimage2.jpg"],
          "basePrice": 100.00
        },
        {
          "cmsId": "subitem3",
          "sku": "987654",
          "name": "Subitem 3",
          "description": "This is the third subitem",
          "images": ["https://www.example.com/subimage3.jpg"],
          "basePrice": 75.00
        }
      ],
      "productUri": "https://www.example.com/products/product2"
    }
  ],
  "deleteProducts": [
    "product3"
  ]
}

Updated 4 months ago