Datastudio Report: Produkte im Merchant Center mit BigQuery analysieren

Wer kennt das nicht im Agenturalltag? Der Onlineshop eines Kundens publiziert neue Produkte, aber in der Ads Agentur hat das keiner mitbekommen, weil die Verantwortlichen für Produktpflege nicht regelmäßig mit den verschiedenen Agenturpartnern kommunizieren. Am Ende werden solche Produktneuheiten dann beiläufig auf dem nächsten Performance-Review Meeting erwähnt. Weil wir diesen Fall schon eher als den Regelfall erleben, haben wir eine datenbasierte Lösung geschaffen, die uns Neuveröffentlichungen im Shop bequem anzeigt.

Der Artikel zeigt, wie man neue Produkte aus dem Google Merchant Center via Google Datastudio einer Gruppe von Personen verfügbar machen kann.

Vorausstetzungen

Für die Implementierung dieser Lösung benötigt man:

• Leserechte für das Google Merchant Center
• Google Bigquery
• Zugang zu Google Ads Skript / Google Apps Skripts
• Google Datastudio

Datastudio Report: Produktdifferenz im Google Merchant Center darstellen

Der Bericht zeigt alle Produkte, die im gewählten Zeitraum neu im Merchant Center erschienen sind. Dazu wird der Bereich der Datastudio Zeitraumsteuerung mit dem vorherigen Zeitraum verglichen.

Zudem bietet der Bericht über das Diagramm „new listed“ / „unlisted“ die Möglichkeit, zwischen Produkten zu unterscheiden, die aktuell neu gelistet wurden bzw. aus dem Merchant Center verschwunden sind.

Im Ergebnis erhält man eine echte Übersicht über die Artikelbewegungen des Google Merchant Centers was für Agenturmitarbeiter und den Kunden gleichsam wertvoll in der Produktanalyse sein kann.

Merchant Center Daten nach BigQuery tagesaktuell importieren

Um mit Merchant Center Daten in BigQuery arbeiten zu können, müssen diese zunächst auf Tagesaktueller Basis dorthin importiert werden.  Dazu gibt es 2 Herangehensweisen.

1. Mit der vordefinierten Merchant Center Datacenter Übertragung, wie von Google in diesem Artikel ausführlich dargestellt.
2. Mit unser Ads Skript Lösung, das Skript spricht die aktuelle Shopping Content API an und ist auf Basis der v2.1 API erstellt.

Option 1 ist die einfachere, hat aber auch den Nachteil, dass die neuesten Felder hier nicht zur Verfügung stehen. Mit dem vorgestellten Ads Skript hat man mehr Einflussmöglichkeiten und kann fehlende Felder leicht nacharbeiten. Für Nutzer mit Ads Skripts Erfahrung sicherlich der bevorzugte Weg.

BigQuery Abfrage für Produktdifferenzen im Google Merchant Center

WITH shopping_current AS (
  ##### AKTUELLE PRODUKTE AUS DEM MERCHANT CENTER HOLEN ######
    SELECT 
        DATE(_PARTITIONTIME) as date, 
        id,
        offerId,
        title,
        link,
        imageLink,
        brand
    FROM `webmasterei-gtm-test.gmc_data.SHOPPING_CONTENT`, UNNEST(destinationStatuses) as destinationStatuses
    WHERE DATE(_PARTITIONTIME) between "2021-11-15" AND "2021-11-21"
    AND status = 'approved' AND availability = 'in stock'
), shopping_past as (
  ##### PRODUKTE DER VERGANGEN PERIODE AUS DEM MERCHANT CENTER HOLEN ######
  SELECT 
        DATE(_PARTITIONTIME) as date, 
        id,
        offerId,
        title,
        link,
        imageLink,
        brand
    FROM `webmasterei-gtm-test.gmc_data.SHOPPING_CONTENT`, UNNEST(destinationStatuses) as destinationStatuses
    WHERE DATE(_PARTITIONTIME) between "2021-11-08" AND "2021-11-14"
    AND status = 'approved' AND availability = 'in stock'
), unlisted AS (
    ##### Produkte "unlisted" marken, die im aktuellen Bestand nicht mehr vorhanden sind. ######
    SELECT 
        shopping_past.*,
        'unlisted' as listing_status
    FROM shopping_past 
    LEFT JOIN shopping_current
    ON shopping_current.offerId = shopping_past.offerId
    WHERE shopping_current.offerId IS NULL
), new_listed AS (
    ##### Produkte "new listed" marken, die im alten Bestand noch nicht vorhanden sind. ######
    SELECT 
        shopping_current.*,
        'new listed' as listing_status
    FROM shopping_current
    LEFT JOIN shopping_past 
    ON shopping_current.offerId = shopping_past.offerId
    WHERE shopping_past.offerId IS NULL
), unionlist AS (
    SELECT * FROM unlisted
    UNION ALL 
    SELECT * FROM new_listed
)
SELECT DISTINCT
    id,
    offerId,
    title,
    link,
    imageLink,
    brand,
    listing_status
