Shopify Reports: Manual Price Drops & Vendor Sales Tracking

Hey everyone, your Shopify migration expert here! I recently stumbled upon a really insightful question in the Shopify community forums that I know many of you are probably grappling with. It came from jamarzy, who was trying to get a specific sales report for an email campaign that featured manual product price decreases, not your typical Shopify discounts. And the big challenge? Grouping those sales by product vendor.

I totally get jamarzy's frustration. They mentioned that "all reporting breaks" when trying to display sales for an email, grouped by vendor, especially since there wasn't a formal discount code involved. Shopify AI wasn't cutting it, and even the Campaigns report wasn't flexible enough. This isn't an uncommon scenario, and it highlights a critical area where Shopify's standard reporting can feel a bit rigid when you're doing things "off-script" with manual price adjustments.

Why Manual Price Drop Reporting Gets Tricky

Here's the core of the issue: Shopify's native reporting excels when you use its built-in features. Discounts created directly in Shopify are clearly tagged to orders, making them easy to track. Similarly, sales from specific marketing campaigns are often linked via UTM parameters in the order source data. But when you manually change a product's price directly on the product page, Shopify simply sees it as the new regular price. It doesn't inherently flag that specific price point as part of a "sale event" or tie it back to a particular marketing push in the same way a discount code would.

So, when you send an email promoting these manually reduced items, getting a precise report that isolates only those sales, attributes them to that specific email, and then breaks them down by vendor becomes a multi-layered puzzle.

Cracking the Code: A Step-by-Step Approach for Current Situations

Since jamarzy was asking for a retroactive report, we need to get a bit creative. While ShopifyQL is the ultimate answer for deep dives, let's first outline the logical steps you'd need to take to piece this together, even if you're not a query wizard (yet!):

1. Pinpointing Sales from Your Email Campaign

The first hurdle is identifying which orders actually came from your email. This relies heavily on how you linked products in your email. Ideally, you used UTM parameters in your email links. For example, if your email links looked like yourstore.com/product-a?utm_source=email&utm_medium=newsletter&utm_campaign=spring_sale, you can filter orders based on these parameters.

Go to your Shopify Admin > Analytics > Reports.
Look at the "Sales by traffic referrer" or "Sales by marketing campaign" reports.
Filter by the date range of your campaign and look for your specific email campaign source (e.g., "Email," "Klaviyo," "Mailchimp") or the specific UTM campaign name you used.
Alternatively, you might need to export your orders for the campaign period and filter by "Referring site" or "Source Name."

2. Identifying Products with Manual Price Drops

This is where it gets tricky without a discount code. You'll need a list of the exact products (and variants, if applicable) that had their prices manually reduced for this specific email campaign. Make sure you have this list handy.

Once you have a filtered list of orders from step 1, you'll need to examine the line items within those orders.
Cross-reference the products in those line items with your list of manually priced-down products.
This often involves exporting the filtered orders (including line item details) and using a spreadsheet program (like Excel or Google Sheets) to filter further.

3. Extracting Vendor Information

Once you've isolated the specific line items from the campaign's orders that match your manually price-dropped products, getting the vendor information is straightforward.

Each product in Shopify has a "Vendor" field. When you export order line items, the product vendor is usually included in the export.
In your spreadsheet, you can then group or pivot your data by this "Vendor" column to see sales totals for each.

Unleashing the Power of ShopifyQL

Now, if you're comfortable with a bit of code, ShopifyQL is absolutely your best friend for these kinds of custom reports. It allows you to query your store's data directly, giving you the flexibility that jamarzy was looking for. While I can't write a perfect query without knowing your exact UTM structure or product IDs, I can provide a robust template and explain the logic.

The key to solving jamarzy's problem with ShopifyQL is to join the orders, line_items, and products tables. We'll filter by the campaign's timeframe and source, then by the specific products involved in the price drop, and finally group by vendor.

Here’s a conceptual ShopifyQL query that you can adapt. Remember to replace the placeholder values with your actual campaign details and product IDs.


-- Sales Report for Manual Price Drops by Vendor
-- Assuming your email campaign used specific UTM parameters and ran within a defined date range.
-- You will need to know the 'product_id' for each product that had a manual price drop.

SELECT
    product_vendor,
    SUM(line_item_quantity * line_item_price) AS total_sales_amount,
    SUM(line_item_quantity) AS total_items_sold
FROM
    orders
LEFT JOIN
    line_items ON orders.id = line_items.order_id
LEFT JOIN
    products ON line_items.product_id = products.id
WHERE
    orders.processed_at >= '2023-10-01 00:00:00' -- Start date of your campaign
    AND orders.processed_at <= '2023-10-15 23:59:59' -- End date of your campaign
    AND orders.source_name = 'email' -- Or 'Marketing Campaign Name', 'utm_source', etc.
    -- If you used a specific UTM campaign name, you might need to check 'orders.landing_page_url'
    -- for a LIKE '%utm_campaign=your_campaign_name%' clause.
    AND line_items.product_id IN (
        12345678901, -- Replace with actual Product ID 1 that had a price drop
        98765432109, -- Replace with actual Product ID 2
        55544433322  -- Replace with actual Product ID 3
        -- Add all other relevant product IDs here
    )
GROUP BY
    product_vendor
ORDER BY
    total_sales_amount DESC

Let's break down that query:

SELECT: We're asking for the product_vendor, the sum of sales amount (quantity * price for each line item), and the total number of items sold.
FROM orders LEFT JOIN line_items LEFT JOIN products: This is crucial. We start with orders, then link to the individual products within those orders (line items), and then link to the full product details to get the vendor.
WHERE orders.processed_at >= '...' AND orders.processed_at <= '...': This filters for the specific time frame your email campaign was active.
AND orders.source_name = 'email': This attempts to filter orders that originated from an email source. You might need to adjust this based on your actual data. Sometimes, this could be a specific app name (e.g., 'Klaviyo') or a more granular UTM parameter if you used landing_page_url.
AND line_items.product_id IN (...): This is the most critical part for manual price drops. Since ShopifyQL can't directly filter by "was this product's price lower than usual?", you explicitly tell it which products were part of your price drop event. You'll need to manually gather these product_ids.
GROUP BY product_vendor: This aggregates your results, giving you a separate row for each vendor.

Future-Proofing Your Sales Tracking

For future campaigns involving price drops, here are a few expert tips to make reporting a breeze:

Use Shopify Discount Codes: Even if it's a "general" price drop, consider creating a 0% off discount code that applies to a specific collection of sale items. While it doesn't change the base price, it tags the order with a discount, making it trackable.
Dedicated Sale Collections: Create a temporary collection for your sale items. This makes filtering in reports much easier, as you can filter by sales of products within that collection.
Robust UTM Tagging: Always, always use consistent and specific UTM parameters for all your marketing links. This allows you to filter orders by source, medium, and campaign with high precision.
Note the Product IDs: Before a manual price drop campaign, make a clear list of all product IDs involved. This is essential for ShopifyQL queries like the one above.

While tracking manual price drops by vendor retrospectively can be a bit of a manual puzzle, especially without direct discount codes, it's definitely achievable with a combination of intelligent filtering and the power of ShopifyQL. It's all about understanding how your data is structured and knowing which levers to pull. Don't be afraid to dive into those reports or experiment with ShopifyQL – the insights you gain are invaluable for optimizing your future campaigns!

Mastering Shopify Reports: Uncovering Sales from Manual Price Drops, Grouped by Vendor