Data warehousing for marketing automation: from four tools to one customer

Every tool knows a piece of the customer, and none knows the whole

Most growing online shops end up with the same arrangement. The shop knows who bought what, the email tool who opened what, Google Ads what each campaign cost, Google Analytics which pages people visited. Each is good at its own job and blind to the others.

That is fine until a question crosses two of them. Which customers from the spring campaign have stopped ordering? No single tool holds both halves, so the answer comes from someone exporting spreadsheets on a Friday afternoon, and by then the moment to act has gone.

A data warehouse is the fix I reach for: one place where a copy of every source lands on a schedule, gets joined on the customer, and turns into lists that flow back out to the tools that act. This article walks that loop from start to finish.

Throughout I use one invented business. Kilbrack Roasters is an online coffee roaster in Galway, run by Aoife, selling one off bags of beans and a monthly subscription. Its shop lives at kilbrackroasters.example. Nobody real, and every number below is made up so I can show every step.

Worked example: one question, four tools

Aoife wants to know which customers bought at least twice, have not ordered for six weeks, and are allowed to receive marketing email. The order history sits in the shop database. The six weeks needs today's date and each customer's last order, also in the shop. The email permission sits in the email tool. And to know whether these people came from paid search, she needs the campaign tag captured with their first visit, which sits in Google Analytics.

Done by hand, that is three exports, a lookup between two sheets on email address, and a filter: about an hour. She does it once and never again, because an hour a week is an hour she would rather spend roasting. In a warehouse it is one query that runs every morning on its own.

A data warehouse is a database built for questions, not for running the shop

Your shop's database is built to take orders: write one row quickly, read one basket back. A warehouse is built for the opposite job, reading millions of rows at once to answer a question about all of them.

Google's BigQuery describes itself as a fully managed, serverless data warehouse, with storage and computing that work independently of each other.¹ In plain words: you do not run a server, you pay for what you store and for what your questions read, and a big question does not slow down storage. Snowflake is the other common choice and does the same job. I use BigQuery for most small and medium shops because the Google Analytics export lands there without any extra plumbing, so that is the one I show. The ideas carry over to Snowflake almost word for word.

The design trick that makes a warehouse fast is columnar storage. BigQuery stores each column of a table separately, because an analytical question usually needs only a few columns out of many.² A shop database stores each order as one row with everything about it side by side. A warehouse stores all the order dates together, all the totals together, and so on. Asking for monthly revenue then only touches two columns.

Worked example: why reading two columns costs less than reading thirty

Kilbrack's orders table has 30 columns: order number, customer, date, total, delivery address, and 25 more. Say the whole table is 6 GB and, to keep it simple, the columns are roughly the same size, so each is about 0.2 GB.

Aoife asks for revenue by month. That needs the order date and the total, two columns. A row store has to read whole rows, so it reads all 6 GB. A column store reads two columns of 0.2 GB each, which is 0.4 GB. That is one fifteenth of the data for the same answer, and on BigQuery's on demand pricing the bill follows the bytes read.³ The lesson that falls out of this is simple: name the columns you need, and never write SELECT * on a big table out of habit.

Getting the data in: copy every source on a schedule, then shape it

Data gets into a warehouse by being copied out of each tool on a timetable. The usual pattern is ELT (extract, load, transform): load the raw data untouched, then clean it inside the warehouse with SQL (Structured Query Language). Keeping the raw copy means a cleaning mistake is fixed by rerunning a query, not by going back to the source.

Some sources come with their own pipe. Google Analytics 4 can export its raw events to BigQuery once a day, and standard properties can also stream events within minutes, on what Google calls a best effort basis. Standard properties are capped at 1 million events a day for the daily export.⁴ Google Ads data arrives through a BigQuery connector that runs at most once a day, and by default it reloads the last seven days on each run, because ad platforms keep adjusting recent figures.⁵ For the other sources, a connector tool does the copying. Airbyte, for example, is open source and lists more than 600 ready made connectors.⁶

Here is the whole loop this article builds, from sources to actions.

Read it from the top. Each box is one stage, and the arrow means the stage below waits for the one above. The decision box is the consent check, which I come back to near the end. The takeaway is that this is a loop, not a line: whatever the email tool and the ads do today becomes tomorrow's raw data.

Worked example: Kilbrack's loading timetable

Aoife's setup copies four sources. The shop database is copied every hour by a connector, because orders are what the segments care about most. The Google Analytics daily export lands overnight, so each morning holds yesterday's visits. The Google Ads connector runs once a day and reloads the last seven days. The email tool's opens and unsubscribes are copied once a day.

