Within-country Pricing

DFS transactions are typically either free, or follow two pricing structures: (1) slab-based pricing, where transactions within a set range are charged a flat fee; or (2) percentage-based pricing, where fees are set as a consistent percentage of the transaction amount.

This visualization shows how providers in the selected country price the selected transaction type. Prices for all providers in the selected country are shown in orange while prices for other providers in different countries are shown in gray.

In the current beta version, we include data on two providers in Sierra Leone and Paraguay; we include data for just one provider for all other countries.

// Setup DuckDB client with data sources
dfsdb = DuckDBClient.of({
  all_prices: FileAttachment("data/all_listed_prices.csv")
})

listed_prices_raw = dfsdb.query(`
  SELECT *,
    CASE WHEN fee < 0 AND fee NOT IN (-55, -99) THEN NULL ELSE fee END as fee_clean,
    CASE WHEN fee_pct < 0 AND fee_pct NOT IN (-55, -99) THEN NULL ELSE fee_pct END as fee_pct_clean,
    CASE WHEN tax < 0 AND tax NOT IN (-55, -99) THEN NULL ELSE tax END as tax_clean,
    CASE WHEN tax_pct < 0 AND tax_pct NOT IN (-55, -99) THEN NULL ELSE tax_pct END as tax_pct_clean,
    CASE WHEN value_max < 0 AND value_max NOT IN (-55, -99) THEN 10000000 ELSE value_max END as value_max_clean,
    CASE WHEN value_min < 0 AND value_min NOT IN (-55, -99) THEN 0 ELSE value_min END as value_min_clean,
    CONCAT(SUBSTR(CAST(date_collection AS VARCHAR), 1, 4), '-', SUBSTR(CAST(date_collection AS VARCHAR), 5, 2)) as month,
    value_min / exchange_rate as value_min_usd,
    CASE WHEN value_max IS NULL THEN NULL ELSE value_max / exchange_rate END as value_max_usd,
    UPPER(SUBSTR(country, 1, 1)) || LOWER(SUBSTR(country, 2)) as country_formatted
  FROM all_prices
`)

// Convert to plain JavaScript (bypass Arquero closure issues)
listed_prices = listed_prices_raw.map(row => ({
  // Keep all columns except the original ones we're replacing
  ...Object.fromEntries(
    Object.entries(row).filter(([key]) =>
      !['fee', 'fee_pct', 'tax', 'tax_pct', 'value_max', 'value_min', 'country'].includes(key)
    )
  ),
  // Rename cleaned columns
  fee: row.fee_clean,
  fee_pct: row.fee_pct_clean,
  tax: row.tax_clean,
  tax_pct: row.tax_pct_clean,
  value_max: row.value_max_clean,
  value_min: row.value_min_clean,
  country: row.country_formatted
}))

transaction_values_usd = Array.from({length: 100}, (_, i) => ({ value_usd: i + 1 }))

// Function to calculate fee percentage for each provider's fee structure
function calculateFees(data, transactionValues) {
  const allFees = [];

  // Use data directly as it's already plain JavaScript objects
  const dataObjects = data;

  for (const row of dataObjects) {
    // Skip rows with -55 or -99 values in fee or fee_pct
    if ((row.fee !== null && (row.fee === -55 || row.fee === -99)) ||
        (row.fee_pct !== null && (row.fee_pct === -55 || row.fee_pct === -99))) {
      continue;
    }

    // Handle missing or invalid value_min and value_max
    const valueMinUsd = (row.value_min == null || row.value_min < 0) ? 0 : row.value_min / row.exchange_rate;
    const valueMaxUsd = (row.value_max == null || row.value_max < 0) ? Infinity : row.value_max / row.exchange_rate;

    // Calculate which transaction values fall within this fee structure's range
    const applicableValues = transactionValues.filter(tv =>
      tv.value_usd >= valueMinUsd && tv.value_usd <= valueMaxUsd
    );

    if (applicableValues.length > 0) {
      // For each applicable value, calculate fee
      for (const tv of applicableValues) {
        const localCurrency = tv.value_usd * row.exchange_rate;
        const fixedFee = row.fee || 0;
        const pctFee = row.fee_pct || 0;
        const tax = row.tax || 0;
        const taxPct = row.tax_pct || 0;
        const taxType = row.tax_type;

        // Calculate total fee depending on tax_type
        let totalFee;
        if (taxType === 1) {
          totalFee = fixedFee + (pctFee / 100 * localCurrency) + tax + (taxPct / 100 * localCurrency);
        } else {
          totalFee = fixedFee + (pctFee / 100 * localCurrency);
        }

        // Calculate as percentage
        const feePercentage = (totalFee / localCurrency) * 100;

        allFees.push({
          value_usd: tv.value_usd,
          provider: row.provider,
          transaction_type: row.transaction_type,
          country: row.country,
          month: row.month,
          local_currency: localCurrency,
          fixed_fee: fixedFee,
          pct_fee: pctFee,
          tax: tax,
          tax_pct: taxPct,
          tax_type: taxType,
          total_fee: totalFee,
          fee_percentage: feePercentage
        });
      }
    }
  }

  return allFees;
}

