How to Use International Sales & Currency in Etsy: Step-by-Step Tutorial

Step-by-Step Guide to Analyzing International Sales & Currency

Step 1: Load the Orders

The block below builds the synthetic orders. To use your own file, replace the generator with pd.read_csv and rename your columns to order_id, sale_date, ship_country, currency and order_total. Convert sale_date to a date with pd.to_datetime, and make sure the currency column holds three-letter ISO codes (USD, not $).

import numpy as np
import pandas as pd

# Synthetic orders generated for this tutorial. Replace this block with
# orders = pd.read_csv('your_etsy_orders.csv') and rename your columns to match.
rng = np.random.default_rng(7)
n = 600
mix = {'United States': ('USD', 0.50, 44), 'United Kingdom': ('GBP', 0.18, 36),
       'Canada': ('CAD', 0.12, 58), 'Australia': ('AUD', 0.08, 70),
       'Germany': ('EUR', 0.07, 38), 'France': ('EUR', 0.05, 40)}
countries = rng.choice(list(mix), size=n, p=[v[1] for v in mix.values()])
orders = pd.DataFrame({
    'order_id': np.arange(10001, 10001 + n),
    'sale_date': pd.Timestamp('2024-01-01') + pd.to_timedelta(rng.integers(0, 90, n), unit='D'),
    'ship_country': countries,
    'currency': [mix[c][0] for c in countries],
    'order_total': [round(float(rng.gamma(4, mix[c][2] / 4)), 2) for c in countries],
})
orders = orders.sort_values(['sale_date', 'order_id']).reset_index(drop=True)
print(orders.head().to_string(index=False))

Expected output:

 order_id  sale_date   ship_country currency  order_total
    10030 2024-01-01 United Kingdom      GBP        46.90
    10162 2024-01-01        Germany      EUR        25.34
    10532 2024-01-01  United States      USD        51.62
    10556 2024-01-01  United States      USD       128.28
    10019 2024-01-02 United Kingdom      GBP         8.19

Step 2: Count Per Currency, in That Currency

Group by currency and report each currency's own totals. The revenue_local column is in the currency named in the row, so the five revenue figures cannot be added to each other. The order counts and shares of orders can be compared, because an order is one order whatever it was paid in.

# Per currency, in that currency. These amounts are NOT added across currencies.
by_cur = orders.groupby('currency').agg(
    orders=('order_id', 'count'),
    revenue_local=('order_total', 'sum'),
    avg_order_local=('order_total', 'mean'),
).round(2)
by_cur['order_share_pct'] = (100 * by_cur['orders'] / by_cur['orders'].sum()).round(1)
print(by_cur.sort_values('orders', ascending=False))

Expected output:

          orders  revenue_local  avg_order_local  order_share_pct
currency                                                         
USD          288       12528.98            43.50             48.0
GBP          121        4208.53            34.78             20.2
CAD           72        4040.14            56.11             12.0
EUR           68        2567.92            37.76             11.3
AUD           51        3358.17            65.85              8.5

In this data set USD is 48.0% of orders and the four other currencies together are 52.0%. EUR covers two countries (Germany and France), so a currency table is not a country table. Step 3 is needed to rank countries by revenue.

Step 3: Convert to One Reporting Currency

To rank countries by revenue you need every order in one currency. The block below converts each order to USD with the rate from the order's date (the last published rate on or before that date, because the ECB publishes only on working days). It prints how many orders found no rate, which should be zero: if it is not, those orders are silently missing from every total that follows.

# Exchange rates in the layout of the ECB euro reference rates file: units of currency per 1 EUR, one row per day.
# These rates are synthetic. For your own file, download the full history CSV (eurofxref-hist.zip) from the ECB page.
days = pd.bdate_range('2023-12-25', '2024-03-31')
base = {'USD': 1.09, 'GBP': 0.86, 'CAD': 1.46, 'AUD': 1.64}
rates = pd.DataFrame({'Date': days})
for cur, r0 in base.items():
    rates[cur] = r0 * np.exp(np.cumsum(rng.normal(0, 0.003, len(days))))
rates['EUR'] = 1.0

# The ECB publishes on working days only, so carry the last published rate over weekends.
daily = rates.set_index('Date').reindex(pd.date_range(days[0], orders['sale_date'].max(), freq='D')).ffill()
long = daily.stack()
key = pd.MultiIndex.from_arrays([orders['sale_date'], orders['currency']])
orders['cur_per_eur'] = long.reindex(key).to_numpy()
orders['usd_per_eur'] = daily['USD'].reindex(orders['sale_date']).to_numpy()
orders['order_usd'] = orders['order_total'] / orders['cur_per_eur'] * orders['usd_per_eur']
print('orders without a rate:', int(orders['order_usd'].isna().sum()))
print('total in USD at order-date rates:', round(orders['order_usd'].sum(), 2))

