Navigation

 

Artikel Kategorien

Viele Webmaster kennen das Problem. Diverse Anbieter nutzen immer noch eine Werbe-Technik die sich Referral Spam nennt. Dabei sendet der Werbetreibende eine spezielle Anfrage, wodurch im Traffic Analyse Tool diese Webseiten als Link auftauchen. Beliebt ist diese Technik bei unseriösen SEO-Firmen, die damit neue Kunden gewinnen wollen.

Piwik kämpft seit einiger Zeit gegen diese Art des Spamming durch eine Black-Liste. Da aber ständig neue Anbieter dazu kommen, bleibt eine große Menge an Spam dennoch in der Traffic Übersicht hängen. Auch Nutzer von Google Analytics sind derzeit noch nicht davor gefeit. (Link zum Github https://github.com/piwik/referrer-spam-blacklist)

In diesem Artikel zeige ich einen Weg wie man auch nachträglich noch diese Fehlerhaften oder falschen Daten aus Piwik entfernen kann.

Spammer finden

Als erstes benötigen wir eine Liste der Spammer um nachhaltig dagegen anzukämpfen. Dazu geht man einfach in Piwik auf die betroffene Seite und dann zur “All Referrers” Übersicht. Dort erhält man im unteren Bereich eine Liste. Diese enthält nicht nur Spam, wird aber die Grundlage unserer Bereinigung sein. Diese Liste können wir jetzt erweitern und als JSON-Datei exportieren.

Jetzt benötigen wir ein kleines Script das uns alle Einträge als eine Liste wiedergibt. Dieses sieht wie folgt aus:

# convert-json-to-list.php > spammer.txt
%lt;?php
$content = json_decode(file_get_contents($argv[1]));
foreach ($content as $website) {
printf('%s'.PHP_EOL, $website->label);
}

Nun können wir die Konvertierung starten indem wir dem Script die Datei als Parameter wie folgt übergeben:

php -f convert-json-to-list.php ~/Downloads/meine-spammer.json

Nun folgt die eigentliche Arbeit. Die Ausgegeben Liste speichert man nun in eine Text Datei und löscht jeden Eintrag heraus, der kein Spam ist.

Spammer aus der Datenbank löschen

Die fertige Liste mit allen Spammern können wir nun an ein weiteres Script geben, das uns die entsprechende SQL-Query zusammen baut und ausgibt.

<?php
# create-spammer-delete-query.php
define('PIWIK_TABLE_LOG_VISIT', 'piwik_log_visit');
define('PIWIK_TABLE_LOG_LINK_VISIT', 'piwik_log_link_visit_action');
$file = $argv[1];
$referers = array();

$statement = "DELETE log_visit, log_link_visit_action FROM %s AS log_visit LEFT JOIN %s as log_link_visit_action ON log_visit.idvisit = log_link_visit_action.idvisit LEFT JOIN piwik_log_action as log_action ON log_action.idaction = log_link_visit_action.idaction_url WHERE log_visit.referer_name LIKE '%%%s%%';";

if (is_file($file)) {
$lines = file($file);

foreach ($lines as $line) {
printf($statement.PHP_EOL, PIWIK_TABLE_LOG_VISIT, PIWIK_TABLE_LOG_LINK_VISIT, trim($line));
}
}

Vor dem ausführen des Scripts muss man noch den Tabellenname anpassen. Dieser muss Deckungsgleich mit der eigenen Datenbank sein und wird in Zeile eins und zwei geändert. Das script starten wir mit folgendem Befehl:

php -f create-spammer-delete-query.php spammer.txt

Das Tool gibt uns nun eine Menge an SQL Queries die nach dem Ausführen z.B. in phpMyAdmin alle Spammer entfernt.

Abschließende Schritte

Die Spammer sind leider immer noch in unserer Datenbank. Deshalb müssen wir noch alle Tabellen löschen die im Namen “archive” tragen. Wer viele Aufrufe auf seiner Webseite hat, sollte danach diese Tabellen neu erstellen mittels dem Konsolen Tool von Piwik, da es sonst sehr lange dauern kann bis man im Web-Interface die Daten wieder aufrufen kann, besonders bei langen Zeiträumen. Folgend ein Beispiel dafür:

./console core:archive --force-all-websites --force-all-periods=315576000 --force-date-last-n=1000

 

Zuletzt aktualisiert am 08.11.2016