Google BigQuery für einen Rails und Solidus Shop: jede Nacht die Bestellungen hinein, Kampagnen und Kundenwert heraus

Was dieser Artikel aufbaut, und was er dem Warehouse Artikel überlässt

Hier geht es um die Leitungen. Am Ende landen die Bestellungen eines Rails und Solidus Shops jede Nacht sicher in Google BigQuery, und zwei Fragen lassen sich beantworten: Welche Kampagnen verkaufen, und welche bringen Kunden, die sich lohnen?

Was ein Data Warehouse überhaupt ist, wie du Tools über den Kunden verknüpfst, ohne deinen Umsatz zu verdoppeln, und wie Segmente mit geprüfter Erlaubnis wieder hinausgehen, steht in Data Warehousing für smarte Marketing Automation. Wenn sich "Warehouse" für dich noch schwammig anfühlt, lies den zuerst.

Durch den ganzen Artikel begleitet mich ein erfundener Shop. Mühlbach Tee verkauft losen Tee und eine monatliche Teebox aus einem kleinen Lager in Köln, geführt von Nadine. Ihr Solidus Shop läuft unter muehlbachtee.example und nimmt rund 1.200 Bestellungen im Monat zu je etwa 31 € an. Die Firma gibt es nicht, und jede Zahl unten ist ausgedacht, damit ich jeden Schritt zeigen kann.

Beispiel: Nadines drei Fragen

Nadine hat drei Fragen, die ihr der Solidus Admin nicht beantwortet. Wie viel hat jede Kampagne letzten Monat verkauft? Sind die Leute, die über ihre Herbstanzeigen auf Meta kamen, je wiedergekommen? Und sind die Kunden, die sie über die Karten in Kölner Cafés gefunden haben, mehr wert als die von Google?

Für die erste Frage brauchst du jede Bestellung mit ihrer Kampagne. Für die beiden anderen brauchst du die Bestellhistorie jedes Kunden, gruppiert nach der Kampagne, die ihn zuerst gebracht hat. Die Shopdatenbank kennt die Bestellungen, aber nicht die Kampagnen, und niemand will schwere Auswertungen auf der Datenbank laufen lassen, die gerade Zahlungen annimmt. Der Plan hat also vier Teile: ein BigQuery Projekt, eine nächtliche Kopie der Bestellungen, ein Kampagnenkennzeichen an jeder Bestellung und zwei Abfragen.

Fang mit einem Projekt in der EU an, und schalte die Abrechnung früh ein

BigQuery lebt in einem Google Cloud Projekt. Darin legst du ein Dataset an, eine Art Ordner für Tabellen, und jedes Dataset hat einen Standort: den Ort, an dem seine Daten gespeichert und verarbeitet werden.

Für einen Shop in Deutschland wähle ich die Multiregion EU. Die Kundendaten bleiben damit in der Europäischen Union, und Google speichert Daten in EU nur in Belgien oder den Niederlanden.¹ Wer die Daten lieber in Deutschland hat, nimmt die Region Frankfurt, bei Google europe-west3.¹ Wichtig ist nur, dass du dich einmal entscheidest. Eine Abfrage kann keine Standorte mischen: Der Standort eines Jobs muss zu jedem Dataset passen, das er liest, und eine einzelne Region passt nicht zur Multiregion um sie herum, auch wenn sie darin liegt.¹ Also kommt alles an denselben Ort, später auch der Export aus Google Analytics.

Dann die Abrechnung. BigQuery hat eine Sandbox, für die du keine Karte brauchst. Sie gibt dir 10 GiB Speicher für ihre ganze Laufzeit und das kostenlose Kontingent von 1 TiB verarbeiteter Abfragedaten im Monat. Aber ihre Tabellen laufen nach 60 Tagen ab, und sie unterstützt weder Streaming noch den Data Transfer Service noch DML Anweisungen (die, die Daten ändern, etwa MERGE).² Die nächtliche Synchronisierung unten braucht MERGE, also braucht ein Shop ein Rechnungskonto. Das heißt nicht, dass eine große Rechnung kommt: Das erste TiB verarbeiteter Abfragedaten im Monat bleibt kostenlos, ebenso die ersten 10 GiB Speicher.³ Auch das Laden von Daten in Stapeln kostet nichts, es nutzt einen gemeinsamen Pool an Kapazität.³ Google nennt die Preise in US Dollar. Wenn du in Euro zahlst, gelten die Preise, die in deiner Währung aufgeführt sind.³

Beispiel: Projekt und Dataset von Mühlbach Tee

Nadines Entwickler legt ein Projekt namens muehlbach-analytics an, verknüpft die Abrechnung und setzt einen Budgetalarm bei 20 € im Monat. So kommt ein Fehler als E Mail an und nicht als Überraschung. Ein Aufruf in der Rails Konsole legt das Dataset an:

BIGQUERY.create_dataset "shop", location: "EU",
  description: "Mühlbach Tee Bestellungen, jede Nacht aus Solidus kopiert"

Hier steht EU statt einer einzelnen Region, weil die Kundendaten eines deutschen Shops in der EU bleiben sollen. Wer Frankfurt will, schreibt europe-west3, und dann gehört auch jedes weitere Dataset dorthin. Woher BIGQUERY kommt, zeigt der nächste Abschnitt.

Gib dem Shop ein eigenes Dienstkonto, mit zwei Rollen

Die Rails App spricht mit BigQuery als Dienstkonto, einer Google Identität für Software statt für einen Menschen. Gib ihm das Mindeste, was es braucht. Zwei Rollen reichen. BigQuery Data Editor, vergeben auf genau dem einen Dataset, erlaubt das Lesen und Schreiben seiner Tabellen. BigQuery Job User, vergeben auf dem Projekt, erlaubt das Ausführen von Jobs, und Laden und Abfragen sind Jobs.⁴ An jedes andere Dataset kommt das Konto nicht heran.