Expected output:

orders without a rate: 0
total in USD at order-date rates: 26219.63

Step 4: Rank Countries

Now group by ship-to country. The last line checks that the country totals add up to the currency totals, which is the reconciliation to run on your own file too: it fails if an order has a country but no currency, or the other way round.

by_country = orders.groupby('ship_country').agg(
    currency=('currency', 'first'),
    orders=('order_id', 'count'),
    revenue_usd=('order_usd', 'sum'),
).sort_values('revenue_usd', ascending=False)
by_country['avg_order_usd'] = (by_country['revenue_usd'] / by_country['orders']).round(2)
by_country['share_pct'] = (100 * by_country['revenue_usd'] / by_country['revenue_usd'].sum()).round(1)
by_country['cumulative_pct'] = by_country['share_pct'].cumsum().round(1)
by_country['revenue_usd'] = by_country['revenue_usd'].round(2)
print(by_country.to_string())
by_currency_usd = orders.groupby('currency')['order_usd'].sum()
print('Sum by country equals sum by currency:', bool(np.isclose(by_country['revenue_usd'].sum(), by_currency_usd.sum(), atol=0.05)))

Expected output:

               currency  orders  revenue_usd  avg_order_usd  share_pct  cumulative_pct
ship_country                                                                          
United States       USD     288     12528.98          43.50       47.8            47.8
United Kingdom      GBP     121      5516.15          45.59       21.0            68.8
Canada              CAD      72      3043.90          42.28       11.6            80.4
Australia           AUD      51      2280.60          44.72        8.7            89.1
Germany             EUR      42      1807.73          43.04        6.9            96.0
France              EUR      26      1042.27          40.09        4.0           100.0
Sum by country equals sum by currency: True

Reading the table: the United States is 47.8% of converted revenue and the top three countries (United States, United Kingdom, Canada) are 80.4% of it, from the cumulative column. The United Kingdom has the highest average order value in this data set (45.59 USD, against 43.50 for the United States). The table does not say why, and with 121 UK orders a difference of a couple of dollars in the average is small next to the spread of individual order values, so do not set prices from it. Germany and France are both paid in EUR; their two revenue figures add up to the EUR figure in Step 5 (1,807.73 + 1,042.27 = 2,850.00).

Step 5: Measure What the Exchange Rate Did to the Converted Figure

A converted total moves when the rate moves, even if buyers paid exactly the same. To see how much, convert the same local amounts twice, once at each order's own date rate and once at the last rate in the file, and compare.

# How much does the exchange rate move the USD figure? Same local amounts, two rates.
last = rates.drop(columns='Date').iloc[-1].astype(float)
orders['order_usd_at_last_rate'] = orders['order_total'] / orders['currency'].map(last) * last['USD']
fx = orders.groupby('currency')[['order_usd', 'order_usd_at_last_rate']].sum().round(2)
fx['difference_usd'] = (fx['order_usd_at_last_rate'] - fx['order_usd']).round(2)
fx['difference_pct'] = (100 * fx['difference_usd'] / fx['order_usd']).round(1)
print(fx)

Expected output:

          order_usd  order_usd_at_last_rate  difference_usd  difference_pct
currency                                                                   
AUD         2280.60                 2328.86           48.26             2.1
CAD         3043.90                 3088.55           44.65             1.5
EUR         2850.00                 2882.34           32.34             1.1
GBP         5516.15                 5649.73          133.58             2.4
USD        12528.98                12528.98            0.00             0.0

The USD row is zero because USD orders do not change when converted to USD. For the other currencies, the difference is 1.1% to 2.4% of the converted figure in this synthetic data, and it depends entirely on the synthetic rate path. It says how sensitive the converted number is to the choice of rate, not what your profit would be: to measure the effect on profit you also need your costs and the rate at which your payments were actually converted.

Step 6: Use the Results Carefully

Some uses that follow from the tables, each of which is a hypothesis to test rather than a conclusion:

• Shipping: if three countries account for most converted revenue, as the top three do in the synthetic example, a shipping profile for each is worth looking at. Whether faster shipping would raise sales there is not something this table can show.
• Pricing by market: if you test different prices in different markets, judge the result with a proper comparison, in the way A/B testing compares variants, and not by comparing two averages.
• Seasonality: compare the same months across years rather than consecutive quarters, if you have the history.