So the freshest true join of ads, visits and orders is yesterday's. The old version of this article promised joins "every hour" and "near real time" decisions. With these sources that is not possible: the ad data is daily, and Google Analytics keeps updating a day's table for up to three days after the event.⁷ Hourly is honest for the shop's own orders. For anything touching ad cost, daily is the truth, and for coffee daily is grand.

One customer, one key: sort out identity before anything else

A join needs something the two tables share. For customers, that is usually an email address or a customer number. The trouble is that each tool stores the email a little differently. The shop has Aoife.Murphy@Example.com with a stray space, the email tool has aoife.murphy@example.com. To a computer those are two different people.

So the first cleaning query in any of my setups builds one customer table with a normalised email: spaces trimmed from both ends, everything in lower case. Google asks for exactly this normalisation before hashing emails for its ad audiences, which I come to later.⁸ Doing it once, in one place, means every later query agrees on who is who.

Google Analytics needs a separate thought. Its export carries a user_pseudo_id, assigned on someone's first visit, and a user_id, filled in only if your site sends your own customer number.⁷ Visitors who never log in cannot be tied to an order history. That is a limit, not a bug, and worth knowing before you promise anyone a complete picture.

Worked example: how much normalising buys

Kilbrack has 4,200 customers in the shop. Aoife joins them to the email tool's contacts on the raw email and gets 3,100 matches. After trimming and lower casing on both sides, she gets 3,900. She also checks how many customers can be found in the Google Analytics export by user_id, and gets 1,700, because only customers who log in to manage their subscription send one.

Each bar is one way of matching. The vertical axis is the share of Kilbrack's 4,200 shop customers that the method finds, from 0% to 100%. The takeaway: two lines of cleaning took the match from 74% to 93%, which is 800 customers who would otherwise have been invisible to every segment. The Analytics bar is low by nature, so I never build a segment that needs website behaviour for everyone.

The join that doubles your revenue: aggregate first, then join

This is the mistake I find most often in other people's warehouses, and the old version of this article made it too. Join two tables that each have several rows per customer, and the database pairs every row on one side with every row on the other. A customer with three orders and two rows in another table comes out as six rows. Sum the order totals over those six rows and each order is counted twice.

The old example also joined ad spend to customers by a user id. Ad platforms do not report spend per person. The Google Ads connector brings in reports at campaign, ad group and keyword level, with cost per campaign per day.⁵ So "lifetime ad spend per customer" is not a column you can join in. Ad cost meets customers through the campaign tag on their first visit, at campaign level.

Worked example: the six row customer

Take one Kilbrack customer, Seán. Seán has three orders: €24, €30 and €36, so €90 in total. Seán also has two rows in the email tool's table, because he signed up twice with two forms. A query that joins orders to email contacts and then sums the totals produces:

$$3 \times 2 = 6 \text{ rows}, \quad (24 + 30 + 36) \times 2 = 180$$

Here $3$ is Seán's number of orders, $2$ is his number of email rows, $6$ is the number of rows the join produces, and $180$ is the summed revenue in euro, exactly double the true €90. It is plain multiplication, but if a ratio built on top of it looks oddly high, this is usually why. Foundations: ratios and rates covers what goes wrong when the thing on the bottom of a ratio is counted wrongly.

The cure is to bring each table down to one row per customer before joining, and to use a left join so customers without a match are kept rather than silently dropped:

WITH orders_per_customer AS (
  SELECT customer_id,
         COUNT(*)          AS order_count,
         SUM(total_eur)    AS revenue_eur,
         MAX(order_date)   AS last_order_date
  FROM shop.orders
  GROUP BY customer_id
),
email_per_customer AS (
  SELECT email_normalised,
         LOGICAL_OR(marketing_consent) AS marketing_consent
  FROM email_tool.contacts
  GROUP BY email_normalised
)
SELECT c.customer_id,
       c.email_normalised,
       o.order_count,
       o.revenue_eur,
       o.last_order_date,
       COALESCE(e.marketing_consent, FALSE) AS marketing_consent
FROM core.customers AS c
LEFT JOIN orders_per_customer AS o ON o.customer_id = c.customer_id
LEFT JOIN email_per_customer  AS e ON e.email_normalised = c.email_normalised;

Now Seán is one row with €90, counted as consenting only if one of his sign ups said yes. The check I run after every join: rows out should equal customers in. If not, something has fanned out.

Worked example: ad cost at campaign level

Aoife ran a spring campaign for single origin beans. The Google Ads connector says it cost €1,800 in April. In the Analytics export, 60 people whose first visit carried that campaign's tag placed a first order in April. The cost per new customer is:

