# How to work out your COD refusal rate in a spreadsheet

> COD refusal rate = COD orders that shipped and were then cancelled ÷ COD orders that shipped, for the same period. Export your orders from Shopify, add four helper columns, and two SUM formulas give the rate. SUMIFS by emirate or city shows where refusals cluster.

URL: https://khaleejicod.com/guides/refusal-rate-spreadsheet/  
Updated: 2026-10-26  
Publisher: Trifid Media LLC

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](/guides/rto-explained/). 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](/guides/fulfill-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.

| Column in "data" | Copy this column from the export |
|---|---|
| A | Name (the order number) |
| B | Payment Method |
| C | Fulfilled at |
| D | Canceled at (some files spell it "Cancelled at") |
| E | Shipping City |
| F | Shipping Province Name (the emirate, for UAE orders) |
| G | Tags |

## 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:

| Cell | Formula | Gives 1 when |
|---|---|---|
| H2 | `=IF(A2=A1,0,1)` | the row is the first row of an order |
| I2 | `=IF(AND(H2=1,ISNUMBER(SEARCH("cash on delivery",B2))),1,0)` | that order was paid by COD |
| J2 | `=IF(AND(I2=1,C2<>""),1,0)` | the COD order was fulfilled |
| K2 | `=IF(AND(J2=1,D2<>""),1,0)` | the shipped COD order was later cancelled |

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](/guides/find-cancelled-cod-orders/).

## Step 4: Work out the rate

Make a second tab named `summary` and enter:

| Cell | Label in column A | Formula in column B |
|---|---|---|
| Row 1 | COD orders shipped | `=SUM(data!J:J)` |
| Row 2 | Came back | `=SUM(data!K:K)` |
| Row 3 | Refusal rate | `=IF(B1=0,"",B2/B1)` |

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

> **Example:** These figures are made up to show how the sheet works. Use your own export.
>
> A few rows from a September export, after Step 3:
>
> - #1041, Cash on Delivery (COD), fulfilled, not cancelled: COD 1, Shipped 1, Came back 0.
> - #1042, Cash on Delivery (COD), fulfilled, two items: it takes two rows. The second row gets New
>   order 0, so the order counts once.
> - #1043, paid by card: COD 0, so it never counts.
> - #1044, Cash on Delivery (COD), fulfilled, then cancelled when the courier returned it: Came back 1.
> - #1045, Cash on Delivery (COD), cancelled before it shipped: Shipped 0, so it isn't a refusal.
>
> Across the whole month, the `summary` tab shows 400 COD orders shipped and 52 that came back:
> 52 ÷ 400 = **13%**. Split by emirate:
>
> | Emirate | Shipped | Came back | Rate |
> |---|---|---|---|
> | Dubai | 180 | 18 | 10.0% |
> | Sharjah | 110 | 17 | 15.5% |
> | Abu Dhabi | 70 | 8 | 11.4% |
> | Ajman | 40 | 9 | 22.5% |
>
> Ajman's rate is more than twice Dubai's, but it rests on only 40 parcels. Check the next month
> before acting on it.

## 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](/guides/cost-of-a-refused-cod-order/).

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](/guides/limit-cod-by-emirate-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.

## Sources

- [Shopify Help Center: Exporting orders](https://help.shopify.com/en/manual/fulfillment/managing-orders/exporting-orders)
- [Shopify Help Center: Canceling orders](https://help.shopify.com/en/manual/fulfillment/managing-orders/canceling-orders)
- [Google Docs Editors Help: Import data sets and spreadsheets](https://support.google.com/docs/answer/40608)
- [Microsoft Support: Opening CSV UTF-8 files correctly in Excel](https://support.microsoft.com/en-us/office/opening-csv-utf-8-files-correctly-in-excel-8a935af5-3416-4edd-ba7e-3dfd2bc4a032)
- [Microsoft Support: SEARCH function](https://support.microsoft.com/en-us/office/search-function-9ab04538-0e55-4719-a72e-b6f54513b495)
- [Google Docs Editors Help: SEARCH function](https://support.google.com/docs/answer/3094154)
- [Microsoft Support: UNIQUE function](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)
- [Microsoft Support: FILTER function](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)