// Calculate fees for all providers and transaction types
fee_data_raw = calculateFees(listed_prices, transaction_values_usd)

// Format transaction type names to be more readable using plain JavaScript
fee_data = fee_data_raw.map(d => {
  let transaction_type_formatted;
  switch(d.transaction_type) {
    case "cash-in via agent": transaction_type_formatted = "Cash-in via agent"; break;
    case "cash-in via atm": transaction_type_formatted = "Cash-in via ATM"; break;
    case "p2p on-network transfer": transaction_type_formatted = "On-net transfer"; break;
    case "p2p off-network transfer": transaction_type_formatted = "Off-net transfer"; break;
    case "p2p to unregistered user": transaction_type_formatted = "Transfer to unregistered user"; break;
    case "cash-out via agent": transaction_type_formatted = "Cash-out via agent"; break;
    case "cash-out via atm": transaction_type_formatted = "Cash-out via ATM"; break;
    case "wallet to bank": transaction_type_formatted = "Wallet-to-bank transfer"; break;
    case "bank to wallet": transaction_type_formatted = "Bank-to-wallet transfer"; break;
    case "payment at merchant": transaction_type_formatted = "Merchant payment"; break;
    case "utility payment": transaction_type_formatted = "Utility payment"; break;
    default: transaction_type_formatted = d.transaction_type; break;
  }

  // Return object without original transaction_type, renamed to transaction_type
  d.transaction_type = transaction_type_formatted;
  return d;
})

import {prettyName} from "/assets/js/utilities.js"
import {primaryPalette as palette} from "/assets/js/colors.js"

// Use data directly from Observable processing
pricesData = listed_prices
feeData = fee_data


//| panel: input
// Extract category values from the data and sort in reverse chronological order
uniqueCountry = [...new Set(feeData.map(prices => prices.country))].sort()

//| panel: input
viewof selectedCountry = Inputs.select(
  uniqueCountry,
  { value: "Bangladesh",
    label: "Country:",
    multiple: false,
    sort: true,
    unique: true
  }
)

// Extract unique month values from the data and sort in reverse chronological order
uniqueMonthRaw = [...new Set(feeData.map(prices => prices.month))].sort().reverse();
uniqueMonthDisplay = uniqueMonthRaw.map(m => {
  const [year, month] = m.split('-');
  const monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
  return `${year}-${monthNames[parseInt(month, 10) - 1]}`;
});

// Map display month to raw value
monthMap = Object.fromEntries(uniqueMonthDisplay.map((disp, i) => [disp, uniqueMonthRaw[i]]));

viewof selectedMonth = Inputs.select(
  uniqueMonthDisplay,
  { value: uniqueMonthDisplay[0],
    label: "Month:",
    multiple: false,
    sort: false, // Disable sorting to preserve reverse chronological order
    unique: true
  }
)

// Define the desired order for transaction types
orderedCategories = [
  "Cash-in via agent",
  "Cash-in via ATM",
  "Cash-out via agent",
  "Cash-out via ATM",
  "On-net transfer",
  "Off-net transfer",
  "Transfer to unregistered user",
  "Utility payment",
  "Merchant payment",
  "Wallet-to-bank transfer",
  "Bank-to-wallet transfer"
]

// Get all available transaction types
availableCategories = [...new Set(feeData.map(d => d.transaction_type))]

// Filter orderedCategories to include only types that exist in the data
uniqueCategory = orderedCategories.filter(type => availableCategories.includes(type))

//| panel: input
viewof selectedTransactionType = Inputs.select(
  uniqueCategory,
  { value: "Cash-out via agent",
    label: "Transaction Types:",
    multiple: false,
    sort: false,
    unique: true
  }
)