FROM unionlist 

Die Abfrage holt sich zunächst alle Produkte des eingestellten Zeitraums. Danach passiert das Gleiche für den vorherigen Zeitraum.

Anschließend werden die Daten mit Join zusammengeführt und Produkte die in beiden Zeiträumen vorhanden waren ausgeschlossen. Die Daten werden dann entsprechen ihr Zugehörigkeit mit „unlisted“ oder „new listed“ gekennzeichnet.

Zu guter Letzt werden die Tabellen für die Abfrage mit UNION ALL zusammengeführt.

Zeiträume für Datastudio anpassen

Damit die Abfrage in Datastudio mit dynamischem Datum betrieben werden kann, müssen noch @DS_START_DATE und @DS_END_DATE in den Code.

Für den Zeitraum der Zeitraumsteuerung ist dies einfach:

WHERE DATE(_PARTITIONTIME) between PARSE_DATE("%Y%m%d",@DS_START_DATE) AND PARSE_DATE("%Y%m%d",@DS_END_DATE)

Für den vorherigen Zeitraum ist das etwas komplexer.

Wir wollen den Zeitraum vor @DS_START_DATE ermitteln, der genau so lang ist wie der Zeitraum zwischen @DS_START_DATE und @DS_END_DATE

Um die Daten später korrekt einsetzen zu können, habe ich diese Testfunktion gebaut:

SELECT
PARSE_DATE("%Y%m%d",@DS_START_DATE) as current_start,
PARSE_DATE("%Y%m%d",@DS_END_DATE) as current_end,
DATE_DIFF(PARSE_DATE("%Y%m%d",@DS_END_DATE),PARSE_DATE("%Y%m%d",@DS_START_DATE),DAY) as datediff,
DATE_SUB(PARSE_DATE("%Y%m%d",@DS_START_DATE), INTERVAL DATE_DIFF(PARSE_DATE("%Y%m%d",@DS_END_DATE),PARSE_DATE("%Y%m%d",@DS_START_DATE),DAY) +1 DAY) as past_start,
DATE_SUB(PARSE_DATE("%Y%m%d",@DS_START_DATE), INTERVAL 1 DAY) as past_end

Fügt man diese im Google Datastudio hinzu, kann man komfortabel kontrollieren ob die Zeiträume korrekt gesetzt sind.

Hier das Fertige Ergebnis für den Datastudio Report:

WITH shopping_current AS (
  ##### AKTUELLE PRODUKTE AUS DEM MERCHANT CENTER HOLEN ######
    SELECT 
        DATE(_PARTITIONTIME) as date, 
        id,
        offerId,
        title,
        link,
        imageLink,
        brand
    FROM `myproject.gmc_data.SHOPPING_CONTENT`, UNNEST(destinationStatuses) as destinationStatuses
    WHERE DATE(_PARTITIONTIME) between PARSE_DATE("%Y%m%d",@DS_START_DATE) AND PARSE_DATE("%Y%m%d",@DS_END_DATE)
    AND status = 'approved' AND availability = 'in stock'
), shopping_past as (
  ##### PRODUKTE DER VERGANGEN PERIODE AUS DEM MERCHANT CENTER HOLEN ######
  SELECT 
        DATE(_PARTITIONTIME) as date, 
        id,
        offerId,
        title,
        link,
        imageLink,
        brand
    FROM `myproject.gmc_data.SHOPPING_CONTENT`, UNNEST(destinationStatuses) as destinationStatuses
    WHERE DATE(_PARTITIONTIME) between 
        DATE_SUB(PARSE_DATE("%Y%m%d",@DS_START_DATE), INTERVAL DATE_DIFF(PARSE_DATE("%Y%m%d",@DS_END_DATE),PARSE_DATE("%Y%m%d",@DS_START_DATE),DAY) +1 DAY) ## START DATE
        AND DATE_SUB(PARSE_DATE("%Y%m%d",@DS_START_DATE), INTERVAL 1 DAY) ### END DATE
    AND status = 'approved' AND availability = 'in stock'
), unlisted AS (
    ##### Produkte "unlisted" marken, die im aktuellen Bestand nicht mehr vorhanden sind. ######
    SELECT 
        shopping_past.*,
        'unlisted' as listing_status
    FROM shopping_past 
    LEFT JOIN shopping_current
    ON shopping_current.offerId = shopping_past.offerId
    WHERE shopping_current.offerId IS NULL
), new_listed AS (
    ##### Produkte "new listed" marken, die im alten Bestand noch nicht vorhanden sind. ######
    SELECT 
        shopping_current.*,
        'new listed' as listing_status
    FROM shopping_current
    LEFT JOIN shopping_past 
    ON shopping_current.offerId = shopping_past.offerId
    WHERE shopping_past.offerId IS NULL
), unionlist AS (
    SELECT * FROM unlisted
    UNION ALL 
    SELECT * FROM new_listed
)
SELECT DISTINCT
    id,
    offerId,
    title,
    link,
    imageLink,
    brand,
    listing_status
