Verteilung der E-Mail-Anbieter unter Newsletter-Abonnenten und Kunden

Beim Versand von Newslettern ist es praktisch zu wissen, welche Email-Anbieter die Kunden verwenden. Die folgende Abfrage extrahiert die Domain des Anbieters und zählt die Email-Adressen der Newsletter-Abonnenten:

SELECT provider_domain AS provider, count(subscriber_email) AS number FROM (
    SELECT
    subscriber_email,
    SUBSTRING_INDEX(subscriber_email, '@', -1) provider_full,
    SUBSTRING_INDEX(SUBSTRING_INDEX(subscriber_email, '@', -1), '.', -2) provider_domain
    FROM newsletter_subscriber
) AS t
GROUP BY provider
ORDER BY number DESC;

Leicht angepasst, kann diese Abfrage auf alle Kunden angewendet werden:

SELECT provider_domain AS provider, count(email) AS number FROM (
    SELECT
    email,
    SUBSTRING_INDEX(email, '@', -1) provider_full,
    SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', -2) provider_domain
    FROM customer_entity
) AS t
GROUP BY provider
ORDER BY number DESC;

Weiter optimiert, fasst die Anfrage Anbieter mit weniger als 50 Email-Adressen in eine Zeile mit dem Eintrag „OTHER“ zusammen:

SELECT provider, SUM(number) number FROM (
SELECT IF(number < 50, '* OTHER', provider) provider, number FROM (
  SELECT provider_domain provider, GROUP_CONCAT(DISTINCT provider_full) domains, COUNT(email) AS number FROM (
    SELECT
    email,
    SUBSTRING_INDEX(email, '@', -1) provider_full,
    SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', -2) provider_domain
    FROM customer_entity
  ) AS t
  GROUP BY provider_domain
) AS v
) AS w
GROUP BY provider
ORDER BY number DESC;

Einige Anbieter-Domains (z.B. gmail und googlemail) sollten noch manuell zusammengefasst werden.

Um auch noch die Summe anzeigen zu lassen, kann WITH ROLLUP verwendet werden. Um die Liste absteigend sortiert zu lassen, ist allerdings eine weitere Schachtelung nötig:

SELECT IF(provider IS NULL, '* TOTAL', provider) provider, number FROM (
SELECT provider, SUM(number) number FROM (
SELECT IF(number < 50, '* OTHER', provider) provider, number FROM (
  SELECT provider_domain provider, GROUP_CONCAT(DISTINCT provider_full) domains, COUNT(email) AS number FROM (
    SELECT
    email,
    SUBSTRING_INDEX(email, '@', -1) provider_full,
    SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', -1), '.', -2) provider_domain
    FROM customer_entity
  ) AS t
  GROUP BY provider_domain
) AS v
) AS w
GROUP BY provider
WITH ROLLUP
) AS x
ORDER BY number DESC

Schreibe einen Kommentar Antworten abbrechen