This is a guest post<\/strong> \u2013 the first one in a long time! The foreword and summary are written by me, Simo, and the rest is by my esteemed guest author.<\/p>\n How fortunate was I to have been contacted by Arben Kqiku<\/a>, Digital Marketing & Data Manager from comtogether<\/a>. Arben is one of our many Simmer<\/a> students, and he\u2019s walked through the Query GA4 Data In Google BigQuery<\/a> course, learning a lot along the way.<\/p>\n He wanted to share with me this wonderful case study he wrote using the lessons learned during that course and his prior knowledge of tools and languages like R<\/a>, Google Sheets, and Google Data Studio.<\/p>\n In this article, Arben tackles the age-old question of how to measure a blog\u2019s effectiveness<\/strong>. The data is based on a BigQuery dataset generated from this blog, which is available to all who take the BigQuery course<\/a> at Simmer.<\/p>\n \n \n Subscribe to the Simmer newsletter<\/a> to get the latest news and content from Simo Ahava into your email inbox!\n <\/p>\n <\/span><\/p>\n Recently, I finished the Query GA4 Data in Google BigQuery<\/a> course, produced by Simo Ahava and taught by Johan van de Werken<\/a>. As the name suggests, you learn how to query Google Analytics 4<\/a> data by using Google BigQuery<\/a>, in this case specifically Simo Ahava\u2019s GA4 blog data.<\/p>\n I wanted to test my newly acquired knowledge and this is why I wrote this article.<\/p>\n A blog is composed of articles, so, logically we need to compare these articles to understand which performs the best. However, what KPIs should we use?<\/p>\n We could use page views<\/strong> \u2013 the more page views an article generates the better. This certainly makes sense since page views can be interpreted as a proxy for interest. However, this metric has some limitations. For instance, people could visit an article and leave it right away without reading it. So, we need a metric that indicates whether someone has read an article or not.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n We could use the Google Analytics 4 scroll<\/strong> event for this purpose. GA4 records a scroll event each time a user scrolls to 90% of the total length<\/strong> of a page. You can instruct GA4 to automatically collect this event<\/a> and others from the GA4 interface.<\/p>\n However, this metric also has its limitations. Namely, the length between articles varies greatly. A page with only 300 words will generate more scroll events than a page with 8,000 words, simply because it\u2019s easier to reach the end of it. Therefore, we need to find a way to integrate an article\u2019s length into the equation.<\/p>\n First of all, we could use scroll events<\/strong> and page views<\/strong> to calculate the scroll conversion rate by using the following formula:<\/p>\n scrolls<\/strong> \/ page views<\/strong><\/p>\n<\/blockquote>\n However, we could have 2 pages with the same scroll conversion rate, one page with 10 views and the other with 1,000 views. Obviously, a page with 1,000 views is more valuable, so we need to refine our approach.<\/p>\n As mentioned earlier, a scroll is more valuable on a page with 8,000 words than on a page with 300 words. So, we could use a formula that weighs the number of scrolls by the number of words present in an article:<\/p>\n scrolls<\/strong> * words_count<\/strong><\/p>\n<\/blockquote>\n In this way, we give more weight to longer articles.<\/p>\n Now that the abstract part of the article is done, let\u2019s move to the juicy part, the code.<\/p>\n In BigQuery, it\u2019s fairly easy to extract scrolls per page.<\/p>\n Let\u2019s take a closer look.<\/p>\n First, with the following code, we need to extract the Scroll events are easier to extract \u2013 we simply need to tell BigQuery to count an event each time it encounters a scroll event while parsing the source table.<\/p>\n Finally, we need to group by<\/strong> To extract the word count per page, additional tools are required. For this, I used R and Google Sheets.<\/p>\n The first thing I did was to extract the pages that had at least 100 page views<\/strong> between June 1st and August 31st, and I pasted the results into this Google Sheet<\/a>.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n Then, I used R to calculate the word count of each page.<\/p>\n However, I encountered one obstacle when retrieving the HTML pages. Namely, there was a lot<\/em> of information that did not correspond to real words, such as JavaScript code snippets and CSS styling. This is why I compared each word to an English dictionary and only counted real English words<\/strong>.<\/p>\n Here are the concrete steps I took in R:<\/p>\n This is what the output looks like.<\/p>\n <\/p>\n <\/a><\/p>\n<\/div>\n And this is the R code I used.<\/p>\n Next, in Google Sheets, I created a <\/p>\n <\/a><\/p>\n<\/div>\n Later, I copied all these With Google Data Studio, I created this horizontal bar chart which displays the performance score by page.<\/p>\n We can clearly see that the article Google Analytics 4: Ecommerce Guide For Google Tag Manager<\/a> dominates the results.<\/p>\n
\n X<\/span><\/p>\n
\n The Simmer Newsletter<\/strong>\n <\/p>\nA web analysis of Simo Ahava\u2019s blog<\/h2>\n
How to measure a blog\u2019s effectiveness?<\/h2>\n
![\"Measure](\"https:\/\/www.simoahava.com\/images\/2022\/09\/pages-measure.jpg#ZgotmplZ\")
<\/p>\nA formula to measure an article\u2019s attractiveness<\/h2>\n
\n
\n
Performance score of each article with scrolls and word count<\/h2>\n
select<\/span>\n (select<\/span> value.string_value from<\/span> unnest<\/span>(event_params) where<\/span> key<\/span> = 'page_location'<\/span>) as<\/span> page_location,\n countif(event_name = 'scroll'<\/span>) as<\/span> scrolls\nfrom<\/span>\n `dataset.table<\/span>.events_*`\nwhere<\/span>\n _table_suffix between<\/span> '20220601'<\/span> and<\/span> '20220831'<\/span>\ngroup<\/span> by<\/span>\n page_location\norder<\/span> by<\/span>\n page_views desc<\/span><\/code><\/pre>\n<\/div>\npage_location<\/code> event parameter. To do so, we first need to unnest<\/strong> the event parameters and then select the page_location<\/code> parameter.<\/p>\n(select<\/span> value.string_value from<\/span> unnest<\/span>(event_params) where<\/span> key<\/span> = 'page_location'<\/span>) as<\/span> page_location<\/code><\/pre>\n<\/div>\ncountif(event_name = 'scroll'<\/span>) as<\/span> scrolls<\/code><\/pre>\n<\/div>\npage_location<\/code>, because we want to use the scrolls per page in our formula.<\/p>\n![\"Pages](\"https:\/\/www.simoahava.com\/images\/2022\/09\/google-sheet-pages.jpg#ZgotmplZ\")
<\/p>\n\n
4.1. Retrieve all the words
4.2. Clean the words
4.3. Join with the English dictionary
4.4. See if there is a match in the dictionary. If there is, count it as 1 (TRUE<\/code>)
4.5. Sum all the words that have a match in the dictionary<\/li>\n![\"Word](\"https:\/\/www.simoahava.com\/images\/2022\/09\/word-count.jpg#ZgotmplZ\")
<\/p>\n# Packages<\/span>\nlibrary<\/span>(tidyverse)\nlibrary<\/span>(googlesheets4)\nlibrary<\/span>(rvest)\n \n# Retrieve data from Google sheet<\/span>\nsheet_URL = 'https:\/\/docs.google.com\/spreadsheets\/d\/1tkOnw41zj7lQLonV-bEjJFszY5jSlMylmn9ntQO2qcA\/edit#gid=0'<\/span>\nsheet_id = gs4_get<\/span>(sheet_URL)\npage_data = range_read<\/span>(sheet_id)\n \n# Retrieve English dictionary<\/span>\ndict = read.table<\/span>(file = url<\/span>(\"https:\/\/github.com\/dwyl\/english-words\/raw\/master\/words_alpha.txt\"<\/span>), col.names = \"words\"<\/span>) %>%\n as_tibble<\/span>() %>%\n mutate<\/span>(condition = TRUE<\/span>)\n \n# Read data from web pages and count words per page<\/span>\nwords_per_page = page_data %>%\n mutate<\/span>(words = map<\/span>(Page, ~return_words<\/span>(web_page = .x))) %>%\n mutate<\/span>(words = words %>% unlist<\/span>())\n \n# Output data to Google sheets<\/span>\nsheet_write<\/span>(words_per_page, sheet = 'output'<\/span>, ss = sheet_id)\n \n# Functions<\/span>\nreturn_words = function<\/span>(web_page){\n words = web_page %>%\n read_html<\/span>() %>%\n html_text<\/span>() %>%\n \n # clean words for table<\/span>\n tolower<\/span>() %>%\n str_remove_all<\/span>(pattern = '\"'<\/span>) %>%\n str_remove_all<\/span>(pattern = \",|:|\\\\.|\\\\\\n\"<\/span>) %>%\n strsplit<\/span>(' '<\/span>) %>%\n as_tibble<\/span>('words'<\/span>) %>%\n set_names<\/span>(\"words\"<\/span>) %>%\n \n # join with English dictionary<\/span>\n left_join<\/span>(dict) %>%\n \n # keep only words from the english dictionary<\/span>\n filter<\/span>(condition == TRUE<\/span>) %>%\n pull<\/span>(condition) %>%\n sum<\/span>()\n \n return<\/span>(words)\n}<\/code><\/pre>\n<\/div>\ncase...when<\/code> statement for each page in order to create a column for word count in BigQuery.<\/p>\n![\"Word](\"https:\/\/www.simoahava.com\/images\/2022\/09\/word-count-sheet.jpg#ZgotmplZ\")
<\/p>\ncase...when<\/code> statements and pasted them back to BigQuery. To avoid showing a query with 358<\/strong> case...when<\/code> statements, I\u2019ll only display a handful of them here.<\/p>\nwith<\/span> prep as<\/span> (\nselect<\/span>\n (select<\/span> value.string_value from<\/span> unnest<\/span>(event_params) where<\/span> key<\/span> = 'page_location'<\/span>) as<\/span> page_location,\n countif(event_name = 'scroll'<\/span>) as<\/span> scrolls\nfrom<\/span>\n `simoahava-com.analytics_206575074.events_*`\nwhere<\/span>\n _table_suffix between<\/span> '20220601'<\/span> and<\/span> '20220831'<\/span>\ngroup<\/span> by<\/span>\n page_location\norder<\/span> by<\/span>\n page_views desc<\/span>),\n \nprep_words as<\/span> (\nselect<\/span>\n *,\n case<\/span>\n when<\/span> page_location = 'https:\/\/www.simoahava.com\/analytics\/google-analytics-4-ecommerce-guide-google-tag-manager\/'<\/span> then<\/span> 5865<\/span>\n when<\/span> page_location = 'https:\/\/www.simoahava.com\/'<\/span> then<\/span> 521<\/span>\n when<\/span> page_location = 'https:\/\/www.simoahava.com\/analytics\/one-tag-rule-them-all-introducing-google-tag\/'<\/span> then<\/span> 1365<\/span>\n else<\/span> null<\/span> end<\/span> as<\/span> words_per_article\nfrom<\/span>\n prep)\n \nselect<\/span>\n *,\n scrolls * words_per_article as<\/span> performance_score\nfrom<\/span>\n prep_words\norder<\/span> by<\/span>\n performance_score desc<\/span><\/code><\/pre>\n<\/div>\nLoad it all in Google Data Studio<\/h3>\n
![\"Load](\"https:\/\/www.simoahava.com\/images\/2022\/09\/load-in-data-studio.jpg#ZgotmplZ\")
<\/p>\n