parse-raw-access-logs.php

<?php

if ( ! defined( 'ABSPATH' ) ) {
	/** Set up WordPress environment */
	require_once( dirname( __FILE__ ) . '/wp-load.php' );
}

/**
 * Here is some pseudo code that will hopefully help you.
 * Generate broadcast stats from Apache raw access logs...
 *
 * 1. Pull all opens for the broadcast from apache logs
 * 2. Parse each open and add it as an open in the Activity DB
 * 3. Pull all opens for the broadcast from apache logs
 * 4. Parse each click and add to activity DB
 * 5. Generate 'Sent' events in the event table
 */

use Groundhogg\Classes\Activity;

define( 'DATE_BROADCAST_SENT', 'date here' ); // Get from the broadcast table in Groundhogg
define( 'BROADCAST_ID', 1234 ); // Get from the broadcast table in Groundhogg

/**
 * Parse a tracking link...
 *
 * Example:
 *   0     1     2     3    4    5   6
 * /gh/tracking/email/open/15a/23b4/a1/
 * /gh/tracking/email/open/contact_id/event_id/email_id
 *
 * @param string $link
 *
 * @return array
 */
function parse_tracking_link( $link = '' ) {
	$parts = explode( '/', $link );

	$return = [
		'contact_id' => hexdec( $parts[4] ),
		'event_id'   => hexdec( $parts[5] ),
		'email_id'   => hexdec( $parts[6] ),
	];

	// If there is a url redirect
	if ( isset( $parts[7] ) ) {
		$return['target'] = base64_decode( $parts[7] );
	}

	return $return;
}

/**
 * Parse the raw access log item...
 *
 * @param $log_item
 *
 * @return array
 */
function parse_raw_access_log_item( $log_item ) {
	// Get the time
	preg_match( '/\[(.*)\]/', $log_item, $matches );
	$time = $matches[1];

	preg_match( '/"GET (.*) HTTP.*"/', $log_item, $matches );
	$url = $matches[1];

	return [
		'time'   => strtotime( $time ),
		'target' => $url
	];
}

$logs      = file_get_contents( 'path/to/log' );
$log_items = explode( PHP_EOL, $logs );

// Match opens...
$matches = preg_grep( '#.*gh/tracking/email/open.*#', $log_items );

foreach ( $matches as $match ) {
	$item     = parse_raw_access_log_item( $match );
	$activity = parse_tracking_link( $item['target'] );

	\Groundhogg\get_db( 'activity' )->add( [
		'contact_id'    => $activity['contact_id'],
		'funnel_id'     => \Groundhogg\Broadcast::FUNNEL_ID,
		'step_id'       => BROADCAST_ID,
		'email_id'      => $activity['email_id'],
		'activity_type' => Activity::EMAIL_OPENED,
		'event_id'      => $activity['event_id'],
	] );

}

// Match opens...
$matches = preg_grep( '#.*gh/tracking/email/clicked.*#', $log_items );

foreach ( $matches as $match ) {
	$item     = parse_raw_access_log_item( $match );
	$activity = parse_tracking_link( $item['target'] );

	\Groundhogg\get_db( 'activity' )->add( [
		'contact_id'    => $activity['contact_id'],
		'funnel_id'     => \Groundhogg\Broadcast::FUNNEL_ID,
		'step_id'       => BROADCAST_ID,
		'email_id'      => $activity['email_id'],
		'activity_type' => Activity::EMAIL_CLICKED,
		'event_id'      => $activity['event_id'],
		'referer'       => $activity['target'],
	] );

}

// Generate events
$matches = preg_grep( '#.*gh/tracking/.*#', $log_items );

foreach ( $matches as $match ) {
	$item     = parse_raw_access_log_item( $match );
	$activity = parse_tracking_link( $item['target'] );

	\Groundhogg\get_db( 'events' )->add( [
		'time'       => strtotime( DATE_BROADCAST_SENT ),
		'contact_id' => $activity['contact_id'],
		'funnel_id'  => 1,
		'step_id'    => BROADCAST_ID,
		'event_type' => \Groundhogg\Event::BROADCAST,
		'status'     => \Groundhogg\Event::COMPLETE
	] );

}