Try our conversational search powered by Generative AI!

Marketing/Promotion sales report


Episerver Commerce should have a report for a sales campaign or promotion. For example, view all orders that have used a specific promotion code.
This would provide useful information for commerce users.

Currently, I can run a SQL query to get a brief report like this:

SELECT TOP(100) pinfo.CouponCode, pinfo.OrderFormId, po.TrackingNumber, po.Created, SUM(pentry.SavedAmount) AS TotalSavedAmount, 
    FORMAT(form.ShippingTotal, 'N2') AS ShippingTotal, FORMAT(form.ShippingTotal, 'N2') AS Total
FROM [PromotionInformation] pinfo
INNER JOIN [PromotionInformationEntry] pentry ON pinfo.PromotionInformationId = pentry.PromotionInformationId
INNER JOIN [OrderForm] as form ON pinfo.OrderFormId = form.OrderFormId
INNER JOIN [OrderGroup_PurchaseOrder] as po ON form.OrderGroupId = po.ObjectId
WHERE pinfo.CouponCode = 'PROMO_CODE'
GROUP BY pinfo.CouponCode, pinfo.OrderFormId, po.TrackingNumber, po.Created,
    form.ShippingTotal, form.ShippingTotal
ORDER BY po.Created DESC
Jul 02, 2020 3:14
This thread is locked and should be used for reference only.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.