get_revenue_by_country

Retrieve revenue data for specific countries within defined date ranges to analyze sales performance by region. Supports filtering by order status and tax inclusion.

Instructions

Get revenue filtered by country for a given date range

Input Schema

TableJSON Schema

Name	Required	Description
`date_range`	Yes	Date range expression (e.g., 'today', 'yesterday', 'last week', 'this month', 'YTD', or a specific date range like '2023-01-01 to 2023-01-31')
`country`	Yes	Country code (e.g., 'US', 'NL', 'GB') or country name (e.g., 'United States', 'The Netherlands', 'United Kingdom')
`status`	No	Filter by order status (e.g., 'processing', 'complete', 'pending')
`include_tax`	No	Whether to include tax in the revenue calculation (default: true)

Implementation Reference

mcp-server.js:1157-1255 (handler)

The core handler function that implements the tool's business logic. Fetches orders from Magento API within the specified date range, filters them by billing or shipping country_id matching the normalized country input, calculates total revenue (grand_total) with optional tax exclusion, order count, average order value, and returns formatted JSON.

  async ({ date_range, country, status, include_tax = true }) => {
    try {
      // Parse the date range expression
      const dateRange = parseDateExpression(date_range);
      
      // Normalize country input (handle both country codes and names)
      const normalizedCountry = normalizeCountry(country);
      
      // Build the search criteria for the date range
      let searchCriteria = buildDateRangeFilter('created_at', dateRange.startDate, dateRange.endDate);
      
      // Add status filter if provided
      if (status) {
        searchCriteria += `&searchCriteria[filter_groups][2][filters][0][field]=status&` +
                          `searchCriteria[filter_groups][2][filters][0][value]=${encodeURIComponent(status)}&` +
                          `searchCriteria[filter_groups][2][filters][0][condition_type]=eq`;
      }
      
      // Fetch all orders using the helper function
      const allOrders = await fetchAllPages('/orders', searchCriteria);
      
      // Filter orders by country and calculate revenue
      let totalRevenue = 0;
      let totalTax = 0;
      let orderCount = 0;
      let filteredOrders = [];
      
      if (allOrders && Array.isArray(allOrders)) {
        // Filter orders by country
        filteredOrders = allOrders.filter(order => {
          // Check billing address country
          const billingCountry = order.billing_address?.country_id;
          
          // Check shipping address country
          const shippingCountry = order.extension_attributes?.shipping_assignments?.[0]?.shipping?.address?.country_id;
          
          // Match if either billing or shipping country matches
          return normalizedCountry.includes(billingCountry) || normalizedCountry.includes(shippingCountry);
        });
        
        orderCount = filteredOrders.length;
        
        // Calculate revenue for filtered orders
        filteredOrders.forEach(order => {
          // Use grand_total which includes tax, shipping, etc.
          totalRevenue += parseFloat(order.grand_total || 0);
          
          // Track tax separately
          totalTax += parseFloat(order.tax_amount || 0);
        });
      }
      
      // Adjust revenue if tax should be excluded
      const revenueWithoutTax = totalRevenue - totalTax;
      const finalRevenue = include_tax ? totalRevenue : revenueWithoutTax;
      
      // Format the response
      const result = {
        query: {
          date_range: dateRange.description,
          country: country,
          normalized_country: normalizedCountry.join(', '),
          status: status || 'All',
          include_tax: include_tax,
          period: {
            start_date: format(dateRange.startDate, 'yyyy-MM-dd'),
            end_date: format(dateRange.endDate, 'yyyy-MM-dd')
          }
        },
        result: {
          revenue: parseFloat(finalRevenue.toFixed(2)),
          currency: 'USD', // This should be dynamically determined from the store configuration
          order_count: orderCount,
          average_order_value: orderCount > 0 ? parseFloat((finalRevenue / orderCount).toFixed(2)) : 0,
          tax_amount: parseFloat(totalTax.toFixed(2))
        }
      };
      
      return {
        content: [
          {
            type: "text",
            text: JSON.stringify(result, null, 2)
          }
        ]
      };
    } catch (error) {
      return {
        content: [
          {
            type: "text",
            text: `Error fetching revenue by country: ${error.message}`
          }
        ],
        isError: true
      };
    }
  }
);

mcp-server.js:1151-1156 (schema)

Input schema using Zod for validation and descriptions of parameters: date_range (string, required), country (string, required), status (string, optional), include_tax (boolean, optional).

{
  date_range: z.string().describe("Date range expression (e.g., 'today', 'yesterday', 'last week', 'this month', 'YTD', or a specific date range like '2023-01-01 to 2023-01-31')"),
  country: z.string().describe("Country code (e.g., 'US', 'NL', 'GB') or country name (e.g., 'United States', 'The Netherlands', 'United Kingdom')"),
  status: z.string().optional().describe("Filter by order status (e.g., 'processing', 'complete', 'pending')"),
  include_tax: z.boolean().optional().describe("Whether to include tax in the revenue calculation (default: true)")
},

