MySQL to retrieve a list of all M1 URL rewrites by store ID, but filter products to only include those that are enabled and visible

m1_url_rewrites.sql

# Enabled product URLs for provided store ID
SELECT
	r.store_id,
	request_path, concat('/', request_path) AS url,
	concat('/', target_path) AS target_url,
	CASE 
		WHEN r.options IS NULL THEN 'internal'
		WHEN r.options = 'RP' THEN '301'
		WHEN r.options = 'R' THEN '302'
	END AS url_type,
	description,
	category_id,
	product_id,
	p.sku
FROM core_url_rewrite AS r
LEFT JOIN catalog_product_entity AS p ON p.entity_id = r.product_id
INNER JOIN eav_attribute AS attr_status_id ON attr_status_id.attribute_code = 'status' AND attr_status_id.entity_type_id = 4
INNER JOIN eav_attribute AS attr_vis_id ON attr_vis_id.attribute_code = 'visibility' AND attr_vis_id.entity_type_id = 4
INNER JOIN catalog_product_entity_int AS attr_status ON attr_status.attribute_id = attr_status_id.attribute_id AND attr_status.entity_id = p.entity_id AND attr_status.store_id = r.store_id
INNER JOIN catalog_product_entity_int AS attr_vis ON attr_vis.attribute_id = attr_vis_id.attribute_id AND attr_vis.entity_id = p.entity_id AND attr_vis.store_id = r.store_id
# Provide desired store ID
WHERE r.store_id = ?
AND attr_status.value = 1
AND attr_vis.value != 1

UNION

# Non-product URLs for provided store ID
SELECT
	r.store_id,
	request_path, concat('/', request_path) AS url,
	concat('/', target_path) AS target_url,
	CASE 
		WHEN r.options IS NULL THEN 'internal'
		WHEN r.options = 'RP' THEN '301'
		WHEN r.options = 'R' THEN '302'
	END AS url_type,
	description,
	category_id,
	product_id,
	NULL
FROM core_url_rewrite AS r
# Provide desired store ID
WHERE r.store_id = ?
AND r.product_id IS NULL;

USAGE:

1. Run ​this Gist on M1 prod for each store ID and export the results for each to their own CSV
2. Export Google Analytics data for domain to CSV
3. Run ​this Gist to create tables in a NON-PRODUCTION database
4. Import exported data into their respective tables
5. Run ​this Gist to generate a list of URLs that are in GA but don't have URL rewrites in M1, this is to make sure we are capturing all paths possible
6. Run ​this Gist to export the M1 URL rewrites for the purpose of mapping to new ones with.