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