Mastering Product Sales Data Export in PrestaShop 8.1.7: A Custom SQL Guide

Unlocking Precise Product Sales Data in PrestaShop: A Community Solution

PrestaShop, a robust e-commerce platform, offers a wealth of data within its back office. However, extracting this data in a precise, customizable format can sometimes present a challenge. A common scenario, recently highlighted in the PrestaShop forum, involves merchants needing to export specific product sales details that aren't readily available through the default CSV export options.

A user, nicomax17, encountered this exact issue with PrestaShop version 8.1.7. While the 'Ventes' (Sales) tab on a product's back-office page clearly displays crucial information like 'Date de vente', 'Nom client', 'Quantité', and 'Prix', the 'Exporter CSV' button generates a file with entirely different, less granular data, such as 'Popularité', 'Ventes', and 'Visite (x100)'. This discrepancy creates a hurdle for merchants needing specific transactional insights for reporting, analysis, or inventory management.

The Challenge: Mismatched Export Data

The core problem lies in the default behavior of the 'Exporter CSV' button within the product sales statistics. As confirmed by community experts, this button is tied to the statistics module (e.g., ps_statsproduct) and exports aggregated statistical data rather than the detailed sales transaction lines displayed directly above it. For merchants requiring a direct export of individual sales records—customer names, specific prices, and quantities per order—the default functionality falls short.

Initial Thought: Module Modification (and its drawbacks)

One initial suggestion from the community was to directly edit the ps_statsproduct module. While technically feasible for developers, this approach comes with significant caveats. Modifying core module files can lead to complications during future PrestaShop updates, potentially overwriting custom changes and introducing compatibility issues. For long-term maintainability and stability, a less intrusive method is always preferred.

The Recommended Solution: Leveraging PrestaShop's SQL Manager

The most robust and update-safe solution, championed by community members BK Modules and Knowband Plugins, involves utilizing PrestaShop's built-in SQL Manager. This powerful tool, accessible via Back-office >> Paramètres avancés >> Base de données >> Gestionnaire SQL, allows merchants and developers to execute custom SQL queries directly against their PrestaShop database and export the results as a CSV.

Here's the refined SQL query provided by the community, designed to extract the desired product sales details:

SELECT 
    o.date_add, 
    CONCAT(c.firstname,' ',c.lastname) AS client, 
    od.product_quantity, 
    od.unit_price_tax_incl 
FROM 
    ps_order_detail od 
JOIN 
    ps_orders o ON o.id_order = od.id_order 
JOIN 
    ps_customer c ON c.id_customer = o.id_customer 
WHERE 
    od.product_id = 123 
    AND o.valid = 1 
ORDER BY 
    o.date_add DESC

Key Customizations and Enhancements:

  • Table Prefix: Remember to replace ps_ with your actual PrestaShop database table prefix if it's different.
  • Product ID: Change 123 to the specific id_product of the item you wish to analyze. You can find this ID in the product's URL when editing it in the back office.
  • Price Inclusion: Use unit_price_tax_incl for prices including tax, or unit_price_tax_excl for tax-excluded prices.
  • Date Range Filtering: To match the period filter often applied in the back-office 'Ventes' tab, add a date range to your WHERE clause. For example:
    AND o.date_add BETWEEN '2026-01-01 00:00:00' AND '2026-12-31 23:59:59'
  • Multi-Store Support: For PrestaShop installations with multiple shops, ensure you filter by the relevant shop ID if needed:
    AND o.id_shop = 1
    (Replace 1 with your desired shop ID).
  • Order Validity: The o.valid = 1 condition correctly filters for validated orders, mirroring the module's internal logic.

Conclusion: Taking Control of Your Data

This community insight demonstrates a powerful workaround for a common PrestaShop reporting limitation. By embracing the SQL Manager, merchants gain granular control over their data exports, ensuring they can extract precisely the information needed without risking core system integrity through module modifications. This method provides a flexible, update-proof way to generate custom sales reports, empowering better business decisions based on accurate, detailed product performance data.

Start with the tools

Explore migration tools

See options, compare methods, and pick the path that fits your store.

Explore migration tools