Skip to content

Instantly share code, notes, and snippets.

@rsisco
Created October 24, 2018 20:52
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save rsisco/99c00035d16476ce20043a8313d621c8 to your computer and use it in GitHub Desktop.
Save rsisco/99c00035d16476ce20043a8313d621c8 to your computer and use it in GitHub Desktop.
Create `google_analytics_export` and `m1_url_export` tables
###################################################################
# Table creation SQL
CREATE TABLE `google_analytics_export` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`page` varchar(255) DEFAULT NULL,
`pageviews` int(11) DEFAULT NULL,
`store_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18180 DEFAULT CHARSET=latin1;
CREATE TABLE `m1_url_export` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`request_path` varchar(500) DEFAULT '',
`url` varchar(500) DEFAULT NULL,
`target_url` varchar(500) DEFAULT NULL,
`url_type` int(3) DEFAULT NULL,
`description` longtext,
`category_id` int(11) DEFAULT NULL,
`product_id` int(11) DEFAULT NULL,
`sku` varchar(255) DEFAULT NULL,
`store_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56610 DEFAULT CHARSET=latin1;
###################################################################
@rsisco
Copy link
Author

rsisco commented Oct 24, 2018

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment