While I was helping my wife setting up her shopify store, I notice there wasn't much shipping cost counter app to choose from. So I thought, why don't I make some quick app to do this?

What are we building?

Shopify has lots of ways to enhance a store capability. For this exercise, I am using their CarrierService thingy. In short, Shopify will issue a callback to an endpoint with some order informations everytime a user checks out. Our job here is to respond with shipping rate(s) information.

Build Phase

This is our MVP:

1. User that checks out can get a shipping cost that is added automatically to his / her total
2. Use only 1 shipping carrier (J&T)
3. Add unique identifier to the amount (for manual bank transfer checking)

Let's setup our build plan.

1. Prepare all the data:

   • prepare shipping cost data
   • look at shopify request and payload

2. Register our endpoint as callback

3. ???

4. Profit.

Preparing the data

Now, we already have an excel sheet of shipping cost from her place of business to all kecamatan across Indonesia, so everything is quite trivial to build. For this private app, I am using sqlite, and Flask.

First, lets look at the shipping cost data. It looks like this after I save that excel into a CSV.

area,asal,kecamatan,kota_tujuan,cost, estimate
SURABAYA,SURABAYA,5 KOTO KAMPUNG DALAM,PADANG PARIAMAN," 78000 ", 2-4 hari 
SURABAYA,SURABAYA,5 KOTO TIMUR,PADANG PARIAMAN," 78000 ", 2-5 hari 
SURABAYA,SURABAYA,7 KOTO SUNGAI SARIK,PADANG PARIAMAN," 62000 ", 3-7 hari 
SURABAYA,SURABAYA,ABAB,PENUKAL ABAB LEMATANG ILIR," 61000 ", 2-7 hari 
SURABAYA,SURABAYA,ABANG,KARANG ASEM," 23000 ", 2-7 hari 
SURABAYA,SURABAYA,ABELI,KENDARI," 68000 ", 2-7 hari 
SURABAYA,SURABAYA,ABENAHO,YALIMO," 197000 ", 2-7 hari 

That doesn't look terribly bad. Notice how the cost is a string instead of an integer. Using sqlite-utils I load that csv file into a sqlite database.

sqlite-utils insert shiptok.sqlite shipping_cost shipping_cost.csv --csv

Next, with little SQL incantation, we can change the cost column into an integer. Open the db with sqlite3 shiptok.sqlite then follow this incantation:

CREATE TABLE shipping_cost_cleaned as
SELECT area, asal, kecamatan, kota_tujuan, CAST(cost as decimal) cost, estimate
FROM shipping_cost;

We gucci.

Looking at Shopify's request, we know there are 2 major values that we need to pay attention to: destination and items. We don't really care about origin because it always points to our business location. This is where things get tricky; on their default settings, there is no kecamatan field, but our shipping_cost table only has kecamatan as an identifier. So its time to get funky.

Path 1: Postal Code Fiasco

We know for sure that postal code is a required field. Abusing this fact, I am adding this postal_code data and then finding the kecamatan via the postal code.

SELECT CAST(cost as decimal), trim(estimate)
FROM shipping_cost sc
JOIN postal_code pc ON pc.sub_district = sc.kecamatan
  AND kecamatan = :kecamatan 

And Bob's your uncle.

Path 2: When Postal Code is no bueno

When we went live, we got reports that not all postal code is bueno; there are some mismatch values on kecamatan column on postal_code github data and J&T Shipping cost data (spelling error, misnaming and etc). So we need a better (funkier) solution. I chose to use the next identifier that are province and city. My algorithm is quite simple, give me the maximum shipping cost to a given city and province. I know it is not exactly 'right' but this is not the time nor place for razor sharp accuracy. Voila:

SELECT CAST(cost as decimal), trim(estimate)
FROM shipping_cost s
JOIN postal_code pc on pc.city = s.kota_tujuan 
  AND pc.city = UPPER(:city)
JOIN provinces p on p.province_code = pc.province_code 
  AND p.code = UPPER(:province)
ORDER BY CAST(cost as decimal) DESC
LIMIT 1

Not the prettiest, but it works.

Writing the API route

Simple stuff really:

def get_shipping_straightforward(req: RateRequest):
    destination = req.destination
    # get postal_code
    postal_code = destination.postal_code
    # First Query above
    return jnt.get_shipping_info(postal_code)


def get_shipping_estimate(req: RateRequest):
    destination = req.destination
    # get province and city
    province = destination.province
    city = destination.city
    # find estimate based of max province, query above
    return jnt.get_shipping_estimate(province, city)


@callback.route("", methods=["POST"])
def shipping_callback():
    req = RateRequest(**request.json["rate"])

    shipping_rate = get_shipping_straightforward(req) or get_shipping_estimate(req)

    if not shipping_rate:
        raise Exception("No shiping rate avail")

    rate_per_1_kg, estimate = shipping_rate

    quantity = sum([item.quantity for item in req.items])
    # One box can fit 3 items, and each box is 1 kg.
    total_approximate_weight = math.ceil(quantity / 3)

    randomizer = randint(10, 100)

    expected_shopify_rates = [
        Rate(
            service_name=f"J&T",
            service_code="J&T Regular",
            total_price=(rate_per_1_kg * total_approximate_weight + randomizer) * 100,
            description=estimate,
        )
    ]

    response = RateResponse(rates=expected_shopify_rates)

    return jsonify(response.dict())

Randomizer is there just for easy identifier (in the case of manual bank transfer).

Now whats left to do is deploying and registering your callback.

Deploying the app

I just go to replit.com and deploy the Flask app there.

Registering your callback.

After you know your replit url, register your callback to your shopify store using this API.

What's next?

So far, this API has served more than 50 orders and up north of 50 mio IDR in value with very little to no cost (being in replit). Maybe I'll open source it after I cleaned up all that git committed environment variable. Or, maybe real API integration to other providers? I don't know.

That is all I wanted to share with y'all today. Hit me up on twitter @tibudiyanto . Stay kool guys, gals, and my non-binary pals!