Ausweisen muss sich die App mit einem Schlüssel, einer JSON Datei, die Google dich einmal herunterladen lässt. Google ist bei diesen Schlüsseln deutlich: Sie werden zum Sicherheitsrisiko, wenn man sie nicht sorgfältig verwaltet, ein geleakter Schlüssel muss sofort in IAM gelöscht werden (ihn aus einem Repository zu entfernen reicht nicht), und Schlüssel sollten regelmäßig rotiert werden.⁵ Wo dein Hosting es zulässt, möchte Google, dass du ganz ohne Schlüssel auskommst, mit einem angehängten Dienstkonto oder mit Workload Identity Federation.⁵ Die meisten kleinen Shops laufen außerhalb von Google Cloud, also bleibt es beim Schlüssel, in den verschlüsselten Credentials von Rails, nie als einfache Datei in git. Das Gem google-cloud-bigquery (Version 1.64.2, während ich das schreibe) nimmt zwar noch einen Pfad oder einen Hash mit dem Schlüssel an, markiert beides aber als veraltet und empfiehlt, zuerst ein Credentials Objekt zu bauen.⁶

Beispiel: die Verbindung aus Rails

Der Entwickler startet bin/rails credentials:edit --environment production und legt die heruntergeladene JSON Datei unter bigquery als service_account_json ab. Daneben kommt pseudonym_key, eine lange Zufallszeichenkette aus bin/rails secret, die gleich wichtig wird. Dann ein Initializer, config/initializers/bigquery.rb:

require "google/cloud/bigquery"
require "googleauth"

json = Rails.application.credentials.dig(:bigquery, :service_account_json)

if json
  credentials = Google::Auth::ServiceAccountCredentials.make_creds(
    json_key_io: StringIO.new(json),
    scope: "https://www.googleapis.com/auth/bigquery"
  )

  BIGQUERY = Google::Cloud::Bigquery.new(
    project_id: "muehlbach-analytics",
    credentials: credentials
  )
end

Auf einem Laptop ohne den Produktionsschlüssel ist json gleich nil, und es entsteht keine Verbindung. Genau das willst du in der Entwicklung.

Entscheide, was den Shop verlässt, bevor du Code für die Synchronisierung schreibst

Solidus hält eine Bestellung in Spree::Order, der Tabelle spree_orders. Die Spalten, die für Auswertungen zählen, sind von Haus aus da: die Bestellnummer number, state, completed_at, item_total (die Waren), shipment_total, promo_total (Rabatte, als negativer Betrag gespeichert), total, payment_state, shipment_state, currency, canceled_at sowie email und user_id des Kunden.⁷ Jeder Artikel im Warenkorb ist ein Spree::LineItem mit variant_id, quantity und price.⁷

Die Spalte state ist ein Zustandsautomat. Ein Checkout wandert von cart nach complete, und eine abgeschlossene Bestellung kann später zu canceled, awaiting_return oder returned werden.⁸ Ich kopiere nur abgeschlossene Bestellungen und kopiere sie weiter, solange sie sich ändern.

Jetzt der Teil, den die meisten Anleitungen auslassen. Wer eine E Mail Adresse in ein anderes System kopiert, verarbeitet personenbezogene Daten. In Deutschland gilt dafür die DSGVO, und bei Unternehmen wachen die nach Landesrecht zuständigen Datenschutzbehörden darüber, für Nadine in Köln also die Behörde in Nordrhein Westfalen.⁹ Der Grundsatz der Datenminimierung in Art. 5 Abs. 1 lit. c DSGVO verlangt, dass personenbezogene Daten dem Zweck angemessen und erheblich sowie auf das für den Zweck notwendige Maß beschränkt sind.¹⁰ Umsatz nach Kampagne braucht niemandes E Mail, Adresse oder Namen. Er braucht einen festen Schlüssel, der sagt: derselbe Kunde.

Also ersetze ich die E Mail durch ein Pseudonym. Pseudonymisierung heißt nach Art. 4 Nr. 5 DSGVO, dass sich Daten ohne zusätzliche Informationen nicht mehr einer bestimmten Person zuordnen lassen, wobei diese Informationen gesondert aufbewahrt und geschützt werden. Erwägungsgrund 26 stellt klar, dass pseudonymisierte Daten trotzdem personenbezogene Daten bleiben.¹⁰ Der Europäische Datenschutzausschuss sagt in seinen Leitlinien zur Pseudonymisierung, dass die Umwandlung ein Geheimnis enthalten muss, das nur der Verantwortliche kennt. Sonst wendet jemand einfach SHA256 auf alle Namen an, die er hat, und schaut, welche Hashes passen.¹¹ Mein Pseudonym ist deshalb ein Hash mit Schlüssel, gebildet mit dem pseudonym_key, der nur im Shop liegt. Das Warehouse sieht für jede Bestellung desselben Kunden dieselbe Zeichenkette und kann daraus keine Adresse zurückrechnen.

Google Cloud handelt dabei als dein Auftragsverarbeiter, und Art. 28 DSGVO verlangt dafür einen Vertrag mit Gegenstand, Dauer, Art und Zweck der Verarbeitung.¹⁰ Diesen Vertrag liefert das Cloud Data Processing Addendum, das die DSGVO abdeckt und Google ausdrücklich als Auftragsverarbeiter benennt.¹² Das ist der Vertrag, die Datenminimierung bleibt deine Aufgabe. Ich bin Ingenieur, kein Anwalt, also frag für alles, was darüber hinausgeht, einen.

Beispiel: die Spaltenliste von Mühlbach Tee

Nadines Entwickler geht spree_orders Spalte für Spalte durch, mit einer einzigen Frage: Braucht eine von Nadines Fragen diese Spalte?

