Attempt #1 - Magento Migration to Shopify

We are going to use Shopify Plus' Transporter App.  It requires 3 CSV to be populated.

The order in which you import your product, customer, and historical order records is important. 

You need to import them in the following order:

1. Products
2. Customers
3. Orders

We attempted to use Cart2Cart but it loaded in too much bull crap so I'm going to document my attempt to do this purely in SQL.

Please note that our catalog contains simple, flat products without custom options, bundles, etc. 

MAGENTO PRODUCTS > SHOPIFY PRODUCTS

STEP 1: Create Export SQL Table

Create the `shopify_product` table using the products.csv sample file format.

CREATE TABLE `shopify_product` (
  `Handle` varchar(255) DEFAULT NULL,
  `Title` varchar(255) DEFAULT NULL,
  `Body (HTML)` varchar(255) DEFAULT NULL,
  `Vendor` varchar(255) DEFAULT NULL,
  `Type` varchar(255) DEFAULT NULL,
  `Tags` varchar(255) DEFAULT z,
  `Template Suffix` varchar(255) DEFAULT NULL,
  `Published Scope` varchar(255) DEFAULT NULL,
  `Published` varchar(255) DEFAULT NULL,
  `Published At` varchar(255) DEFAULT NULL,
  `Option1 Name` varchar(255) DEFAULT NULL,
  `Option1 Value` varchar(255) DEFAULT NULL,
  `Option2 Name` varchar(255) DEFAULT NULL,
  `Option2 Value` varchar(255) DEFAULT NULL,
  `Option3 Name` varchar(255) DEFAULT NULL,
  `Option3 Value` varchar(255) DEFAULT NULL,
  `Variant SKU` varchar(255) DEFAULT NULL,
  `Metafields Global Title Tag` varchar(255) DEFAULT NULL,
  `Metafields Global Description Tag` varchar(255) DEFAULT NULL,
  `Metafield Namespace` varchar(255) DEFAULT NULL,
  `Metafield Key` varchar(255) DEFAULT NULL,
  `Metafield Value` varchar(255) DEFAULT NULL,
  `Metafield Value Type` varchar(255) DEFAULT NULL,
  `Variant Grams` varchar(255) DEFAULT NULL,
  `Variant Inventory Tracker` varchar(255) DEFAULT NULL,
  `Variant Inventory Qty` varchar(255) DEFAULT NULL,
  `Variant Inventory Policy` varchar(255) DEFAULT NULL,
  `Variant Fulfillment Service` varchar(255) DEFAULT NULL,
  `Variant Price` varchar(255) DEFAULT NULL,
  `Variant Compare At Price` varchar(255) DEFAULT NULL,
  `Variant Requires Shipping` varchar(255) DEFAULT NULL,
  `Variant Taxable` varchar(255) DEFAULT NULL,
  `Variant Barcode` varchar(255) DEFAULT NULL,
  `Image Attachment` varchar(255) DEFAULT NULL,
  `Image Src` varchar(255) DEFAULT NULL,
  `Image Position` varchar(255) DEFAULT NULL,
  `Image Alt Text` varchar(255) DEFAULT NULL,
  `Variant Image` varchar(255) DEFAULT NULL,
  `Variant Weight Unit` varchar(255) DEFAULT NULL,
  `Variant Tax Code` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

STEP 2: FLATTEN MAGENTO PRODUCTS

This step reduces that amount of joins you'll need to produce SQL.

  1. Magento Admin > System > Configuration > Catalog > Enable 'Use Flat Product Catalog'
  2. Magento Admin > System > Index > Re-Index the 'Flat Product Catalog' index to generate an up to date product list in the catalog_product_flat_1 MySQL table.

STEP 3: RUN SQL FOR EXTRACT & TRANSFORM

This was trial and error as we had to do a couple small transformations.  We also had some custom attributes that we wanted to port over as Metafields.

The SQL we used is below:

/* 
	Shopify Product CSV Generator 
	
	Populates shopify_product in a couple passes.
	1. Create the Base Record
	3. Adds the Images
	2. Creates the Metafields for Ingredients, Quote, 

*/

TRUNCATE TABLE shopify_product;

-- Create Base Record
INSERT INTO shopify_product
(
	`Handle`, `Title`, 
	`Body (HTML)`, 
	`Vendor`, `Type`, `Tags`, 
	`Template Suffix`, 
	`Published Scope`, `Published`, `Published At`, 
	`Option1 Name`, `Option1 Value`, 
	`Variant SKU`, 
	`Variant Grams`, 
	`Variant Inventory Tracker`, `Variant Inventory Qty`, `Variant Inventory Policy`, 
	`Variant Fulfillment Service`, `Variant Price`, 
	`Variant Requires Shipping`, 
	`Variant Taxable`, 
	`Variant Weight Unit`
)
SELECT DISTINCT
	url_key as handle,
	REPLACE(name, '"', '\'') as title,
	REPLACE(at_description_default.value, '"', '\'') as body,
	'Maggie Louise Confections' as vendor,
	'Chocolate Gift Box' as `type`,
	'' as tags,
	'' as template_suffix,
	'web' as published_scope,
	'TRUE' as published,
	DATE_FORMAT(p.created_at, '%Y-%m-%d') as published_at,
	'Title' as option1_name,
	'Default Title' as option1_value,
	p.sku as variant_sku,
	453.592 * p.weight as variant_grams,
	'' as variant_inventory_tracker,
	1 as variant_inventory_qty,
	'deny' as variant_inventory_policy,
	'manual' as variant_fulfillment_service,
	p.price as variant_price,
	'TRUE' as variant_requires_shipping,
	'TRUE' as variant_taxable,
	'lb' as variant_weight_unit
FROM 
	catalog_product_flat_1 p
	INNER JOIN catalog_product_entity_text AS at_description_default ON 
		(
		at_description_default.entity_id = p.entity_id AND 
		at_description_default.store_id = 0  AND
		at_description_default.attribute_id = (
              SELECT attribute_id 
              FROM 
              	eav_attribute ea 
               	LEFT JOIN eav_entity_type et ON ea.entity_type_id = et.entity_type_id  
              WHERE 
			  		ea.attribute_code = 'description' AND 
					et.entity_type_code = 'catalog_product'
				)
		);

-- Create Images
INSERT INTO shopify_product
(
	`Handle`,
	`Image Src`,
	`Image Position`,
	`Image Alt Text`
)
SELECT DISTINCT
	url_key as handle,
	CONCAT('https://mlc.imgix.net/media/catalog/product', g.value) as image_url,
	gv.position as sort_order,
	gv.label as alt_tag
FROM 
	catalog_product_flat_1 p
	INNER JOIN catalog_product_entity_media_gallery g ON g.entity_id = p.entity_id
	INNER JOIN catalog_product_entity_media_gallery_value gv ON gv.value_id = g.value_id
WHERE
	gv.disabled = 0
ORDER BY
	url_key, gv.position;
	
-- Set the 1st Image to the Base Record
UPDATE shopify_product JOIN
	(
	SELECT
		p.`Handle`, p.`Image Src`, p.`Image Position`, p.`Image Alt Text`
	FROM 
		shopify_product p,
		(
			SELECT Handle, MIN(`Image Position`) AS MinPosition
			FROM shopify_product
			GROUP BY Handle
		) as m
	WHERE
		p.Handle = m.Handle AND
		p.`Image Position` = m.MinPosition
	) as min_p ON shopify_product.Handle = min_p.Handle
SET
	shopify_product.`Image Src` = min_p.`Image Src`,
	shopify_product.`Image Position` = min_p.`Image Position`,
	shopify_product.`Image Alt Text` = min_p.`Image Alt Text`
WHERE
	shopify_product.Title IS NOT NULL;

-- Delete the Duplicative Image Row
DELETE dup
FROM
	shopify_product AS dup,
	shopify_product AS p
WHERE
	p.Handle = dup.Handle AND
	dup.Title IS NULL AND
	p.Title IS NOT NULL AND
	p.`Image Src` = dup.`Image Src` AND
	p.`Image Position` = dup.`Image Position` AND
	p.`Image Alt Text` = dup.`Image Alt Text`;

/* catalog_product_entity_varchar for short text attributes */

INSERT INTO shopify_product
(
	`Handle`,
	`Metafield Namespace`,
	`Metafield Key`,
	`Metafield Value`,
	`Metafield Value Type`
)
SELECT DISTINCT
	url_key as handle,
	CASE
		WHEN e.attribute_id IN (188, 190) THEN 'google-shopping-feed'
		WHEN e.attribute_id = 199 THEN 'bi'
		ELSE 'details'
	END as metafield_namespace,
	CASE
		WHEN e.attribute_id = 193 THEN 'quote'
		WHEN e.attribute_id = 189 THEN 'ingredients'
		WHEN e.attribute_id = 201 THEN 'short-description'
		WHEN e.attribute_id = 199 THEN 'segmentation_tags'
		WHEN e.attribute_id = 188 THEN 'title'
		WHEN e.attribute_id = 190 THEN 'description'
	END as metafield_key,
	REPLACE(e.value, '"', '\'') as metafield_value,
	'string' as metafield_value_type
FROM 
	catalog_product_flat_1 p
	INNER JOIN catalog_product_entity_varchar e ON e.entity_id = p.entity_id AND e.entity_type_id = 4
WHERE
	e.attribute_id IN (189,193,188,189,190,201) AND
	e.value IS NOT NULL AND
	e.value != ''
ORDER BY
	url_key, e.attribute_id;

/* catalog_product_entity_text for long text attributes */
INSERT INTO shopify_product
(
	`Handle`,
	`Metafield Namespace`,
	`Metafield Key`,
	`Metafield Value`,
	`Metafield Value Type`
)
SELECT DISTINCT
	url_key as handle,
	CASE
		WHEN e.attribute_id IN (188, 190) THEN 'google-shopping-feed'
		WHEN e.attribute_id = 199 THEN 'bi'
		ELSE 'details'
	END as metafield_namespace,
	CASE
		WHEN e.attribute_id = 193 THEN 'quote'
		WHEN e.attribute_id = 189 THEN 'ingredients'
		WHEN e.attribute_id = 201 THEN 'short-description'
		WHEN e.attribute_id = 199 THEN 'segmentation_tags'
		WHEN e.attribute_id = 188 THEN 'title'
		WHEN e.attribute_id = 190 THEN 'description'
	END as metafield_key,
	REPLACE(e.value, '"', '\'') as metafield_value,
	'string' as metafield_value_type
FROM 
	catalog_product_flat_1 p
	INNER JOIN catalog_product_entity_text e ON e.entity_id = p.entity_id AND e.entity_type_id = 4
WHERE
	e.attribute_id IN (189,193,188,189,190,201) AND
	e.value IS NOT NULL AND
	e.value != ''
ORDER BY
	url_key, e.attribute_id;

/* Run through some Character Updating for Shopify */

-- Replace LF, CR
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(REPLACE(`Body (HTML)`, CHAR(13), ' '), CHAR(10), ' ');
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(REPLACE(`Metafield Value`, CHAR(13), ' '), CHAR(10), ' ');

-- FIRST, REPLACE UTF-8 characters.
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, 0xE28098, "'");
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, 0xE28099, "'");
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, 0xE2809C, '"');
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, 0xE2809D, '"');
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, 0xE28093, '-');
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, 0xE28094, '--');
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, 0xE280A6, '...');

