Cracking Shopify Sales Reports: Tracking Manual Price Drops by Vendor with ShopifyQL

Hey everyone,

It’s a common scenario, isn’t it? You’ve put together a fantastic email campaign, dropped prices manually on a selection of products for a limited time, and now you want to see the exact impact. But then you hit the reporting wall. That’s precisely the challenge our community member, jamarzy, recently brought up in the forums, and it’s a pain point many of you might recognize.

Jamarzy was trying to run a report for a specific email campaign featuring products with a manual price decrease – not a Shopify discount code, but a direct change to the product's price for, say, two weeks. The goal? To report sales for each brand (or vendor) mentioned in that email. And the hiccup? Shopify’s standard reporting, and even the AI tools, struggled to pull this data, especially when trying to group it by vendor. Jamarzy even mentioned that the Campaigns reports weren't editable enough to get what was needed, leading to a direct ask for some ShopifyQL magic.

Why Manual Price Drops Are Tricky for Standard Reports

This isn't just a quirky issue; there's a good reason why Shopify's default reports get a bit flummoxed here. When you create a discount code in Shopify, that code is explicitly linked to orders. Shopify knows exactly which sales benefited from which discount. But a manual price change? That's just a change to the product's base price. From a reporting perspective, it looks like any other sale at a given price point. There's no inherent tag or flag saying, "Hey, this sale happened because of a temporary price drop linked to Email Campaign X."

This becomes even more complex when you want to segment by something like product vendor, as jamarzy needed. The standard "Sales by Vendor" report might give you overall sales, but it won't easily filter for only those sales that occurred during your specific email campaign and price drop period.

Enter ShopifyQL: Your Custom Reporting Hero

This is where ShopifyQL truly shines. It’s Shopify's custom query language, and it allows you to dig much deeper into your store's data than the pre-built reports. Think of it like speaking directly to your database, asking it very specific questions to get exactly the answers you need. For jamarzy's situation, ShopifyQL is the perfect tool to bridge the gap between a manual price change and a targeted sales report.

The key here is to identify the common denominators:

• The timeframe: When did the manual price drop and email campaign run?
• The products: Which specific products (and by extension, which vendors) were featured in the email and had their prices adjusted?

How to Build Your ShopifyQL Query for Manual Price Drops by Vendor

Let's craft a query that should get you the data you need. We'll focus on filtering by the campaign's date range and the specific products involved, then grouping by their vendor.

First, you'll need to compile a list of all the product titles that were part of your email campaign and had their prices manually reduced. You'll also need the exact start and end dates/times for your two-week price drop period.

Step-by-Step Instructions to Create Your Custom Report:

1. Navigate to Shopify Analytics: In your Shopify admin, go to Analytics > Reports.

2. Create a Custom Report: Scroll down to the bottom and click on Custom reports. Then, click the Create custom report button.

3. Select "Sales" as your Data Source: This is crucial as you're reporting on sales data.

4. Input Your ShopifyQL Query: In the text editor provided, paste and modify the following query. Remember to replace the placeholder values with your specific campaign details:

   SELECT
     product_vendor,
     sum(line_item_price) AS total_sales,
     count(distinct order_id) AS total_orders
   FROM
     sales
   WHERE
     processed_at >= '2023-10-01 00:00:00' -- Replace with your campaign start date and time
     AND processed_at <= '2023-10-15 23:59:59' -- Replace with your campaign end date and time
     AND product_title IN (
       'Product Title A',
       'Product Title B',
       'Product Title C' -- Add all specific product titles from your email campaign here
     )
   GROUP BY
     product_vendor
   ORDER BY
     total_sales DESC

   A quick breakdown of this query:

   • product_vendor: This is the dimension we want to group our sales by.
   • sum(line_item_price) AS total_sales: This calculates the total revenue from the line items. You could also use sum(net_sales) if you prefer to account for refunds, but line_item_price is usually sufficient for campaign tracking.
   • count(distinct order_id) AS total_orders: Gives you a count of unique orders that contained these products during the period.
   • FROM sales: Specifies that we are querying the sales data table.
   • WHERE processed_at >= '...' AND processed_at <= '...': This is your critical date filter. Make sure these dates accurately reflect your two-week campaign period.
   • AND product_title IN ('Product Title A', 'Product Title B', 'Product Title C'): This is how you target only the products that were part of your specific price drop. List all relevant product titles here. Ensure they exactly match your product titles in Shopify.
   • GROUP BY product_vendor: This is the magic line that aggregates your sales by each unique vendor associated with the selected products.
   • ORDER BY total_sales DESC: Sorts the results from highest to lowest sales, making it easy to see top performers.

5. Run and Save Your Report: Once you've entered and customized the query, click Run report. If everything looks good, you can then Save report and give it a descriptive name like "Email Campaign X - Manual Price Drop Sales by Vendor."

Pro Tips for Future Campaigns and Better Tracking

While ShopifyQL is fantastic for retrospective analysis, it also highlights areas where we can improve tracking moving forward. For future manual price drop campaigns, consider these strategies:

• Use Unique UTM Parameters: Always, always, always tag your email campaign links with unique UTM parameters (e.g., ?utm_source=email&utm_medium=campaign&utm_campaign=price_drop_oct_23). While ShopifyQL doesn't directly query UTMs, this data is invaluable in Google Analytics and other tools to cross-reference traffic and conversions with your Shopify sales data.

• Consider a "Fake" Discount Code: If strict "manual price drop" reporting is a recurring need, you *could* create a 100% off discount code and apply it to a single, zero-value "campaign tracking" product that customers don't see. When the campaign runs, you "sell" this tracking product to customers who convert from the email. This is a bit of a workaround, but it creates a traceable discount event. (Though for price drops, it's generally easier to stick to the ShopifyQL approach.)

• Tag Orders or Customers: If your email marketing platform integrates deeply with Shopify, you might be able to tag customers or orders that come through a specific campaign. This creates another filter point for future reports.

• Document Everything: Keep a detailed log of your campaigns, including start/end dates, specific products involved, and the exact price changes. This documentation is gold when you need to construct a ShopifyQL query later.

It’s clear that jamarzy hit on a common challenge for store owners who are trying to get granular with their sales data, especially when promotions don't perfectly align with Shopify's built-in discount structures. By leveraging ShopifyQL, you gain the power to pull exactly the insights you need, turning those reporting frustrations into actionable data. It's all about understanding what data points you *do* have – like dates and specific products – and using ShopifyQL to connect those dots. Happy reporting!