How to Get Marketing and Webshop Data Into Your BI Tool

Every BI tool can connect to a store and an ad platform. The connection is rarely what goes wrong. What goes wrong is that each report ends up with its own idea of revenue, its own way of matching ad spend to orders, and its own refresh moment, so two reports opened in the same meeting disagree. Which route you choose to get data into the tool decides where those definitions live, and that matters more than the brand of the tool.

This guide compares the three routes, shows where each one breaks, and works through the details for Data Studio, the tool most webshops know as Looker Studio, with a shorter section on Power BI.

What a BI data pipeline is

A BI data pipeline is everything between the systems where your business happens and the chart someone looks at. It has four steps: taking data out of the store, the ad platforms, analytics and the email tool; storing it in one place; shaping it into tables where an order, a customer and a euro of spend mean one thing; and showing it in a report.

Every route below does all four steps. They differ in where the third step happens. That step is where "revenue" becomes revenue without VAT, after discounts and returns, and where a Meta campaign and a Shopify order get connected. If it happens inside each report, you have as many definitions as reports.

The three routes

The first route fits one or two sources and one report builder, and its first failure is two reports with different revenue. The second fits companies with data engineers, where the first failure is usually a broken sync nobody was watching. The third fits marketing and ecommerce teams without engineers, and its limit is a source the platform does not cover yet. With the first route you also have no history of your own: you get what the source still returns. The other two keep everything loaded since day one.

Route 1: connectors straight into the BI tool

You add a connector per source and build charts on what comes back. For one source this is fine. A Google Ads report on the Google Ads connector is a good report.

It breaks when you combine sources. To show ad spend next to store revenue, you join data inside the report, and the logic for that join lives in that one report. The next report builds it again, slightly differently. Take a month in which customers paid €261,360 at checkout, of which €189,000 is left for products after VAT, shipping, discounts and returns. One report builder takes the store's total, another takes it without tax, a third subtracts refunds. Three marketing cost ratios, one company.

History is the second problem. A connector returns what the platform's interface still holds. When a platform limits how far back you can ask, or changes how it counts conversions, your older numbers change or disappear with it.

Route 2: your own warehouse in between

A loading tool copies each source into a warehouse such as BigQuery. Your team writes the models that turn raw data into reporting tables, and the BI tool reads those tables. Definitions now live in one place, history is yours, and any tool can read the same tables.

This is the right architecture. The cost is the people. Someone has to notice when a sync stops on a Saturday, update the models when a platform renames a field, and decide what net revenue means in code. We added up what that takes in The Real Cost of the Self-Built Data Stack. If you are not sure you need a warehouse yet, read When Do You Need a Data Warehouse? Four Signals first.

Route 3: a managed platform on a warehouse you own

The same architecture, with the loading and modeling run for you. The data lands in a warehouse in your own cloud account, already shaped into tables for orders, customers, spend and sessions, and the BI tool reads those. You keep the benefits of route 2 without the pipeline maintenance, and because the warehouse is yours, the data stays if you change vendors.

The limit is coverage. A managed platform supports the sources it supports. Check the list against yours before you start, and ask what happens with the one that is missing.

Worked example: Looker Studio, now called Data Studio

First the name, because it causes confusion. Google's documentation states that the Looker Studio name returned to Data Studio in April 2026, and that the old address redirects to the new one. Existing reports keep working. Google describes it as a no cost tool, with a paid Pro edition next to it. What follows is from Google's documentation as read on November 13, 2026.

Why direct connectors run out

Data Studio has connectors built by Google, for products such as BigQuery, Google Sheets and Google Analytics, and connectors built by third party developers for other platforms. To combine sources inside a report you use a blend. Google documents the limits in how blends work:

• A blend can include up to five tables.
• Joins only support equality between fields.
• A blend is always embedded in the report where you create it. It cannot be shared with another report.

That last point is the definition problem in one sentence. The logic that connects ad spend to store revenue exists inside one report, and the next report starts from zero.

Calculated fields have the same split. A calculated field in a data source is available in every report that uses that data source, which is what you want for something like net revenue. But Google's page on calculated fields notes that data source calculated fields cannot be used with blended data, and a calculated field made in a chart exists only in that chart. So the moment you blend store and ad data, your shared formula is out of reach and someone types it again.

Freshness

Data Studio keeps query results in memory and fetches new data when a data source's freshness window has passed. According to Manage data freshness, the Google Ads and Search Console connectors refresh every 12 hours and that cannot be changed, a BigQuery data source can be set anywhere from every minute to every 12 hours with 12 hours as the default, and Google Sheets defaults to 15 minutes. A blend takes its freshness from the data sources underneath.

