Having worked with web analytics since 2014, I’ve seen how approaches and tools for correct data collection and processing change every year. I decided to describe the stack and methods that, in my opinion, are most relevant in 2026. This vision is tailored for medium-sized businesses, ranging from 1,000 to one million daily users.

Collection

• GTM Web + CMP: GTM remains a convenient tool for configuring tags and conversions via CSS selectors or, more reliably, through dataLayer. Integration with CMP services and native consent settings eliminates many headaches. However, standard GTM is no longer enough; due to ad blockers and browser privacy features, 10–30% of traffic often goes unrecognized.
• sGTM (Custom Loader, 1st Party Cookies): Server-side GTM is now mandatory for accurate data collection. It allows setting cookies server-side, protecting them from extensions, and even renaming the GTM JS file to bypass blocklists. It also enables direct data transfer to BigQuery, bypassing standard GA4 processing for better accuracy.
• MMP (Mobile Measurement Partner): For iOS or Android apps, services like Adjust or AppsFlyer are essential for attributing installs using identifiers like IDFA/AAID or probabilistic modeling (fingerprinting) where compliant. Adjust is a preferred choice, though most top-tier MMPs offer similar core functionality.

Ingestion & Warehousing

• Streaming (Real-time): Using sGTM or GA4’s intraday export allows streaming data directly into BigQuery for operational dashboards and ML applications.
• Batch (GA4 Export): The structured GA4 BigQuery export remains the gold standard for deep analysis and building predictive models.

Transformation & Modeling

• dbt (Data Build Tool): Essential for orchestrating SQL queries, cleaning data, and building Identity Graphs or custom attribution models.

Efficiency: Incremental models and daily partitioning significantly reduce BigQuery costs and increase processing speed. Dependencies allow for modular table construction (A -> B -> C), making the system cost-effective and scalable.

Intelligence (AI/ML)

• BQ ML: Google Cloud makes it easy to apply ML to BigQuery data for:
• pLTV: Predicting long-term value to signal ad systems about high-value customers.
• Segmentation: Automated user clustering for personalized communication.
• Churn Prediction: Identifying at-risk customers early.
• Recommendation Systems: Providing complex, personalized user suggestions.

Activation

• S2S API (CAPI): The standard for sending conversions to ad platforms. By controlling data flow from BigQuery, you can enrich conversions with personal data, often seeing a 15–30% increase in attributed conversions compared to standard browser tracking.
• Reverse ETL: Syncing BigQuery data back into operational tools for:

Dynamic Remarketing: Moving cart abandoners from GA4/BQ to FB Ads.

Audience Exclusion: Removing recent buyers or those with open support tickets from ad lists.

Enhanced Lookalikes: Exporting top 10% pLTV customers to Google Ads.

Lead Scoring: Prioritizing “hot” leads in the CRM for sales teams.

Visualization

• Looker Studio: While PowerBI or Tableau are full BI platforms, Looker Studio excels within the Google stack due to native connectivity and support for partitioned tables, which keeps costs low. If all logic is already handled in BigQuery via dbt, Looker Studio is often the most efficient choice for visualization.

The Ideal Web Analytics Setup in 2026 was originally published in Beards Analytics 🇺🇦 on Medium, where people are continuing the conversation by highlighting and responding to this story.

This is an opportunity to create your pipeline with an API call, data transformation, use loops, and other logical constructions.

The interface is interactive but still raw, with bugs. But it’s an excellent tool that, without code, can significantly speed up the creation of various marketing automation.

Pricing

If we compare the prices with volumes of less than 800,000 operations, Application Integration is two times cheaper.

Intergromat pricing

Application Integration pricing

An example of automation for Facebook offline conversion

If you have a task to transfer the fact of conversion to Facebook with your logic, GCP can help with this.

First, you need to configure the transfer of the value of the fbp/fbc cookie as user properties in Google Tag Manager. You can do it in the configuration tag for GA4:

Where the type of fbp/fbc variable is first-party cookies:

After that, you need to enable data upload from Google Analytics 4 to Google BigQuery. You can do it in the settings from the side of Google Analytics.

Here is the code to create a table with connected to Application Integration:

fb_offline_conv_application_integration/ga4_fb.sql at main · snegireff/fb_offline_conv_application_integration

Then we go to https://console.cloud.google.com/integrations/ and create a new integration. Along the way, include all the necessary APIs and download the configuration file:

You also need to integrate with Google BigQuery in the from_bq module, make changes to fb_url, specify your pixel, add a token, and publish.

After that, you run it and see the result:

Offline conversion for Facebook with Application Integration on the Google Cloud Platform was originally published in Beards Analytics 🇺🇦 on Medium, where people are continuing the conversation by highlighting and responding to this story.

We use DWH based on Google BigQuery, which helps to make good reports with its logic. These reports allow our clients to get various answers quickly and safely, including concerning marketing costs.

If the company has several websites, mobile applications, and different traffic sources, such as Google ADS, Facebook ADS, Twitter ADS, LinkedIn ADS, and others, it isn’t easy to see marketing cost efficiency in one place.

Usually for this

- Use simple tools such as Google Analytics that don’t allow you to combine different data sources correctly;

- Look at separate insight account for each source;

- Combine all data on Google Sheets.

Regarding Google Analytics, it is a good tool. Still, it has its calculation logic, and it is not possible to calculate your metrics, especially if it is metrics from other data sources (such as an internal database).

For example, here is such a case: on a particular day, there was a fact of registration of the user, after which they made a few more important actions, but when it happened, it was not important for the business. Therefore, in one report, we can show the situation with the actions today and those that will occur in the future, with a binding to the registration date.

In addition, combining different data, such as costs, in Google Analytics is often done with simple keys, only by the name of campaigns, source, and date. But in general, if the structure of our advertising campaigns includes country, device, and language, then it is possible to use these parameters as a filter.