$$\text{CAC} = \frac{S}{N} = \frac{1{,}800}{60} = 30$$

Here $\text{CAC}$ is the customer acquisition cost in euro, $S$ is the campaign spend in euro, and $N$ is the number of new customers attributed to it. So each new customer cost €30. It is a rate like any other, explained in Foundations: ratios and rates. The warehouse then lets her follow those 60 customers forward and see whether their second and third orders pay that €30 back, which no single tool can do.

A segment is a query that runs every morning

Once the customer table is clean, a segment is just a question written in SQL whose answer is a list of people. The important part is that it runs on its own. In BigQuery that is a scheduled query, which runs any SQL statement on a timetable, as often as every five minutes, and can overwrite a results table each time (the setting is called WRITE_TRUNCATE).⁹ In Snowflake the same job is done by a task, which runs SQL on a fixed interval or a cron style timetable.¹⁰

The old version of this article showed a "plateau" query that filtered on SUM(value) and MAX(event_date) inside a WHERE clause. That does not run. WHERE filters rows before they are grouped, and conditions on totals belong in HAVING, which filters after grouping.¹¹ It is an easy slip, and it is a good reason to test every query on real data before it goes on a schedule.

Worked example: lapsed repeat buyers

Aoife's segment is customers who have ordered at least twice, whose last order is more than 42 days old, who do not hold a subscription, and who have marketing permission. Here it is, written against raw orders so the grouping is visible:

SELECT o.customer_id,
       c.email_normalised,
       COUNT(*)          AS order_count,
       MAX(o.order_date) AS last_order_date
FROM shop.orders AS o
JOIN core.customers AS c ON c.customer_id = o.customer_id
WHERE c.has_active_subscription = FALSE
  AND c.marketing_consent = TRUE
GROUP BY o.customer_id, c.email_normalised
HAVING COUNT(*) >= 2
   AND DATE_DIFF(CURRENT_DATE('Europe/Dublin'), MAX(o.order_date), DAY) > 42;

The WHERE lines filter customers before grouping, because they are facts about the customer. The HAVING lines filter after grouping, because the order count and the last order date only exist once the orders are added up. Scheduled for 6am each day with WRITE_TRUNCATE, it rewrites a table called segments.lapsed_repeat_buyers.

On the first run, 1,150 customers have at least two orders and 420 of them have not ordered for 42 days. Take out the 70 subscribers (not lapsed, just being delivered to) and the 40 without marketing permission, and 310 are left. Tomorrow some order and drop out, others cross the line and drop in. Nobody has to remember to rerun anything.

Sending segments back out to the tools that act

A segment sitting in the warehouse does nothing until it reaches the tool that sends the email or shows the advert. Copying it back out is often called reverse ETL: buy a tool for it, or write a small job that reads the segment table and calls each tool's API.

For the email side, I show Klaviyo here because its documentation is clear. Its profile import endpoint creates or updates one person, identified by email, and lets you set custom properties on them.¹² A property such as segment is then something the email tool can filter and trigger on.

import requests

API_KEY = "pk_your_private_key"   # kept in a secret store, never in code

def push_profile(email, properties):
    response = requests.post(
        "https://a.klaviyo.com/api/profile-import",
        headers={
            "Authorization": f"Klaviyo-API-Key {API_KEY}",
            "revision": "2026-10-15",
            "Content-Type": "application/vnd.api+json",
        },
        json={"data": {"type": "profile",
                       "attributes": {"email": email,
                                      "properties": properties}}},
        timeout=10,
    )
    response.raise_for_status()

push_profile("sean.walsh@example.com",
             {"segment": "lapsed_repeat_buyer", "last_order_date": "2026-09-14"})

The old version of this article posted to a Customer.io address that does not match its documented Track API, which lives on track.customer.io and updates a person with a PUT request.¹³ Whichever tool you use, take the address and the authentication from its current documentation, not from a blog post, this one included.

For the ad side, Google Ads takes customer lists through a feature called Customer Match. Emails are hashed with SHA256 before matching, either by you or by Google on upload, and Google asks you to trim spaces and lower case each address first.⁸ Hashing turns an email into a fixed string of letters and numbers that cannot be turned back, so the platform can match it against its own users without you handing over the address in the clear.

Worked example: Seán, both ways

The 6am query puts Seán in the segment. At 6.30am a small job reads the 310 rows and calls push_profile for each, setting segment to lapsed_repeat_buyer. In the email tool, Aoife has a flow that starts when a profile carries that value, and Seán receives a note about the new Colombian roast that morning.