number, state, completed_at, item_total, shipment_total, promo_total, total, payment_state, shipment_state, currency, canceled_at und updated_at kommen mit. Sie beschreiben den Verkauf, nicht die Person.

email kommt nicht mit. An ihre Stelle tritt customer_key, der Hash mit Schlüssel aus der bereinigten E Mail, gleich, ob der Kunde als Gast oder mit Konto bestellt hat.

bill_address_id, ship_address_id, special_instructions und last_ip_address bleiben im Shop. Nadine hat nichts über Adressen gefragt, und eine Geschenknachricht ("Alles Gute, Mama, und sorry wegen letztem Jahr") gehört in keine Auswertung.

Der Schlüssel, in app/models/customer_key.rb:

class CustomerKey
  def self.for(email)
    key = Rails.application.credentials.dig(:bigquery, :pseudonym_key)
    OpenSSL::HMAC.hexdigest("SHA256", key, email.to_s.strip.downcase)
  end
end

Ohne strip.downcase werden Nadine@Example.com und nadine@example.com mit einem Leerzeichen am Ende zu zwei Kunden.

Drei Tabellen, geformt nach den Fragen

Mühlbach Tee braucht drei Tabellen: orders, eine Zeile pro abgeschlossener Bestellung, line_items, eine Zeile pro Artikel mit Bestellnummer und SKU, und order_events, ein kleines Live Protokoll, zu dem ich später komme.

Zwei Funktionen von BigQuery formen Tabellen: Partitionierung und Clustering. Partitionierung teilt eine Tabelle nach einer Datums oder Zeitstempelspalte in Segmente, standardmäßig pro Tag. Eine Abfrage, die auf diese Spalte filtert, überspringt dann die Partitionen, die sie nicht braucht, und liest weniger Bytes.¹³ Clustering sortiert die Daten im Speicher nach bis zu vier Spalten, sodass ein Filter auf die erste davon weniger Blöcke liest.¹⁴

Jetzt Googles eigene Empfehlung. Clustering ist meist das Erste, wonach du greifst, und eine nicht partitionierte Tabelle über 64 MB profitiert wahrscheinlich davon. Darunter ist der Gewinn meist vernachlässigbar.¹⁴ Partitionierung lohnt sich, wenn jede Partition eine ordentliche Menge Daten enthält. Kommen die Partitionen auf weniger als etwa 10 GB, rät Google stattdessen zu Clustering.¹³ Dazu kommt: Eine Abfrage wird pro referenzierter Tabelle mit mindestens 10 MB berechnet.³

Die Bestelltabelle von Mühlbach Tee hat ein paar Megabyte. Sie zu partitionieren, würde Hunderte winziger Partitionen erzeugen und nichts sparen, und die Partitionierung einer bestehenden Tabelle lässt sich später nicht mehr ändern.¹⁵ Das Clustering schon. Für einen kleinen Shop clustere ich also nach completed_at und partitioniere nicht. Partitionierung zahlt sich bei großen Tabellen aus, etwa bei den Google Analytics Ereignissen einer viel besuchten Website.

Beispiel: die Bestelltabelle anlegen

dataset = BIGQUERY.dataset "shop"

dataset.create_table "orders" do |t|
  t.schema do |s|
    s.string    "number",         mode: :required
    s.string    "customer_key",   mode: :required
    s.string    "state"
    s.timestamp "completed_at",   mode: :required
    s.numeric   "item_total"
    s.numeric   "shipment_total"
    s.numeric   "promo_total"
    s.numeric   "total"
    s.string    "currency"
    s.string    "payment_state"
    s.string    "shipment_state"
    s.timestamp "canceled_at"
    s.timestamp "updated_at"
    s.string    "utm_source"
    s.string    "utm_medium"
    s.string    "utm_campaign"
  end
  t.clustering_fields = ["completed_at"]
end

Geld gehört in NUMERIC, nie in FLOAT, das 31,10 € nicht exakt darstellen kann. Die utm Spalten tragen das Kampagnenkennzeichen, dazu später mehr. orders_incoming bekommt dasselbe Schema, als nächtliche Landetabelle.

Beispiel: was ein Monat Dashboards liest

Nadines Dashboard schickt 20 Abfragen am Tag ab, jede berührt orders und line_items. Beide Tabellen sind klein, also wird jede Abfrage mit dem Minimum berechnet: 10 MB pro Tabelle, 20 MB pro Abfrage. Über 30 Tage:

$$B = q \times d \times b = 20 \times 30 \times 20 \text{ MB} = 12{.}000 \text{ MB} \approx 11{,}7 \text{ GiB}$$

Dabei ist $B$ die im Monat berechnete Datenmenge, $q$ die Zahl der Abfragen pro Tag, $d$ die Zahl der Tage und $b$ die pro Abfrage berechnete Datenmenge. Das ist schlicht eine Rate mal eine Anzahl, siehe Grundlagen: Verhältnisse und Raten. Gemessen am kostenlosen TiB, also 1.024 GiB:

$$s = \frac{B}{F} \times 100 = \frac{11{,}7}{1{.}024} \times 100 \approx 1{,}1\,\%$$

Dabei ist $s$ der Anteil des kostenlosen Kontingents, der verbraucht wird, $B$ die berechnete Datenmenge und $F$ das kostenlose Kontingent, beide in GiB. Wie aus einem Verhältnis ein Prozentwert wird, erkläre ich von Grund auf in Grundlagen: Prozent. Nadines Dashboards verbrauchen etwa 1 % dessen, was kostenlos ist. Die Arbeit steckt darin, die Daten richtig zu bekommen, nicht in der Rechnung.

Lade jede Nacht in Stapeln, und lass den nächtlichen Lauf die Wahrheit sein

