Heatmap on Price Transparency

The heatmap shows, for each country and transaction type, the share of providers with available, missing or ambiguous fee information. Each cell represents a country and transaction type, colored by the proportion of providers with any missing or ambiguous fee information.

Fee information is considered missing if the provider’s website contains no pricing information for the transaction type (or the website was entirely inaccessible). This may mean the provider does not offer the service, or that they do but do not publish fees online. Fee information is considered ambiguous if the provider’s website provides conflicting or incomplete details that prevent identifying an exact fee (e.g. fees listed as “variable”). Cases where fee information was available online but IPA could not collect it due to technical issues were excluded from the dataset but documented in the footnote.

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, -66, -88) THEN NULL ELSE fee END as fee_clean,
    CASE WHEN fee_pct < 0 AND fee_pct NOT IN (-55, -99, -66, -88) THEN NULL ELSE fee_pct END as fee_pct_clean,
    CASE WHEN tax < 0 AND tax NOT IN (-55, -99, -66, -88) THEN NULL ELSE tax END as tax_clean,
    CASE WHEN tax_pct < 0 AND tax_pct NOT IN (-55, -99, -66, -88) THEN NULL ELSE tax_pct END as tax_pct_clean,
    CASE WHEN value_max < 0 AND value_max NOT IN (-55, -99, -66, -88) THEN 10000000 ELSE value_max END as value_max_clean,
    CASE WHEN value_min < 0 AND value_min NOT IN (-55, -99, -66, -88) 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
}))

pricesData = Object.values(
  listed_prices.reduce((acc, row) => {
    const key = [
      row.country,
      row.fsp_type,
      row.provider,
      row.transaction_type,
      row.date_collection
    ].join("|");
    if (!acc[key]) {
      acc[key] = {
        country: row.country,
        fsp_type: row.fsp_type,
        provider: row.provider,
        transaction_type: row.transaction_type,
        date_collection: row.date_collection,
        fees: [],
        fee_pcts: []
      };
    }
    acc[key].fees.push(row.fee);
    acc[key].fee_pcts.push(row.fee_pct);
    return acc;
  }, {})
).map(group => {
  // Filter out technical issues (-55) from calculations
  const validFees = group.fees.filter(fee => fee !== -55);
  const validFeePcts = group.fee_pcts.filter(fee_pct => fee_pct !== -55);
  
  // Skip providers that only have technical issues
  if (validFees.length === 0 && validFeePcts.length === 0) {
    return null;
  }
  
  // Determine status with priority: Missing > Ambiguous > Available
  // If ANY record is missing (-99), mark as missing
  const hasMissing = validFees.some(fee => fee === -99) || validFeePcts.some(fee_pct => fee_pct === -99);
  
  // If no missing but ANY record is ambiguous (-88 or -66), mark as ambiguous  
  const hasAmbiguous = !hasMissing && (
    validFees.some(fee => fee === -88 || fee === -66) || 
    validFeePcts.some(fee_pct => fee_pct === -88 || fee_pct === -66)
  );
  
  // Otherwise check if all valid records are available
  const allAvailable = !hasMissing && !hasAmbiguous && 
    validFees.every(fee => fee >= 0 || fee === -77) &&
    validFeePcts.every(fee_pct => fee_pct >= 0 || fee_pct === null || fee_pct === "");
  
  return {
    country: group.country,
    fsp_type: group.fsp_type,
    provider: group.provider,
    transaction_type: group.transaction_type,
    date_collection: group.date_collection,
    trans_index: allAvailable ? 1 : 0,
    trans_missing: hasMissing ? 1 : 0,
    trans_amborvar: hasAmbiguous ? 1 : 0
  };
}).filter(item => item !== null);

// Summarize proportions for heatmap
summary_data = {
  const result = [];
  const grouped = {};
  
  for (const row of pricesData) {
    const key = [row.country, row.transaction_type, row.date_collection].join("|");
    if (!grouped[key]) {
      grouped[key] = [];
    }
    grouped[key].push(row);
  }
  
  for (const [key, group] of Object.entries(grouped)) {
    const [country, transaction_type, date_collection] = key.split("|");
    const n = group.length;
    const uniqueProviders = [...new Set(group.map(r => r.provider))].length;
    const available = group.filter(r => r.trans_index === 1).length / n;
    const missing = group.filter(r => r.trans_missing === 1).length / n;
    const ambiguous = group.filter(r => r.trans_amborvar === 1).length / n;
    result.push(
      { country, transaction_type, date_collection, category: "Available", proportion: available, providers: uniqueProviders, total: n },
      { country, transaction_type, date_collection, category: "Missing", proportion: missing, providers: uniqueProviders, total: n },
      { country, transaction_type, date_collection, category: "Ambiguous", proportion: ambiguous, providers: uniqueProviders, total: n }
    );
  }
  
  return result;
}

// Remove rows with NA in proportion, country, transaction_type, or category
heatmap_data_filtered_na= summary_data.filter(d =>
  d.proportion !== null &&
  d.proportion !== undefined &&
  !isNaN(d.proportion) &&
  d.country &&
  d.transaction_type &&
  d.category
);

