Subscriber engagement statistics now available

We’ve introduced a new feature in Mailcoach v8.8.0 that lets you track subscriber engagement in a privacy-friendly way. This update also allows you to segment your email lists based on engagement.

The feature is backwards compatibility in mind and only becomes active once the required migrations have been executed.

If you’re using our hosted Mailcoach platform, you’ll be happy to know this new feature is already available and ready to use.

You can enable this feature in your self-hosted installation by running the necessary migration:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
use Spatie\Mailcoach\Domain\Shared\Traits\UsesMailcoachModels;

return new class extends Migration
{
    use UsesMailcoachModels;

    public function up()
    {
        Schema::table(self::getSubscriberTableName(), function (Blueprint $table) {
            $table->after('extra_attributes', function (Blueprint $table) {
                $table->unsignedInteger('emails_received')->default(0);
                $table->unsignedInteger('emails_opened')->default(0);
                $table->unsignedInteger('emails_clicked')->default(0);
                $table->dateTime('last_open_at')->nullable();
                $table->dateTime('last_click_at')->nullable();
            });

            $table->index(['email_list_id', 'emails_received', 'emails_opened', 'emails_clicked'], 'engagement_index');
            $table->index(['email_list_id', 'last_open_at']);
            $table->index(['email_list_id', 'last_click_at']);
        });
    }
};

After this, the feature flag will pick up that the necessary columns are available and will start tracking engagement.

To backfill existing open & click data, you can create a command with the following code:

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Spatie\Mailcoach\Domain\Audience\Models\Subscriber;
use Spatie\Mailcoach\Domain\Shared\Traits\UsesMailcoachModels;

class BackfillSubscriberEngagement extends Command
{
    use UsesMailcoachModels;

    protected $signature = 'app:backfill-subscriber-engagement';

    protected $description = 'Backfill subscriber engagement statistics';

    public function handle(): void
    {
        $this->getOutput()->progressStart(self::getSubscriberClass()::count());

        self::getSubscriberClass()::each(function (Subscriber $subscriber) {
            $subscriber->update([
                'emails_received' => $subscriber->sends()->sent()->count(),
                'emails_opened' => $subscriber->opens()->get()->unique('content_item_id')->count(),
                'emails_clicked' => $subscriber->uniqueClicks()->count(),
                'last_open_at' => $subscriber->opens()->latest('created_at')->select('created_at')->first()?->created_at,
                'last_click_at' => $subscriber->clicks()->latest('created_at')->select('created_at')->first()?->created_at,
            ]);

            $this->getOutput()->progressAdvance();
        });

        $this->getOutput()->progressFinish();
    }
}

Once that’s done, you can start segmenting your email lists based on this data!