PHP Script to Parse Slack Messages and Tally Data from Bots

tally-email-subscribers-via-slack

<?php
/**
 * This script will parse our Slack history and pull email subscriber counts that were posted by our bots.
 * Place in the root of a WordPress install, add a Slack token for API access, and visit the URL
 *
 * A CSV file of the results will be downloaded.
 * For further analys import into a database (See Below)
 *
 */

/*

CREATE TABLE `email-subscribers` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `count` int(11) DEFAULT NULL,
  `username` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

# Billy Penn
SELECT count, date FROM `email-subscribers` WHERE `username` IN ('billypennbot', 'pedestalbot');

# The Incline
SELECT count, date FROM `email-subscribers` WHERE `username` IN ('theinclinebot')

 */

define( 'WP_DEBUG', false );
define('WP_USE_THEMES', false);
require('./wp-blog-header.php');

global $csv_out;
$csv_out = [];

function fetch_page( $page = 1 ) {
    global $csv_out;
    $page = intval( $page );
    $args = [
        'token' => '', // <---- Put your slack token here for API access, https://api.slack.com/docs/oauth-test-tokens
        'query' => '"email addresses subscribed"',
        'sort'  => 'date',
        'page'  => $page,
    ];
    $url = add_query_arg( $args, 'https://slack.com/api/search.messages' );
    $request = wp_remote_get( $url );
    $payload = json_decode( $request['body'] );
    $total_pages = $payload->messages->pagination->page_count;
    if ( $page > $total_pages ) {
        return;
    }
    $messages = $payload->messages;
    $matches = $messages->matches;

    foreach ( $matches as $message ) {
        preg_match( '/\d+/i', $message->text, $match );
        $num = $match[0];

        $date = $message->ts;
        $date = explode( '.', $date )[0];
        $date = date( 'Y-m-d H:i:s', intval( $date ) );

        $csv_out[] = [
            'date' => $date,
            'count' => intval( $num ),
            'username' => $message->username,
        ];
    }

    if ( $page < $total_pages ) {
        fetch_page( $page + 1 );
    }
}
fetch_page();

if ( empty( $csv_out ) ) {
    wp_die('Something went wrong! The CSV is empty!');
}

$name = 'spirited-media-email-subscribers';
$filename = sanitize_file_name( $name . '.csv' );
header_remove();
http_response_code(200);
header('Content-type: text/csv');
header("Content-Disposition: attachment;filename={$filename}");
$df = fopen( 'php://output', 'w' );
fputcsv( $df, array_keys( $csv_out[0] ) );
foreach ( $csv_out as $index => $row ) {
    fputcsv( $df, $row );
}
fclose( $df );
die();