connecting wordpress to google analytics in MySQL

google-analytics-for-wordpress-sql-join.sql

SELECT 
  `wp_terms`.`slug` AS wordpress_category,
  COUNT(1) AS post_count,
  SUM(`google_stats`.`Visits`) AS total_visits,
  SUM(`google_stats`.`New Visits`) AS total_new_visits,
  ROUND((SUM(`google_stats`.`Visits`) / COUNT(1)), 1) AS visits_per_post


  FROM `wp_posts`
  INNER JOIN `wp_term_relationships`
    ON `wp_term_relationships`.`object_id` = `wp_posts`.`ID`
  INNER JOIN `wp_term_taxonomy`
    ON `wp_term_relationships`.`term_taxonomy_id` = `wp_term_taxonomy`.`term_taxonomy_id`

  INNER JOIN `wp_terms`
    ON `wp_term_taxonomy`.`term_id` = `wp_terms`.`term_id`
  INNER JOIN `google_stats`
    ON 
    `google_stats`.`Landing Page` =  CONCAT_WS('/' , '', 
                      YEAR(`post_date`), LPAD(MONTH(`post_date`), 2, 0), 
                      LPAD(DAY(`post_date`), 2, 0), `post_name`, '')   

/* limit by type and date of posts */

  WHERE `post_status` = 'publish'
    AND `post_type` = 'post'

	AND `wp_term_taxonomy`.`taxonomy` = 'category'
    AND `post_date` >= DATE('2014-01-01')
    

/***************/

  GROUP BY `wp_terms`.`slug`
  ORDER BY `total_visits` DESC