For me it’s very interesting to see, which are the top-performing articles, to recognize, which content my readers want. Because more and more people subscribe to my newsletters over the time, I need to compare the number of clicks on an article to the numbers of emails sent.

The following snippet works perfectly for me to get a good understanding about the content, however the report is pretty ugly. So, use the code as a base for your needs.

<?php
$servername = "yourIP";
$username = "yourDatabaseUser";
$password = "yourPassword";
$database = "yourDatabase";

$connection = new mysqli($servername, $username, $password, $database);

$sql = "SELECT email_id, COUNT(url) AS numberOfLinkClicks, url FROM wp_newsletter_stats w WHERE url <>'' GROUP BY url";
$result_article_counts = $connection->query($sql);

while($rowOuter = $result_article_counts->fetch_assoc()) {
	$sql = "SELECT id, sent FROM wp_newsletter_emails WHERE id = " . $rowOuter['email_id'];
	$result_newsletter_counts = $connection->query($sql);
	while($rowInner = $result_newsletter_counts->fetch_assoc()) {
		$numberOfSentEmails = $rowInner['sent'];
	}
	$link = $rowOuter['url'];
	$numberOfLinkClicks = $rowOuter['numberOfLinkClicks'];
	$click_rate = $numberOfLinkClicks / $numberOfSentEmails;
	if ($click_rate > 0.15) {
		$bold = "<b>"; 
	$bold2 = "</b>";
	} else {
		$bold =""; 
	$bold2 = "";
	}
	echo "Link: " . $link . " -> Clickrate: " . $bold . $click_rate . $bold2 . "<br>";
}
	
$connection->close();
?>