Google BigQuery for a Rails and Solidus shop: orders in every night, campaigns and customer value out

What this sets up, and what it leaves to the warehouse article

This is the plumbing article. It gets a Rails and Solidus shop's orders into Google BigQuery, safely, every night, and then answers two questions: which campaigns sell, and which bring customers worth having.

What a data warehouse is, how to join tools on the customer without doubling your revenue, and how to send segments back out with permission checked all live in data warehousing for marketing automation. If "warehouse" still feels vague, read that one first.

Throughout I use one invented shop. Corrib Leaf Teas sells loose leaf tea and a monthly tea box from a small unit in Galway, run by Aoife. Its Solidus shop lives at corribleafteas.example and takes about 1,200 orders a month at about €31 each. Nobody real, and every number below is made up so I can show every step.

Worked example: the three questions Aoife wants answered

Aoife has three questions she cannot answer from the Solidus admin. How much did each campaign sell last month? Did the customers who came from her autumn Meta ads ever come back? And are the people who found her through the cards she leaves in Galway cafés worth more than the ones from Google?

The first needs every order with its campaign. The other two need every customer's order history, grouped by the campaign that first brought them. The shop database has the orders but not the campaigns, and nobody wants heavy reports running against the database that takes payments. So the plan has four parts: a BigQuery project, a nightly copy of the orders, a campaign tag on every order, and two queries.

Start with a project in the EU, and switch billing on early

BigQuery lives inside a Google Cloud project. Inside the project you create a dataset, which is a folder for tables, and every dataset has a location: the place where its data is stored and processed.

For an Irish shop I pick a location inside the EU. Google has no BigQuery region in Ireland, so I take Belgium, which Google calls europe-west1. Customer data stays in the EU, and a query should not mix locations: a job runs in one location, and a single region does not count as matching the multi region around it.¹ So put everything in one place, including the Google Analytics export later. The EU multi region would also do, and it stores data only in Belgium or the Netherlands.¹ Pick one and stick to it.

Then billing. BigQuery has a sandbox that needs no card. It gives you 10 GiB of storage for the lifetime of the sandbox and the free tier's 1 TiB of query processing a month, but tables expire after 60 days, and it does not support streaming, the Data Transfer Service or DML statements (the ones that change data, such as MERGE).² The nightly sync below uses MERGE, so a shop needs a billing account. That does not mean a big bill: the first 1 TiB of query processing each month is still free, and so is the first 10 GiB of storage.³ Loading data in batches is free as well, using a shared pool of capacity.³ Prices on Google's page are in US dollars. If you pay in euro, the prices listed in your own currency apply.³

Worked example: Corrib Leaf's project and dataset

Aoife's developer creates a project called corrib-leaf-analytics, links billing, and sets a budget alert at €20 a month, so a mistake arrives as an email rather than a surprise. One call from a Rails console creates the dataset:

BIGQUERY.create_dataset "shop", location: "europe-west1",
  description: "Corrib Leaf Teas orders, copied nightly from Solidus"

Where BIGQUERY comes from is the next section.

Give the shop its own service account, with two roles

The Rails app talks to BigQuery as a service account: a Google identity for software, not a person. Give it the least it needs. Two roles do the job. BigQuery Data Editor, granted on the one dataset, lets it read and write that dataset's tables. BigQuery Job User, granted on the project, lets it run jobs, which is what loads and queries are.⁴ It cannot touch any other dataset.

The app proves who it is with a key, a JSON file Google lets you download once. Google is blunt about these keys: they become a security risk if not managed carefully, a leaked key must be deleted in IAM straight away (removing it from a repository is not enough), and keys should be rotated routinely.⁵ Where your hosting supports it, Google prefers that you avoid keys altogether, with an attached service account or Workload Identity Federation.⁵ Most small shops run outside Google Cloud, so a key it is, in Rails encrypted credentials, never in git as a plain file. The google-cloud-bigquery gem (version 1.64.2 as I write) still accepts a key path or hash, but marks both as deprecated and recommends building a credentials object first.⁶