Laden in Stapeln schickt eine Datei und kostet nichts. Streaming schickt Zeilen, sobald sie entstehen, und wird berechnet.³ Für eine Bestellhistorie gewinnt der Stapel. Bestellungen ändern sich nach dem Checkout weiter (sie werden versandt, sie werden storniert), also korrigiert der nächtliche Job auch alte Zeilen.

Mein Muster: Jede Nacht exportiere ich jede abgeschlossene Bestellung, die sich in den letzten 48 Stunden geändert hat, in eine Datei mit zeilenweise getrenntem JSON (ein JSON Objekt pro Zeile), lade sie in orders_incoming, wobei der bisherige Inhalt ersetzt wird, und führe sie dann per MERGE in orders zusammen. Ein MERGE aktualisiert die Zeilen, deren Schlüssel schon existiert, und fügt die übrigen neu ein.¹⁶ Die 48 Stunden überlappen mit Absicht: Fällt eine Nacht aus, holt die nächste sie nach, und wer dieselbe Nacht zweimal laufen lässt, ändert nichts.

Eine Regel aus der Dokumentation musst du beachten: Ein MERGE schlägt fehl, wenn mehr als eine eingehende Zeile auf dieselbe Zielzeile passt.¹⁶ Bestellnummern sind in Solidus eindeutig, der Export hat also eine Zeile pro Bestellung, und die Regel hält.

Lies das Diagramm von oben nach unten: Jeder Kasten wartet auf den darüber, und der Entscheidungskasten ist das, was MERGE Zeile für Zeile tut. Worauf es ankommt, zeigt der letzte Kasten: Nach jedem Lauf stimmt das Warehouse mit dem Stand des Shops von letzter Nacht überein.

Beispiel: die Nacht zum 14. März

Am 14. März 2026 um 2:00 Uhr läuft der Job. In den letzten 48 Stunden wurden gestern 41 Bestellungen abgeschlossen und vorgestern 39. Die 39 sind schon letzte Nacht geladen worden. Außerdem haben sich vier ältere Bestellungen geändert: eine wurde storniert, drei wurden versandt. Der Export enthält also 41 + 39 + 4 = 84 Zeilen.

Der MERGE findet 43 der 84 Bestellnummern schon in orders (die 39 und die vier) und aktualisiert sie. Bei den 39 hat sich nichts geändert, das Update schreibt also dieselben Werte noch einmal, was nicht schadet. Bei den vier ändern sich Status und Versandstatus. Die übrigen 41 sind neu und werden eingefügt. 43 + 41 = 84, jede Zeile ist erklärt.

Der Job, in app/jobs/export_orders_to_bigquery_job.rb:

class ExportOrdersToBigqueryJob < ApplicationJob
  queue_as :low

  def perform(since: 48.hours.ago)
    file = Tempfile.new(["orders", ".json"])
    Spree::Order.complete.where("spree_orders.updated_at >= ?", since).find_each do |order|
      file.puts(order_row(order).to_json)
    end
    file.flush

    shop = BIGQUERY.dataset("shop", skip_lookup: true)
    loaded = shop.table("orders_incoming", skip_lookup: true)
                 .load(file.path, format: "json", write: "truncate")
    raise "BigQuery load into orders_incoming failed" unless loaded

    BIGQUERY.query(MERGE_ORDERS_SQL)
  ensure
    file&.close!
  end

  private

  def order_row(order)
    {
      number: order.number,
      customer_key: CustomerKey.for(order.email),
      state: order.state,
      completed_at: order.completed_at.utc.iso8601,
      item_total: order.item_total.to_s("F"),
      shipment_total: order.shipment_total.to_s("F"),
      promo_total: order.promo_total.to_s("F"),
      total: order.total.to_s("F"),
      currency: order.currency,
      payment_state: order.payment_state,
      shipment_state: order.shipment_state,
      canceled_at: order.canceled_at&.utc&.iso8601,
      updated_at: order.updated_at.utc.iso8601,
      utm_source: order.utm_source,
      utm_medium: order.utm_medium,
      utm_campaign: order.utm_campaign
    }
  end
end

Table#load nimmt einen lokalen Dateipfad an, format: "json" bedeutet zeilenweise getrenntes JSON, write: "truncate" ersetzt den Inhalt der Tabelle, und die Methode gibt true zurück, wenn der Ladejob gelingt.¹⁵ Beträge gehen als Zeichenketten hinüber, damit unterwegs nichts gerundet wird. Und der MERGE:

MERGE `muehlbach-analytics.shop.orders` AS t
USING `muehlbach-analytics.shop.orders_incoming` AS s
ON t.number = s.number
WHEN MATCHED THEN
  UPDATE SET
    state = s.state,
    total = s.total,
    item_total = s.item_total,
    shipment_total = s.shipment_total,
    promo_total = s.promo_total,
    payment_state = s.payment_state,
    shipment_state = s.shipment_state,
    canceled_at = s.canceled_at,
    updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT ROW

line_items bekommt dieselbe Behandlung, mit der ID des Artikels als Schlüssel.

Streame nur, was live sein muss, in eine eigene Tabelle

Die erste Fassung dieses Artikels versprach Auswertungen "in Echtzeit". Ein Bildschirm im Packraum, der die Bestellungen von heute zeigt, hat das tatsächlich verdient. Solidus veröffentlicht Ereignisse auf einem Bus. Ein Subscriber ist eine Klasse, die Omnes::Subscriber einbindet, handle :order_finalized, with: :method_name deklariert und in einem Initializer innerhalb eines to_prepare Blocks mit subscribe_to(Spree::Bus) registriert wird.¹⁷ Der Kern registriert Ereignisse wie order_finalized, order_canceled, carton_shipped und reimbursement_reimbursed.¹⁸ Die Anleitung warnt außerdem, dass ein Subscriber den Checkout selbst nicht blockieren oder zum Scheitern bringen soll.¹⁷ Mein Subscriber reiht deshalb nur einen Hintergrundjob ein.

