PrestaShop

Mastering PrestaShop Product Sales Exports: Beyond the Default CSV

As e-commerce experts at Migrate My Shop, we understand that precise data is the lifeblood of any successful online store. PrestaShop, a powerful platform, offers a wealth of information within its back office, but extracting this data in a highly customized format can sometimes be a puzzle. 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.

PrestaShop SQL Manager for Custom Data Export
PrestaShop SQL Manager for Custom Data Export

The Challenge: Mismatched Sales Data Exports in PrestaShop

A user, nicomax17, encountered this exact issue with PrestaShop version 8.1.7. While navigating to a product's back-office page and selecting the 'Ventes' (Sales) tab, they observed a clear, detailed table displaying crucial information:

  • Date de vente (Sale Date)
  • Nom client (Customer Name)
  • Quantité (Quantity)
  • Prix (Price)

However, upon clicking the 'Exporter CSV' button, the generated file contained entirely different, less granular data, such as 'Popularité', 'Ventes', and 'Visite (x100)'. This discrepancy creates a significant hurdle for merchants requiring specific transactional insights for detailed reporting, inventory management, or customer analysis.

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 is designed to export aggregated statistical data rather than the detailed sales transaction lines displayed directly above it. For merchants requiring a direct export of individual sales records—including customer names, specific prices, and quantities per order—the default functionality falls short.

Initial Thoughts: 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 store stability and ease of maintenance, especially when considering future PrestaShop migrations, directly altering core module files is generally not recommended.

The Robust Solution: Leveraging PrestaShop's SQL Manager

Fortunately, there's a more robust, update-proof solution that empowers PrestaShop merchants to extract precisely the data they need: the built-in SQL Manager. This powerful tool, accessible via Back-office >> Paramètres avancés >> Base de données >> Gestionnaire SQL, allows you to run custom SQL queries directly against your PrestaShop database and export the results as a CSV file. This method ensures your custom exports survive PrestaShop updates and provides unparalleled control over your data.

Crafting Your Custom Sales Export Query

To achieve the desired export of 'Date de vente', 'Nom client', 'Quantité', and 'Prix' for a specific product, you'll need to construct a SQL query that joins relevant tables in your PrestaShop database. Here's a breakdown of the recommended query, along with crucial refinements:

SELECT
    o.date_add AS "Date de vente",
    CONCAT(c.firstname, ' ', c.lastname) AS "Nom client",
    od.product_quantity AS "Quantité",
    od.unit_price_tax_incl AS "Prix (TTC)"
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;

Understanding the Query Components:

  • SELECT Clause: Specifies the columns you want to retrieve. We're selecting the order date (o.date_add), concatenating the customer's first and last names (CONCAT(c.firstname, ' ', c.lastname)), the product quantity from the order detail (od.product_quantity), and the unit price including tax (od.unit_price_tax_incl). Aliases (e.g., AS "Date de vente") make your CSV headers user-friendly.
  • FROM and JOIN Clauses: These clauses link the necessary tables: ps_order_detail (for product-specific order lines), ps_orders (for general order information like date and validity), and ps_customer (for customer names).
  • WHERE Clause: This is crucial for filtering. od.product_id = 123 targets a specific product (remember to replace 123 with your actual product ID). o.valid = 1 ensures only validated orders are included, mirroring PrestaShop's internal logic.
  • ORDER BY Clause: Sorts the results, in this case, by sale date in descending order.

Crucial Refinements for Accuracy and Specificity

To ensure your export precisely matches what you see in the back office or meets specific analytical needs, consider these important additions:

  1. Database Prefix: Always replace ps_ with your actual PrestaShop database prefix if it's different (e.g., _order_detail, _orders, _customer).

  2. Price Type: If you need prices excluding tax, simply change od.unit_price_tax_incl to od.unit_price_tax_excl.

  3. Date Range Filtering: The default module view often applies a period filter. To match this or analyze specific periods, add a date range to your WHERE clause:

    AND o.date_add BETWEEN 'YYYY-MM-DD HH:MM:SS' AND 'YYYY-MM-DD HH:MM:SS'

    For example: AND o.date_add BETWEEN '2026-01-01 00:00:00' AND '2026-12-31 23:59:59'.

  4. Multi-Shop Considerations: If you operate a PrestaShop multi-store environment, the module view typically restricts data to the currently selected shop. To replicate this, add a shop ID filter:

    AND o.id_shop = 1

    Replace 1 with the id_shop of the specific store you wish to query.

Executing Your Query and Exporting the Data

Once you've tailored your SQL query:

  1. Navigate to Paramètres avancés >> Base de données >> Gestionnaire SQL in your PrestaShop back office.
  2. Click on 'Ajouter une nouvelle requête SQL' (Add new SQL query).
  3. Give your query a descriptive name (e.g., "Product Sales Export - Product ID 123").
  4. Paste your refined SQL query into the 'Requête SQL' (SQL query) field.
  5. Save the query.
  6. From the list of saved queries, locate yours and click the 'Exporter' (Export) button to download your custom CSV file.

Beyond the Export: Leveraging Your Precise Data

With this method, you gain unparalleled control over your PrestaShop sales data. This precise information can be invaluable for:

  • Detailed Sales Reporting: Generate custom reports for specific products, customer segments, or timeframes.
  • Inventory Optimization: Understand exact sales velocity per product to refine stock levels and reorder points.
  • Customer Behavior Analysis: Identify top customers for specific products or track purchasing patterns.
  • Migration Preparation: Clean and prepare your sales history for a seamless transition to a new PrestaShop version or another e-commerce platform.

Conclusion: Empowering Your PrestaShop Data Strategy

While PrestaShop's default export options serve many purposes, the ability to directly query your database via the SQL Manager unlocks a new level of data precision and flexibility. This approach is not only more robust than module modifications but also empowers you to gain deeper insights into your store's performance. At Migrate My Shop, we specialize in helping merchants navigate complex PrestaShop data challenges, from custom exports to full-scale migrations. Understanding and leveraging your data effectively is key to sustained e-commerce success.

Share:

Start with the tools

Explore migration tools

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

Explore migration tools