mcp-server.js:1148-1256 (registration)

Registration of the tool with the MCP server using server.tool(), specifying name, description, input schema, and handler function.

server.tool(
  "get_revenue_by_country",
  "Get revenue filtered by country for a given date range",
  {
    date_range: z.string().describe("Date range expression (e.g., 'today', 'yesterday', 'last week', 'this month', 'YTD', or a specific date range like '2023-01-01 to 2023-01-31')"),
    country: z.string().describe("Country code (e.g., 'US', 'NL', 'GB') or country name (e.g., 'United States', 'The Netherlands', 'United Kingdom')"),
    status: z.string().optional().describe("Filter by order status (e.g., 'processing', 'complete', 'pending')"),
    include_tax: z.boolean().optional().describe("Whether to include tax in the revenue calculation (default: true)")
  },
  async ({ date_range, country, status, include_tax = true }) => {
    try {
      // Parse the date range expression
      const dateRange = parseDateExpression(date_range);
      
      // Normalize country input (handle both country codes and names)
      const normalizedCountry = normalizeCountry(country);
      
      // Build the search criteria for the date range
      let searchCriteria = buildDateRangeFilter('created_at', dateRange.startDate, dateRange.endDate);
      
      // Add status filter if provided
      if (status) {
        searchCriteria += `&searchCriteria[filter_groups][2][filters][0][field]=status&` +
                          `searchCriteria[filter_groups][2][filters][0][value]=${encodeURIComponent(status)}&` +
                          `searchCriteria[filter_groups][2][filters][0][condition_type]=eq`;
      }
      
      // Fetch all orders using the helper function
      const allOrders = await fetchAllPages('/orders', searchCriteria);
      
      // Filter orders by country and calculate revenue
      let totalRevenue = 0;
      let totalTax = 0;
      let orderCount = 0;
      let filteredOrders = [];
      
      if (allOrders && Array.isArray(allOrders)) {
        // Filter orders by country
        filteredOrders = allOrders.filter(order => {
          // Check billing address country
          const billingCountry = order.billing_address?.country_id;
          
          // Check shipping address country
          const shippingCountry = order.extension_attributes?.shipping_assignments?.[0]?.shipping?.address?.country_id;
          
          // Match if either billing or shipping country matches
          return normalizedCountry.includes(billingCountry) || normalizedCountry.includes(shippingCountry);
        });
        
        orderCount = filteredOrders.length;
        
        // Calculate revenue for filtered orders
        filteredOrders.forEach(order => {
          // Use grand_total which includes tax, shipping, etc.
          totalRevenue += parseFloat(order.grand_total || 0);
          
          // Track tax separately
          totalTax += parseFloat(order.tax_amount || 0);
        });
      }
      
      // Adjust revenue if tax should be excluded
      const revenueWithoutTax = totalRevenue - totalTax;
      const finalRevenue = include_tax ? totalRevenue : revenueWithoutTax;
      
      // Format the response
      const result = {
        query: {
          date_range: dateRange.description,
          country: country,
          normalized_country: normalizedCountry.join(', '),
          status: status || 'All',
          include_tax: include_tax,
          period: {
            start_date: format(dateRange.startDate, 'yyyy-MM-dd'),
            end_date: format(dateRange.endDate, 'yyyy-MM-dd')
          }
        },
        result: {
          revenue: parseFloat(finalRevenue.toFixed(2)),
          currency: 'USD', // This should be dynamically determined from the store configuration
          order_count: orderCount,
          average_order_value: orderCount > 0 ? parseFloat((finalRevenue / orderCount).toFixed(2)) : 0,
          tax_amount: parseFloat(totalTax.toFixed(2))
        }
      };
      
      return {
        content: [
          {
            type: "text",
            text: JSON.stringify(result, null, 2)
          }
        ]
      };
    } catch (error) {
      return {
        content: [
          {
            type: "text",
            text: `Error fetching revenue by country: ${error.message}`
          }
        ],
        isError: true
      };
    }
  }
);

mcp-server.js:152-256 (helper)

Supporting helper function that normalizes flexible country input (names or codes) to standard ISO country codes array, used for filtering orders by billing_address.country_id or shipping address country_id.

