Subscriber engagement statistics now available

Our latest update of Mailcoach v8.8.0 now includes a way to track subscriber engagement. We’ve added this feature in a backwards-compatible way so it’s only enabled when the necessary migrations have been executed.

Of course, our hosted Mailcoach offering already includes this new feature.

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!