For a report that mixes several direct connectors, this means the tiles on one page can be from different moments. Spend from this morning next to orders from fifteen minutes ago makes today's ratio look better or worse than it is. With one warehouse underneath, everything on the page is as fresh as the last load, and you know when that was.

Reading BigQuery from Data Studio

Routes 2 and 3 both end here, and the setup is short. Per Google's BigQuery connector page:

• Access. The account that runs the queries needs permission to read the data and to run query jobs, and you choose a billing project that pays for them.
• Credentials. With owner's credentials, the default, only the data source owner needs BigQuery access and viewers see the charts without it. With viewer's credentials every viewer needs their own access. A service account is the third option, and the one to prefer for reports that should survive a colleague leaving.
• Cost. BigQuery's usual query costs apply every time Data Studio queries the project, including refreshes. Google notes that partitioned tables make charts faster and cheaper.

Three habits keep this pleasant. Connect to finished reporting tables and leave the raw source tables alone, so the report does no modeling. Create the data source once from the home page so it is reusable, and build every report on it. And set freshness to match how often the warehouse loads: if orders load every hour, a one minute setting only buys you a larger bill. What BigQuery costs in practice for a team like yours is worked out in What BigQuery Actually Costs for a Marketing Team.

For small reports there is also data extract: a static copy of part of a data source, limited to 100 MB, that updates on a schedule you set.

If you are weighing Data Studio against a platform that brings its own dashboards, we set the two side by side in Verity compared with Looker Studio.

Power BI in short

The same reasoning holds in Power BI, with different mechanics. From Microsoft's documentation, read on the same day:

• The Google BigQuery connector is generally available and supports both importing the data and querying BigQuery live, which Microsoft calls DirectQuery. In the Power BI service it uses a cloud to cloud connection.
• Only imported models need a refresh. Microsoft's page on data refresh puts the limit at eight scheduled refreshes a day on shared capacity and 48 on Premium or Fabric capacity.
• A data gateway is only needed when the Power BI service cannot reach a source directly over the internet, such as a database inside your office network. A webshop whose data sits in BigQuery does not need one for that data.

For daily marketing and store reporting, importing once or a few times a day is usually enough and keeps the BigQuery bill predictable. One caution: Power BI has its own formula layer. If the warehouse table already holds net revenue, let the report read it. A second calculation of the same number in the report is a second definition.

Marketing data and finance BI

Finance BI is reporting on the bookkeeping: the ledger, budgets, cash and margin per business unit. It usually lives in a BI suite, close to the accounting system, and it has a stricter standard than marketing reporting, because the numbers have to tie to the books.

Marketing data enters that picture in two places: revenue per channel and ad spend as a cost line. Both go wrong for the same reason. The shop counts an order on the day it was placed and the bookkeeping counts an invoice on the day it was booked, and returns land in different months in each. Before a marketing number appears in a finance report, agree which system is the source for revenue, on which date basis, and how returns are assigned to a month. Write that down once. The guide to the ecommerce KPI scorecard has a template for it.

A sensible split is to let the BI suite own company wide and financial reporting, and to give the marketing team a faster tool for its own daily questions, with both reading the same warehouse tables.

Where Verity fits

Verity is route 3 for marketing and ecommerce data. It loads your store, ad, analytics and email sources into your own BigQuery and models them there with definitions you approve. Because the project is yours, Data Studio, Power BI or any other tool that reads BigQuery can use the same tables for company wide and financial reporting. Verity does not replace a BI suite for all company data. For marketing and store performance it brings its own dashboards, reports and AI analytics on those definitions, and an assistant can work with them through MCP, which we cover in MCP for Your BI Data.

Frequently asked questions

Is Looker Studio the same as Data Studio?

Yes. According to Google's documentation, the Looker Studio name returned to its original name, Data Studio, in April 2026. Existing reports keep working and the old address redirects to the new one.

Can I skip the warehouse and blend sources in the report?

For one report with two sources, yes. Beyond that the blend limits and the fact that a blend cannot be reused across reports mean every report rebuilds the same logic. That is the point at which a warehouse starts paying for itself.

How fresh is the data in my report?

As fresh as the slowest step. With direct connectors that is the connector's own refresh interval, which for several Google marketing connectors is fixed at 12 hours. With a warehouse it is the last load plus the freshness setting of the data source or the refresh schedule of the BI tool.

Do I need a gateway to use Power BI with BigQuery?

No. Microsoft documents a cloud to cloud connection for BigQuery in the Power BI service. A gateway comes into play for sources the service cannot reach over the internet, for example an accounting database on your own network.

Should finance and marketing use the same BI tool?

They should use the same definitions and the same tables. The tool on top can differ. Finance needs reporting that ties to the books, and marketing needs to ask new questions every day, and few tools are equally good at both.