Drei Tatsachen entscheiden, wie die Streaming Seite aussehen sollte.

Erstens nutzt die Methode insert des Ruby Gems die REST Streaming API, die Google heute Storage Write API (REST) nennt und früher tabledata.insertAll nannte.¹⁹ Sie wird nach Datenmenge berechnet, wobei jede Zeile mit mindestens 1 KB zählt.³ Für neue Projekte empfiehlt Google die gRPC Variante der Storage Write API, aber die dokumentierten Clientbibliotheken dafür gibt es für Java, Go, Python, Node.js und Rust, nicht für Ruby.²⁰ Aus Rails heraus bleibt es also bei REST.

Zweitens ist der Schutz vor Duplikaten dünn. Jede Zeile kann eine Insert ID mitbringen, und BigQuery entfernt damit Duplikate nach bestem Bemühen für bis zu eine Minute.¹⁹ Ein Job, der nach fünf Minuten erneut läuft, schreibt die Zeile zweimal.

Drittens lassen sich Zeilen, die in den letzten 30 Minuten über die REST API geschrieben wurden, nicht per UPDATE, DELETE oder MERGE ändern.²¹ Streamst du in die Tabelle orders, kann der nächtliche MERGE über die neuesten Zeilen stolpern.

Gestreamte Zeilen kommen deshalb in order_events, ein Protokoll, an das nur angehängt wird und in das nichts zusammengeführt wird. Der Bildschirm im Packraum liest es. Jede Auswertung, die stimmen muss, liest orders. Kommt ein Ereignis doppelt an, liegt der Bildschirm einen Tag lang um eins daneben, und sonst nichts.

Beispiel: ein Protokoll der Bestellereignisse

module Muehlbach
  class OrderEventsSubscriber
    include Omnes::Subscriber

    handle :order_finalized, with: :finalized, id: :muehlbach_bigquery_order_finalized
    handle :order_canceled, with: :canceled, id: :muehlbach_bigquery_order_canceled

    def finalized(event)
      LogOrderEventJob.perform_later(event[:order].number, "finalized", Time.current.utc.iso8601)
    end

    def canceled(event)
      LogOrderEventJob.perform_later(event[:order].number, "canceled", Time.current.utc.iso8601)
    end
  end
end

Rails.application.config.to_prepare do
  Muehlbach::OrderEventsSubscriber.new.subscribe_to(Spree::Bus)
end

class LogOrderEventJob < ApplicationJob
  def perform(number, event, occurred_at)
    table = BIGQUERY.dataset("shop", skip_lookup: true)
                    .table("order_events", skip_lookup: true)
    response = table.insert(
      [{ number: number, event: event, occurred_at: occurred_at }],
      insert_ids: ["#{number}:#{event}"]
    )
    raise "BigQuery insert failed: #{response.insert_errors.inspect}" unless response.success?
  end
end

Der Subscriber liegt in app/subscribers, registriert in config/initializers/omnes.rb, und der Job übernimmt das Einfügen. An einem vollen Samstag protokolliert Mühlbach Tee etwa 60 Ereignisse, beim Minimum von 1 KB also etwa 60 KB. Um die Kosten geht es dabei nicht. Es geht darum, dass der schnelle Weg und der richtige Weg nie dieselbe Tabelle berühren.

Eine Solidus Bestellung weiß nicht, aus welcher Kampagne sie kam

Die erste Fassung dieses Artikels verknüpfte orders über o.campaign_id mit einer Tabelle campaigns. Diese Spalte hat Solidus nicht. Eine Bestellung hält fest, was gekauft wurde, von wem und für wie viel, nicht, welche Anzeige den Käufer gebracht hat.⁷

Eine Quelle ist Google Analytics 4: Sein BigQuery Export enthält zu jedem Kauf die Transaktions ID und die Kampagne aus dem Link.²² Die Verknüpfung über die Bestellnummer funktioniert, aber nur für Besucher, die deine Analytics messen durfte. Diese Lücke behandelt Event Tracking und Consent Mode.

Die andere Quelle ist der Shop selbst: Die Rails App merkt sich die UTM Parameter (Urchin Tracking Module, also utm_source, utm_medium und utm_campaign an deinen Anzeigen und E Mail Links) und schreibt sie an die Bestellung. Wenn deine Kennzeichen noch nicht einheitlich sind, kommt UTM Tracking für deine Online Campaigns meistern zuerst, denn kein Warehouse repariert "Frühling", "frühling" und "fruehling_2026". Ob das Merken des Kennzeichens in der Session eine Einwilligung braucht, hängt von deinem Aufbau ab, und genau das geht der Artikel zum Consent Mode durch.

Werbeausgaben und E Mail Statistiken (in der ersten Fassung Google Ads und Mailchimp) kommen über Connectoren, und wie du Ausgaben auf Kampagnenebene mit Bestellungen verknüpfst, steht im Warehouse Artikel.

Beispiel: die Kampagne an die Bestellung schreiben

Eine Migration fügt spree_orders drei Spalten hinzu:

class AddCampaignToSpreeOrders < ActiveRecord::Migration[7.2]
  def change
    add_column :spree_orders, :utm_source, :string
    add_column :spree_orders, :utm_medium, :string
    add_column :spree_orders, :utm_campaign, :string
  end
end

Ein Concern in app/controllers/concerns/captures_campaign.rb, eingebunden im Basiscontroller des Storefronts, merkt sich die Kennzeichen und schreibt sie einmal an die Bestellung:

module CapturesCampaign
  extend ActiveSupport::Concern

  UTM_KEYS = %w[utm_source utm_medium utm_campaign].freeze

  included do
    before_action :remember_campaign
    before_action :stamp_campaign_on_order
  end

  private

  def remember_campaign
    tags = request.query_parameters.slice(*UTM_KEYS)
    session[:campaign] = tags if tags["utm_campaign"].present?
  end

  def stamp_campaign_on_order
    tags = session[:campaign]
    order = current_order
    return if tags.blank? || order.nil? || order.utm_campaign.present?

    order.update_columns(tags.slice(*UTM_KEYS))
  end
