Conversation Analytics Dashboard

Course 11’s session transcripts live in transients with a 20-minute expiry, exactly right for conversational memory and exactly wrong for anything a site owner would want to review a week later. A production chatbot needs a permanent record, even a thin one, of how much it’s actually being used, what it’s being asked about, and how often a human had to step in. This lesson adds a small, permanent log table and a genuinely useful wp-admin page over it.

Step 1: a permanent log table

// File: my-plugin/class-chat-log.php
function my_plugin_create_chat_log_table() {
	global $wpdb;

	$table_name      = $wpdb->prefix . 'ai_chat_log';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE {$table_name} (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		session_id VARCHAR(64) NOT NULL,
		role VARCHAR(20) NOT NULL,
		cited_post_id BIGINT UNSIGNED NULL,
		handed_to_human TINYINT(1) NOT NULL DEFAULT 0,
		created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY  (id),
		KEY session_id (session_id),
		KEY created_at (created_at)
	) {$charset_collate};";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );
}
register_activation_hook( __FILE__, 'my_plugin_create_chat_log_table' );

Deliberately no chunk_text-equivalent column here: this table exists to answer “how much” and “about what,” not to store full conversation content a second time, that’s still the session transient’s job while a conversation is live. Keeping this table thin also sidesteps a chunk of the GDPR data-minimization discussion Lesson 9 covers in full.

Step 2: log from the existing callback, one call added

The chat callback (Lesson 4) already knows the session ID, the citations (Lesson 3), and whether control just moved to human. Logging is one function call added where that information already exists, not a new code path:

// File: my-plugin/class-chat-log.php
function my_plugin_log_chat_event( string $session_id, string $role, array $citations = array(), bool $handed_to_human = false ) {
	global $wpdb;

	$cited_post_id = null;
	if ( ! empty( $citations[0] ) ) {
		$cited_post_id = url_to_postid( $citations[0] );
	}

	$wpdb->insert(
		$wpdb->prefix . 'ai_chat_log',
		array(
			'session_id'      => $session_id,
			'role'            => $role,
			'cited_post_id'   => $cited_post_id ?: null,
			'handed_to_human' => $handed_to_human ? 1 : 0,
		),
		array( '%s', '%s', '%d', '%d' )
	);
}

url_to_postid() turns the first verified citation URL from Lesson 3 back into a post ID, cheaply, so this table can answer “what’s actually getting cited” without storing full URLs or chunk text per row.

Step 3: aggregating real numbers

// File: my-plugin/class-chat-log.php
function my_plugin_get_chat_stats( int $days = 30 ): array {
	global $wpdb;
	$table = $wpdb->prefix . 'ai_chat_log';
	$since = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );

	$total_sessions = (int) $wpdb->get_var( $wpdb->prepare(
		"SELECT COUNT(DISTINCT session_id) FROM {$table} WHERE created_at >= %s", $since
	) );

	$total_messages = (int) $wpdb->get_var( $wpdb->prepare(
		"SELECT COUNT(*) FROM {$table} WHERE created_at >= %s", $since
	) );

	$handover_sessions = (int) $wpdb->get_var( $wpdb->prepare(
		"SELECT COUNT(DISTINCT session_id) FROM {$table} WHERE handed_to_human = 1 AND created_at >= %s", $since
	) );

	$top_cited = $wpdb->get_results( $wpdb->prepare(
		"SELECT cited_post_id, COUNT(*) as hits FROM {$table}
		 WHERE cited_post_id IS NOT NULL AND created_at >= %s
		 GROUP BY cited_post_id ORDER BY hits DESC LIMIT 5", $since
	) );

	return array(
		'total_sessions'   => $total_sessions,
		'total_messages'   => $total_messages,
		'handover_rate'    => $total_sessions ? round( $handover_sessions / $total_sessions * 100, 1 ) : 0.0,
		'top_cited'        => $top_cited,
	);
}

top_cited, grouped by cited_post_id, is a genuinely honest stand-in for “common topics”: it’s not topic modeling, it’s simply which pages and products the chatbot ends up citing most often, which in practice tells a site owner exactly what visitors keep asking about.

Step 4: a wp-admin page over it

// File: my-plugin/class-chat-log.php
add_action( 'admin_menu', function () {
	add_menu_page(
		'Chatbot Analytics',
		'Chatbot Analytics',
		'manage_options',
		'my-plugin-chat-analytics',
		'my_plugin_render_analytics_page',
		'dashicons-format-chat'
	);
} );

function my_plugin_render_analytics_page() {
	$stats = my_plugin_get_chat_stats( 30 );
	?>
	<div class="wrap">
		<h1>Chatbot Analytics, last 30 days</h1>
		<p><strong>Conversations:</strong> <?php echo esc_html( $stats['total_sessions'] ); ?></p>
		<p><strong>Messages:</strong> <?php echo esc_html( $stats['total_messages'] ); ?></p>
		<p><strong>Human handover rate:</strong> <?php echo esc_html( $stats['handover_rate'] ); ?>%</p>

		<h2>Most-cited content</h2>
		<ol>
			<?php foreach ( $stats['top_cited'] as $row ) : ?>
				<li>
					<a href="<?php echo esc_url( get_edit_post_link( $row->cited_post_id ) ); ?>">
						<?php echo esc_html( get_the_title( $row->cited_post_id ) ); ?>
					</a>
					, cited <?php echo esc_html( $row->hits ); ?> times
				</li>
			<?php endforeach; ?>
		</ol>
	</div>
	<?php
}

Test it: generate some traffic and confirm the numbers move

wp-env run cli wp eval '
my_plugin_log_chat_event( "test-session-1", "visitor" );
my_plugin_log_chat_event( "test-session-1", "assistant", array( get_permalink( 1 ) ) );
my_plugin_log_chat_event( "test-session-2", "visitor", array(), true );

print_r( my_plugin_get_chat_stats( 30 ) );
'

You should see total_sessions at 2, a non-zero handover_rate, and the post behind get_permalink( 1 ) appearing in top_cited. Then visit the Chatbot Analytics page in wp-admin and confirm the same numbers render there.

Recap

A thin, permanent log table, separate from the session transient’s 20-minute memory, gives a site owner something Course 11 never provided: a real, lasting record of chatbot usage. One logging call added to the existing chat callback captures volume, which content actually gets cited, and how often a human had to take over. A simple add_menu_page() dashboard turns that log into numbers a site owner can actually act on, most usefully, treating a rising handover rate as a signal to index more content, not just a staffing metric.

Resources & further reading

• add_menu_page() function reference, developer.wordpress.org
• dbDelta() function reference, developer.wordpress.org
• Building the Live Chat Takeover, Lesson 4