get_product_sales

Retrieve product sales quantity statistics for a specified date range, with optional filters for order status and country.

Instructions

Get statistics about the quantity of products sold in 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')
`status`	No	Filter by order status (e.g., 'processing', 'complete', 'pending')
`country`	No	Filter by country code (e.g., 'US', 'NL', 'GB') or country name (e.g., 'United States', 'The Netherlands', 'United Kingdom')

Implementation Reference

mcp-server.js:1015-1144 (handler)

Inline async handler function implementing the core logic: parses date range, builds API query, fetches all orders via pagination helper, filters by status/country, processes order items to compute totals (orders, items, quantity, revenue), tracks top products by SKU, formats and returns JSON response.

async ({ date_range, status, country }) => {
  try {
    // Parse the date range expression
    const dateRange = parseDateExpression(date_range);
    
    // 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 if provided
    let filteredOrders = allOrders;
    if (country) {
      // Normalize country input
      const normalizedCountry = normalizeCountry(country);
      
      // Filter orders by country
      filteredOrders = filteredOrders.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);
      });
    }
    
    // Calculate statistics
    let totalOrders = filteredOrders.length;
    let totalOrderItems = 0;
    let totalProductQuantity = 0;
    let totalRevenue = 0;
    let productCounts = {};
    
    // Process each order
    filteredOrders.forEach(order => {
      // Add to total revenue
      totalRevenue += parseFloat(order.grand_total || 0);
      
      // Process order items
      if (order.items && Array.isArray(order.items)) {
        // Count total order items (order lines)
        totalOrderItems += order.items.length;
        
        // Process each item
        order.items.forEach(item => {
          // Add to total product quantity
          const quantity = parseFloat(item.qty_ordered || 0);
          totalProductQuantity += quantity;
          
          // Track product counts by SKU
          const sku = item.sku;
          if (sku) {
            if (!productCounts[sku]) {
              productCounts[sku] = {
                name: item.name,
                quantity: 0,
                revenue: 0
              };
            }
            productCounts[sku].quantity += quantity;
            productCounts[sku].revenue += parseFloat(item.row_total || 0);
          }
        });
      }
    });
    
    // Convert product counts to array and sort by quantity
    const topProducts = Object.entries(productCounts)
      .map(([sku, data]) => ({
        sku,
        name: data.name,
        quantity: data.quantity,
        revenue: data.revenue
      }))
      .sort((a, b) => b.quantity - a.quantity)
      .slice(0, 10); // Top 10 products
    
    // Format the response
    const result = {
      query: {
        date_range: dateRange.description,
        status: status || 'All',
        country: country || 'All',
        period: {
          start_date: format(dateRange.startDate, 'yyyy-MM-dd'),
          end_date: format(dateRange.endDate, 'yyyy-MM-dd')
        }
      },
      result: {
        total_orders: totalOrders,
        total_order_items: totalOrderItems,
        total_product_quantity: totalProductQuantity,
        average_products_per_order: totalOrders > 0 ? parseFloat((totalProductQuantity / totalOrders).toFixed(2)) : 0,
        total_revenue: parseFloat(totalRevenue.toFixed(2)),
        average_revenue_per_product: totalProductQuantity > 0 ? parseFloat((totalRevenue / totalProductQuantity).toFixed(2)) : 0,
        top_products: topProducts
      }
    };
    
    return {
      content: [
        {
          type: "text",
          text: JSON.stringify(result, null, 2)
        }
      ]
    };
  } catch (error) {
    return {
      content: [
        {
          type: "text",
          text: `Error fetching product sales: ${error.message}`
        }
      ],
      isError: true
    };
  }
}

mcp-server.js:1010-1014 (schema)

Zod-based input schema defining parameters: date_range (required string for date expressions), status (optional string for order status filter), country (optional string for country filter).

{
  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')"),
  status: z.string().optional().describe("Filter by order status (e.g., 'processing', 'complete', 'pending')"),
  country: z.string().optional().describe("Filter by country code (e.g., 'US', 'NL', 'GB') or country name (e.g., 'United States', 'The Netherlands', 'United Kingdom')")
},

