Tag Archives: BigQuery
Building Smart Data Pipeline with Firestore, LangChain, and BigQuery
I recently had to build a data pipeline for my website, Sphinx Mind, where I aimed to achieve two objectives: The task of moving data from Firestore to BigQuery is straightforward. We can either use Firebase Functions or extensions. In this case, I went ahead with functions as I wanted to perform data transformation before…
Detecting and Classifying Bot Traffic in GA4 with BigQuery ML
Bot traffic is a common problem for website owners, as it can skew analytics data and misrepresent user behavior. Bots can be used for various purposes, including web scraping, spamming, and click fraud, among others. In order to accurately analyze user behavior and make data-driven decisions, it is essential to detect and classify bot traffic…
Solving Facebook Reach Deduplication in BigQuery
One of the unique metrics in Facebook Ads is Reach, and it works differently than other metrics. Reach is a de-duplicated metric, meaning that you might see different numbers depending on the level at which you view the data. For instance, the campaign-level Reach may not match the sum of your ad set-level Reach because…
Auditing GA4 Traffic Source UTMs via BigQuery
One of the new fields in GA4 is ‘Landing Page + query string‘, which displays all query parameters, whether you exclude them or not. Digital marketers can leverage this field to solve one of their biggest pain points, which is missing or inconsistent UTMs. Today, I will show you how to run an audit on…
Media Planning via Machine Learning in BigQuery
Media planners usually use Excel and formulas to create detailed media plans, capturing how much investment is put behind each media channel, each market, each month ..etc. Then perform analysis using static benchmarks to see the impact on leads, sales, and return on investment. This process is tricky as it requires a lot of time…
Upload Facebook Ads to BigQuery via App Scripts
If you run ads across different channels like Google, Facebook, LinkedIn ..etc, you already know reporting consolidation is now a must. Having all of your ads data in one storage is the only way to get a holistic view of your marketing investment and understand which channel is more profitable to the business. BigQuery is…
How to link BigQuery to GA4?
BigQuery is a cloud data warehouse designed to help you turn big data into valuable business insights. Linking BigQuery to Google Analytics was available only to GA360 users – the enterprise versions of Google Analytics. However, since the introduction of the new GA4, linking BigQuery to GA has become now available to everyone with no…
Recreating GA4 Traffic Acquisition Report in BigQuery
Google Analytics 4 allows exporting all the raw data and events to BigQuery automatically, and use SQL syntax to query and model the data. When you export GA4 data to BigQuery, you own that data, and you can analyze users and their traffic on your property. To make sense of the data and create reports,…