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?
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.
This is our MVP:
- User that checks out can get a shipping cost that is added automatically to his / her total
- Use only 1 shipping carrier (J&T)
- Add unique identifier to the amount (for manual bank transfer checking)
Let's setup our build plan.
Prepare all the data:
- prepare shipping cost data
- look at shopify request and payload
Register our endpoint as callback
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;
Looking at Shopify's request, we know there are 2 major values that we need to pay attention to:
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.
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.
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
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.
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.
I just go to replit.com and deploy the Flask app there.
After you know your replit url, register your callback to your shopify store using this API.
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!