mcp-server.js:1007-1145 (registration)

MCP server tool registration call: server.tool('get_product_sales', description, schema, handler). Includes comment '// Tool: Get product sales'.

server.tool(
  "get_product_sales",
  "Get statistics about the quantity of products sold in 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')"),
    status: z.string().optional().describe("Filter by order status (e.g., 'processing', 'complete', 'pending')"),
    country: z.string().optional().describe("Filter by country code (e.g., 'US', 'NL', 'GB') or country name (e.g., 'United States', 'The Netherlands', 'United Kingdom')")
  },
  async ({ date_range, status, country }) => {
    try {
      // Parse the date range expression
      const dateRange = parseDateExpression(date_range);
      
      // 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 if provided
      let filteredOrders = allOrders;
      if (country) {
        // Normalize country input
        const normalizedCountry = normalizeCountry(country);
        
        // Filter orders by country
        filteredOrders = filteredOrders.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);
        });
      }
      
      // Calculate statistics
      let totalOrders = filteredOrders.length;
      let totalOrderItems = 0;
      let totalProductQuantity = 0;
      let totalRevenue = 0;
      let productCounts = {};
      
      // Process each order
      filteredOrders.forEach(order => {
        // Add to total revenue
        totalRevenue += parseFloat(order.grand_total || 0);
        
        // Process order items
        if (order.items && Array.isArray(order.items)) {
          // Count total order items (order lines)
          totalOrderItems += order.items.length;
          
          // Process each item
          order.items.forEach(item => {
            // Add to total product quantity
            const quantity = parseFloat(item.qty_ordered || 0);
            totalProductQuantity += quantity;
            
            // Track product counts by SKU
            const sku = item.sku;
            if (sku) {
              if (!productCounts[sku]) {
                productCounts[sku] = {
                  name: item.name,
                  quantity: 0,
                  revenue: 0
                };
              }
              productCounts[sku].quantity += quantity;
              productCounts[sku].revenue += parseFloat(item.row_total || 0);
            }
          });
        }
      });
      
      // Convert product counts to array and sort by quantity
      const topProducts = Object.entries(productCounts)
        .map(([sku, data]) => ({
          sku,
          name: data.name,
          quantity: data.quantity,
          revenue: data.revenue
        }))
        .sort((a, b) => b.quantity - a.quantity)
        .slice(0, 10); // Top 10 products
      
      // Format the response
      const result = {
        query: {
          date_range: dateRange.description,
          status: status || 'All',
          country: country || 'All',
          period: {
            start_date: format(dateRange.startDate, 'yyyy-MM-dd'),
            end_date: format(dateRange.endDate, 'yyyy-MM-dd')
          }
        },
        result: {
          total_orders: totalOrders,
          total_order_items: totalOrderItems,
          total_product_quantity: totalProductQuantity,
          average_products_per_order: totalOrders > 0 ? parseFloat((totalProductQuantity / totalOrders).toFixed(2)) : 0,
          total_revenue: parseFloat(totalRevenue.toFixed(2)),
          average_revenue_per_product: totalProductQuantity > 0 ? parseFloat((totalRevenue / totalProductQuantity).toFixed(2)) : 0,
          top_products: topProducts
        }
      };
      
      return {
        content: [
          {
            type: "text",
            text: JSON.stringify(result, null, 2)
          }
        ]
      };
    } catch (error) {
      return {
        content: [
          {
            type: "text",
            text: `Error fetching product sales: ${error.message}`
          }
        ],
        isError: true
      };
    }
  }
);

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

parseDateExpression(dateExpression): Parses relative date strings (e.g., 'today', 'last month', '2023-01-01 to 2023-01-31') into {startDate, endDate, description} using date-fns.

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}`);
      }
  }
}

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

normalizeCountry(country): Maps common country names/variations to ISO codes (e.g., 'netherlands' -> 'NL') for use in order filtering by billing/shipping address.

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()];
}

Magento 2 MCP Server