function create_table($period_start, $period_end)
{
global $wpdb;
$sendgrid_sga_table = sendgrid_sga_get_table();
$create_table = $wpdb->query($wpdb->prepare("CREATE TEMPORARY TABLE `post_stats` AS (\n\t\t\t\t\tSELECT *\n\t\t\t\t\tFROM `{$wpdb->posts}` AS `posts`\n\t\t\t\t\tINNER JOIN `{$sendgrid_sga_table}` AS `stats`\n\t\t\t\t\tON `posts`.`ID` = `stats`.`post_id`\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t`stats`.`post_id` IS NOT NULL AND\n\t\t\t\t\t\t`post_date` >= %s AND\n\t\t\t\t\t\t`post_date` <= %s\n\t\t\t\t)", $period_start, $period_end));
return $create_table;
}
function sendgrid_sga_analyzeposts($posts = false)
{
global $wpdb, $debug_page;
$sendgrid_sga_table = sendgrid_sga_get_table();
$sendgrid_sga_client = sendgrid_sga_get_analytics_client();
$access_token = get_option("sendgrid_sga_accesstoken");
$profile_id = get_option("sendgrid_sga_profile");
if (!($access_token && $profile_id)) {
return false;
}
if (!$sendgrid_sga_client) {
return false;
}
$sendgrid_sga_client->setAccessToken($access_token);
$analytics = new Google_AnalyticsService($sendgrid_sga_client);
if (!$posts) {
// Find all posts that have not been observed enough
$posts = $wpdb->get_col($wpdb->prepare("SELECT `posts`.`ID`\n\t\t\t\t\tFROM `{$wpdb->posts}` AS `posts`\n\t\t\t\t\tLEFT JOIN (\n\t\t\t\t\t\tSELECT `post_id`\n\t\t\t\t\t\tFROM `{$sendgrid_sga_table}`\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t`done` = 1\n\t\t\t\t\t) as `stats`\n\t\t\t\t\tON `posts`.`ID` = `stats`.`post_id`\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tDATE(`posts`.`post_date`) <= %s AND\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\t`stats`.`post_id` IS NULL OR\n\t\t\t\t\t\t\t`stats`.`post_id` = 0\n\t\t\t\t\t\t) AND\n\t\t\t\t\t\t`post_status` = 'publish' AND\n\t\t\t\t\t\t`post_type` = 'post'\n\t\t\t\t\tORDER BY `posts`.`post_date` DESC", date("Y-m-d", strtotime("-8days"))));
}
if ($posts) {
$site_url = get_site_url();
$prepend_url = get_option("sendgrid_sga_prepend_url");
foreach ($posts as $post_id) {
$post = get_post($post_id);
// Generate the post URL in the way Google Analytics will understand.
$post_url = get_permalink($post_id);
if (strpos($post_url, $site_url) === 0) {
$post_path = $prepend_url . substr($post_url, strlen($site_url));
}
if (SENDGRID_SGA_DEBUG_PAGE) {
echo $post_path . "\r\n";
}
// Determine the dates to observe
$post_publishing_date = substr($post->post_date_gmt, 0, 10);
$post_observation_end = strtotime("+6days", strtotime($post->post_date_gmt));
$final_observation = time() > $post_observation_end;
$last_observation_time = !$final_observation ? time() : $post_observation_end;
$last_observation_date = date("Y-m-d", $last_observation_time);
if ($post_publishing_date == '0000-00-00') {
continue;
}
try {
// Get GA Data for a post
$response = $analytics->data_ga->get("ga:" . $profile_id, $post_publishing_date, $last_observation_date, "ga:visitors,ga:pageviews,ga:avgTimeOnPage,ga:entrances,ga:exits", array("dimensions" => "ga:pagePath", "filters" => "ga:pagePath==" . $post_path));
$ga_values = $response['totalsForAllResults'];
} catch (Google_ServiceException $e) {
}
if ($ga_values) {
// Insert the data into WordPress
$wpdb->replace($sendgrid_sga_table, array("post_id" => $post_id, "visits" => $ga_values['ga:visitors'], "pageviews" => $ga_values['ga:pageviews'], "avg_time_on_page" => $ga_values['ga:avgTimeOnPage'], "entrances" => $ga_values['ga:entrances'], "exits" => $ga_values['ga:exits'], "done" => (int) $final_observation), array('%d', '%d', '%d', '%d', '%d', '%d', '%d'));
}
}
}
}
