Checking Redemption Rate for Coupons

Overview

You may want to know how many coupons of specific value were used during a certain time period (e.g., promotion campaign).

Solution

  • If you created coupons that can be used multiple times, navigate to Discounts > Coupons - you can check how many times specific codes were used between the start and expiry dates.

    mceclip0.png

  • If you created a large number of unique codes, submit a request to Symphony Support, including the corresponding codes and required date range. 

<supportagent>

  1. Check the information for several codes to see if they have the same creation time (it is usually the same when coupons are created in bulk).

    select * from CouponCode where Code = '<coupon code>'
  2. Identify how many coupons were provided by the brand by checking the number of the last line in the CSV file provided by the brand.

  3. Use the following query to check how many coupons were created during the time (+-1 hour) identified in step 1; the number of coupons found should match or exceed the number provided by the requester. If the number is smaller, check creation time for other coupons from the provided list and run the query again.
    select count(*), site from CouponCode where id between (select min(id) 
    from CouponCode where created
    between '2020-07-08 00:00:00' and '2020-07-08 00:01:00' and site='<brand>')
    and (select max(id)
    from CouponCode where created between '2020-07-08 00:00:00' and '2020-07-08 00:01:00'
    and site='<brand>') group by site;
  4. Use the following query to find minimum and max IDs for coupons created during the selected time period.
    select max(id),min(id) from CouponCode 
    where created between '2020-07-08 00:00:00' and '2020-07-08 00:01:00'
    and site='<brand>';
  5. Find all orders where any of the found coupons were used.
    select s.id, s.created, s.email, c.code, s.price, s.couponValue, s.total, v.site 
    from SPOrder s, CouponCode c, Victory v
    where c.id=s.couponCode_id and v.id=s.victory_id and v.site='<brand>'
    and s.couponCode_id between <min coupon ID> and <max coupon ID> order by s.created;
  6. Save the data for the retrieved orders in a CSV file.
  7. Confirm that the coupons used in the retrieved orders are in the list provided by the requester. It can be done:
    • By adding all coupons provided by the customer into the CSV file with the retrieved orders and using VLOOKUP or INDEX and MATCH functions to find matching codes. Then, you need to remove all orders with codes that do not match and save the file.
    • Using a script (if the number of codes is huge):
      1. Copy coupon codes from the CSV file with the orders data (created in step 6) and save them in a text file (e.g., coupons_used.txt).
      2. Save all coupons provided by the brand in another text file (e.g., all_coupons.txt) in the same directory as the coupons_used.txt file.
      3. If you are using Windows or macOS, download and install Git Bash.
      4. Right-click in the folder where the coupon text files were saved and click Git Bash Here.

        mceclip0.png

      5. Run the following script to confirm that coupons provided by the brand were used for the retrieved orders:
        cat coupons_used.txt |  while read line; do  echo "$line -- `grep $line ./all_coupons.txt | wc -l`"  >> coupons_verified.txt; done
      6. Once the script is complete, check that all lines in the coupons_verified.txt file have '-- 1' at the end. If there are lines with '-- 0' at the end, remove the corresponding orders from the CSV file (created in step 6).

        mceclip1.png
  8. Inform the requester of the total number of orders that were placed using the reported codes and  attach the CSV file with the orders.

<supportagent>

Comments

0 comments

Article is closed for comments.