end

Eine Besucherin in Ehrenfeld tippt auf Nadines Herbstanzeige bei Meta. Der Link endet auf ?utm_source=meta&utm_medium=paid_social&utm_campaign=autumn_sampler. Die Session merkt ihn sich. Sie legt eine Probierdose in den Warenkorb, wodurch die Bestellung entsteht, und beim nächsten Seitenaufruf bekommt die Bestellung die drei Kennzeichen. Am nächsten Abend schließt sie im selben Browser ab, und der nächtliche Job kopiert die Bestellung mit utm_campaign gleich autumn_sampler. Ein späterer Klick im Newsletter würde das nicht überschreiben.

Umsatz nach Kampagne: zähl jede Bestellung dort, wo sie passiert ist

Die erste Frage ist die leichte. Jede Bestellung trägt eine Kampagne, also ist Umsatz nach Kampagne eine Summe, gruppiert nach dem Kennzeichen. Ich nehme die Waren nach Rabatt, item_total + promo_total, weil eine Kampagne keinen Versand verkauft. Stornierte Bestellungen lasse ich weg.

SELECT
  COALESCE(utm_campaign, '(none)') AS campaign,
  COUNT(*) AS orders,
  SUM(item_total + promo_total) AS revenue
FROM `muehlbach-analytics.shop.orders`
WHERE completed_at >= TIMESTAMP('2026-10-01', 'Europe/Berlin')
  AND completed_at <  TIMESTAMP('2026-11-01', 'Europe/Berlin')
  AND state != 'canceled'
GROUP BY campaign
ORDER BY revenue DESC

Die Zeitzone zählt. completed_at ist in UTC gespeichert, und der Oktober beginnt in Köln wegen der Sommerzeit zwei Stunden vor dem Oktober in UTC. Ohne die Zone zählen die Bestellungen aus den ersten zwei Stunden des Oktobers noch zum September.

Beispiel: der Oktober bei Mühlbach Tee

Die Abfrage liefert für Oktober 2026 fünf Zeilen. Ohne Kampagnenkennzeichen: 610 Bestellungen, 18.300 €. newsletter_monthly: 290 Bestellungen, 9.860 €. google_brand: 180 Bestellungen, 6.120 €. autumn_sampler: 120 Bestellungen, 3.240 €. cafe_cards: 40 Bestellungen, 1.600 €.

Die Summen zur Probe: 610 + 290 + 180 + 120 + 40 = 1.240 Bestellungen, und 18.300 € + 9.860 € + 6.120 € + 3.240 € + 1.600 € = 39.120 €. Das sollte für dieselben Tage mit dem Solidus Admin übereinstimmen, bis auf Versand und Stornierungen. Eine Auswertung, die dem Shop widerspricht, hat einen Fehler, keine Erkenntnis. Merk dir den Newsletter auf Platz zwei.

Kundenwert nach Kampagne: rechne die Kampagne an, die den Kunden gefunden hat

Die erste Fassung dieses Artikels nannte die Gesamtausgaben jedes Kunden seinen "Lifetime Value" und summierte sie dann pro Kampagne, nach der Kampagne der jeweiligen Bestellung. Beide Schritte muss ich korrigieren.

SUM(total) pro Kunde ist der bisherige Umsatz. Der Lifetime Value ist eine Prognose der Marge über die ganze Kundenbeziehung, mit Kundenbindung und Abzinsung darin, erklärt in Grundlagen: Kundenwert.

Und wer jede Bestellung ihrer eigenen Kampagne zurechnet, beantwortet die Frage "welche Kampagne hat diesen Verkauf abgeschlossen", nicht "welche hat wertvolle Kunden gefunden". Ein Newsletter kann keinen neuen Kunden finden, denn um ihn zu verschicken, brauchst du die Adresse schon, und trotzdem sammelt er jede Wiederholungsbestellung ein, die er anstößt. Also ordne ich jeden Kunden der Kampagne seiner ersten Bestellung zu und zähle zusammen, was er in einem festen Zeitfenster danach ausgegeben hat. Ein Kunde, der vor zwei Jahren kam, hatte schließlich länger Zeit zum Kaufen als einer vom letzten Monat.

Der Durchschnitt pro Kampagne ist ein gewöhnlicher Mittelwert:

$$\bar{v}_c = \frac{1}{n_c} \sum_{i=1}^{n_c} v_i$$

Dabei ist $\bar{v}_c$ der durchschnittliche Umsatz pro Kunde in den ersten zwölf Monaten für die Kampagne $c$, $n_c$ die Zahl der Kunden, deren erste Bestellung aus dieser Kampagne kam, und $v_i$ das, was Kunde $i$ in den zwölf Monaten nach seiner ersten Bestellung ausgegeben hat. Den Mittelwert, und warum ein einzelner großer Käufer ihn weit verschieben kann, erkläre ich in Grundlagen: der Mittelwert.

WITH firsts AS (
  SELECT
    customer_key,
    ARRAY_AGG(COALESCE(utm_campaign, '(none)') ORDER BY completed_at LIMIT 1)[OFFSET(0)] AS first_campaign,
    MIN(completed_at) AS first_order_at
  FROM `muehlbach-analytics.shop.orders`
  WHERE state != 'canceled'
  GROUP BY customer_key
),
value AS (
  SELECT f.customer_key, f.first_campaign,
         SUM(o.item_total + o.promo_total) AS revenue_12m
  FROM firsts AS f
  JOIN `muehlbach-analytics.shop.orders` AS o
    ON o.customer_key = f.customer_key
   AND o.state != 'canceled'
   AND o.completed_at < TIMESTAMP_ADD(f.first_order_at, INTERVAL 365 DAY)
  WHERE f.first_order_at >= TIMESTAMP('2025-07-01', 'Europe/Berlin')
    AND f.first_order_at <  TIMESTAMP('2025-10-01', 'Europe/Berlin')
  GROUP BY f.customer_key, f.first_campaign
)
SELECT first_campaign,
       COUNT(*) AS customers,
       SUM(revenue_12m) AS revenue,
       ROUND(AVG(revenue_12m), 2) AS revenue_per_customer
