Example 2 - Shipment

Building an example order shipment transformation file from scratch.

Scenario

In our first example we created a sales order transformation file that could be used in the example of taking sales order data from a ecommerce system and sending it to a 3PL provider as a CSV file. Building on this, in this second example we're going to have a look at building a custom Shipment transformation file. This scenario would be for shipment data flowing back to the ecommerce system from the 3PL provider.

In this example we're going to move data from a CSV file on FTP to the Shopify fulfilment REST API.

Starting with the destination schema

To begin with lets take a look at the fields that Shopify needs to create a fulfilment against a Sales Order, the example below being taken from https://shopify.dev/api/admin/rest/reference/shipping-and-fulfillment/fulfillment#create-2021-04

Destination schema example

{
  "fulfillment": {
    "location_id": 905684977,
    "tracking_number": "123456789010",
    "tracking_company": "fed ex",
    "tracking_url": "https://www.new-fedex-tracking.com/?number=123456789010",
    "line_items": [
      {
        "id": 466157049
      },
      {
        "id": 518995019
      },
      {
        "id": 703073504
      }
    ]
  }
}

Reviewing the input data

Next lets take a look at the example CSV file that has been provided from the 3PL partner:

CSV input data example

"order","shipment_date","courier","tracking_code","tracking_url"
"#GB123042","2021-07-01","DPD","DPD1234567890-AA","https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567890-AA"
"#GB123043","2021-07-01","DPD","DPD1234567891-BB","https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567891-BB"
"#GB123044","2021-07-01","DPD","DPD1234567892-CC","https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567892-CC"

In this typical CSV file that has been provided by a 3PL partner, we can see the basic fields required to keep the ecommerce system up to date with the shipment information.

Creating the transformation

Above, we have taken a look at the required destination format (payload out) and the input data from the 3PL (payload in). The next step is to bring this all together in our transformation file, mapping the input data to create the output data.

The example tabs below show the data as received by HighCohesion, the transformation and finally the payload out that would be sent to Shopify.

Payload In

{
    "order": "#GB123042",
    "shipment_date": "2021-07-01",
    "courier": "DPD",
    "tracking_code": "DPD1234567890-EE",
    "tracking_url": "https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567890-EE"
}

Transformation

{
  "fulfillment": {
    "location_id": {
      "*static_value*": 12345678900
    },
    "tracking_number": {
      "*ppk*": "tracking_code"
    },
    "tracking_company": {
      "*ppk*": "courier"
    },
    "tracking_url": {
      "*ppk*": "tracking_url"
    }
  }
}

Payload Out

{
  "fulfillment": {
    "location_id": 12345678900,
    "tracking_number": "DPD1234567890-EE",
    "tracking_company": "DPD",
    "tracking_url": "https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567890-EE"
  }
}

The transformation file ends up being quite simple, just four fields are required to ship the sales order in Shopify. Three of these fields are mapped using the *ppk* option, meaning that the transformation logic will use a value from the payload in. The fourth value location_id is populated using a *static_value* which in the example above enters the value 12345678900 in to the payload out.

No Products?

In the example above there are no line items covered in the transformation file. This is due (in this example) to the 3PL file not containing the line level detail. In the HighCohesion system, if there are no line items, then all lines will be fulfilled automatically.

Example with line items

Building on the example above, some 3PL partners offer good line level detail which can be mapped over to Shopify to create partial or split shipments. In the example below we look at how the source information has changed and how the transformation file is changed to handle it:

CSV input data with line items example

"order","sku","qty","shipment_date","courier","tracking_code","tracking_url"
"#GB123042","ABC001",3,"2021-07-01","DPD","DPD1234567890-AA","https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567890-AA"
"#GB123042","CEG032",4"2021-07-01","DPD","DPD1234567890-AA","https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567890-AA"
"#GB123042","AGJF22",1"2021-07-01","DPD","DPD1234567890-AA","https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567890-AA"
"#GB123043","BBC901",1"2021-07-01","DPD","DPD1234567891-BB","https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567891-BB"
"#GB123043","BBC902",1"2021-07-01","DPD","DPD1234567891-BB","https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567891-BB"

The lines of the CSV will be grouped together by the common piece of data, in this case the order column that contains the order number will be used to group the data.

Payload In

{
    "order": "#GB123042",
    "sku": "ABC001",
    "qty": 3,
    "shipment_date": "2021-07-01",
    "courier": "DPD",
    "tracking_code": "DPD1234567890-EE",
    "tracking_url": "https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567890-EE"
    "line_items": [
        {
            "order": "#GB123042",
            "sku": "ABC001",
            "qty": 3,
            "shipment_date": "2021-07-01",
            "courier": "DPD",
            "tracking_code": "DPD1234567890-EE",
            "tracking_url": "https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567890-EE"
        },
        {
            "order": "#GB123042",
            "sku": "CEG032",
            "qty": 4,
            "shipment_date": "2021-07-01",
            "courier": "DPD",
            "tracking_code": "DPD1234567890-EE",
            "tracking_url": "https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567890-EE"
        },
        {
            "order": "#GB123042",
            "sku": "AGJF22",
            "qty": 1,
            "shipment_date": "2021-07-01",
            "courier": "DPD",
            "tracking_code": "DPD1234567890-EE",
            "tracking_url": "https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567890-EE"
        }
    ]
}

Transformation

{
    "fulfillment": {
        "location_id": {
            "*static_value*": 12345678900
        },
        "tracking_number": {
            "*ppk*": "tracking_code"
        },
        "tracking_company": {
            "*ppk*": "courier"
        },
        "tracking_url": {
            "*ppk*": "tracking_url"
        },
        "line_items": {
            "*list*": "line_items",
            "*list_fields*":
            {
                "sku": {
                    "*ppk*": "sku"
                },
                "quantity": {
                    "*ppk*": "qty"
                }
            }
        }
    }
}

Payload Out

{
  "fulfillment": {
    "location_id": 12345678900,
    "tracking_number": "DPD1234567890-EE",
    "tracking_company": "DPD",
    "tracking_url": "https://www.dpd.co.uk/apps/tracking/?reference=DPD1234567890-EE"
    "line_items": [
      {
        "sku": "ABC001",
        "quantity": 3
      },
      {
        "sku": "CEG032",
        "quantity": 4
      },
      {
        "sku": "AGJF22",
        "quantity": 1
      },
    ]
  }
}

The transformation above adds in a *list* and *list_fields* block in order to create the array of line items that is sent to Shopify.

SKU vs ID In the example above we use the SKU code in the line items as that is the data that is available from the 3PL partner shipment file. The HighCohesion system will automatically match SKU code to the line item ID in Shopify for fulfilment to work correctly.

For a tutorial example for FTP to Shopify integration with CSV files, check this link.