function normalizeCountry(country) {
  // Normalize the country input (handle both country codes and names)
  const countryInput = country.trim().toLowerCase();
  
  // Map of common country names to ISO country codes
  const countryMap = {
    // Common variations for The Netherlands
    'netherlands': 'NL',
    'the netherlands': 'NL',
    'holland': 'NL',
    'nl': 'NL',
    
    // Common variations for United States
    'united states': 'US',
    'usa': 'US',
    'us': 'US',
    'america': 'US',
    
    // Common variations for United Kingdom
    'united kingdom': 'GB',
    'uk': 'GB',
    'great britain': 'GB',
    'gb': 'GB',
    'england': 'GB',
    
    // Add more countries as needed
    'canada': 'CA',
    'ca': 'CA',
    
    'australia': 'AU',
    'au': 'AU',
    
    'germany': 'DE',
    'de': 'DE',
    
    'france': 'FR',
    'fr': 'FR',
    
    'italy': 'IT',
    'it': 'IT',
    
    'spain': 'ES',
    'es': 'ES',
    
    'belgium': 'BE',
    'be': 'BE',
    
    'sweden': 'SE',
    'se': 'SE',
    
    'norway': 'NO',
    'no': 'NO',
    
    'denmark': 'DK',
    'dk': 'DK',
    
    'finland': 'FI',
    'fi': 'FI',
    
    'ireland': 'IE',
    'ie': 'IE',
    
    'switzerland': 'CH',
    'ch': 'CH',
    
    'austria': 'AT',
    'at': 'AT',
    
    'portugal': 'PT',
    'pt': 'PT',
    
    'greece': 'GR',
    'gr': 'GR',
    
    'poland': 'PL',
    'pl': 'PL',
    
    'japan': 'JP',
    'jp': 'JP',
    
    'china': 'CN',
    'cn': 'CN',
    
    'india': 'IN',
    'in': 'IN',
    
    'brazil': 'BR',
    'br': 'BR',
    
    'mexico': 'MX',
    'mx': 'MX',
    
    'south africa': 'ZA',
    'za': 'ZA'
  };
  
  // Check if the input is in our map
  if (countryMap[countryInput]) {
    return [countryMap[countryInput]];
  }
  
  // If it's not in our map, assume it's a country code or name and return as is
  // For a more robust solution, we would validate against a complete list of country codes
  return [countryInput.toUpperCase()];
}

mcp-server.js:23-124 (helper)

Helper utility to parse human-readable date_range strings into precise startDate/endDate Date objects, supporting relative terms like 'ytd', 'last week', exact dates, and ranges.

function parseDateExpression(dateExpression) {
  const now = new Date();
  const currentYear = now.getFullYear();
  const currentMonth = now.getMonth();
  const currentDay = now.getDate();
  
  // Normalize the date expression
  const normalizedExpression = dateExpression.toLowerCase().trim();
  
  // Handle relative date expressions
  switch (normalizedExpression) {
    case 'today':
      return {
        startDate: startOfDay(now),
        endDate: endOfDay(now),
        description: 'Today'
      };
    case 'yesterday':
      const yesterday = subDays(now, 1);
      return {
        startDate: startOfDay(yesterday),
        endDate: endOfDay(yesterday),
        description: 'Yesterday'
      };
    case 'this week':
      return {
        startDate: startOfWeek(now, { weekStartsOn: 1 }), // Week starts on Monday
        endDate: endOfDay(now),
        description: 'This week'
      };
    case 'last week':
      const lastWeekStart = subDays(startOfWeek(now, { weekStartsOn: 1 }), 7);
      const lastWeekEnd = subDays(endOfWeek(now, { weekStartsOn: 1 }), 7);
      return {
        startDate: lastWeekStart,
        endDate: lastWeekEnd,
        description: 'Last week'
      };
    case 'this month':
      return {
        startDate: startOfMonth(now),
        endDate: endOfDay(now),
        description: 'This month'
      };
    case 'last month':
      const lastMonth = new Date(currentYear, currentMonth - 1, 1);
      return {
        startDate: startOfMonth(lastMonth),
        endDate: endOfMonth(lastMonth),
        description: 'Last month'
      };
    case 'ytd':
    case 'this ytd':
    case 'this year to date':
    case 'year to date':
      return {
        startDate: startOfYear(now),
        endDate: endOfDay(now),
        description: 'Year to date'
      };
    case 'last year':
      const lastYear = new Date(currentYear - 1, 0, 1);
      return {
        startDate: startOfYear(lastYear),
        endDate: endOfYear(lastYear),
        description: 'Last year'
      };
    default:
      // Try to parse as ISO date or other common formats
      try {
        // Check if it's a single date (not a range)
        const parsedDate = parseISO(normalizedExpression);
        if (isValid(parsedDate)) {
          return {
            startDate: startOfDay(parsedDate),
            endDate: endOfDay(parsedDate),
            description: format(parsedDate, 'yyyy-MM-dd')
          };
        }
        
        // Check if it's a date range in format "YYYY-MM-DD to YYYY-MM-DD"
        const rangeParts = normalizedExpression.split(' to ');
        if (rangeParts.length === 2) {
          const startDate = parseISO(rangeParts[0]);
          const endDate = parseISO(rangeParts[1]);
          
          if (isValid(startDate) && isValid(endDate)) {
            return {
              startDate: startOfDay(startDate),
              endDate: endOfDay(endDate),
              description: `${format(startDate, 'yyyy-MM-dd')} to ${format(endDate, 'yyyy-MM-dd')}`
            };
          }
        }
        
        // If we can't parse it, throw an error
        throw new Error(`Unable to parse date expression: ${dateExpression}`);
      } catch (error) {
        throw new Error(`Invalid date expression: ${dateExpression}. ${error.message}`);
      }
  }
}

Magento 2 MCP Server