How to create a fully automated BigQuery ecommerce data warehouse with Eyk: get pre-built marketing insights in minutes

Pre-built insights

Eyk simplifies the marketing ELT (Extract, Load, Transform) process. Traditional approaches often require multiple connectors and extensive query logic, demanding significant time and technical expertise.

One of the most time-consuming aspects of marketing data analytics is building custom SQL models for all data sources and creating reports. Eyk provides a range of pre-built insights and Looker Studio report templates that can be deployed rapidly.

Data storage in BigQuery

Despite its streamlined approach, Eyk does not restrict data ownership, customization and flexibility. Because all data is stored in BigQuery, either hosted by Eyk (core plan) or in a self-owned Google Cloud project (advanced plan), users can engage with the extracted source data and create custom data models on top of the existing data models.

Eyk's ongoing development, with regular updates and new data source integrations, ensures that it remains relevant in the ever-changing landscape of ecommerce marketing.

Code-free approach

While Eyk presents an efficient and user-friendly option for data warehouse creation in BigQuery, it's essential to consider specific business needs and technical capabilities. The platform is well-suited for those looking for a streamlined, code-free approach to data integration and analysis.

However, businesses with a preference for highly customized data processing methods might find the pre-built nature of Eyk's tools less flexible than developing their own solutions.

This tutorial provides a detailed overview of Eyk, which integrates with numerous platforms like Shopify, Magento, GA4, Google Ads, Facebook, Instagram, LinkedIn, Pinterest, Semrush and Search Console.

Tutorial: data warehouse creation

Key prerequisites for using Eyk include a Google account and access to various marketing data tools. The tutorial will cover how to automatically collect data from these sources and manage it within a dedicated Google BigQuery environment.

Visit the Eyk trial page and click Continue with Google.

Make sure to log in with the Google account you want to use for your BigQuery environment.

Provide a name for your account, this can be the name of an organization. It will be visible in your Eyk environment and in the BigQuery console.

Click Create warehouse. Your data warehouse is being created and will be ready shortly.

The fist page you will see after the creation of the warehouse is the Overview.

Click Settings in the menu bar and select the Data warehouse tab. Here you'll find the details of your BigQuery project, data set and location.

Click on the link in the left bottom corner of your screen to switch to the BigQuery console. This is your confirmation that Eyk did their magic on the background and you actually have access to your newly created BigQuery environment.

Next step: add insights

Return to the Eyk platform and click Insights > Add insight to add your first out-of-the-box insight model. Let's select Paid Performance from the insight library and click Next.

Now we've selected an insight, we are asked to add the relevant data sources for this specific insight. For the Paid Performance insight we need to connect at least one source from the list (LinkedIn, Pinterest, Facebook Ads, Google Ads, Microsoft Advertising).

First we add LinkedIn. Click the Add (next to LinkedIn) > Next. On the next screen click Sign in and provide your LinkedIn credentials.

Click Allow when permission is asked for Eyk to process your advertising data.

If all goes well a message will tell you that the connection was successful. Now you can configure the data source.

Provide the account id(s) that you want to be synced. Leave empty if you want to pull the data from all associated accounts.

Then select the starting date for the historical data. Finally, select a refresh rate and click Add source.

The next thing you'll see is a spinning icon. Your data is now being processed.

Let's add another data source and select Facebook Ads as well.

Repeat the previous steps and follow the Facebook authentication flow. If all goes well, you will see a success message both in Facebook...

...as in the Eyk platform. Again, configure the data source as you wish.

Facebook data will start syncing. As you can see the LinkedIn data sync is already finished.

Finally we add Google Ads as a data source. Follow the Google Ads authentication flow.

While the data syncs are in progress, click Next and Add insight on the next screen.

On the Overview screen you can see the status of the connected insights.

Click on the insight to see how you can use the data. You have multiple options here:

• Go to insight table in BigQuery
• Go to Connected Sheets
• Open Looker Studio template

We need a bit of patience here, because the historical data of multiple data sources is still being synced.

This is also visible on the Overview.

Looker Studio report templates

In the mean time we will look at the Looker Studio template for the Paid Performance insight. It contains dummy data that Eyk has generated and made available publicly.

When our data syncs are ready, we can use these templates to replace the dummy data with our own insights with a couple of clicks: all powered by our own data warehouse in BigQuery, without writing a single line of code.

Let's see how the underlaying data model looks in BigQuery. This is a public data set, so you can query it from your own project.

select
  *
from
  `eykdata-eyk.public.paid_performance`

The result is a very detailed table, that is grouped by data source, date, campaign name, and so on.

We can do the same for the Ecommerce Attribution insight.

select
  *
from
  `eykdata-eyk.public.ecommerce_attribution_sessions`

Or the RFM Segmentation insight.

select
  *
from
  `eykdata-eyk.public.rfm_analysis_scores`

Now we have seen what is possible, let's check the status of our own data syncs. Looks like we have fresh data in our BigQuery environment!

As you can see Eyk created dedicated tables for each data source and also added an insight table paid_performance, based on their data model.

So even if you don't want to use the Looker Studio templates, you are free to use the BigQuery data in the way you want. You could:

• visualize the data in another tool (e.g. Power BI, Tableau)
• export data to Google Sheets
• perform analysis based on the source data
• create your own data models using SQL
• or just store the data for later use

What about GA4?

GA4 is available as a data source as well. This data source is pulling your data from the Google Analytics API and can be used in multiple insights (e.g. Website Performance, Daily Performance, Organic Traffic). But as all data source data is available in BigQuery, you are free to build your own additional models using your GA4 export data.

Eyk is adding more data sources and insights regularly, so you don't have to reinvent the wheel every for every marketing use case.

And to add a new insight, you only have to sync data sources you didn't sync before. In our case, we can add three additional insights, by adding just two extra data sources: Google Search Console and Google Analytics 4.

The other way around also works: select an existing data source and use the Monitoring tab to see which additional insights are available to add.

Whichever route you choose: within minutes, we will have a fully functional and managed marketing data warehouse running in BigQuery.

Now it's your turn!

I hope you've enjoyed this tutorial and feel a bit more confident to utilise your own ecommerce and marketing data in BigQuery. Drop a line in the comments if you have any questions, feedback or suggestions related to this article.