Firstly, working with Google Sheets is uncomfortable. It takes a lot of time to correctly collect all the data from various sources. As a result, often, this report compiles only once a month, making it impossible to decide quickly on changes in advertising campaigns. Also, there is no way to drill down this report. Usually, it is the level of ad sources or campaigns, not ad groups or ads.

Now, the capabilities of Google Data Studio allow you to drill down different levels in a table:

Using the down/up arrows, we can look at different parameters.

Comparison of different periods on Google Data Studio

If you expand the functionality of Google Data Studio a little, you can get opportunities in the displayed data. For example, you can compare different periods. If you need to compare some metrics with the previous period or the last year, you can do it (by simply switching the green switch).

Calculated metrics on GDS

Usually, in ready-made analytics systems, indicators are calculated from some metric, for example, sessions or users. That is, you can see what percentage of users took helpful action. But often, you also need to see the percentages between different activities.

You can do this simply on Data Studio:

Data from the backend to GDS

For the correct calculation of marketing effectiveness, it is often necessary to take data from internal systems, such as a database or CRM. It cannot be easy to load this data into Google Analytics correctly, and in many cases, it is not possible.

For example, there is a situation where there can be many users under one google client ID (user_id) or vice versa. One user has multiple devices at once (home computer, work computer, tablet, phone). In such a case, it is impossible to see correct statistics in conditional Google Analytics.

There is a big question about where and how such data is stored and who has access to it. In cases with Google BigQuery, you can only provide access to the data necessary for a specific user’s work.

We do all complex data calculations in DBT, which connect to the Source Repository, which helps to use logic to calculate metrics and control the process of changes.

Four tips on how to make your GDS reports faster and easier to use

• The reports generate in 1–2 seconds, even with large volumes of data.

Using DBT helps not only to perform complex calculations but also to create optimized tables that work quickly with Data Studio.

• You can use filtering directly in the data.

This is a great opportunity when the tables you work with are filters. That is, by clicking on a variable, the entire report displays data only on it. It helps a lot in data analysis.

• Show any deviations on small charts.

For essential metrics, we also create small graphs showing this metric’s trend, so you can immediately see if something is wrong.

• Charts, funnels, and tables at once

It’s convenient when there are both tables with data and graphs showing how the situation changes over time on one report sheet.

Report that saves your marketing team 40 hours every month was originally published in Beards Analytics 🇺🇦 on Medium, where people are continuing the conversation by highlighting and responding to this story.

Therefore, when it comes to optimizing advertising campaigns, using not the fact of registration but beneficial action. A substantial time lag does not allow algorithms, for example, in Google Ads, to conduct optimizing advertising campaigns well.

A good solution would be to know at the time of registration which user will perform this beneficial action and, if possible, how much this user will bring to the business. Such data made it possible to solve several problems at once.

Firstly, it improves Ad bidding algorithms in Ad networks and optimizes Ad campaigns to attract more relevant audiences.

Secondly, knowing which clients will give what result, it is possible to use different communication channels with the client. For less valuable users, you can use remarketing, and for those who can potentially bring a lot of money, you can call and conduct personal product presentations.

Lastly, it allows you to organize profit at current costs and manage marketing costs more stably.

