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.).

 

submit.jpg

 

<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 120.000 and max id is 1.520.000 and we have 400k of records in total, divide 1.4kk by 8; in this example, you will need to run the query for the range of 175k member IDs in each query, out of which only 50k would be for the selected brand.
  6. Modify the following query to:
    • Include the fields requested by the brand
    • Specify the min member id from step 4 and the member id calculated from the previous step to retrieve approximately 50k of records in a single query.

      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>'
      between <min member id> and <the next member id to retrieve ~50k records>
  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>

Comments

0 comments

Article is closed for comments.