For Customer Match, the same job takes Seán's normalised email, sean.walsh@example.com, and hashes it. The result is 305f3705e4993d0507398f1485c932bfd76f5cc7f2a8ba0509394398dd28ebd4, the same 64 characters every time for that address and a completely different string for any other. Those hashes go to a Customer Match list, but only for customers whose permissions cover it, which is the subject of the last section but one. For how the email and ad sides can trigger each other once both are fed from here, I wrote a separate piece on triggering ads from email activity, and for wiring the delivery itself, webhooks and serverless functions.

Trigger on movement, not only on clicks

Most marketing automation reacts to one person doing one thing: a form filled, a basket left. The warehouse adds a second kind of trigger, one that notices a number moving: a drop in the daily conversion rate, a cohort that stops reordering.

The simplest test for "this is unusual" is to ask how far today sits from normal, measured in standard deviations. Quality engineers have long drawn their alarm lines three standard deviations either side of the average.¹⁴

$$z = \frac{x - \bar{x}}{s}$$

Here $z$ is how unusual today is, in standard deviations, $x$ is today's value, $\bar{x}$ is the mean of the recent days you count as normal, and $s$ is their standard deviation. A $z$ below $-3$ or above $3$ is worth a look. The mean and the standard deviation are explained from scratch in Foundations: the mean and Foundations: spread.

Worked example: the Tuesday the checkout broke

Over the previous 28 days, Kilbrack's daily conversion rate (orders divided by visits) has a mean of 2.4% and a standard deviation of 0.3 percentage points. On Tuesday it is 1.4%.

$$z = \frac{1.4 - 2.4}{0.3} = \frac{-1.0}{0.3} = -3.33$$

Here $1.4$ is Tuesday's rate $x$, $2.4$ is the 28 day mean $\bar{x}$, and $0.3$ is the standard deviation $s$, all as percentages. Tuesday sits 3.33 standard deviations below normal, past the line of three. The scheduled query writes a row to an alerts table, and a job sends Aoife a message. She finds that a payment update has broken card payments on one browser.

The horizontal axis is the day, across two weeks. The vertical axis is the daily conversion rate. The upper line is the daily rate, the flat line is the alarm at 1.5%, which is the mean of 2.4% minus three times 0.3. The takeaway: thirteen ordinary days wobble between 2.1% and 2.7% and never come near the line, so nobody gets woken up by noise. Day 14 drops straight through it. One rule, written once, watches every day for her.

Worked example: is the winback voucher worth it

A trigger also needs a decision about what to do. Take the 310 lapsed repeat buyers. From past sends, Aoife estimates that 7% come back within a month after a plain email, and 12% after an email with a €5 voucher. A returning order earns her €14 of margin, and the voucher is only paid by the people who use it. The expected value per person emailed is:

$$E[\text{plain}] = 0.07 \times 14 = 0.98, \qquad E[\text{voucher}] = 0.12 \times (14 - 5) = 1.08$$

Here $E[\cdot]$ is the expected margin in euro per person emailed, $0.07$ and $0.12$ are the chances of a return, $14$ is the margin on a returning order in euro and $5$ is the voucher in euro. The voucher wins, but only by 10 cent a person, €31 across all 310. Close enough that I would test it rather than assume it, and easy to work out because margins and past response rates sit in the same place. Expected value is explained in Foundations: expected value. If you want to go further and predict who is about to lapse rather than wait for 42 days, that is survival analysis, which has its own article on churn and lifetime value.

Permission belongs in the warehouse, not just in the email tool

Putting every source in one place makes it very easy to use data for something it was not collected for. The law in Ireland is clear that you should not. Under the EU GDPR, personal data is collected for specified, explicit and legitimate purposes and is not further processed in a way that is incompatible with them, and it should be adequate, relevant and limited to what is necessary for those purposes.¹⁵ The Data Protection Commission (DPC) explains both principles in plain terms, including that data should be processed only where the purpose could not reasonably be met another way.¹⁶

For marketing email, the ePrivacy Regulations 2011 (S.I. No. 336 of 2011) apply on top. Regulation 13 says you need the person's consent, with one narrow exception for existing customers: you got their email in the context of a sale, you are marketing your own products of a similar kind, you gave them a clear and easy way to object when you collected their details and in every message since, and the sale was no more than 12 months before the email.¹⁷ The DPC's own guidance describes it the same way, as a limited exception for existing customers that holds only while those requirements are met.¹⁸ For ad audiences, Google's Customer Match policy requires your privacy policy to say you share customer data with third parties who provide services to you, and requires consent where the law calls for it.¹⁹ I am an engineer, not a solicitor, so for anything that goes beyond these basics I would ask one.