filteredData = feeData.filter(d =>
  d.month === monthMap[selectedMonth] &&
  d.transaction_type === selectedTransactionType
)
// Create a unique ID for each provider+country combination to ensure we draw one line per combination
filteredData.forEach(d => {
  d.lineId = d.provider + "_" + d.country;

  // Add display name for tooltips - include provider name for all countries
  if (filteredData.filter(item => item.country === d.country).length > 1) {
    // Multiple providers for this country
    d.displayName = d.provider + " (" + d.country + ")";
  } else {
    // Single provider for this country
    d.displayName = d.country;
  }

  // Add tooltipPriority to ensure selected country takes precedence in overlapping cases
  d.tooltipPriority = d.country === selectedCountry ? 1 : 0;
})

sortedByValue = filteredData.sort((a, b) => a.value_usd - b.value_usd);

// For rendering, specifically in case of overlaps, we want to prepare our data in two sets
// First, we'll have all non-selected countries, then selected country on top
// This way, the tooltips for selected country will take precedence when there's overlap
plotData = [
  ...sortedByValue.filter(d => d.country !== selectedCountry),
  ...sortedByValue.filter(d => d.country === selectedCountry)
]

Plot

hasSelectedCountryData = plotData.some(d => d.country === selectedCountry)

// First get unique countries from the raw data
dataCountries = [...new Set(pricesData.map(d => d.country))]

// Get countries with plotted data - these have entries in plotData
countriesWithPlottedData = [...new Set(plotData.map(d => d.country))]

// Map formatted transaction type back to original CSV transaction type
rawTransactionType = (() => {
  switch(selectedTransactionType) {
    case "Cash-in via agent": return "cash-in via agent";
    case "Cash-in via ATM": return "cash-in via atm";
    case "On-net transfer": return "p2p on-network transfer";
    case "Off-net transfer": return "p2p off-network transfer";
    case "Transfer to unregistered user": return "p2p to unregistered user";
    case "Cash-out via agent": return "cash-out via agent";
    case "Cash-out via ATM": return "cash-out via atm";
    case "Wallet-to-bank transfer": return "wallet to bank";
    case "Bank-to-wallet transfer": return "bank to wallet";
    case "Merchant payment": return "payment at merchant";
    case "Utility payment": return "utility payment";
    default: return selectedTransactionType.toLowerCase();
  }
})()

// Identify countries with technical issues (-55 values) not already in plotData
countriesWithTechnicalIssues = [...new Set(pricesData
  .filter(d =>
    d.transaction_type.toLowerCase() === rawTransactionType.toLowerCase() &&
    d.month === monthMap[selectedMonth] &&
    (d.fee == -55 || d.fee_pct == -55) && // Using == instead of === for numeric comparison
    !countriesWithPlottedData.includes(d.country) // Make sure this country doesn't already have plotted data
  )
  .map(d => d.country)
)]

// Get countries that are in the dataset but have -99 fees (not listed)
// These are countries with "not listed" fees
countriesWithNoListedFees = [...new Set(pricesData
  .filter(d =>
    d.transaction_type.toLowerCase() === rawTransactionType.toLowerCase() &&
    d.month === monthMap[selectedMonth] &&
    (d.fee == -99 || d.fee_pct == -99)
  )
  .map(d => d.country)
)]

// Create combined footnote text with both notes and country listings
footnoteText = html`
<div style="margin-top: 15px; font-size: 14px; max-width: 800px;">
  <p><strong>Notes:</strong></p>
  <ol style="padding-left: 20px; margin-top: 5px;">
    <li>
      ${countriesWithNoListedFees.length > 0 ? html`<span style="font-style: italic;">Countries with unlisted fees for ${selectedTransactionType}: ${countriesWithNoListedFees.join(", ")}</span><br/>` : ''}
      Fees labeled "not listed" are not clearly available on provider websites. This may indicate the transaction is not offered by the provider, or the transaction is offered but lacks clear pricing on the website. In these cases, prices may be missing, ambiguous, inconsistent, or described only as "varies."
    </li>
    <li>
      ${countriesWithTechnicalIssues.length > 0 ? html`<span style="font-style: italic;">Countries with unavailable fees for ${selectedTransactionType} due to technical issues: ${countriesWithTechnicalIssues.join(", ")}</span><br/>` : ''}
      Fees labeled as "unavailable due to technical issues" are available on provider websites, but IPA was unable to collect this information due to technical difficulties with our data collection process.
    </li>
  </ol>
</div>`