FROM unionlist 

Nun kann diese Abfrage mit Hilfe des BigQuery Datastudio Connectors in Datastudio genutzt werden. Wie das funktioniert wird weiter unten im Artikel gezeigt.

Produkte mit Problemen & Warnungen des Google Merchant Centers

Diese BigQuery Abfrage zeigt Produkte aus dem Google Merchant Center die gestern ein Problem hatten.

Dazu müssen lediglich die Produkte mit gestrigem Datum geladen werden. Anschließend Unnesten wir die itemLevelIssues, sodass wir diese einzeln abrufen können.

WITH shopping AS (
    SELECT DATE(_PARTITIONTIME) as date, 
        * 
    FROM `myproject.gmc_data.SHOPPING_CONTENT`, UNNEST(itemLevelIssues) as itemLevelIssues
    WHERE DATE(_PARTITIONTIME) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
)
SELECT * FROM shopping

Ist die Abfrage im Datastudio mit einer sinnvollen Kreuzfilterung für die Probleme hinterlegt, dient sie als effizientes Werkzeug um Probleme zu erkennen und diese systematisch zu lösen.

BigQuery Benutzerdefinierten Report im Google Datastudio aufsetzen

Für den Datastudio Report nutzen wir die oben gezeigte BigQuery Abfrage und setzen diese im Google Datastudio als Datenquelle für den Bericht.

1. Datastudio Report öffnen
2. Daten hinzufügen wählen
3. BigQuery als Datenquelle wählen
4. Benutzerdefinierte Abfrage wählen
5. Abrechnungskonto wählen
6. Abfrage einfügen
7. Enable Date Range Parameter = true setzen
8. Hinzufügen wählen

Die Datenquelle steht nun unter „Ressourcen > Datenquellen“ bereit. Den Luxus, hier vorab noch einen Namen für die Datenquelle zu vergeben, besteht nicht. Dies muss nun nachträglich erfolgen. Die neu erstellte Datenquelle heißt „BigQuery“.

Rudimentär ist die Datenquelle nun bereit und man kann loslegen sich entsprechende Reporting Tabellen zu basteln.

Das SEA Team kann jetzt zwar sehen welche Produkte neu sind, muss aber noch jede Nummer im Merchant Center einzeln aufrufen und die Shop Suche betätigen. Irgendwie… uncool – wo doch das Merchant Center eigene Produktseiten und die Produkte eigene Webseiten haben und wo wir schon dabei sind…. gehen Produktbilder auch? Damit man nicht lesen muss ob es Jacke oder Pullover ist?

Ja. Geht.

HYPERLINK() Funkion für Verlinkung aus Google Datastudio Tabellen

Einer meiner neuen Lieblinge ist die HYPERLINK() Funktion in Google Datastudio geworden.

Googles Kurzhilfe ist:

HYPERLINK(URL, Link Label)

Beispiele
HYPERLINK(CONCAT(‚https://www.youtube.com/watch?v=‘, External Video Id), Video Title)

Zurück in der eben erstellen Datenquelle via Ressourcen > Datenquellen > Bearbeiten

Kann man nun ein neues Feld anlegen und dort mit der Hyperlink Funktion einen verlinkten Titel anlegen.

HYPERLINK(link,title)

Und weil das so schön funktioniert, machen wir das auch gleich für die Merchant Center Seite. Somit haben wir ein Feld, indem „View in Merchant Center“ heißt und direkt auf die Produktdetailseite verlinkt.

HYPERLINK(CONCAT('https://merchants.google.com/mc/items/details?a=123456&offerId=',id,'-S&country=CH&language=de&channel=0'), 'View in Merchant Center')

Kreuzfilterung im Google Datastudio

Etwas mehr Value bietet dann noch die Kreuzfilterung im Google Datastudio. Damit werden die Tabellen interaktiv. Aktivieren wir die Option für die Markenliste, sorgt dies dafür, dass bei einem Klick auf eine Marke in der 1. Tabelle die Artikeltabelle nur noch Artikel dieser Marke darstellt.

Bilder im Google Datastudio

Bilder in Tabellen des Google Datastudio anzeigen zu lassen, ist sehr einfach.

In der Datenquelle kann man definieren, um was für einen Wert es sich handelt. Für URL-Werte stehen hier „Url“ oder „Bild“ zur Verfügung. Wir konfigurieren als den imageLink für die Berichte als Bild und haben sofort eine schnellere Wahrnehmung, worum es sich bei dem Produkt handelt.