Products

Custom Gift Card Report

Sometimes store associates place a custom gift card order that is not mapped to the parent product in Shopify and NetSuite. In such cases, retailers face synchronization issues between Shopify and NetSuite due to unmapped gift cards. The custom gift card report serves as a solution to address these synchronization issues. This report shows unmapped products, enabling retailers to manually map these products to NetSuite, inventory them, and associate them with NetSuite IDs.

Glossary

SELECT `ORDER_ID` AS `ORDER_ID`,
       `ORDER_NAME` AS `ORDER_NAME`,
       `PRODUCT_ID` AS `PRODUCT_ID`,
       `PRODUCT_TYPE_ID` AS `PRODUCT_TYPE_ID`,
       `CREATED_DATE` AS `CREATED_DATE`,
       `PRODUCT_NAME` AS `PRODUCT_NAME`
FROM
  (SELECT OH.ORDER_ID,
          OH.ORDER_NAME,
          P.PRODUCT_ID,
          P.PRODUCT_TYPE_ID,
          P.PRODUCT_NAME,
          P.CREATED_DATE
   FROM ORDER_HEADER OH
   JOIN ORDER_ITEM OI ON OH.ORDER_ID = OI.ORDER_ID
   JOIN PRODUCT P ON OI.PRODUCT_ID = P.PRODUCT_ID
   WHERE P.PRODUCT_TYPE_ID = 'DIGITAL_GOOD'
     AND P.PRODUCT_ID NOT IN
       (SELECT pa.product_id_to
        FROM product_assoc pa
        WHERE pa.product_id = "53051")) AS virtual_table
WHERE `CREATED_DATE` >= STR_TO_DATE('2024-05-03 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `CREATED_DATE` < STR_TO_DATE('2024-05-10 00:00:00.000000', '%Y-%m-%d %H:%i:%s.%f')
  AND `PRODUCT_NAME` != 'Test'
LIMIT 1000;

Query Logic

Data Selection: The query selects data from the ORDER_HEADER, ORDER_ITEM, and PRODUCT tables. These tables contain information about orders, order items, and products, respectively. By joining these tables and selecting specific fields, the query retrieves necessary details related to custom gift card orders.

Defining Criteria: The query establishes specific criteria to identify custom gift card orders. It filters products based on their product type, ensuring only digital goods are considered. Then, it excludes products associated with a particular ID that is used for gift cards. This criterion ensures that the report focuses exclusively on digital goods matching the characteristics of custom gift cards which are not linked with the ID of the gift cards.

Selecting Necessary Information: Essential information such as order IDs, order names, product IDs, product type IDs, product names, and creation dates is retrieved. This selection provides comprehensive insights into custom gift card orders, enabling retailers to track and analyze their performance over time.