// Empty placeholders for sections that will no longer be used
technicalIssuesSection = html``;
notListedSection = html``;

// Get providers for the selected country and sort them alphabetically for consistent coloring
selectedCountryProviders = [...new Set(plotData
  .filter(d => d.country === selectedCountry)
  .map(d => d.provider))].sort()

// Create a special color scheme for the selected country's providers
hasMultipleProvidersForSelectedCountry = selectedCountryProviders.length > 1;

// Define a consistent color palette for providers
providerColorPalette = ["#f26529", "#9b59b6"];

// Create a color mapping function that handles multiple providers in selected country
function getStrokeColor(d) {
  // If not the selected country, always gray
  if (d.country !== selectedCountry) return "#c9c9c8";

  // If only one provider in selected country, use orange
  if (!hasMultipleProvidersForSelectedCountry) return "#f26529";

  // Otherwise, return a provider-specific color from palette
  const providerIndex = selectedCountryProviders.indexOf(d.provider);
  if (providerIndex === -1) return "#c9c9c8"; // Fallback for unexpected cases
  return providerColorPalette[providerIndex % providerColorPalette.length];
}

// Create a legend specifically for providers in the selected country
providerLegendItems = hasMultipleProvidersForSelectedCountry ?
  selectedCountryProviders.map((provider, i) => {
    return {
      color: providerColorPalette[i % providerColorPalette.length],
      label: provider
    };
  }) : [];

// Create the legend HTML if there are multiple providers
providerLegend = hasMultipleProvidersForSelectedCountry ?
  html`<div style="margin-top: 10px; margin-bottom: 10px;">
    <strong>Providers in ${selectedCountry}:</strong>
    <div style="display: flex; gap: 15px; margin-top: 5px;">
      ${providerLegendItems.map(item => html`
        <div style="display: flex; align-items: center;">
          <div style="width: 12px; height: 12px; background-color: ${item.color}; margin-right: 5px;"></div>
          <span>${item.label}</span>
        </div>
      `)}
    </div>
  </div>` :
  html``;

html`
<div>
${Plot.plot({
  title: `${prettyName(selectedTransactionType)} Pricing in ${prettyName(selectedCountry)} (${selectedMonth})`,
  width: 800,
  height: 500,
  x: {
    label: "Transaction Value (USD)",
    domain: [0,100]
  },
  y: {
    label: "Fee (% of Transaction Value)",
    nice: true,
    grid: true,
    tickFormat: d => d + "%"
  },
  color: {
    legend: false
  },
  marks: [
      Plot.ruleY([0]),
      // Draw all lines in one plot mark to leverage plot.js's own tooltip priority
      Plot.line(
        plotData, // All the data, but ordered with selected country last
        {
          x: "value_usd",
          y: "fee_percentage",
          stroke: d => getStrokeColor(d),
          strokeWidth: d => d.country === selectedCountry ? 3 : 2,
          opacity: d => d.country === selectedCountry ? 1 : 0.4,
          curve: "linear",
          // IMPORTANT: Use provider name in the z grouping for multiple providers to get separate lines
          z: d => d.country === selectedCountry && hasMultipleProvidersForSelectedCountry ? d.provider : d.lineId,
          sort: {y: "x"},
          channels: {
            "Country": "country",
            "Provider": "provider",
            "Fee (%)": d => d.fee_percentage != null ? d.fee_percentage.toFixed(2) + "%" : "N/A"
          },
          tip: {
            format: {
              y: false,
              x: false,
              stroke: false,
              opacity: false,
              z: false,
              strokeWidth: false
            }
          }
        }
      ),
  ]
})}
${providerLegend}
${footnoteText}
</div>`

Data

Inputs.table(plotData, {select: false})

// from https://observablehq.com/@jeremiak/download-data-button
button = (data, filename = 'data.csv') => {
  if (!data) throw new Error('Array of data required as first argument');

  let downloadData;
  if (filename.includes('.csv')) {
    downloadData = new Blob([d3.csvFormat(data)], { type: "text/csv" });
  } else {
    downloadData = new Blob([JSON.stringify(data, null, 2)], {
      type: "application/json"
    });
  }

  const size = (downloadData.size / 1024).toFixed(0);
  const button = DOM.download(
    downloadData,
    filename,
    `Download Data (~${size} KB)`
  );
  return button;
}