BigQuery + AI: the new cheat code for ecommerce analytics (no data team required)
A cheap Google database plus an AI assistant now gives Shopify brands the analytics that used to need a data team. Here's how it works, what it costs, and where it trips you up.
For years, “proper” ecommerce analytics came with a catch. If you wanted to join your Shopify orders to your ad spend, work out real margin by channel, or see how long customers actually take to buy, you needed one of two things: a data analyst who speaks SQL, or an expensive tool that did it for you (and usually didn’t quite do what you needed).
So most £1m–£10m brands settle. They live in the Shopify dashboard, squint at GA4, trust the ROAS figure Meta gives them, and stitch the rest together in a spreadsheet every Monday morning.
That trade-off is gone. Put your data in a database like Google BigQuery, pair it with an AI assistant, and you can answer questions that used to need a data team. You don’t need to be technical, just curious and a bit careful.
Here’s how it works, what it costs, and where it can go wrong.
The problem: your numbers live in different places
Every Shopify brand has the same mess:
- Shopify knows your orders, products, refunds and customers.
- GA4 knows what people did on the site and where they came from (sort of).
- Google Ads, Meta, Pinterest, TikTok each know what they spent and what they claim they drove.
- Your spreadsheet knows your product costs, your margin and the stuff nobody else tracks.
None of them talk to each other. Each one marks its own homework. So the questions that matter most to profit — which channel brings customers who come back? What’s my real margin after discounts, refunds and shipping? How long does it take someone to go from first visit to first order? — sit in the gaps between tools.
Dashboards can’t answer them because the data isn’t in one place.
Step one: get it all in one place (BigQuery)
BigQuery is Google’s cloud database. Ignore the enterprise-sounding name. For a brand your size, it’s basically a very large, very fast spreadsheet that can hold everything and doesn’t fall over at 50,000 rows.
Three reasons it suits Shopify brands:
1. It’s very cheap, often free. BigQuery has a permanent free tier: 10GB of storage and 1TB of queries every month. Most brands under £10m won’t get close to that for a long time. Even when you do, the bill is usually pocket change compared with a single analytics tool subscription.
2. GA4 plugs straight in for free. GA4 has a built-in BigQuery export, and there’s no GA4 fee for the export on a standard (free) property. You get raw, unsampled, event-level data: every page view, add to cart and purchase. Most GA4 problems people moan about (sampling, thresholds, “(not set)” everywhere) are much easier to see and fix in the raw data. One caveat: standard properties are capped at 1 million events a day on the daily export. That’s plenty for most brands at this size, but worth knowing.
3. Google Ads is free to bring in too. Google’s own BigQuery Data Transfer Service pulls in your Google Ads data daily. The connector is free; you only pay normal BigQuery storage and query costs, which usually sit inside the free tier.
Shopify, Meta and the rest need a connector (Fivetran, Airbyte and plenty of others do this). Some cost money, so start small: only sync the tables and fields you actually need. Connector bills grow fast when you sync everything “just in case”.
Once it’s all in, the magic is the join. Orders sit next to sessions, which sit next to ad spend, which sit next to your product costs. One source of truth you control, not one each platform controls.
Step two: let AI do the technical bit
This is where it changed.
The historic barrier to BigQuery was SQL, the language you use to ask a database questions. It’s not hard exactly, but it’s a skill, and most ecommerce managers have better things to do than learn window functions.
AI assistants are now very good at writing SQL. You describe what you want in plain English, and they write the query. Better still, many can now connect directly to BigQuery, look at your tables, run the query and tell you what came back.
So instead of this:
Learn SQL → work out your table structure → write the query → debug it for an hour → export to Sheets → build a chart
You do this:
“Show me repeat purchase rate by first-order acquisition channel for customers acquired in the last 12 months.”
And get the answer, plus the query behind it, in minutes.
What you can actually do with it
Here are the kinds of questions that go from “we’d need a data person for that” to “done by lunch”:
Real margin, not revenue. Join order lines to your product cost sheet and get gross margin by product, category, channel or campaign, after discounts and refunds. You’ll probably find a best-seller that barely makes money.
Time to purchase. How many days between first visit (or first sample, or first email sign-up) and first order? Use the median, not the average, because a few very slow buyers drag the average out. This one number changes how you judge campaigns. If your typical customer takes five weeks to buy, a seven-day attribution window is lying to you.
Customer quality by channel. Which channels bring customers who buy again, and which bring one-and-done discount hunters? Platform dashboards can’t tell you this. Your Shopify customer data can.
Offline and phone sales. If you sell through showrooms, a sales team or phone orders, you can match those sales back to online activity and send them to Google and Meta as offline conversions. The ad platforms then optimise towards customers who actually spend money, not just the ones who checkout online.
Tracking health checks. Compare Shopify orders with GA4 purchases day by day. If GA4 is missing 30% of orders, you want to know before you make budget decisions on it.
Ad hoc “why did that happen?” Revenue dipped last Tuesday. Was it traffic, conversion rate or order value? Which channel? Which device? Ask, get an answer, ask a follow-up. It’s a conversation, not a ticket in someone’s queue.
Then point Looker Studio (also free) at the results and you’ve got live dashboards that update themselves. No more Monday spreadsheet.
The honest bit: where this goes wrong
“Minimal technical knowledge” is true. “No knowledge” isn’t. AI makes the doing easy, but it doesn’t make the thinking optional. Watch for these:
AI is confidently wrong sometimes. It will write a query that runs perfectly and gives you the wrong number. A classic: double-counting revenue because it joined orders to order lines and summed the order total on every line. Always sense-check against a number you already trust. If Shopify says £84k last month and your query says £131k, something’s off.
Definitions matter more than SQL. What is “revenue” to you? Gross or net? Including VAT? Before or after refunds? Including shipping? The AI will pick something. Make sure it’s what you mean, and write your definitions down so every query uses the same ones.
Rubbish in, rubbish out. If your GA4 tracking is broken, BigQuery just gives you broken data faster. Fix the tracking first. (Our free GA4 Health Check is a good place to start.)
Keep an eye on costs. The free tier is generous, but a badly written query against a huge table can scan a lot of data. Set a budget alert in Google Cloud on day one. It takes two minutes.
Mind your customer data. You’re putting customer information in a database. Keep it in the right region (EU/UK if that’s where you operate), restrict who has access, and be careful what you paste into AI tools that aren’t connected through a proper, approved integration.
None of these are reasons not to do it. They’re reasons to do it with your eyes open.
How to get started this month
- Create a Google Cloud project and turn on BigQuery. Set a budget alert straight away.
- Link GA4 to BigQuery (Admin → Product links → BigQuery links). Turn on the daily export. Note it doesn’t backfill, so the sooner you start, the more history you’ll have.
- Set up the Google Ads Data Transfer for free daily ad data.
- Get your Shopify orders in with a connector. Start with just orders, order lines and customers.
- Upload your product costs from a Google Sheet (BigQuery can read it directly).
- Ask your first question. Pick one thing you’ve always wanted to know. Get the AI to write the query, then check the answer against something you trust.
Start with one question. Get it right. Then ask the next one.
The bottom line
The brands that win the next few years won’t be the ones with the biggest ad budgets. They’ll be the ones that know their numbers: real margin, real customer value, real channel performance. Not what each platform says about itself.
That used to need a data team. Now it needs a cheap database, an AI assistant and someone who asks good questions.
Not sure your tracking is solid enough to build on? Take the free GA4 Health Check first. And if you’d rather someone set it up properly the first time, with the definitions agreed and the dashboards built, that’s exactly what Reporting & Dashboards is for.