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 DESCKey Customizations and Enhancements:
- Table Prefix: Remember to replace
ps_with your actual PrestaShop database table prefix if it's different. - Product ID: Change
123to the specificid_productof 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_inclfor prices including tax, orunit_price_tax_exclfor 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
WHEREclause. 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:
(ReplaceAND o.id_shop = 11with your desired shop ID). - Order Validity: The
o.valid = 1condition 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.