Simple Attribution Models via SQL in BigQuery.
May 31, 2020
You know, one of the main reasons why this blog is named like the old-fashioned attribution “Last Click” is that I and my co-host started our marketing careers when this attribution – like other classical ones such as “First Click” – was the only one we could use. Continue reading
Reverse Page Path in Google BigQuery.
May 7, 2020
BigQuery export for Google Analytics is one of the most powerful tools, which can show you up to three of the steps preceding goal completion. But, to my bewilderment, there isn’t an option to obtain a similar report which can show you the reverse top paths to a particular page. Such reports can undoubtedly be useful in many cases when a user’s journey to a specific page needs to be analyzed. Let’s try to build it using BigQuery. Continue reading
Five reasons why you should use BigQuery for your Google Analytics data.
May 6, 2020
BigQuery export for Google Analytics is one of the most powerful tools you can use to understand and get the most out of your data for proper marketing and business decisions. But what exactly makes BigQuery so impressive? And why should you consider investing your precious time and effort in the export configuration? Continue reading
Google Analytics user-based events funnel in BigQuery.
April 24, 2020
There are a few ways to create an event funnel in Google Analytics. The first is the default events flow report but everyone who has tried this report will know that it’s only suitable for the superficial analysis of small resources. When you work with data of a significant size it’s impossible to avoid sampling and therefore, it is challenging to analyze certain events. Continue reading
How to work in Google BigQuery using SQL.
March 26, 2020
This might be your first guide to SQL and BigQuery. If so, that’s cool, but if it isn’t, we hope that our guide will provide clear explanations and help you understand things better. The goal of this guide is to help you become more familiar with SQL and BigQuery as quickly as possible so that you can make simple queries by the end of it. Continue reading
BigQuery: User-Defined Functions.
March 8, 2020
BigQuery has an option called User-Defined Functions, which helps users to maintain data as they want, row by row, using JavaScript. You will only want to use this if you cannot do something with data by standard BQ methods. Continue reading
BigQuery interface overview.
July 2, 2019
These instructions will help you to get started with the BigQuery interface. All you need to try it is a Google account: that’s it! BigQuery, in a nutshell, is cloud storage that may include data from many data sources, such as Google Analytics, Google Ads, Salesforce, etc. Continue reading
Pulling out the next page visited by new users in Google BigQuery.
June 28, 2019
Suppose that you have a particular landing page on your website, and you want to understand how it works for new users: how often they abandon the site after visiting this page or, for those who continue, which pages they visit afterwards. You could try to find these data in Google Analytics – it appears that the Navigation Summary report was invented for exactly this purpose and, to some extent, this is true. Continue reading
BigQuery: totals by day, totals by date range in one table.
June 22, 2019
Let’s say that you want to know the conversion amount in a specific date range for streaming in BigQuery: the total amount for each day and the total for all days as well. You could do this by making either two queries resulting in two tables, or one query resulting in one table. Continue reading
BigQuery: how to update tables using views and Google Apps Scripts.
June 21, 2019
Let’s assume you have a big table with raw data, for example, from Google Analytics. You make a query for data processing and obtain a more informative table. The question is how to update it on a daily basis and append new data from the main table. Continue reading
Full Referrer Dimension: Measuring Assisted Revenue.
June 03, 2019
Let’s imagine that someone has posted a few articles that lead to your website, and you are able to find the data about this traffic source under the /referral section (because the links posted are https, luckily). Continue reading
Calculating Audience Intersection in Google BigQuery.
February 24, 2019
Let’s imagine that we need to analyze the number of users who have visited two particular webpages during the last year. We want to know how many of them have visited Page A, how many Page B and how many have visited both pages during the period. Continue reading
Top Conversion Paths in Google Analytics and BigQuery.
February 5, 2019
Understanding top conversion paths can be a crucial part of the customer journey analysis and an important step towards better understanding of how marketing channels cooperate. It is particularly useful to know the top paths to conversion when planning marketing and advertising budgets. Continue reading
Going Beyond the Limits of the Assisted Conversions Report with BigQuery Hit-level Data.
January 19, 2019
Assisted conversions can be a rather valuable metric in a number of cases when there is a need to determine whether a piece of content or an online marketing campaign has contributed to a long-term conversion path. However, an assisted conversions report has a number of limitations. Continue reading
Multi-Channel Funnels And Acquisition Reports: Difference in Conversion Data.
January 9, 2019
In our work we quite often use various Google Analytics reports to evaluate the channels and the campaign’s performance. While most of the standard reports are a perfect place to superficially check the metrics and get some basic information, multi-channel funnels reports are used when there is a need for a little more sophisticated analysis. Continue reading