get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE batch_id = %d AND contact_id = %d', $table, absint( $batch_id ), absint( $contact_id ) ) ); } /** * Record an email open. * * Inserts a new row or updates the existing one. * Sets opened_at only on the first open; always increments open_count. * * @param int $batch_id Batch ID. * @param int $contact_id Contact ID. * @return void */ public static function record_open( $batch_id, $contact_id ) { $now = current_time( 'mysql' ); Mailpuff_Schema::upsert( Mailpuff_Schema::TRACKING, array( 'batch_id' => absint( $batch_id ), 'contact_id' => absint( $contact_id ), 'opened_at' => $now, 'open_count' => 1, ), array( 'batch_id', 'contact_id' ), 'opened_at = COALESCE( opened_at, %s ), open_count = open_count + 1', array( $now ) ); } /** * Record a link click. * * Inserts a new row or updates the existing one. * Always increments click_count. * * @param int $batch_id Batch ID. * @param int $contact_id Contact ID. * @return void */ public static function record_click( $batch_id, $contact_id ) { $now = current_time( 'mysql' ); Mailpuff_Schema::upsert( Mailpuff_Schema::TRACKING, array( 'batch_id' => absint( $batch_id ), 'contact_id' => absint( $contact_id ), 'click_count' => 1, 'last_clicked_at' => $now, ), array( 'batch_id', 'contact_id' ), 'click_count = click_count + 1, last_clicked_at = %s', array( $now ) ); } /** * Signature for a click-tracking link. Binds (batch, contact-token, url) so the * public redirect can verify it generated the link and refuse to forward to an * arbitrary URL — closing the open-redirect hole that naive click tracking opens. * * @param int $batch_id Batch ID. * @param string $token Recipient unsubscribe token. * @param string $url Destination URL. * @return string 20-char hex signature. */ public static function click_sig( $batch_id, $token, $url ) { return substr( hash_hmac( 'sha256', absint( $batch_id ) . '|' . $token . '|' . $url, wp_salt( 'auth' ) ), 0, 20 ); } /** * Count one click on one link of a campaign, for its Links report: the * link gets a row once, and the person's clicks on it add up. * * @param int $batch_id Batch ID. * @param int $contact_id Contact ID. * @param string $url The destination. * @return void */ public static function record_link_click( $batch_id, $contact_id, $url ) { global $wpdb; $sends = Mailpuff_Schema::table( Mailpuff_Schema::CAMPAIGN_SENDS ); $links = Mailpuff_Schema::table( Mailpuff_Schema::LINKS ); $url = substr( (string) $url, 0, 2000 ); $hash = hash( 'sha256', $url ); $campaign_id = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT campaign_id FROM %i WHERE id = %d', $sends, absint( $batch_id ) ) ); if ( $campaign_id < 1 || '' === $url ) { return; } $link_id = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE campaign_id = %d AND url_hash = %s', $links, $campaign_id, $hash ) ); if ( ! $link_id ) { // Two clicks at once may both insert; the unique key keeps one row. $suppress = $wpdb->suppress_errors( true ); $wpdb->insert( $links, array( 'campaign_id' => $campaign_id, 'url_hash' => $hash, 'url' => $url, 'created_at' => current_time( 'mysql' ), ), array( '%d', '%s', '%s', '%s' ) ); $wpdb->suppress_errors( $suppress ); $link_id = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE campaign_id = %d AND url_hash = %s', $links, $campaign_id, $hash ) ); } if ( ! $link_id ) { return; } Mailpuff_Schema::upsert( Mailpuff_Schema::LINK_CLICKS, array( 'link_id' => $link_id, 'contact_id' => absint( $contact_id ), 'clicks' => 1, 'first_at' => current_time( 'mysql' ), ), array( 'link_id', 'contact_id' ), 'clicks = clicks + 1' ); } /** * A campaign's clicked links, most clicked first: how many people clicked * each and how many clicks it had. * * @param int $campaign_id Campaign ID. * @param int $limit How many links. * @return array[] Each { url, people, clicks }. */ public static function links( $campaign_id, $limit = 50 ) { global $wpdb; $links = Mailpuff_Schema::table( Mailpuff_Schema::LINKS ); $clicks = Mailpuff_Schema::table( Mailpuff_Schema::LINK_CLICKS ); $rows = (array) $wpdb->get_results( $wpdb->prepare( 'SELECT l.url, COUNT(lc.contact_id) AS people, COALESCE(SUM(lc.clicks), 0) AS clicks FROM %i l LEFT JOIN %i lc ON lc.link_id = l.id WHERE l.campaign_id = %d GROUP BY l.id, l.url ORDER BY clicks DESC, people DESC, l.id ASC LIMIT %d', $links, $clicks, absint( $campaign_id ), max( 1, (int) $limit ) ) ); return array_map( static function ( $row ) { return array( 'url' => (string) $row->url, 'people' => (int) $row->people, 'clicks' => (int) $row->clicks, ); }, $rows ); } /** * The token one recipient's tracking links carry in one batch: the * contact's ID and an HMAC of (batch, contact). Unlike the unsubscribe * token, it can't unsubscribe the person or open their preferences. * * @param int $batch_id Batch ID. * @param int $contact_id Contact ID. * @return string */ public static function tracking_token( $batch_id, $contact_id ) { return 't' . absint( $contact_id ) . 'x' . substr( hash_hmac( 'sha256', 'track|' . absint( $batch_id ) . '|' . absint( $contact_id ), wp_salt( 'auth' ) ), 0, 32 ); } /** * The contact a tracking link names, or 0 when it does not verify. Emails * sent before tracking tokens carried the unsubscribe token; those still * count. * * @param string $token The link's token. * @param int $batch_id Batch ID. * @return int Contact ID, or 0. */ public static function contact_for_token( $token, $batch_id ) { global $wpdb; $token = (string) $token; if ( preg_match( '/^t(\d+)x[a-f0-9]{32}$/', $token, $m ) ) { return hash_equals( self::tracking_token( $batch_id, (int) $m[1] ), $token ) ? (int) $m[1] : 0; } if ( '' === $token ) { return 0; } $table = Mailpuff_Schema::table( Mailpuff_Schema::SUBSCRIBERS ); return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE unsubscribe_token = %s', $table, $token ) ); } /** * Record an unsubscribe event. * * @param int $batch_id Batch ID. * @param int $contact_id Contact ID. * @return void */ public static function record_unsubscribe( $batch_id, $contact_id ) { $now = current_time( 'mysql' ); Mailpuff_Schema::upsert( Mailpuff_Schema::TRACKING, array( 'batch_id' => absint( $batch_id ), 'contact_id' => absint( $contact_id ), 'unsubscribed_at' => $now, ), array( 'batch_id', 'contact_id' ), 'unsubscribed_at = COALESCE( unsubscribed_at, %s )', array( $now ) ); } /** * Get aggregate stats for a campaign. * * Joins campaign_sends with tracking to produce totals. * * @param int $campaign_id Campaign ID. * @return object { total_sent, total_opened, total_clicked, total_unsubscribed, open_rate, click_rate } */ public static function get_campaign_stats( $campaign_id ) { global $wpdb; $sends_table = Mailpuff_Schema::table( Mailpuff_Schema::CAMPAIGN_SENDS ); $tracking_table = Mailpuff_Schema::table( Mailpuff_Schema::TRACKING ); $row = $wpdb->get_row( $wpdb->prepare( 'SELECT COUNT( t.id ) AS total_sent, SUM( CASE WHEN t.opened_at IS NOT NULL THEN 1 ELSE 0 END ) AS total_opened, SUM( CASE WHEN t.click_count > 0 THEN 1 ELSE 0 END ) AS total_clicked, SUM( CASE WHEN t.unsubscribed_at IS NOT NULL THEN 1 ELSE 0 END ) AS total_unsubscribed FROM %i b INNER JOIN %i t ON t.batch_id = b.id WHERE b.campaign_id = %d', $sends_table, $tracking_table, absint( $campaign_id ) ) ); if ( ! $row || 0 === (int) $row->total_sent ) { return (object) array( 'total_sent' => 0, 'total_opened' => 0, 'total_clicked' => 0, 'total_unsubscribed' => 0, 'open_rate' => 0.0, 'click_rate' => 0.0, ); } $total_sent = (int) $row->total_sent; return (object) array( 'total_sent' => $total_sent, 'total_opened' => (int) $row->total_opened, 'total_clicked' => (int) $row->total_clicked, 'total_unsubscribed' => (int) $row->total_unsubscribed, 'open_rate' => round( ( (int) $row->total_opened / $total_sent ) * 100, 1 ), 'click_rate' => round( ( (int) $row->total_clicked / $total_sent ) * 100, 1 ), ); } }