How to work out your COD refusal rate in a spreadsheet

Your COD refusal rate is the share of shipped COD orders that came back: refusal rate = COD orders that shipped and were then cancelled ÷ COD orders that shipped, for the same period. You can work it out from a Shopify order export: keep the COD orders, count the ones that shipped, count the ones you cancelled after shipping, and divide. The formulas below work in both Google Sheets and Excel.

What does this rate count?

In this guide a "refusal" is any COD order that you marked as fulfilled (it left your warehouse) and later cancelled (the parcel came back). That includes shoppers who refused at the door and parcels that came back after failed delivery attempts. If you tag the reason on each returned order, you can split the two (see Step 5).

The same idea from your courier's side is the RTO (return to origin) rate, based on the courier's shipment report. See RTO (return to origin) explained for GCC stores. The spreadsheet method is useful when you don't get a courier report, or when you want to break the rate down by emirate, city or tag.

What do you need before you start?

Two habits in Shopify, kept every time:

• Mark COD orders as fulfilled when they ship. That fills the Fulfilled at date in the export. Without it, the sheet can't tell which orders left your warehouse. See fulfilling COD orders.
• Cancel refused COD orders when the parcel comes back. That fills the Canceled at date. Shopify lets you cancel a fulfilled order that hasn't been paid (it can't cancel a partially fulfilled one until you cancel the fulfillment). If you mark returned parcels with a tag such as rto instead, put =IF(AND(J2=1,ISNUMBER(SEARCH("rto",G2))),1,0) in K2 (Step 3) so it checks the tag instead of the cancel date.

Pick a period that has finished. Measure a month a couple of weeks after it ends, once every parcel has been delivered or returned. If you measure too early, parcels still on the road count as delivered and the rate looks better than it is.

Step 1: Export the orders

1. From your Shopify admin, go to Orders and click Export.
2. Choose Orders by date and set the start and end dates of the month you want to measure.
3. Under Export as, choose a CSV format, then click Export orders.

An export by date is emailed to you and the store owner as a CSV file.

To open it:

• Google Sheets: open a new spreadsheet, click File > Import, choose the file, pick Insert new sheets and click Import.
• Excel: go to Data > Get Data > From File > From Text/CSV. Shopify's exports are UTF-8. Microsoft says Excel opens a UTF-8 CSV correctly only if it has a byte order mark (BOM), and gives this import for files without one. Otherwise Arabic names can turn into strange symbols.

Step 2: Copy seven columns into a tab called "data"

Shopify's export has many columns, and an order with several items takes several rows (Shopify leaves many fields blank on the extra rows). To keep the formulas simple, make a new tab named data and copy these whole columns into it, in this order. Copy whole columns so the rows stay lined up, and don't sort anything yet.

Step 3: Add four helper columns

In row 1 of the data tab, type the headers New order, COD, Shipped and Came back in columns H to K. Put these formulas in row 2, then fill them down to the last row:

Column H stops an order with three items from being counted three times. SEARCH ignores upper and lower case in both Google Sheets and Excel, so "Cash on Delivery (COD)" matches.

If your COD method has an Arabic name, use this version of I2 instead, and change the text to the exact name your orders show:

=IF(AND(H2=1,OR(ISNUMBER(SEARCH("cash on delivery",B2)),ISNUMBER(SEARCH("الدفع عند الاستلام",B2)))),1,0)

Not sure what COD is called in your export? See how to find cancelled COD orders in Shopify admin.

Step 4: Work out the rate

Make a second tab named summary and enter:

Format B3 as a percentage. SUM skips the text headers, so whole-column ranges are fine.

Step 5: Break it down by emirate, city or reason

One number for the whole store hides where the problem is. Start in cell A6 of the summary tab:

1. In A6, list each emirate once: =UNIQUE(FILTER(data!F2:F5000,data!J2:J5000=1)). Change 5000 to your last row if you have more. UNIQUE and FILTER work in Google Sheets and in Excel for Microsoft 365, Excel 2021 and later. In older Excel, use a PivotTable with the emirate as rows and the sums of Shipped and Came back as values.
2. In B6, count shipped orders for that emirate: =SUMIFS(data!J:J,data!F:F,A6)
3. In C6, count the ones that came back: =SUMIFS(data!K:K,data!F:F,A6)
4. In D6, the rate: =IF(B6=0,"",C6/B6)
5. Fill B6 to D6 down next to every emirate in the list.

For cities, use column E instead of F. Shoppers can write the same city in several ways ("Al Ain", "AL AIN", «العين»), so tidy the spellings with find and replace before you count. Column F is empty for countries where Shopify's checkout doesn't ask for a region, so use cities for those.

To count by reason, tag each returned order (for example rto-refused or rto-no-answer) and add a helper column L with =IF(AND(K2=1,ISNUMBER(SEARCH("rto-refused",G2))),1,0). Then =SUM(data!L:L) gives the number of parcels refused at the door.

A worked example

How do you read the result?

• Compare month with month. Repeat the same steps each month and keep the results in one table. If a change works, such as a new confirmation message, the rate should fall in the months after it.
• Be careful with small groups. A city with 10 shipped parcels can jump from 10% to 30% because of two orders.
• Compare with your break-even rate. The refusal rate at which COD stops paying depends on your margin and your cost per refusal. The formula is in what one refused COD order really costs.

If one emirate or city stays well above the rest, you can limit COD there and keep it everywhere else. See how to limit COD by emirate, region or city. Khaleeji COD's money-lost report uses a similar test (cancelled COD orders with at least one fulfillment recorded) for orders placed in the last 60 days, so its count should be close to your sheet's for that window.