-- NEXT, REPLACE their Windows-1252 equivalents.
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, CHAR(145), "'");
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, CHAR(146), "'");
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, CHAR(147), '"');
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, CHAR(148), '"');
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, CHAR(150), '-');
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, CHAR(151), '--');
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, CHAR(133), '...');

-- FIRST, REPLACE UTF-8 characters.
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, 0xE28098, "'");
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, 0xE28099, "'");
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, 0xE2809C, '"');
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, 0xE2809D, '"');
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, 0xE28093, '-');
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, 0xE28094, '--');
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, 0xE280A6, '...');

-- NEXT, REPLACE their Windows-1252 equivalents.
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, CHAR(145), "'");
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, CHAR(146), "'");
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, CHAR(147), '"');
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, CHAR(148), '"');
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, CHAR(150), '-');
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, CHAR(151), '--');
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, CHAR(133), '...');

-- FIRST, REPLACE UTF-8 characters.
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, 0xE28098, "'");
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, 0xE28099, "'");
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, 0xE2809C, '"');
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, 0xE2809D, '"');
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, 0xE28093, '-');
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, 0xE28094, '--');
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, 0xE280A6, '...');

-- NEXT, REPLACE their Windows-1252 equivalents.
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, CHAR(145), "'");
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, CHAR(146), "'");
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, CHAR(147), '"');
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, CHAR(148), '"');
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, CHAR(150), '-');
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, CHAR(151), '--');
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, CHAR(133), '...');

