Exporting Customer Information

Overview

You may need to export data about all your customers into a spreadsheet for marketing or other purposes.

Solution

Submit a request to Symphony Support to export data for all your consumers, describing the information (fields) you want to retrieve for each customer (e.g., email, name, creation date, phone, etc.).

<supportagent>

Follow the steps below to extract all customer records for the brand. This approach may be used to extract a large amount of data from other tables.
Note: If you need to also retrieve all orders, refer to the article Exporting Orders Data.

1. Connect to the database.

2. Run the following query to check available field names that were requested by the brand:
   

   select * from Member where site = '<brand>' LIMIT 0, 10

3. Run the following query to get the number of records for the brand:

   select count(*) from Member where site = '<brand>'

4. Find the min and max ids for created members.
   

   select max(id),min(id) from Member where site = '<brand>'

5. Divide the ids to retrieve the data in several queries - exporting ~50k rows each time. 
   For example, if min id is 120000 and max id is 1520000, there's a total of (1520000 - 120000), or 1400000 rows for the brand which is greater than 50k so we will range the queries each time.
6. Modify the following query to:
   • Include the fields requested by the brand.
   • Start with the lower bound of id being the min member id from step 4 and adding 50k to it to get the upper bound for the query. In the subsequent query, the upper bound of the previous query becomes the lower bound and add 50k to it to get the upper bound. Repeat this step until all the required records are exported.
     Note: If you retrieve all records at once, it may take a lot of time to fetch the data and save it in a CSV file (e.g. 30 minutes to export 300k rows).
     

     select id,email,firstName,lastName
     from Member
     where site = '<brand>' and
     id between <starting id> and <starting id + 50000>

7. Run the query from step 5 several times changing the member ids to retrieve all the data for the brand and export the data into CSV each time.
8. Merge the data into a single CSV file and archive it.
9. Send the archive to the brand via SFTP.

</supportagent>