Exporting Orders Data

Overview

You may want to retrieve orders that were placed during a specific timeframe (e.g., to check if there were any issues) or all historical orders.

Solution

Submit a request to Symphony Support specifying the date and time for which you need to have the orders extracted and the required fields for each order.

<supportagent>

• Connect to the database and run the following query to retrieve the orders for the required date range, specifying the required brand, start and end date and time.
  
  

  select sp.id as OrderId, sp.created as CreatedAt, sp.email as CustomerEmail, sp.member_id 
  as CustomerId, fs.status as OrderStatus from SPOrder sp, Victory vc, FulfillmentShipment fs 
  where fs.orderId=sp.id and vc.id=sp.victory_id and vc.site='<brand>' 
  and sp.created between '2020-12-23 19:00:00' AND '2020-12-23 20:00:00' 
  order by sp.created, sp.id;

  
  If you need to include additional fields, run a simple SELECT query to check which data is available in the SPOrder, Victory, or FulfillmentShipment tables; for example:

  select * from SPOrder 
  where victory_Id in (select id from Victory where site = '<brand>')

• If you need to export all orders for a customer:
  1. Check how many orders are there in total for the brand:

     select count(id) from SPOrder where victory_Id in (select id from Victory where site = '<brand>')

  2. If the number of orders is less than 50k, you can export all orders at once:

     select sp.id as OrderId, sp.created as CreatedAt, sp.email as CustomerEmail, sp.member_id 
     as CustomerId, fs.status as OrderStatus from SPOrder sp, Victory vc, FulfillmentShipment fs 
     where fs.orderId=sp.id and vc.id=sp.victory_id and vc.site='<brand>' 
     order by sp.created, sp.id;

     
     If the number of orders exceeds 50k, export 50,000 rows at a time. For example:

     select sp.id as OrderId, sp.created as CreatedAt, sp.email as CustomerEmail, sp.member_id 
     as CustomerId, fs.status as OrderStatus from SPOrder sp, Victory vc, FulfillmentShipment fs 
     where fs.orderId=sp.id and vc.id=sp.victory_id and vc.site='<brand>' 
     order by sp.created, sp.id
     limit 0, 50000;

     Then, run the same query for the next 50k:

     <query>
     limit 50001, 100000;

     Note: If orders were deleted for an offboarded customer from the SPOrder table, you can export data from the Victory table selecting the fields requested by the customer. For example:

     select id, created, auctionId, price, discount, firstName, lastName, city, state, street1, street2, country, zip, shippingOption_id, company, phone, email 
     from Victory where site ='<brand>'

  3. Archive the exported data and send it to the customer in a secure way (e.g., via SFTP).

</supportagent>