Worked example: connecting from Rails

The developer runs bin/rails credentials:edit --environment production and pastes the downloaded JSON under bigquery as service_account_json. Next to it goes pseudonym_key, a long random string from bin/rails secret, which matters shortly. Then an 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: "corrib-leaf-analytics",
    credentials: credentials
  )
end

On a laptop without the production key, json is nil and nothing connects, which is exactly what you want in development.

Decide what leaves the shop before you write any sync code

Solidus keeps an order in Spree::Order, the table spree_orders. The columns that matter for analytics are there out of the box: the order number, state, completed_at, item_total (the goods), shipment_total, promo_total (discounts, stored as a negative amount), total, payment_state, shipment_state, currency, canceled_at, and the customer's email and user_id.⁷ Each item in the basket is a Spree::LineItem with its variant_id, quantity and price.⁷

The state column is a state machine. A checkout moves from cart to complete, and a completed order can later become canceled, awaiting_return or returned.⁸ I copy only completed orders, and keep copying them as they change.

Now the part most tutorials skip. Copying an email address into another system is processing personal data. In Ireland that is the EU GDPR, with the Data Protection Commission (DPC) as the regulator. Article 5(1)(c), data minimisation, says personal data should be adequate, relevant and limited to what is necessary for the purpose.⁹ The DPC puts it the same way in its plain guide to the principles.¹⁰ Revenue by campaign does not need anyone's email, address or name. It needs a stable key that says "same customer".

So I replace the email with a pseudonym. Article 4(5) defines pseudonymisation as data that can no longer be tied to a person without additional information, kept separately and protected.⁹ The DPC is plain that pseudonymised data remains personal data.¹¹ And the European Data Protection Board warns that a pseudonym must depend on a secret with enough randomness to resist a guess or a brute force search, giving the example of a plain SHA256 hash of a name, which anyone holding a list of names can recompute.¹² So the pseudonym is a keyed hash, made with the pseudonym_key that lives only in the shop. The warehouse sees the same string for every order by the same customer, and cannot turn it back into an address.

Google Cloud acts as your processor under its Cloud Data Processing Addendum,¹³ which is the written processor contract that Article 28 of the GDPR requires.⁹ That is the contract, the minimisation is your job. I am an engineer, not a solicitor, so for anything beyond this, ask one.

Worked example: Corrib Leaf's column list

Aoife's developer goes through spree_orders column by column: does one of Aoife's questions need this?

number, state, completed_at, item_total, shipment_total, promo_total, total, payment_state, shipment_state, currency, canceled_at and updated_at all go. They describe the sale, not the person.

email does not go. In its place goes customer_key, the keyed hash of the cleaned email, the same whether the customer ordered as a guest or with an account.

bill_address_id, ship_address_id, special_instructions and last_ip_address stay in the shop. Aoife asked nothing about addresses, and a gift message ("happy birthday, Mam, sorry about last year") is nobody's analytics.

The key, 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

Without strip.downcase, Aoife@Example.com and nadia@example.com with a trailing space become two customers.

Three tables, shaped for the questions

Corrib Leaf needs three tables: orders, one row per completed order, line_items, one row per item with the order number and SKU, and order_events, a small live log I come to later.

Two BigQuery features shape tables: partitioning and clustering. Partitioning splits a table into segments by a date or timestamp column, daily by default, so that a query filtered on that column skips the partitions it does not need and reads fewer bytes.¹⁴ Clustering sorts the data in storage by up to four columns, so a filter on the first of them reads fewer blocks.¹⁵

Now Google's own guidance. Clustering is usually the first thing to reach for, and an unpartitioned table bigger than 64 MB is likely to benefit from it. Below that, the improvement is usually negligible.¹⁵ Partitioning is worth adding when each partition would hold a fair amount of data, and when partitions come out at less than about 10 GB each, Google suggests clustering instead.¹⁴ On top of that, a query is billed for at least 10 MB per table it references.³