FROM value
GROUP BY first_campaign
ORDER BY revenue_per_customer DESC

Der erste Block findet für jeden Kunden die erste Kampagne und das erste Datum. Der zweite zählt seine Bestellungen in den 365 Tagen danach zusammen, für Kunden, die zwischen Juli und September 2025 zum ersten Mal bestellt haben, sodass alle ein volles Jahr hatten. Der letzte bildet den Durchschnitt pro Kampagne. Der zweite Block ergibt eine Zeile pro Kunde, bevor irgendetwas pro Kampagne summiert wird, das ist die Regel "erst verdichten" aus dem Warehouse Artikel.

Beispiel: die Sommerkunden 2025 von Mühlbach Tee

800 Kunden haben zwischen Juli und September 2025 zum ersten Mal bestellt. Die Abfrage liefert:

• cafe_cards: 60 Kunden, 6.600 € im ersten Jahr. 6.600 € geteilt durch 60 sind 110 € pro Kunde.
• google_brand: 180 Kunden, 14.760 €. 14.760 € geteilt durch 180 sind 82 € pro Kunde.
• (none): 320 Kunden, 24.000 €. 24.000 € geteilt durch 320 sind 75 € pro Kunde.
• autumn_sampler (ein früher Lauf der Meta Kampagne, im September): 240 Kunden, 11.520 €. 11.520 € geteilt durch 240 sind 48 € pro Kunde.

Zur Probe: 60 + 180 + 320 + 240 = 800 Kunden, und 6.600 € + 14.760 € + 24.000 € + 11.520 € = 56.880 €. Der Mittelwert über alle ist 56.880 € geteilt durch 800, also 71,10 €.

Die waagrechte Achse zeigt die Kampagne, die jede Gruppe gebracht hat. Die senkrechte Achse zeigt, was der durchschnittliche Kunde darin in seinen ersten zwölf Monaten ausgegeben hat, Waren nach Rabatt. Die Erkenntnis: Die Cafékarten bringen wenige Leute, aber die besten, mit mehr als doppelt so viel Wert wie die Kunden aus der Probierdosen Kampagne bei Meta, die meist die Dose für 12 € gekauft und dann aufgehört haben.

Und der Newsletter, im Oktober auf Platz zwei, taucht hier gar nicht auf, weil niemandes erste Bestellung aus ihm kam. Beide Sichten stimmen, für verschiedene Fragen. Der Newsletter holt Kunden zurück, die Cafékarten finden Kunden, die es wert sind, zurückgeholt zu werden.

Zwei Vorbehalte, bevor Nadine ihr Budget verschiebt. 60 Kunden sind eine kleine Gruppe, die eine einzige große Bestellung tragen kann. Schau dir also auch den Median an und lies Grundlagen: Stichproben, bevor du dem Abstand traust. Und Umsatz ist keine Marge: Die Probierdose kann sich trotzdem lohnen, wenn sie für Nadine der günstigste Weg ist, neue Leute zu finden.

Abonnements gehören nicht zum Kern von Solidus

Die erste Fassung dieses Artikels fragte auch eine Tabelle subscriptions ab. Eine einfache Solidus Installation hat keine. Abonnements kommen aus einer Erweiterung, meist solidus_subscriptions, veröffentlicht unter der Solidus Organisation auf GitHub. Sie erzeugt für jeden Abozyklus eine Rate, und jede Rate ist eine Bestellung.²³ Wenn dein Shop sie nutzt, kopier ihre Tabellen mit demselben nächtlichen MERGE und demselben Kundenschlüssel. Die Teebox von Mühlbach Tee läuft darüber, ihre monatlichen Boxbestellungen landen also wie alle anderen in orders. Die Abotabellen ergänzen nur, wer abonniert hat und seit wann.

Die Prüfungen, die ich behalte, damit Rechnung und Zahlen langweilig bleiben

Zwei Einstellungen verhindern die häufigste Überraschung, eine Abfrage, die viel mehr liest als gedacht. Ein Probelauf (Dry Run) schätzt die Bytes, die eine Abfrage verarbeiten würde, ohne sie auszuführen, als Obergrenze. Und ein Limit für die maximal berechneten Bytes lässt eine Abfrage kostenlos scheitern, wenn ihre Schätzung über dem Limit liegt.²⁴ In Ruby liefert BIGQUERY.query_job(sql, dryrun: true).bytes_processed das Erste, und maximum_bytes_billed: an query_job setzt das Zweite. Außerdem: LIMIT verringert auf einer Tabelle ohne Clustering nicht, was eine Abfrage liest, und taugt deshalb nicht dazu, eine Abfrage auf einer großen Tabelle günstiger zu machen.²⁴

Die Liste, die ich bei einem neuen Shop durchgehe, eine Zeile pro Abschnitt:

• Ein Projekt, ein Dataset in EU (oder alles in europe-west3), Abrechnung verknüpft und Budgetalarm gesetzt.
• Ein Dienstkonto mit Data Editor auf dem Dataset und Job User auf dem Projekt, sein Schlüssel in den verschlüsselten Credentials.
• Keine E Mail, keine Adresse, kein Name und keine Notiz in BigQuery, nur ein Hash mit Schlüssel, und der Schlüssel bleibt im Shop.
• NUMERIC für Geld, Clustering vor Partitionierung, Partitionierung nur für große Tabellen.
• Ein nächtlicher Export der letzten 48 Stunden, in eine Landetabelle geladen und zusammengeführt, damit Wiederholungen nicht schaden.
• Live Ereignisse in einer eigenen Tabelle, an die nur angehängt und in die nie zusammengeführt wird.
• Ein Kampagnenkennzeichen an jeder Bestellung, und einheitliche UTM Namen vor allem anderen.
• Jede Auswertung gegen den Solidus Admin geprüft, bevor irgendwer danach handelt.

Nichts davon ist raffiniert, und genau darum geht es. Analytics sollte der langweiligste Teil des Systems sein, damit die Gespräche, die sie anstößt, von Tee und Kunden handeln und nicht davon, wessen Zahlen stimmen. Von hier aus erzählt der Warehouse Artikel weiter.

Footnotes

1. Google Cloud, BigQuery Standorte, BigQuery Dokumentation, 2026, https://docs.cloud.google.com/bigquery/docs/locations?hl=de

2. Google Cloud, BigQuery mit der Sandbox ausprobieren, BigQuery Dokumentation, 2026, https://docs.cloud.google.com/bigquery/docs/sandbox?hl=de

3. Google Cloud, BigQuery Preise, Google Cloud, 2026, https://cloud.google.com/bigquery/pricing?hl=de

4. Google Cloud, BigQuery IAM Rollen und Berechtigungen, BigQuery Dokumentation, 2026, https://docs.cloud.google.com/bigquery/docs/access-control?hl=de

5. Google Cloud, Best Practices für die Verwaltung von Dienstkontoschlüsseln, IAM Dokumentation, 2026, https://docs.cloud.google.com/iam/docs/best-practices-for-managing-service-account-keys?hl=de

6. Google, google-cloud-bigquery: lib/google/cloud/bigquery.rb (Version 1.64.2), googleapis/google-cloud-ruby auf GitHub, 2026, https://github.com/googleapis/google-cloud-ruby/blob/main/google-cloud-bigquery/lib/google/cloud/bigquery.rb

7. Solidus, Solidus 1.4 Schema Migration (spree_orders und spree_line_items), solidusio/solidus auf GitHub, 2026, https://github.com/solidusio/solidus/blob/main/core/db/migrate/20160101010000_solidus_one_four.rb

8. Solidus, Definition des Zustandsautomaten für Bestellungen, solidusio/solidus auf GitHub, 2026, https://github.com/solidusio/solidus/blob/main/core/app/models/spree/core/state_machines/order/class_methods.rb

9. Bundesministerium der Justiz, Bundesdatenschutzgesetz (BDSG), § 40 Aufsichtsbehörden der Länder, Gesetze im Internet, 2026, https://www.gesetze-im-internet.de/bdsg_2018/__40.html

10. Europäische Union, Verordnung (EU) 2016/679 (Datenschutz Grundverordnung), Art. 4 Nr. 5, Art. 5 Abs. 1 lit. c, Art. 28 und Erwägungsgrund 26, EUR Lex, 2016, https://eur-lex.europa.eu/legal-content/DE/TXT/HTML/?uri=CELEX:32016R0679

11. Europäischer Datenschutzausschuss (EDPB), Guidelines 01/2025 on Pseudonymisation, Randnummern 88 und 89, 2025, https://www.edpb.europa.eu/system/files/2025-01/edpb_guidelines_202501_pseudonymisation_en.pdf

12. Google Cloud, Cloud Data Processing Addendum, Google Cloud Nutzungsbedingungen, 2026, https://cloud.google.com/terms/data-processing-addendum?hl=de

13. Google Cloud, Einführung in partitionierte Tabellen, BigQuery Dokumentation, 2026, https://docs.cloud.google.com/bigquery/docs/partitioned-tables?hl=de

14. Google Cloud, Einführung in geclusterte Tabellen, BigQuery Dokumentation, 2026, https://docs.cloud.google.com/bigquery/docs/clustered-tables?hl=de

15. Google, google-cloud-bigquery: lib/google/cloud/bigquery/table.rb (Version 1.64.2), googleapis/google-cloud-ruby auf GitHub, 2026, https://github.com/googleapis/google-cloud-ruby/blob/main/google-cloud-bigquery/lib/google/cloud/bigquery/table.rb

16. Google Cloud, DML Anweisungen in GoogleSQL: MERGE, BigQuery Dokumentation, 2026, https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax?hl=de

17. Solidus, Subscribing to events, Solidus Guides, 2026, https://guides.solidus.io/customization/subscribing-to-events

18. Solidus, core/lib/spree/core/engine.rb (registrierte Ereignisse), solidusio/solidus auf GitHub, 2026, https://github.com/solidusio/solidus/blob/main/core/lib/spree/core/engine.rb

19. Google Cloud, Storage Write API (REST) verwenden, BigQuery Dokumentation, 2026, https://docs.cloud.google.com/bigquery/docs/streaming-data-into-bigquery?hl=de

20. Google Cloud, Einführung in die BigQuery Storage Write API, BigQuery Dokumentation, 2026, https://docs.cloud.google.com/bigquery/docs/write-api?hl=de

21. Google Cloud, Daten mit DML transformieren: Einschränkungen, BigQuery Dokumentation, 2026, https://docs.cloud.google.com/bigquery/docs/data-manipulation-language?hl=de

22. Google, [GA4] BigQuery Export Schema, Google Analytics Hilfe, 2026, https://support.google.com/analytics/answer/7029846?hl=de

23. Solidus, solidus_subscriptions, solidusio auf GitHub, 2026, https://github.com/solidusio/solidus_subscriptions

24. Google Cloud, Kosten schätzen und kontrollieren, BigQuery Dokumentation, 2026, https://docs.cloud.google.com/bigquery/docs/best-practices-costs?hl=de