In practice I carry permission as columns in the customer table, one per channel, and put the check inside every segment query. A list that leaves the warehouse then cannot contain someone it should not. I also copy only the columns the segments need, which is data minimisation in practice. Vetting the tools you send data to is its own subject, covered in how data processors can get you fined.

Worked example: two permissions, two lists

Of Aoife's 310 lapsed repeat buyers, all 310 already passed the email permission check inside the segment query. For Customer Match she keeps a separate column, ads_audience_ok, set from her sign up form, where customers are told their details may be used to show them Kilbrack adverts on Google. 240 of the 310 have it. So the email list is 310 people and the Customer Match list is 240. The 70 others still get the email, and nobody has to remember to remove them from the ad list by hand, because they were never on it.

What it costs, and when you are not ready yet

For a business Kilbrack's size, the warehouse itself costs very little. BigQuery's free tier includes 1 TiB of query processing a month, and the sandbox, which needs no billing account, adds 10 GiB of storage.²⁰ The catch is that the sandbox does not support the Data Transfer Service, which scheduled queries and the Google Ads connector rely on, and its tables expire after 60 days.²⁰ So the moment you want anything to run on its own, you switch billing on. Queries are billed by the bytes they read, and a LIMIT on an ordinary table does not reduce what is read.³

Worked example: Kilbrack's monthly reading

Aoife's segment query reads the order and customer columns it needs, about 150 MB a run. The alert query reads the daily visits and orders, about 50 MB. Both run daily, so in a 30 day month they read:

$$(150 + 50) \times 30 = 6{,}000 \text{ MB} \approx 6 \text{ GB}$$

Here $150$ and $50$ are the megabytes each query reads per run and $30$ is the number of daily runs. Six gigabytes against a free allowance of about 1,000 is well inside it. The real cost of a warehouse is not the bill. It is the time to set it up properly and the discipline to keep the cleaning queries honest.

In my experience, a shop with a few hundred orders a month and one email tool gets most of the value from the built in integrations, and a warehouse would be a well engineered answer to a question nobody is asking yet. It pays when the questions cross tools, someone exports spreadsheets every week to answer them, and a wrong segment (a "we miss you" voucher to a loyal subscriber, say) starts to show up in replies. If you are setting one up on a Rails and Solidus shop, I have written up the BigQuery setup itself step by step.

The warehouse is the memory, the tools are the hands

The old version of this article called the warehouse the brain. I would put it more modestly now. It is the memory, the one place where everything that happened to a customer is written down in a form you can question. The email tool, the ad platforms and your team are the hands.

None of the loop is clever. Copy in on an honest schedule, clean the email, aggregate before you join, check permission inside the query, send by the documented API. It is the difference between automation that quietly does the right thing every day and automation that emails your best subscribers asking where they went.

Footnotes

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

2. Google Cloud, Overview of BigQuery storage, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/storage_overview

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

4. Google, BigQuery Export, Analytics Help, 2026, https://support.google.com/analytics/answer/9358801

5. Google Cloud, Load Google Ads data into BigQuery, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/google-ads-transfer

6. Airbyte, Platform, Airbyte documentation, 2026, https://docs.airbyte.com/platform/

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

8. Google, About the customer matching process, Google Ads Help, 2026, https://support.google.com/google-ads/answer/7474263

9. Google Cloud, Scheduling queries, BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/scheduling-queries

10. Snowflake, Introduction to tasks, Snowflake documentation, 2026, https://docs.snowflake.com/en/user-guide/tasks-intro

11. Google Cloud, Query syntax (GoogleSQL), BigQuery documentation, 2026, https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

12. Klaviyo, Create or Update Profile, Klaviyo developer documentation, 2026, https://developers.klaviyo.com/en/reference/create_or_update_profile

13. Customer.io, Track API reference, Customer.io documentation, 2026, https://docs.customer.io/api/track/

14. NIST and SEMATECH, What are control charts?, Engineering Statistics Handbook, National Institute of Standards and Technology, 2012, https://www.itl.nist.gov/div898/handbook/pmc/section3/pmc31.htm

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

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

17. Government of Ireland, European Communities (Electronic Communications Networks and Services) (Privacy and Electronic Communications) Regulations 2011, S.I. No. 336 of 2011, regulation 13, Irish Statute Book, 2011, https://www.irishstatutebook.ie/eli/2011/si/336/made/en/print

18. Data Protection Commission, FAQ on Consent for Electronic Direct Marketing, dataprotection.ie, 2026, https://www.dataprotection.ie/dpc-guidance/faq-consent-electronic-direct-marketing

19. Google, Customer Match policy, Advertising Policies Help, 2026, https://support.google.com/adspolicy/answer/6299717

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