// Add month and format country
heatmap_data = heatmap_data_filtered_na.map(d => ({
  ...d,
  month: d.date_collection.slice(0, 4) + "-" + d.date_collection.slice(4, 6),
  country: d.country.charAt(0).toUpperCase() + d.country.slice(1).toLowerCase()
}));

import {Plot} from "@observablehq/plot"
import {formatMonthDisplay} from "./utils/utils.js"

// Function to format transaction type names
function formatTransactionType(type) {
  switch(type) {
    case "cash-in via agent": return "Cash-in via agent";
    case "cash-in via atm": return "Cash-in via ATM";
    case "p2p on-network transfer": return "On-net transfer";
    case "p2p off-network transfer": return "Off-net transfer";
    case "p2p to unregistered user": return "Transfer 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": return "Wallet-to-bank transfer";
    case "bank to wallet": return "Bank-to-wallet transfer";
    case "payment at merchant": return "Merchant payment";
    case "utility payment": return "Utility payment";
    default: return type;
  }
}

// Format transaction types in heatmap_data
data = heatmap_data.map(d => ({
  ...d,
  transaction_type: formatTransactionType(d.transaction_type)
}));


// Add radio button for category selection
viewof selectedCategory = Inputs.radio(
  [ "Missing", "Ambiguous"],
  {label: "Select category to display:", value: "Missing"}
)
 // Get unique dates for dropdown
uniqueMonth = [...new Set(data.map(d => d.month))].sort()
uniqueMonthDisplay = uniqueMonth.map(month => ({
  value: month,
  label: formatMonthDisplay(month)
}))
viewof selectedDate = Inputs.select(uniqueMonth, {
    label: "Select date:",
    value: uniqueMonth[uniqueMonth.length - 1], // Default to latest date
    sort: false,
    unique: true,
    format: formatMonthDisplay, 
    multiple:false
  })
filteredData = data.filter(d => d.category === selectedCategory
  &&
      d.month === selectedDate
)

Plot.plot({
  marks: [
    Plot.cell(filteredData, {
      x: "transaction_type",
      y: "country",
      fill: "proportion",
      stroke: "white",
      strokeWidth: 1
    }),
    Plot.text(filteredData, {
      x: "transaction_type",
      y: "country",
      text: d => (d.proportion * 100).toFixed(0) + "%",
      fill: "black",
      fontSize: 12,
      fontWeight: "bold"
  }),
    Plot.tip(filteredData, Plot.pointer({
        x: "transaction_type",
    y: "country",
    title: d => [
  `Country: ${d.country}`,
      `Transaction: ${d.transaction_type}`,
          `Providers: ${d.providers}`,
      `${d.category}: ${(d.proportion * 100).toFixed(1)}%`
    ].join("\n")
    }))
  ],
  color:{
    type: "linear", 
    domain: [0, 0.5, 1],
    range: ["#2d8f47", "#e8de6cff", "#d73027"],
    legend: true,
    label: selectedCategory + " (%)"
  },
  x: {
    label: null,
    axis: "top",
    tickRotate: -45,
    tickFormat: d => d,
    tickSize: 6,
    domain: filteredData.map(d => d.transaction_type).filter((v, i, a) => a.indexOf(v) === i).sort()
  },
  y: {
    label: "Country",
    domain: filteredData.map(d => d.country).filter((v, i, a) => a.indexOf(v) === i).sort()
  },
  width: 1000,
  height: 450,
  marginLeft: 150,
  marginBottom: 40,
  marginTop: 120,
  marginRight: 100
})

{
  const selectedDateFormatted = selectedDate.replaceAll('-', '') + '01';
  const technicalIssuesData = listed_prices.filter(d => 
    String(d.date_collection) === selectedDateFormatted &&
    (d.fee === -55 || d.fee_pct === -55)
  );
  
  if (technicalIssuesData.length === 0) {
    return html``;
  }
  
  // Get unique countries and transaction types with technical issues
  const issuesGrouped = technicalIssuesData.reduce((acc, row) => {
    const country = row.country;
    const transactionType = formatTransactionType(row.transaction_type);
    
    if (!acc[country]) {
      acc[country] = new Set();
    }
    acc[country].add(transactionType);
    return acc;
  }, {});
  
  // Format the footnote text
  const footnoteEntries = Object.entries(issuesGrouped)
    .map(([country, transactions]) => 
      `${country} (${Array.from(transactions).sort().join(', ')})`
    )
    .sort();
  
  const footnoteText = footnoteEntries.join('; ');
  
  return html`<div style="margin-top: 20px; padding: 15px; background-color: #f8f9fa; border-left: 4px solid #17a2b8; font-size: 0.9em;">
    <strong>Note on Technical Issues:</strong> Some providers had fees that were available on their websites but could not be collected due to technical difficulties with our data collection process. 
    For ${formatMonthDisplay(selectedDate)}, technical issues affected the following: ${footnoteText}. These providers were excluded from the transparency calculations above.
  </div>`;
}