Corrib Leaf's orders table is a few megabytes. Partitioning it would create hundreds of tiny partitions and save nothing, and partitioning cannot be changed later on an existing table.¹⁶ Clustering can. So for a small shop I cluster on completed_at and do not partition. Partitioning pays on big tables, such as a busy site's Google Analytics events.

Worked example: creating the orders table

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

Money goes into NUMERIC, never FLOAT, which cannot hold €31.10 exactly. The utm columns hold the campaign tag, explained later. orders_incoming gets the same schema, as the nightly landing table.

Worked example: what a month of dashboards reads

Aoife's dashboard runs 20 queries a day, each touching orders and line_items. Both tables are small, so each query is billed the minimum: 10 MB per table, 20 MB a query. Over 30 days:

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

Here $B$ is the volume billed in the month, $q$ is the number of queries a day, $d$ is the number of days and $b$ is the volume billed per query. It is a plain rate times a count, see Foundations: ratios and rates. Against the free 1 TiB, which is 1,024 GiB:

$$s = \frac{B}{F} \times 100 = \frac{11.7}{1{,}024} \times 100 \approx 1.1\%$$

Here $s$ is the share of the free allowance used, $B$ is the volume billed and $F$ is the free allowance, both in GiB. Turning a ratio into a percentage is explained from scratch in Foundations: percentages. Aoife's dashboards use about 1% of what is free. The work is in getting the data right, not in the bill.

Load in batches every night, and let the nightly run be the truth

Batch loading sends a file and is free. Streaming sends rows as they happen and is charged.³ For an order history, batch wins. Orders keep changing after checkout (they ship, they get cancelled), so the nightly job also corrects old rows.

The pattern I use: every night, export every completed order that changed in the last 48 hours to a newline delimited JSON file (one JSON object per line), load it into orders_incoming, replacing whatever was there, and then MERGE it into orders. A MERGE updates the rows whose key already exists and inserts the ones that do not.¹⁷ The 48 hours overlap on purpose: if one night fails, the next night catches up, and running the same night twice changes nothing.

One rule from the documentation to respect: a MERGE fails when more than one incoming row matches the same target row.¹⁷ Order numbers are unique in Solidus, so the export has one row per order, and the rule holds.

Read it from the top: each box waits for the one above, and the decision box is what MERGE does row by row. The takeaway is the last box: after every run, the warehouse agrees with the shop as of last night.

Worked example: the night of 14 March

At 2am on 14 March 2026 the job runs. In the last 48 hours, 41 orders were completed yesterday and 39 the day before. The 39 were already loaded last night. Four older orders also changed: one was cancelled and three were shipped. So the export holds 41 + 39 + 4 = 84 rows.

The MERGE finds 43 of the 84 order numbers already in orders (the 39 and the four) and updates them. For the 39, nothing has changed, so the update writes the same values again, which is harmless. For the four, the state and shipment state change. The other 41 are new and get inserted. 43 + 41 = 84, every row accounted for.

The 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 accepts a local file path, format: "json" means newline delimited JSON, write: "truncate" replaces the table's contents, and the method returns true when the load job succeeds.¹⁶ Amounts go as strings so no rounding happens on the way. And the MERGE:

MERGE `corrib-leaf-analytics.shop.orders` AS t
USING `corrib-leaf-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 gets the same treatment, keyed on the line item's id.

Stream only what must be live, into a table of its own

The first version of this article promised "real time" analytics. A packing room screen showing today's orders does deserve it. Solidus publishes events on a bus, and a subscriber is a class that includes Omnes::Subscriber, declares handle :order_finalized, with: :method_name, and is registered with subscribe_to(Spree::Bus) inside a to_prepare block in an initializer.¹⁸ The core registers events such as order_finalized, order_canceled, carton_shipped and reimbursement_reimbursed.¹⁹ The guide also warns that a subscriber should not block or fail the checkout itself.¹⁸ So the subscriber only queues a background job.

Three facts decide how the streaming side should look.

First, the Ruby gem's insert method uses the REST streaming API, which Google now calls the Storage Write API (REST) and used to call tabledata.insertAll.²⁰ It is billed per volume, with each row counted as at least 1 KB.³ Google recommends the gRPC version of the Storage Write API for new projects, but its documented client libraries are Java, Go, Python, Node.js and Rust, not Ruby.²¹ From Rails, REST it is.

Second, the duplicate protection is thin. Each row can carry an insert id, and BigQuery uses it to remove duplicates on a best effort basis for up to one minute.²⁰ A job retried after five minutes writes the row twice.

Third, rows written through the REST API in the last 30 minutes cannot be changed by UPDATE, DELETE or MERGE.²² Stream into the orders table and the nightly MERGE can trip over the newest rows.

So streamed rows go into order_events, an append only log nothing merges into. The packing room screen reads it. Every report that must be right reads orders. If an event arrives twice, the screen is off by one for a day, and nothing else is.

Worked example: an order event log

module CorribLeaf
  class OrderEventsSubscriber
    include Omnes::Subscriber

    handle :order_finalized, with: :finalized, id: :corrib_leaf_bigquery_order_finalized
    handle :order_canceled, with: :canceled, id: :corrib_leaf_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
  CorribLeaf::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

The subscriber lives in app/subscribers, registered in config/initializers/omnes.rb, and the job queues the insert. On a busy Saturday Corrib Leaf logs about 60 events, about 60 KB at the 1 KB minimum. The point is not cost. It is that the fast path and the correct path never touch the same table.

A Solidus order does not know which campaign it came from

The first version of this article joined orders to a campaigns table on o.campaign_id. Solidus has no such column. An order records what was bought, by whom and for how much, not which advert brought the buyer.⁷

One source is Google Analytics 4: its BigQuery export carries each purchase's transaction id and the campaign collected from the link.²³ Joining on the order number works, but only for visitors your analytics was allowed to measure. Event tracking and consent mode covers that gap.

The other is the shop itself: the Rails app remembers the UTM tags (Urchin Tracking Module, the utm_source, utm_medium and utm_campaign on your ad and email links) and stamps them onto the order. If your tags are not consistent yet, mastering UTM tracking comes first, because no warehouse can fix "Spring", "spring" and "spring_2026". Whether keeping the tag in the session needs consent depends on your setup, which the consent article goes through.

Ad spend and email statistics (Google Ads and Mailchimp in the first version) arrive through connectors, and joining spend to orders at campaign level is in the warehouse article.

Worked example: stamping the campaign on the order

A migration adds three columns to spree_orders:

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

A concern in app/controllers/concerns/captures_campaign.rb, included in the storefront's base controller, remembers the tags and stamps them once:

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

A visitor in Salthill taps Aoife's autumn Meta ad. The link ends in ?utm_source=meta&utm_medium=paid_social&utm_campaign=autumn_sampler. The session remembers it. She adds a sampler tin to the basket, which creates the order, and on her next page view the order gets the three tags. She checks out the next evening in the same browser, and the nightly job copies the order with utm_campaign set to autumn_sampler. A later newsletter click would not overwrite it.

Sales by campaign: count each order where it happened

The first question is the easy one. Each order carries a campaign, so revenue by campaign is a sum grouped by the tag. I use goods after discounts, item_total + promo_total, because delivery is not something a campaign sells. And I leave cancelled orders out.

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

The time zone matters. completed_at is stored in UTC, and October in Galway starts an hour before October in UTC, because Irish clocks are still on summer time. Without the zone, the orders from the first hour of October count as September.

Worked example: October at Corrib Leaf

The query returns five rows for October 2026. No campaign tag: 610 orders, €18,300. newsletter_monthly: 290 orders, €9,860. google_brand: 180 orders, €6,120. autumn_sampler: 120 orders, €3,240. cafe_cards: 40 orders, €1,600.

Check the totals: 610 + 290 + 180 + 120 + 40 = 1,240 orders, and €18,300 + €9,860 + €6,120 + €3,240 + €1,600 = €39,120. That should match the Solidus admin for the same dates, give or take delivery and cancellations. A report that disagrees with the shop has a bug, not an insight. Note the newsletter in second place.

Customer value by campaign: credit the campaign that found the customer

The first version of this article called the total each customer has spent their "lifetime value", and then summed it per campaign by the campaign on each order. Both steps need correcting.

SUM(total) per customer is revenue to date. Lifetime value is a forecast of margin over the whole relationship, with retention and discounting in it, explained in Foundations: lifetime value.

And crediting each order to its own campaign answers "which campaign closed this sale", not "which found valuable customers". A newsletter cannot find a new customer, since you need their address to send it, yet it collects every repeat order it prompts. So give each customer to the campaign on their first order, and add up what they spent in a fixed window afterwards, because a customer found two years ago has had longer to spend than one found last month.

The average per campaign is a plain mean:

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

Here $\bar{v}_c$ is the average twelve month revenue per customer for campaign $c$, $n_c$ is the number of customers whose first order came from that campaign, and $v_i$ is what customer $i$ spent in the twelve months after their first order. The mean, and why one big spender can pull it a long way, is in Foundations: the mean.

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 `corrib-leaf-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 `corrib-leaf-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/Dublin')
    AND f.first_order_at <  TIMESTAMP('2025-10-01', 'Europe/Dublin')
  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

The first block finds each customer's first campaign and date. The second adds up their orders in the 365 days after it, for customers who first ordered between July and September 2025, so all have had a full year. The last averages per campaign. The second block is one row per customer before anything is summed per campaign, the "aggregate first" rule from the warehouse article.

Worked example: Corrib Leaf's summer 2025 customers

800 customers placed their first order between July and September 2025. The query returns:

• cafe_cards: 60 customers, €6,600 in their first year. €6,600 divided by 60 is €110 each.
• google_brand: 180 customers, €14,760. €14,760 divided by 180 is €82 each.
• (none): 320 customers, €24,000. €24,000 divided by 320 is €75 each.
• autumn_sampler (an early run of the Meta campaign, in September): 240 customers, €11,520. €11,520 divided by 240 is €48 each.

Check: 60 + 180 + 320 + 240 = 800 customers, and €6,600 + €14,760 + €24,000 + €11,520 = €56,880. The mean across all of them is €56,880 divided by 800, which is €71.10.

The horizontal axis is the campaign that brought each group. The vertical axis is what the average customer in it spent in their first twelve months, goods after discounts. The takeaway: the café cards bring few people but the best ones, more than twice the value of the Meta sampler customers, who mostly bought the €12 tin and stopped.

And the newsletter, second in October, does not appear here at all, because nobody's first order came from it. Both views are right, for different questions. The newsletter brings customers back, the café cards find customers worth bringing back.

Two cautions before Aoife moves her budget. Sixty customers is a small group that one big order could carry, so look at the median too and read Foundations: sampling before trusting the gap. And revenue is not margin: the sampler might still pay if it is the lowest cost way she finds new people.

Subscriptions are not in Solidus core

The first version of this article also queried a subscriptions table. A plain Solidus install does not have one. Subscriptions come from an extension, usually solidus_subscriptions, published under the Solidus organisation on GitHub. It generates an installment for each subscription cycle, and each installment is an order.²⁴ If your shop runs it, copy its tables with the same nightly MERGE and the same customer key. Corrib Leaf's tea box runs on it, so its monthly box orders arrive in orders like any others. The subscription tables only add who is subscribed and since when.

The checks I keep, so the bill and the numbers stay boring

Two settings stop the commonest surprise, a query reading far more than you meant. A dry run estimates the bytes a query would process without running it, as an upper bound, and a maximum bytes billed limit makes a query fail, free of charge, if its estimate is above the limit.²⁵ From Ruby, BIGQUERY.query_job(sql, dryrun: true).bytes_processed gives the first, and maximum_bytes_billed: on query_job sets the second. Also: LIMIT does not reduce what a query reads on an unclustered table, so it is no way to make a query on a big table less expensive.²⁵

The list I run through on a new shop, one line per section:

• One project, one dataset in europe-west1, billing linked and a budget alert set.
• A service account with Data Editor on the dataset and Job User on the project, its key in encrypted credentials.
• No email, address, name or note in BigQuery, only a keyed hash, with the key kept in the shop.
• NUMERIC for money, clustering before partitioning, partitioning only for big tables.
• A nightly export of the last 48 hours, loaded into a landing table and merged, so reruns are harmless.
• Live events in their own append only table, never merged into.
• A campaign tag on every order, and UTM names that are consistent before anything else.
• Every report checked against the Solidus admin before anyone acts on it.

None of this is clever, and that is the point. Analytics should be the dullest part of the stack, so the conversations it starts are about tea and customers, not whose numbers are right. From here, the warehouse article picks up the story.

Footnotes

1. Google Cloud, BigQuery locations, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/locations

2. Google Cloud, Try BigQuery using the sandbox, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/sandbox

3. Google Cloud, BigQuery pricing, Google Cloud, 2026, https://cloud.google.com/bigquery/pricing

4. Google Cloud, BigQuery IAM roles and permissions, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/access-control

5. Google Cloud, Best practices for managing service account keys, IAM documentation, 2026, https://docs.cloud.google.com/iam/docs/best-practices-for-managing-service-account-keys

6. Google, google-cloud-bigquery: lib/google/cloud/bigquery.rb (version 1.64.2), googleapis/google-cloud-ruby on 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 and spree_line_items), solidusio/solidus on GitHub, 2026, https://github.com/solidusio/solidus/blob/main/core/db/migrate/20160101010000_solidus_one_four.rb

8. Solidus, Order state machine definition, solidusio/solidus on GitHub, 2026, https://github.com/solidusio/solidus/blob/main/core/app/models/spree/core/state_machines/order/class_methods.rb

9. European Parliament and Council, Regulation (EU) 2016/679 (General Data Protection Regulation), Articles 4(5), 5(1)(c) and 28, EUR-Lex, 2016, https://eur-lex.europa.eu/eli/reg/2016/679/oj

10. Data Protection Commission, Principles of Data Protection, dataprotection.ie, 2026, https://dataprotection.ie/en/individuals/data-protection-basics/principles-data-protection

11. Data Protection Commission, Guidance on Anonymisation and Pseudonymisation, dataprotection.ie, 2019, https://www.dataprotection.ie/dpc-guidance/anonymisation-and-pseudonymisation

12. European Data Protection Board, Guidelines 01/2025 on Pseudonymisation, version for public consultation, EDPB, 2025, https://www.edpb.europa.eu/our-work-tools/documents/public-consultations/2025/guidelines-012025-pseudonymisation_en

13. Google Cloud, Cloud Data Processing Addendum, Google Cloud terms, 2026, https://cloud.google.com/terms/data-processing-addendum

14. Google Cloud, Introduction to partitioned tables, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/partitioned-tables

15. Google Cloud, Introduction to clustered tables, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/clustered-tables

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

17. Google Cloud, Data manipulation language (DML) statements in GoogleSQL: MERGE statement, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax

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

19. Solidus, core/lib/spree/core/engine.rb (registered events), solidusio/solidus on GitHub, 2026, https://github.com/solidusio/solidus/blob/main/core/lib/spree/core/engine.rb

20. Google Cloud, Use the Storage Write API (REST), BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/streaming-data-into-bigquery

21. Google Cloud, Introduction to the BigQuery Storage Write API, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/write-api

22. Google Cloud, Transform data with data manipulation language (DML): limitations, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/data-manipulation-language

23. Google, BigQuery Export schema, Analytics Help, 2026, https://support.google.com/analytics/answer/7029846

24. Solidus, solidus_subscriptions, solidusio on GitHub, 2026, https://github.com/solidusio/solidus_subscriptions

25. Google Cloud, Estimate and control costs, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/best-practices-costs