Compare M1 URL rewrites to Google Analytics data to find GA requests that are missing from rewrites

compare_m1_urls_to_ga_urls.sql

# This script assumes that a table has been created (m1_url_export) containing the output generated by https://gist.github.com/rsisco/a14b3383b2ab9f6435139d107e428a43
# as well as a table (google_analytics_export) that contains page request paths and counts from Google Analytics:

# Select the path without the query string
SELECT SUBSTRING_INDEX(page, '?', 1) AS page, sum(pageviews) AS pageviews FROM google_analytics_export AS gae
LEFT JOIN m1_url_export AS m ON gae.page = m.url and gae.store_id = m.store_id
# Only include results with no M1 match
WHERE m.url IS NULL
# Exclude paths we don't need to know about to keep the list pared down
AND page NOT LIKE '/catalog/product/gallery/%'
AND page NOT LIKE '/checkout/%'
AND SUBSTRING_INDEX(page, '?', 1) NOT IN ('/', '')
# Provide desired store ID
AND m.store_id = ?
# Group by requested page (minus query string)
GROUP BY SUBSTRING_INDEX(page, '?', 1)
ORDER BY pageviews DESC;

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.