-- Clear out Quotes from Title
UPDATE `shopify_product` SET `Title` = REPLACE(`Title`, '"', "'");
UPDATE `shopify_product` SET `Body (HTML)` = REPLACE(`Body (HTML)`, '"', "'");
UPDATE `shopify_product` SET `Metafield Value` = REPLACE(`Metafield Value`, '"', "'");
UPDATE `shopify_product` SET `Image Alt Text` = REPLACE(`Image Alt Text`, '"', "'");

-- Clearing out Characters that are causing Errors
UPDATE shopify_product SET 
	`Body (HTML)` = REPLACE(CONVERT(`Body (HTML)` USING ASCII), '?', '')
WHERE
	NOT HEX(`Body (HTML)`) REGEXP '^([0-7][0-9A-F])*$';
	
UPDATE shopify_product SET 
	`Metafield Value` = REPLACE(CONVERT(`Metafield Value` USING ASCII), '?', '')
WHERE
	NOT HEX(`Metafield Value`) REGEXP '^([0-7][0-9A-F])*$';

STEP 4: EXPORT CSV

Export the shopify_product SQL table to CSV.  3 very important things to remember:

  1. Remember to convert NULL => '' (blank)
  2. Terminate your lines using \r\n
  3. Ensure that you are exporting the result set with the Handles grouped together:
    SELECT * FROM shopify_product ORDER BY Handle