Together with our ML partners (https://www.beinf.ai/), we described how you could predict the practical actions of users in businesses with a long decision time.

General scheme of work

It is essential to have as much data to predict user actions. You can use any data:

• data that the user provides during registration,
• data about the behavior on the site/app,
• data the user used by,
• open data on the economic state of countries.

All this data must be stored somewhere, regularly updated, and actualized. We create a Data warehouse (DW) as a single source of truth. We use a serverless, highly scalable, and cost-effective multi-cloud data warehouse designed for business agility — Google BigQuery. The most considerable amount of data, as a rule, is data on client events from an application or website. Where Google Analytics 4 has a native and, most importantly, free connector to Google BigQuery.

Preparing data for models

We use DBT to work with data stored in Google BigQuery conveniently. Due to incremental_strategy = ‘insert_overwrite’ in DBT, it is possible to work with incoming data for a low cost.

The data must be valid! It is possible to check their correctness at each stage of the transformation by using DBT. Combine data from various data sources and get a single table that you can feed into the work of the ML algorithm, which calculates the probability of conversion for each user.

Features engineering

In order for the model to make predictions about the user’s LTV, we should pass to the model as much useful information as possible. Each information about the user that is used in the model is called a feature and the more useful features we give to the model, the more accurately it will be able to predict the user’s LTV.

Examples of features that we can create: which country the user is from, how many pages he visited on the website, the number of days since registration, user’s device info, etc.

Model validation

In order to evaluate how well the model performs, we run it on existing data, get its predictions, and see how closely predictions match the actual values.

There are a lot of model evaluation metrics, for example, there is a metric called “accuracy”, which shows the percentage of the model’s right predictions.

Models are also evaluated on data that it has not seen before, and thanks to this, we can accurately determine its predictive performance.

Automation of data transfer in Google Ads

It is extremely simple to create a conversion in Google Ads.

There are two ways to do this:

• Conversions from clicks using Google Click Identifier (GCLID)
• Enhanced conversions for leads

The first option involves sending custom conversion data and Google Click ID. An identifier automatically appears in the query parameters when switching from Google Ads.

When the user opens the site for the first time, the client_id from Google Analytics 4 stores in the cookie, by which, in the future, it is possible to link between the ad click and the predicted conversion value, for example, LTV.

The second method (Enhanced conversions for leads) involves transferring personal data, such as email or address (first name, last name, postal code, and country are required if you choose to use this data).

You can upload the conversion values with user ID manually or using API.

Results

Finally, we need to evaluate how well the model performs and in what cases it makes wrong predictions. To calculate LTV, the model predicts whether the user will have conversion or not.

In order to make this evaluation, we build a matrix, that is called the confusion matrix. Each row is a prediction of the model (Positive — the model predicts that the user will have a conversion, Negative — the model predicts that there will be no conversion), and each column is the actual value ​​(True — there was a conversion, False — there was no conversion) and in each cell is the percentage of users. For example, in the picture above, in the False Negative cell, there is 86.6%, which means that for 86.6% of users, the model said there would be no conversion, and in fact, it wasn’t.

If you have a considerable budget and think that you haven’t the possibility of increasing ROI, try to work with our data engineering and ML teams.

Prediction of LTV for Google Ads bidding optimization was originally published in Beards Analytics 🇺🇦 on Medium, where people are continuing the conversation by highlighting and responding to this story.

In addition, without users, the application itself does not make sense, and attracting traffic is the next step after creating the application. And most often, this is paid or conditionally paid traffic that we can manage.

Analytical tools most often solve two main blocks of tasks:

— User behavior within the application

— Evaluation of the effectiveness of advertising campaigns

In this article, I don’t compare all the tools on the market. I talk about those that we use in our work.

User behavior analysis for app

When we launched the application, it seemed to us that users were comfortable using it. But this may be a misconception. Or, if we want to add some new functionality to the application, we need to understand how users perceive it. Most often, if the application brings money, there is a specific funnel, from opening the application to the fact of purchase.

Firebase

https://firebase.google.com/docs/analytics

Firebase is a shareware tool that integrates with Google Analytics and Google BigQuery (to get raw data). It is a market standard.

Allows you to solve many tasks related to analyzing user behavior within the application, building funnels, segment intersections, and analyzing user cohorts.

It also has a report builder and the ability to run simple a/b tests. Also, Firebase has many tools to help develop a mobile application and hosting.

In addition, a simple way to get raw data allows you to build your custom analytics or automation based on the Google Cloud Platform.

In my opinion, you should install this tool in the app.

Amplitude

Amplitude is a user behavior analytics tool with more functionality than Firebase in terms of reporting.

Many product analysts use the capabilities of Amplitude to solve problems in behavior analysis. There is real-time analytics, the ability to build complex funnels, and user segmentation. ML algorithms are used to generate insights and recommendations.

But unlike Firebase, getting raw data is much more complex. In addition, the tool is somewhat complicated, and it will take time to learn it.

Flurry solutions from Yahoo and Mixpanel deserve attention, but they repeat the above in terms of functionality.

Mobile app traffic analysis

If analyzing behavior is relatively simple and many tools provide approximately the same functionality, analyzing traffic is much more difficult. It relates to devices on IOS.

In the case of websites, the attribution task is relatively simple. After clicking on an advertisement, if there are UTM tags or click ID, the data on them is immediately stored in the website analytics. That allows you to build accurate attribution simply.

The situation is different for mobile applications. First, users have to use an App Store. The App Store gives very little information about the sources of install traffic, and this information is aggregated. We receive the fact of installation, but subsequent actions, such as registration or purchase, don’t receive.

Secondly, due to IOS updates in 2021, we can only get accurate data for Apple devices if the user agrees.

Adjust/ Appsflyer

These tools are similar in functionality. Both allow you to connect to advertising systems such as Google Ads/Facebook Ads, Facebook Ads, Twitter, and TikTok and send the installation and useful actions back.

It is possible to create deep links for all other traffic sources and the ability to save raw data.

We prefer an Adjust. It is more convenient to work, but both do their job well.

https://www.adjust.com/

https://www.appsflyer.com/

Firebase dynamic link

Firebase itself integrates nicely with Google Ads and has a deep link. This technology, based on fingerprint, allows you to link the transition to the App Store and the installation of the application and track the user’s subsequent actions.

We often use it to track push/email campaigns and build links with the website, referral systems, and traffic sources that do not natively integrate with Adjust/Appsflyer.

And this tool is free.

https://firebase.google.com/docs/dynamic-links

Facebook analytics

In case you use Facebook Ads, installation of Facebook SDK is mandatory. Due to this, Facebook advertising accounts can track installations and other user actions in the application.

https://www.facebook.com/business/help/673327316061433

Apple frameworks

In connection with the IOS update, you must install the following frameworks to collect analytics:

— SKAdNetwork

Allows advertising systems to receive aggregated data about installing a mobile application.

https://developer.apple.com/documentation/storekit/skadnetwork

— App Tracking Transparency

The framework allows you to access the IDFA ID (important for getting insights).

https://developer.apple.com/documentation/apptrackingtransparency

Please feel free to ask a question!

Minimum set of analytical tools when launching a new app was originally published in Beards Analytics 🇺🇦 on Medium, where people are continuing the conversation by highlighting and responding to this story.

Data Studio has a lot of connectors to connect for different sources including a lot of google products. Google BigQuery is one of the entrances to Google Cloud.

Besides, you can use the pay connectors, for example from supermetrics or opensource connectors.

Similarly, the types of visualizations expand actively. You can find interesting options in the Community Gallery.

Sometimes recommended to look in there to explore new types of decisions. You need to turn on job opportunities with Community Visualization Access, to have an opportunity to use it.

Simple and not the most effective way

Actually, we can simply connect the table we need by selecting BigQuery in the list of available connectors.

In this case you have access to the whole table firebase / app+web with improved displaying metrics and parameters. How usual tables in BigQuery interface have metrics users, events count and it’s all about the money.

All event parameters and user-properties are already laid out, and by creating your own functions, you can access the attributes.

For example, to access the page location parameter in the pageview event, you can create new Page fields and write this formula:

case when Event Name = ‘page_view’ and Event Param Name = ‘page_location’ then Event Param Value (String) else ‘’ end

Funnel visualization

What was lacked is the ability to build a funnel based on the events or pages, with the right conditions. Recently, the People from powermyanalytics.com added this feature:

For example, if we want to build a funnel based on certain pages, we can create several metrics by specifying these values:

CASE

WHEN Event Name = ‘session_start’ AND Event Param Name = ‘page_location’ AND CONTAINS_TEXT(Event Param Value (String), ‘https://web-analytics.me/') THEN 1

ELSE 0

END

When we are working with filters, we can include only certain segment of the audience, for example only google traffic:

After adding the necessary metrics, each of which will correspond to a separate step in our funnel, we can get a similar result:

When necessary, we can count a unique user, not by the number of page views. We can change the formula to this:

case when Event Name = ‘page_view’ and Event Param Name = ‘page_location’ and Event Param Value (String) = ‘https://web-analytics.me/' then User Pseudo ID else ‘’ end

And indicate — count distinct:

Time distribution

Using visualization from https://www.clickinsight.ca/ we can do line chars with the distribution of users by the hour:

And with the help of filters, we can look for interesting patterns. To find the hour from the time parameter, we need to create another parameter using the formula:

HOUR(Event Time)

Google maps

Known for beauty, but it finally added the ability to put data on Google Maps without the difficulties:

You have a lot of visualization variants because it is integrated visualization.

Gauge chart

Despite it not being popular, this is a nice visualization. It allows us to understand how our indicators are in the normal zone. This makes it easier to evaluate it.

Most often, we work with tables, where, among other things, it is possible to compare current indicators with a previous or a fixed period. To enable this feature, select the comparison data range, in the upper right menu — Рrevious period:

And at the output, we get something like this:

The images in the table

A great opportunity to evaluate the effectiveness of banners is to look at the creatives in the form of a picture along with the indicators in the table. For this, the corresponding field should contain the url of the image. In the parameter setting, change the Type — url and in the Show as — Image:

There is a more complicated, but flexible method.

Since we have access to raw data in BigQuery, we have the opportunity to compose our sql queries with the data we need and visualize the result. This is especially true if, in addition to our app + web data, there are Google Ads, data from the Search Console, also Facebook ads and sales data from the database.

We have the opportunity to combine the data we need and to calculate metrics on sql from several data tables at once. And in Google Data Studio, it just visualizes the results of these calculations.

Although, even super fast BigQuery can process a significant amount of data for a long time and our visualizations will not work quickly.

The easiest way to add your query when adding a data source is to select the custom query item:

In this case, we can not access the whole table, but filtering the results using custom parameters:

This will greatly speed up data sampling. This is especially true for clustered and partition tables:

WHERE

_TABLE_SUFFIX >= PARSE_DATE(‘%Y%m%d’, @DS_START_DATE)

And _TABLE_SUFFIX <= PARSE_DATE(‘%Y%m%d’, @DS_END_DATE)

Where

_PARTITIONTIME >= PARSE_DATE(‘%Y%m%d’, @DS_START_DATE);

AND _PARTITIONTIME <= PARSE_DATE(‘%Y%m%d’, @DS_END_DATE);

By the way, recently it became possible to work not only with dates, but also with other parameters, including your own.

Or, we have the opportunity to create a View based on queries by clicking on the save view button:

The speed of accessing such a table will not be accelerated, compared to a regular query, but it’s more convenient to store ready queries.

After creating and checking your query, we can save the result of its work, ready for the real table. For this option, in the more section, select Query settings:

This is a ready, counted table, in fact. The speed of work with which will be higher than performing the entire request.

If you don’t use static data, then as a rule it is necessary that our table is calculated regularly, for example, once a day. For this, scheduled queries are suitable for us, a function that allows you to calculate tables on a schedule:

When we are composing such queries, we can work not with the entire dataset, but only with the last day, when using this opportunity — append to table:

And in our queries, we indicate the sample only from yesterday:

where parse_date(‘%Y%m%d’, event_date) = date_sub(current_date(), interval 1 day)

On live projects, I add data to the partition tables day by day, or more often through scheduled queries.

Each such table contains all the necessary metrics and parameters to create a visualization on a single sheet in Data Studio. Moreover, by connecting these tables, I am using custom parameters. This allows me to reduce time and resources when creating such tables and significantly increase the speed of the reports themselves.

Join our channel in telegrams: https://t.me/firebase_app_web_bigquery

How to make fast and convenient visualization in Data Studio based on BigQuery was originally published in Beards Analytics 🇺🇦 on Medium, where people are continuing the conversation by highlighting and responding to this story.

For example, if you use App+Web Google Analytics you have a free connector for Google BigQuery. We can do the classic reports by using Google Data Studio (in the last article I described a few tricks about it) or other visualization systems. We need data from advertising activities for that.

Everything is simple with Google Ads. We can use a BigQuery transfer, which can send Google BigQuery a lot of data from the advertising and you can set it for several minutes. Working with other data sources are difficult.

There are many paid and partially free services on the market that solve this problem, but why pay money if we have cool Cloud Functions in Google Cloud.

And besides visualizing data on Facebook Ads in Data Studio, we can upload this data to Google Analytics from BigQuery, or automate marketing in general.

Creating Google Cloud Project

If you haven’t got your Project in Google Cloud it is time to create that. For that we go to console.cloud.google.com and create an account.

Next, we need to create a new project.

You get $300 for the year, so first year will be free.

You need to fill all the necessary billing data to create a new project:

Yes! We have a customized project:

To publish functions, we have the opportunity to work with gcloud (command-line tool, in the terminal) or work with web UI, which we just got access to.

If you do not have experience with the terminal, this can lead to difficulties, so I recommend that you configure everything in the web version.

To install gcloud go to the address, and, depending on your operating system, follow the instructions.

Don’t forget to check the boxes next to beta commands, we need it.

Facebook ads ETL

Getting Facebook credentials.

At first, we need create app on https://developers.facebook.com/

Choose, Create App:

Here you can get app_id, app_secret and access_token:

APP_ID will show directly on the main screen:

Next, APP_SECRET, we can select from the Settings menu — Basic section

The most interesting thing is to take a token. To do this, click on the last item — Product on the left side of the menu and select the Marketing API, click on Set up:

Furthermore, after adding the Marketing API, we go to the Tools section, where we select the read_insights and ads_read checkmarks, click on the “GET TOKEN” and save the token.

The token that was obtained this way is live for 60 days, after that we will need to generate it again. We can go through Facebook verification to take the long live token.

We will need these three variables to load scheduled data.

Also, we will need account_id. We can find it in Ads Manager:

Installation via web UI

First, create a topic in Pub/Sub. For this, go to the Pub/Sub section in the left menu and select topics:

Click on create topic and type facebook_run:

Now, go to the repository https://github.com/snegireff/facebook_ads_bigquery_etl and click on the clone or download button and download the zip file yourself:

Since the zip file contains a folder with files with source code, you need to unzip the zip file, go to the folder and make a zip file archive:

Then, go to the cloud function section in the left menu and click on create function:

The name can be arbitrary, in memory we allocated 1GiB, as a trigger we specify cloud pub/sub, and the topic is the one that we created in the last step.

After that, specify the type of ZIP upload:

Runtime we will have python 3.7, select our archive file and specify any bucket available in the storage bucket, the function should be called get_facebook_data.

Next, click on more:

And change the maximum execution time to 540sec:

And click on Create, then, after a couple of minutes, our function is published:

After that, we need to make sure that our function runs on schedule. For this, cloud scheduler is perfect for us, since attributes cannot be set via web UI, yet, we will use Cloud Shell, for this, click on the terminal icon:

gcloud beta scheduler jobs create pubsub facebook — time-zone “Europe/Kiev” — schedule “0 5 * * *” — topic facebook_run — message-body “get_facebook” — attributes project_id=PROJECT_ID,dataset_id=DATASET_ID,table_id=TABLE_ID,account_id=ACCOUNT_ID,app_id=APP_ID,app_secret=APP_SECRET,access_token=ACCESS_TOKEN

Where

PROJECT_ID = your google cloud PROJECT_ID

DATASET_ID = for example “facebook”

TABLE_ID = for example “fb_stat”

ACCOUNT_ID = your facebook account id without act_

APP_ID, APP_SECRET, APP_TOKEN = from apps developers.facebook.com

And at 5am EST. (the runtime is encoded with the — schedule key, and the time zone — time-zone) every day. The data will be written to our table in Google BigQuery.

To download data from yesterday, go to the Cloud Scheduler, in the Google Cloud console and click Run now:

Then, after a couple of minutes, in BigQuery we will have a new dataset and a table with data for yesterday:

Installation via terminal

Now, go to the repository https://github.com/snegireff/facebook_ads_bigquery_etl and click on

Or we can download the code from github repository. To do this, create a new folder. In the terminal, in this new folder we run the command:

git clone https://github.com/snegireff/facebook_ads_bigquery_etl.git

Furthermore, we can start work. Install the project for our gcloud. Project id is displayed on the home page in the Google Cloud Console in the Project info section. To do this, in the terminal, run:

gcloud config set project YOUR_PROJECT_ID

To communicate with our function, the pubsub mechanism from Google Cloud is used, so create a new topic. To do this use the terminal, run:

gcloud pubsub topics create facebook_run

To publish the cloud function, which includes the ability to collect statistics on Facebook and data on the exchange rate, in the terminal, in the source folder, we run the command (we answer Y for all questions):

gcloud functions deploy get_facebook_data — runtime python37 — trigger-topic facebook_run — timeout=540 — memory=1024MB

After that, we need to make the launch of our functions on a schedule, for Facebook, in the terminal we run a command where we fill in the values (we answer Y for all questions, I usually use zone 13):

PROJECT_ID = your google cloud PROJECT_ID

DATASET_ID = for example “facebook”

TABLE_ID = for example “fb_stat”

ACCOUNT_ID = your facebook account id without act_

APP_ID, APP_SECRET, APP_TOKEN = from apps developers.facebook.com

gcloud beta scheduler jobs create pubsub facebook — time-zone “Europe/Kiev” — schedule “0 5 * * *” — topic facebook_run — message-body “get_facebook” — attributes project_id=PROJECT_ID,dataset_id=DATASET_ID,table_id=TABLE_ID,account_id=ACCOUNT_ID,app_id=APP_ID,app_secret=APP_SECRET,access_token=ACCESS_TOKEN

The data will be written to our table in Google BigQuery at 5am EST. (the runtime is encoded with the — schedule key, and the time zone — time-zone) every day.

To download data for yesterday, go to Cloud Scheduler, in the Google Сloud Сonsole and click Run now:

Then, after a couple of minutes, in BigQuery, we will have a new dataset and a table with data from yesterday:

Currency converter

It is not enough just uploading data from Facebook. The costs data on Facebook is displayed in USD only, the other metrics such as revenue, can be displayed in other currencies. We make the second function, which collects daily data on the exchange rate and inputs it in another table in the same dataset.

There is a good service for this — currencylayer.com, which allows you to download data up to 250 times a month for free. It is more than enough for us.

We are registering on the free plan:

In the https://currencylayer.com/dashboard section, copy and save the token, we need it to download data on a schedule.

To create a scheduled download, in the terminal, run the following command, where we fill the values:

PROJECT_ID = your google cloud PROJECT_ID

DATASET_ID = for example “facebook”

TABLE_ID = for example “exchange_rate”

API_KEY = key value from currencylayer.com

From_currency= leave USD

To_currency = indicate your currency, for example UAH

gcloud beta scheduler jobs create pubsub converter — time-zone “Europe/Kiev” — schedule “0 5 * * *” — topic facebook_run — message-body “get_currency” — attributes project_id=PROJECT_ID,dataset_id=DATASET_ID,table_id=TABLE_ID,api_key=API_KEY,from_currency=USD,to_currency=UAH

A few words about dynamic links

In case you use App + web or Firebase for mobile devices, we have the opportunity to see the application’s installation (or rather first open) from Facebook in BigQuery.

We can see not only the installation, but also the opening of the application. For this we use dynamic links from Firebase.

Here are detailed instructions on how to implement them in the application. In Facebook’s case, you should pay attention to a couple of points.

Firstly, the presence of og tags on the site, without them, Facebook ads, give an error, and doesn’t allow placing a deep link in the ad settings.

Secondly, we can collect the links ourselves, then we can register all the necessary parameters (description of the parameters) and the link will look something like this:

https://domain.page.link/?link=https://raketaapp.com/&apn=package_name&ibi=package_name&afl=https://play.google.com/store/apps/details?id=package_name&ifl=https://itunes.apple.com/us/app/id________&utm_source=facebook&utm_medium=cpc&utm_campaign=install_google_play|23843962811990104|23843962819200104|23844002612500104

Now, through dynamic links we can only pass three parameters, namely utm_source, utm_medium and utm_campaign, and we want to see statistics data before each ad, we can write all necessary values with a separator in utm_campaign for each ad, for example |.

So utm_campaing parameters would look like this:

utm_campaign=CAMPAIGN_NAME|CAMPAIGN_ID|ADSET_ID|AD_ID

We can use this following query in BigQuery:

select

concat((select value.string_value from t0.event_params where key = ‘source’), ‘ / ’, (select value.string_value from t0.event_params where key = ‘source’)) as sourceMedium,

split((select value.string_value from t0.event_params where key = ‘campaign’), ‘|’) [safe_offset(0)] campaign_name,

split((select value.string_value from t0.event_params where key = ‘campaign’), ‘|’) [safe_offset(1)] fb_campaign_id,

split((select value.string_value from t0.event_params where key = ‘campaign’), ‘|’) [safe_offset(2)] fb_adset_id,

split((select value.string_value from t0.event_params where key = ‘campaign’), ‘|’) [safe_offset(3)] fb_ad_id

from `project.dataset.table` t0

where event_name like ‘%dynamic%’

You only need to join them.

Join our channel in telegrams: https://t.me/firebase_app_web_bigquery

Writing your data connector from Facebook ads to Google BigQuery was originally published in Beards Analytics 🇺🇦 on Medium, where people are continuing the conversation by highlighting and responding to this story.

There are currently two approaches to attributing mobile app installs:

• Getting a device identifier from an advertising network (for example, idfa for IOS) on which the user clicks on the ad.
• Fingerprinting, i.e. linking a click on an advertisement and the fact of installation based on device parameters.

Current mobile attribution systems use either both of these approaches or only link by identifier. With the advent of IOS 14, for Apple devices, the ability to attribute by idfa will disappear and the task of attributing installs somehow or another will be solved through fingerprint.

This article is considered the possibility building of attribution and a referral program based on dynamic links from Firebase. Since Firebase or App + Web allows you to solve any problems in mobile / web analytics and marketing automation, dynamic links make it possible to build a complete analytics system without using other services.

How dynamic links work

It is important to understand a dynamic link is not just a link that leads the user to a specific application. It is a constructor into which we can set our own logic of work. Below is a diagram of a possible work for the IOS link.

That is, we can use one link for both IOS and Android. If necessary, you can set the conditions for opening for different versions of operating systems.

Installation in the application

This part is pretty simple, Google prepares the excellent documentation. On my own, I want to add that there are implementations for Cordova, on which there are more and more applications lately.

Basically, dynamic links allow you to open your application from a link while preserving the utm_source, utm_medium, and utm_campaign parameters. Also, it is possible, using Universal links for IOS or App links for Android, to open a specific application screen. This is especially useful for remarketing campaigns, including push notifications.

In Firebase console, add the domain on which our links will be displayed:

Also, it is possible to bind your subdomain or the domain on which these links will be displayed. It does not give anything but beauty, but it looks more serious.

An important point to prevent attackers from using your links is to add your domain (or domains) and links to Google play Store / App Store of your applications to the white list.

Creating dynamic links

There are four ways to create a link:

- from Firebase Console.

This method is suitable for creating test or static links. From the advantages of this method, when you use these links, you have statistics right in the Firebase console.

- collect the link manually.
In real situation, this is the most convenient way that allows you to quickly collect the necessary link from a template and automate their creation:

https://example.page.link/?link=https://www.example.com/someresource&apn=com.example.android&amv=3&ibi=com.example.ios&isi=1234567&ius=exampleapp

- using the rest API.

Using the rest API you can make a long link into a short one.

- create a link in the application.

This method is great for creating a referral program, where in utm_campaign, you can pass the user_pseudo_id or user_id of the user to which this link belongs.

Debugging links

If you add the? D = 1 or & d = 1 parameter to your link, you will see the entire script for its opening, as well as possible errors, if any.

Firebase dynamic links generator

To make it easier to create links through the REST API, I wrote a small extension to Google Sheets scripts that generates them automatically.

To install, create a new Google Sheets, go to Tools -> Script Editor and paste the code from this repository:

https://github.com/snegireff/firebase_dynamic_links_generator

In the code, you need to change it:

sheet.getRange(‘A2’).setValue — just a test url (specify your website domain)

Firebase_url — your domain for short links

Apn — The package name of the Android app

Afl — The link to open when the app isn’t installed (usually url on Android Store)

Ibi — The bundle ID of the iOS app to use to open the link.

Ifl — The link to open when the app isn’t installed (usually url on IOS Store).

Api_key — can be taken in the Firebase console in the settings section.

After that, call the onOpen function:

Which will create a context menu in your document:

Where the first item clears the sheet from the created links, and the second generates them.

Working with dynamic links in Firebase / App + Web

When a user clicks on a dynamic link, the following events happen:

dynamic_link_first_open — the moment, the user opens the application for the first time.

dynamic_link_app_open — the moment, the user opens the application the second and more times.

dynamic_link_app_update — the moment, when the application is updated, after clicking on the link, but only for Android.

Also, in event_params these events save the parameters source, medium, campaign, which can be used, for example, like this:

SELECT

user_pseudo_id,

event_name,

concat((select value.string_value from t0.event_params where key = ‘source’),’ / ‘, (select value.string_value from t0.event_params where key = ‘medium’)) as sourceMedium

FROM

XXX.events_intraday_20200903 t0

where

event_name like ‘%dynamic%’

An important addition. The attribution model is currently first click, i.e. in the traffic_source parameters, there will always be data on the initial traffic source.

In this case, dynamic links can be used as a marker for changing the source and consider any attribution model to your choosing.

If the link was not created manually, but through the console / API / SDK, then the data for click, install, re-open, redirect and first open can be obtained through the corresponding API.

Website Script

An important case is the ability to understand the number of installs through your website. For example, if you have campaigns for both the site and the installation.

In this case, you can save any source, campaign and other parameters when transitioning cookies and dynamically generate a unique link for each client by inserting its parameters into the link.

I give an example based on the Sbjs script. Despite the fact that it is large, it does its job well.

This is how we can realize via GTM:

- Insert all the code or a link to the script into a new GTM tag of the custom html type and activate it with the sbjs.init () command. Trigger is Page Load.

- We create variables of js type, which correspond to source, medium, etc. All possible variables can be found in this document.

- We make another tag, such as custom html, which will contain the substitution algorithm. Trigger is Window load.

<script>

var link = “https://page.page.link/?link=https://play.google.com/store/apps/details?id=ua.page.app&utm_source=web_"+{{current.src}}+"&utm_medium="+{{current.mdm}}+"&utm_campaign="+{{current.cmp}}+"&gclid="+{{gclid}}+"&utm_term="+{{current.trm}}

$(‘[href=”https://play.google.com/store/apps/details?id=ua.page.app"]').attr('href', link)

var link2 = “https://page.page.link/?link=https://itunes.apple.com/us/app/id111111&utm_source=web_"+{{current.src}}+"&utm_medium="+{{current.mdm}}+"&utm_campaign="+{{current.cmp}}+"&gclid="+{{gclid}}+"&utm_term="+{{current.trm}}

$(‘[href=”https://itunes.apple.com/us/app/id111111"]').attr('href', link2)

</script>

Now, for the user, the links will be generated dynamically, taking into account their last non direct traffic source. In Firebase analytics, such transitions will contain the web_ prefix.

Features of Facebook ads

Google Ads is natively integrated with Firebase analytics and you just need to link your accounts. On Facebook and other ad systems, you need to insert dynamic links.

There are no problems to insert dynamic links into advertising systems, but Facebook has some peculiarity.

Since when forming dynamic links, our website goes first, then when inserting dynamic links, Facebook validator goes to the site and looks for the presence of meta tags, with a description of the application, and in their absence, does not allow publishing an ad, therefore, it is necessary to publish on the website tags (by inserting the actual values into the content):

<meta property=”al:ios:url” content=”example://applinks” />

<meta property=”al:ios:app_store_id” content=”12345" />

<meta property=”al:ios:app_name” content=”Example App” />

<meta property=”al:android:url” content=”example://applinks” />

<meta property=”al:android:package” content=”com.example” />

<meta property=”al:android:app_name” content=”Example App” />

After that, the ad will be successfully moderated.

Join our channel in telegrams: https://t.me/firebase_app_web_bigquery

Firebase Dynamic links setup and using guide + Firebase dynamic links generator based on Google was originally published in Beards Analytics 🇺🇦 on Medium, where people are continuing the conversation by highlighting and responding to this story.

The relatively new analytics tool, Google Analytics 4, has collected the best solutions on the market for marketing and product analysis. But in addition to a convenient, flexible, and customizable interface, it became possible to save raw data in Google BigQuery for free and quickly. Thanks to this, a simple opportunity to work with raw data has appeared, available to large companies and medium and small businesses.

There are many tasks for using the raw data, from reporting automation and conversion prediction to recommender systems and marketing automation. Let’s discuss the last in more detail and analyze the possibility of creating and filling a remarketing audience based on raw data stored in Google BigQuery.

Everyone knows that if we segment the audience, and for each segment, we make our advertising message that better describes the needs of this segment, the higher the resulting conversion.

The problem is that achieving a good audience segmentation, according to many parameters, technically, without automation, is rather laborious work that takes a lot of time. But by using Google Cloud and other tools, it is possible to make a simple, effective solution.

How to create a custom audience using only Google Ads/ Facebook Ads opportunities

You need to install a tag for tracking user actions for a website or an SDK to track user activity in an application to collect users in an audience.

Google Ads custom audience

Cons of working with Google Ads audience segments:

1. Audiences are not flexible. You can create audiences only for those events made in the system.

2. Lack of flexibility. After creating an audience, you cannot make changes to the audience because you must restart the data collection process.

3. Google Ads has a limit of 540 days for user participation in audience lists.

To get started with custom remarketing audiences, head to the Audience Manager in your Google Ads Tools and Instruments tab:

In Google Ads, you have the following audience source options:

• App users
• Website visitors
• Customer lists
• YouTube users
• Adding your customer list
• Custom combination

The most common sources of app audiences are websites and apps. You can choose one of the following options. Using specific selections, you can customize which users get funneled into your audience:

• Visitors of a page
• Visitors of a page who also visited another page
• Visitors of a page who did not visit another page
• Visitors of a page during specific dates
• Visitors of a page with specific tags

Remarketing using the Customer List option

The customer list option works by matching the data you provide Google on your existing customers to Google accounts. You can fill out the data for customers based on email, phone, name, country, zip codes, and any additional information you might have.

Facebook Ads custom audience

Cons of working with Facebook Ads audience segments:

1. Facebook Ads has a limit of 180 days for user participation in audience lists.

2. Audiences are not flexible. You can create audiences only for those events made in the system.

3. Lack of flexibility. After creating an audience, you cannot make changes to the audience because you must restart the data collection process.

The Facebook Sources options will be easier for many people than the others, as Facebook supplies all the data you need. You won’t need to import or sync data from your site, app, or customer lists. The main, most popular data sources are web and app sources and customer list audience.

If you are interested in making custom audiences based on your own website data, you’ll need to install a Facebook pixel.

After that, you need to choose a data source for your audience and the user’s period of participation in the audience and specify the related settings.

You also have an option to create a customer list audience. You need to click on the Customer List option on the Custom Audiences page.

In the next step, you can begin importing your customer list. You can do this in one of two ways:

• Import a customer list from Mailchimp
• Import a CSV or TXT file

With Mailchimp, you need to click on “Import from Mailchimp” and follow the prompts.
If you are manually importing a list as a CSV or TXT file, you’ll need to pay attention to file formatting so that Facebook can understand all the data you’re giving it.
Facebook matches users with your data through identifiers like email addresses, phone numbers, and names. The more identifiers you include, the more accurate Facebook will be targeting.
When formatting your CSV or TXT file, follow the guidelines that Facebook sets out in this document to make your list as clear as possible to Facebook’s AI.
People new to this sort of thing use Facebook’s template to avoid mistakes.

So the common problems for both Google and Facebook are:

1. Lack of flexibility once the audience is already created: it is impossible to make adjustments without re-run the audience collection.

2. Restriction on the duration of the user’s participation in the audience.

3. Restrictions on data sources for the audience. It is possible to set up audiences only for events that are created.

How to build automatization of creating custom audience with Google Cloud and Retool

We have an option to create audiences for remarketing in Google and Facebook is not very flexible and allows you to work only with those event parameters that are made as events into these advertising systems or manually upload data collected from other systems.

Since we store a lot of data from different sources in Google BigQuery, we can automatically create more flexible audiences.

For example, we can use RFM segments:

An alternative approach is to create audiences based on RFM analysis.

RFM analysis is a marketing technique used to quantitatively rank and group customers based on the recency, frequency, and monetary total of their recent transactions to identify the best customers and perform targeted digital-marketing campaigns.

We divided users into the following segments:

Users who made transactions:

• for a period of 1 to 30 days
• for a period of 30 to 90 days
• for a period of 90 to 190 days
• for the period from 180 to 359 days
• for a period of more than 360 days

Also created segments by the number of transactions per user:

• more than 1 transaction
• from 2 to 4 transactions
• from 5 to 9 transactions
• from 10 to 19 transactions
• over 20 transactions

Technical process

To accomplish the task of creating audiences, we combine data from GA4, Firebase, as well as data from CRM systems, using the convergence and transformation of data from various systems. We bring the data to the form in which we received segments that can later be used for creating audiences in advertising systems. In order to merge data, we combine data from GA4 / Firebase and CRM by user_id and user_pseudo_id, obtaining information about how many users made transactions in the specified time periods and the number of transactions made by a particular user.

After that, in our models, we converted them into data segments based on the number of transactions per user and the time and amount of time since the last transaction.

As a result, we get data that will be convenient to further segment into audiences that we will use in Google Ads and Facebook Ads.

SQL query constructor and execution

Using the functionality of Retool, we made a query builder, which relies on the data of the auxiliary plate. Using a nice interface, you can easily build a SQL query, which will be displayed in the appropriate field, which, if necessary, can be edited manually. It is also possible to add the received query to the BigQuery table. After executing the query, we get a table, the data of which is subsequently uploaded to custom audiences by pressing the Upload Audience button.

Authorization.

Authorization is done in two ways. This can be OAuth2 or authorization using the API and client configuration data (since applications created in Retool can be private)

Tech process in general

Retool allows you to implement automation and a responsive interface using JavaScript and built-in UI components. The service also provides the ability to construct API requests and execute them without code. Communication with BigQuery is organized at the built-in Retool level, which can also connect to various databases. Direct connection to BigQuery is implemented using OAuth2. This allows you to write SQL queries and, in general, perform various data manipulations at the built-in Retool service level.

In the case with Google Ads Custom Audience, you need to create a Google Cloud Function with a server that provides an API for interacting with audiences: getting their list, creating new ones, and loading user data into existing ones. After creating such a server, you can make authorized HTTP requests to the URL address of the created cloud function.

Result

Finally, we have tables with hashed user data, which we’re going to fill our audience with by pressing the Upload audience button. All you have to do next is to wait for the result and enjoy your audience.

Conclusions

Using data from GA4 / Firebase + CRM + Retool allows us to have a more flexible approach to forming audiences. Enable us to make changes to the audience, add a column or parameter to the audience, instantly get a new audience with new parameters, which you need to upload to the advertising account. Our approach allows you to be very flexible in building audiences.

Custom audiences for Facebook Ads/ Google Ads based on Google Cloud and Retool was originally published in Beards Analytics 🇺🇦 on Medium, where people are continuing the conversation by highlighting and responding to this story.