Report to see how many people who used a certain discount code were new customers

Shopify Discussion

Topic summary

A user seeks to determine how many first-time customers used a specific discount code out of 70 total users, utilizing Shopify’s new SQL-based Explore reporting feature.

Initial Challenge:

  • Unable to filter by discount code name in the SQL query
  • Consistently encountering a “did you mean ‘SINCE’” error when attempting custom SQL queries

Proposed Solutions:

  • First response provided a standard SQL query using JOIN operations to identify first-time purchasers by matching order dates with customers’ earliest purchase dates
  • After error feedback with screenshot, the solution was revised to use ShopifyQL syntax instead of standard SQL

Final Working Approach:
Uses ShopifyQL-specific syntax with:

  • FILTER clause for discount code filtering (instead of WHERE)
  • SINCE and UNTIL for date ranges
  • COUNT(DISTINCT customer_id) to identify unique first-time purchasers

Status: The discussion appears resolved with a ShopifyQL-compatible query, though final confirmation of success is pending.

Summarized with AI on November 2. AI used: claude-sonnet-4-5-20250929.
1

Hi,

Im trying the new explore report using SQL to run the query but im unable to filter by discount code / code name. Im wondering if theres a way i can see of the 70 who used the code how many were new / first time purchasers

2

Hey @aliciat134 ,

You can filter by discount code and identify first-time purchasers with a query like this:

SELECT 
    COUNT(DISTINCT o.customer_id) AS first_time_purchasers
FROM 
    orders o
JOIN 
    discount_codes d ON o.discount_code_id = d.discount_code_id
LEFT JOIN 
    (SELECT customer_id, MIN(order_date) AS first_order_date
     FROM orders
     GROUP BY customer_id) first_time_orders ON o.customer_id = first_time_orders.customer_id
WHERE 
    d.code_name = 'YOUR_DISCOUNT_CODE' -- Replace with your discount code
    AND o.order_date = first_time_orders.first_order_date;

This counts distinct customers who used the discount code and made their first purchase.

If I was able to help you, please don’t forget to Like and mark it as the Solution!
If you’re looking for expert help with customization or coding, I’d be delighted to support you. Please don’t hesitate to reach out via the email in my signature below—I’m here to help bring your vision to life!

Best Regard,
Rajat

3

Hi, Thank you for the fast response, im getting the error ‘did you mean ‘SINCE’’ for the first line, i’ve been recieving this error every time i try do custom SQL on shopify. Do you have a solution please?

Thanks!

1 Like
4

@aliciat134 ,

Thank you for your question! I’ve adjusted the SQL query to better suit Shopify’s reporting tool. Feel free to reach out via email, and we can discuss the details further. With over 8 years of experience, I’m confident I can assist you in optimizing your reports and addressing any challenges you may face.

1 Like
5

Hi, Thank you but still the same issue, ive provided a screenshot of the issue

Screenshot (8).png
Screenshot (8).png1629×537 47.2 KB

Thanks!

1 Like
6

@aliciat134 ,

Thank you for sharing the details! To filter for first-time purchasers using a specific discount code in ShopifyQL, you can use the following query:

SELECT
  COUNT(DISTINCT customer_id) AS first_time_purchasers
FROM orders
FILTER discount_code = 'Blade-6-Event'
SINCE '2024-12-10'
UNTIL '2025-01-09'

This will count unique customers using the code in the specified date range. Let me know if you need any more adjustments!