MAGENTO "CUSTOMERS" > SHOPIFY CUSTOMERS

"Customers" is in quotes as we are actually taking anybody who purchased a product. Magento only creates customer records for those who want their username/password saved.  We want to migrate all user data.

STEP 1: Create Export SQL Table

Create the `shopify_customer` table from the customers.csv sample file.

CREATE TABLE `shopify_customer` (
  `First Name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Last Name` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Email` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Company` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Address1` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Address2` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `City` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Province` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Province Code` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Country` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Country Code` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Zip` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Phone` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Accepts Marketing` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Tags` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Note` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Tax Exempt` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Metafield Namespace` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Metafield Key` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Metafield Value` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `Metafield Value Type` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  KEY `Email` (`Email`,`Phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

STEP 2: RUN SQL FOR EXTRACT & TRANSFORM

This was trial and error as we had to do a couple small transformations.  Here are things to note:

  • Email Address AND Phone Number are unique to customer.  This caused some issues and with consultation with ShopifyPlus partners, we moved the duplicate phone number to the 'Note' field.  (First version tried to beat the duplicate nature by creating phone numbers like "(555) 555-5555 v2" but that failed the Phone Number Regexp Validation.
     
  • We have a denormalized table of the customer that gets updated nightly -- `bi_f_magento_customer`.  You can pull this information from customer_entity or sales_flat_order directly.

The SQL we used is below:

/* 
	Shopify Customer CSV Generator 
	
	Populates shopify_customer in a couple passes.
	1. Create the Base Record
	2. Updates the Customer Group as a Tag
	3. Adds the Addresses
	4. Concats the Notes

*/

-- Reset Table
TRUNCATE TABLE shopify_customer;

-- Create Base Record
-- Does not import records with email addresses like 1476728733@maggielouiseconfections.com
-- ie., MLC Orders, or incorrectly placed Wholesale Marketplace Orders
INSERT INTO shopify_customer
(
	`First Name`, 
	`Last Name`, 
	`Email`, 
	`Company`, 
	`Address1`, 
	`Address2`, 
	`City`, 
	`Province`, `Province Code`, 
	`Country`, `Country Code`, 
	`Zip`, 
	`Phone`, 
	`Accepts Marketing`, `Tags`, `Note`, 
	`Tax Exempt`
)
SELECT SQL_CACHE DISTINCT
	cust.first_name,
	cust.last_name,
	cust.email,
	cust.company,
	cust.street_address_1,
	cust.street_address_2,
	cust.city,
	cust.state, r.code,
	c.country_name, cust.country_code,
	cust.postal_code,
	cust.phone,
	CASE
		WHEN ns.subscriber_status = 1 THEN 'TRUE'
		ELSE 'FALSE'
	END AS accepts_marketing,
	rfm_segment AS tags,
	'' AS note,
	CASE
		WHEN cust.customer_group_code LIKE '%Non-Profit%' THEN 'TRUE'
		ELSE 'FALSE'
	END AS tax_exempt 
FROM 
	bi_f_magento_customer cust
	INNER JOIN directory_country_region r ON r.default_name = cust.state
	INNER JOIN sys_country c ON cust.country_code = c.country_code
	LEFT OUTER JOIN newsletter_subscriber ns ON ns.subscriber_email = cust.email;

-- Updates Customer Group Tag
UPDATE 
	shopify_customer sc 
	INNER JOIN bi_f_magento_customer cust ON sc.email = cust.email
SET
	sc.tags = CONCAT(CONCAT(sc.tags, ","), cust.customer_group_code);

-- Builds Additional Addresses
INSERT INTO shopify_customer
(
	`First Name`, 
	`Last Name`,
	`Email`, 
	`Company`, 
	`Address1`, 
	`Address2`, 
	`City`, 
	`Province`, `Province Code`, 
	`Country`, `Country Code`, 
	`Zip`, 
	`Phone`
)
SELECT
    cev5.value AS first_name,
    cev7.value AS last_name,
    ce.email,
    IFNULL(caev24.value, '') AS company,
    IFNULL(SUBSTRING_INDEX(caet.value, '\n', 1), '') as street_address_1,
    IFNULL(SUBSTRING_INDEX(SUBSTRING_INDEX(caet.value, '\n', 2), '\n', -1), '') as street_address_2,
    caev26.value AS city,
    caev28.value as state, r.code as state_code,
    country.country_name, caev27.value as country_code,
    caev30.value AS postcode,
    caev31.value as phone
FROM
    customer_entity ce
 	
 	-- join on shopify customer
    INNER JOIN shopify_customer c ON c.Email = ce.email
 	
    -- first name
    INNER JOIN
    customer_entity_varchar cev5 ON (ce.entity_id = cev5.entity_id
        AND cev5.attribute_id = 5)

    -- last name
    INNER JOIN
    customer_entity_varchar cev7 ON (ce.entity_id = cev7.entity_id
        AND cev7.attribute_id = 7)   

    -- address
    INNER JOIN
    customer_address_entity cae ON (ce.entity_id = cae.parent_id)
    INNER JOIN
    customer_address_entity_text caet ON (cae.entity_id = caet.entity_id)

    -- town
    INNER JOIN
    customer_address_entity_varchar caev26 ON (cae.entity_id = caev26.entity_id
    	AND caev26.attribute_id = 26)

    -- region / state
    INNER JOIN
    customer_address_entity_varchar caev28 ON (cae.entity_id = caev28.entity_id
	    AND caev28.attribute_id = 28)
	INNER JOIN directory_country_region r ON r.default_name = caev28.value

    -- postcode
    INNER JOIN
    customer_address_entity_varchar caev30 ON (cae.entity_id = caev30.entity_id
	    AND caev30.attribute_id = 30)
	   
    -- country
    INNER JOIN
    customer_address_entity_varchar caev27 ON (cae.entity_id = caev27.entity_id
	    AND caev27.attribute_id = 27) 	
    INNER JOIN sys_country country ON caev27.value = country.country_code
    
    -- phone
    INNER JOIN
    customer_address_entity_varchar caev31 ON (cae.entity_id = caev31.entity_id
    	AND caev31.attribute_id = 31)
    	
    -- company
    INNER JOIN
    customer_address_entity_varchar caev24 ON (cae.entity_id = caev24.entity_id
    	AND caev24.attribute_id = 24);

UPDATE shopify_customer SET
	Address2 = ''
WHERE
	Address1 != '' AND
	Address1 = Address1;

-- Clear Duplicate Addresses
DELETE dup
FROM
	shopify_customer AS dup,
	shopify_customer AS c
WHERE
	c.Email = dup.Email AND
	dup.`Accepts Marketing` IS NULL AND
	c.`Accepts Marketing` IS NOT NULL AND
	c.`Address1` = dup.`Address1` AND
	c.`City` = dup.`City` AND
	c.`Province` = dup.`Province`;

/* Normalize Phone Numbers */

## ##
DELIMITER ;;
DROP FUNCTION IF EXISTS `STRIP_NON_DIGIT`;;

CREATE FUNCTION `STRIP_NON_DIGIT`(input VARCHAR(255)) RETURNS VARCHAR(255) CHARSET utf8 
READS SQL DATA
BEGIN
	DECLARE output VARCHAR(255) DEFAULT '';
	DECLARE iterator INT DEFAULT 1;
	DECLARE lastDigit INT DEFAULT 1;
	DECLARE len INT;

	SET len = LENGTH(input) + 1;
	WHILE iterator < len DO
		-- skip past all digits
		SET lastDigit = iterator;
		
		WHILE ORD(SUBSTRING(input, iterator, 1)) BETWEEN 48 AND 57 AND iterator < len DO
			SET iterator = iterator + 1;
		END WHILE;

		IF iterator != lastDigit THEN
			SET output = CONCAT(output, SUBSTRING(input, lastDigit, iterator -lastDigit));
		END IF;

		WHILE ORD(SUBSTRING(input, iterator, 1)) NOT BETWEEN 48 AND 57 AND
			iterator < len DO
			SET iterator = iterator + 1;
		END WHILE;
	END WHILE;

	RETURN output;
END;;

DELIMITER ;
## ##

-- Strip Non Digits from US Numbers
UPDATE shopify_customer SET
	phone = STRIP_NON_DIGIT(phone)
WHERE
	LENGTH(STRIP_NON_DIGIT(phone)) = 10;
	
-- Remove MLC Phone Number
UPDATE shopify_customer SET
	phone = ''
WHERE
	phone IN ('8888862342', '5128400233');
	
-- Reformat Number
UPDATE shopify_customer SET 
	phone = CONCAT('(', SUBSTR(phone,1,3),') ', SUBSTR(phone,4,3), '-', SUBSTR(phone,7)) 
WHERE
	LENGTH(phone) = 10;	

-- Remove Function
DROP FUNCTION IF EXISTS `STRIP_NON_DIGIT`;

-- Deal with Duplicate Phone Numbers
UPDATE shopify_customer SET phone = '' WHERE phone in ('TBD', 'n/a', '(888) 888-8888', '(512) 200-3201');

-- Create PhoneCount Column
ALTER TABLE shopify_customer ADD PhoneCount INT(11) DEFAULT 0;

-- Populate the PhoneCount index
UPDATE shopify_customer cust,
	(
	SELECT 
	  c.Email, 
	  c.Phone,
	  @i:=IF(c.Phone=@phone, @i+1, 1) AS idx,
	  @phone:=c.Phone
	FROM 
	  shopify_customer c 
	  CROSS JOIN (SELECT @i:=0, @id:=0) AS init 
	WHERE 
		c.Phone != ''
	ORDER BY c.Phone
	) as i
SET cust.PhoneCount = i.idx
WHERE
	cust.Email = i.Email AND
	cust.Phone = i.Phone;

-- Preserve Uniqueness of Phone Number by Copying it to Note
UPDATE shopify_customer SET
	Note = Phone,
	Phone = ''
WHERE
	PhoneCount > 1 AND
	Phone != '';

-- Drop Phone Count Column
ALTER TABLE shopify_customer DROP COLUMN PhoneCount;

STEP 3: EXPORT CSV

Export the shopify_customer SQL table to CSV.  3 very important things to remember:

  1. Remember to convert NULL => '' (blank)
  2. Terminate your lines using \r\n
  3. Ensure that you are exporting the result set with the Email grouped together:
    SELECT * FROM shopify_customer ORDER BY Email

MAGENTO ORDERS > SHOPIFY ORDERS

STEP 1: Create Export SQL Table

Create the `shopify_order` table using the orders.csv sample file format.  Note that the field descriptions can be found in the Shopify "Export Orders to CSV" page.

““Why the Transporter Docs don’t reference their own internal docs is a f*Q@# mystery to me.””
— Me
Kevin Callahan