{"id":754,"date":"2024-10-22T08:58:45","date_gmt":"2024-10-22T06:58:45","guid":{"rendered":"https:\/\/www.syntera.ch\/blog\/?p=754"},"modified":"2024-10-22T08:58:46","modified_gmt":"2024-10-22T06:58:46","slug":"google-analytics-4-data-in-microsoft-fabric","status":"publish","type":"post","link":"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/","title":{"rendered":"Google Analytics 4 Data in Microsoft Fabric"},"content":{"rendered":"
<\/div>

LEAD DATA ENGINEERING<\/p>

Dominic Schranz<\/p><\/div><\/div>\n\n\n

<\/div>\n\n\n\n

Google Analytics 4 data is essential for understanding website performance and user behavior. The native dashboard is great for initial analysis but there are other use cases where it is just not quite enough. What if you want to export the data to combine it with other sources? Or give new users the required permissions all at once? What if you just want to consolidate all your dashboards in a single place? For us, this place is called Microsoft Fabric and in this blog post we’ll explore how to use Fabric Notebooks to connect directly to Google Analytics with the goal of exporting and storing the data in OneLake.<\/p>\n\n\n\n

Preparing Google Analytics<\/h2>\n\n\n\n

Navigate to Google Analytics (GA) API Quickstart<\/a> and follow the steps to enable the API access. Step 1 will create a service account, which can be added to your Google Analytics property in Step 2.<\/p>\n\n\n\n

\"\"
GA API Quickstart<\/figcaption><\/figure>\n\n\n\n

To add the newly created service account to your GA property, follow the “add users” section of this guide<\/a>. In short, navigate to google analytics<\/a> and click on the large gear icon in the bottom left. Select “Property access management” and in the upper right corner click on the large “+” sign to add a new permission. Grab the Email-address from the client configuration you downloaded in step 1. and add this user with “Viewer” permissions. The end result should look something like this:<\/p>\n\n\n\n

\"\"
Permissions in GA for API access<\/figcaption><\/figure>\n\n\n\n

Preparing Fabric Notebook<\/h2>\n\n\n\n

On the Fabric side, we\u2019ll use a Notebook to connect to Google Analytics via the API. To do this, store the credentials file directly in the Notebook\u2019s built-in resources. Open a new Notebook and, in the “Explorer” section on the left, navigate to “Resources.” Under “Built-in,” click the three dots and select “Upload Files.” Upload the client credentials JSON file.<\/p>\n\n\n\n

\n
\n
\"\"<\/figure>\n<\/div>\n\n\n\n
\n
\"\"<\/figure>\n<\/div>\n<\/div>\n\n\n\n

Warning: Storing the credentials directly in the built-in resources can be a security risk. To store credentials securely, take a look at this post on using Key Vault with Fabric Notebooks<\/a>.<\/p>\n\n\n\n

The Code<\/h2>\n\n\n\n

(Full code at the end of page)<\/em>
First we need to install the Python client library. Fabric Notebooks provide the possibility to use inline installation to directly get additional packages via PIP. The PySpark kernel will be restarting after the installation and it is therefore required to run in the first cell.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

To ensure the successful authentication with the GA API, the environment variable for the credentials needs to be set. This corresponds to step 3 in the API Quickstart<\/a>. The simplest way to do this in a Notebook is via os.environ. Setting the path to the uploaded credentials json in the builtin resources.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Additionally, the GA property id needs to be set to instruct the client which property will be queried.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

After setting up the application credentials and property ID, the Notebook is ready to call the API and extract data. In the example below, we retrieve views per page and day. While getting page views is relatively straightforward, other queries can get quite complex. Visit the API Dimensions & Metrics Documentation<\/a> to get a full overview.<\/p>\n\n\n\n

\"\"
Get views by page and day<\/figcaption><\/figure>\n\n\n\n

The results of the API calls are stored in a Python list called “results,” which can then be added to a DataFrame for easy storage in a Fabric Lakehouse or Warehouse. The schema defines the data types and column names, which are applied in the spark.createDataFrame()<\/code> call. Running display(df)<\/code> shows the extracted data inline, giving you a first look at the results.<\/p>\n\n\n\n

\"\"
Loading results into a DataFrame<\/figcaption><\/figure>\n\n\n\n
\"\"
Displayed DataFrame with the Results ordered by pageViews.<\/figcaption><\/figure>\n\n\n\n

The code below stores this data in a Fabric Lakehouse Table. Ensure that you are not overwriting an existing Table and keep in mind that the write mode “overwrite” makes sense for this initial setup. To add data incrementally each day you would use mode “append” with a different date range and\/or upsert logic.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Results<\/h2>\n\n\n\n

Now that the website performance data is in Fabric, you can transform, combine, and visualize it however you like. Below is a comparison of the original Google Analytics dashboard (left) with the exported data in a Power BI report (right).<\/p>\n\n\n\n

\"\"
Google Analytics dashboard (left) and the now exported equivalent in Fabric with Power BI (right)<\/figcaption><\/figure>\n\n\n\n

Extracting data, while not the most thrilling task, forms the basis for many valuable insights. The method shown in this blog is the foundation for a range of use cases – from centralizing data in a single dashboard to building advanced models, like using AI to analyze similarities in your best-performing pages.<\/p>\n\n\n\n

COPY & Paste<\/h6>\n\n\n\n
# Installing the Google Analytics Python Client Library for our API Calls\n%pip install google-analytics-data\n\n# set the environment variable for the GA Credentials to the credentials json file\nimport os\nos.environ[\"GOOGLE_APPLICATION_CREDENTIALS\"] = \".\/builtin\/your_credentials_file.json\"\n\n# set your Google Analytics Property Id\nYOUR_GA4_PROPERTY_ID = \"**********\"\n\nfrom datetime import date, timedelta\nfrom google.analytics.data_v1beta import BetaAnalyticsDataClient\nfrom google.analytics.data_v1beta.types import (\n    DateRange,\n    Dimension,\n    Metric,\n    RunReportRequest,\n)\n\n# Using a default constructor instructs the client to use the credentials# specified in GOOGLE_APPLICATION_CREDENTIALS environment variable.client = BetaAnalyticsDataClient()\n\n# creating an api call for each day in the past 28 days \n# and storing the results\nresults = []\n\nfor i in range(1, 29):\n    day = date.today() - timedelta(days=i)\n    # get the views per page and day\n    request = RunReportRequest(\n        property=f\"properties\/{YOUR_GA4_PROPERTY_ID}\",\n        dimensions=[Dimension(name=\"pagePath\")],\n        metrics=[Metric(name=\"screenPageViews\")],\n        date_ranges=[\n            DateRange(\n                start_date=day.strftime(\"%Y-%m-%d\"), end_date=day.strftime(\"%Y-%m-%d\")\n            )\n        ],\n    )\n    response = client.run_report(request)\n    for row in response.rows:\n        row_val = (day, row.dimension_values[0].value, int(row.metric_values[0].value))\n        results.append(row_val)\n\nfrom pyspark.sql.types import *\n    \n# Create the schema for the table\norderSchema = StructType([\n    StructField(\"date\", DateType()),\n    StructField(\"pagePath\", StringType()),\n    StructField(\"pageViews\", IntegerType()),\n    ])\n\n# create a spark dataframe with the GA result data\ndf = spark.createDataFrame(results, orderSchema)\ndisplay(df)\n\n# writing the data to lakehouse\n# add a lakehouse first in the Explorer view to the left if none is connected\ndf.write.format(\"delta\").mode(\"overwrite\").saveAsTable(\"web_page_views\")<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Google Analytics 4 data is essential for understanding website performance and user behavior. The native dashboard is great for initial analysis but there are other use cases where it is just not quite enough. What if you want to export the data to combine it with other sources? Or give new users the required permissions […]<\/p>\n","protected":false},"author":3,"featured_media":773,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,7],"tags":[34,36,6],"class_list":["post-754","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-allgemein","category-data-engineering","tag-microsoft-fabric","tag-notebook","tag-power-bi"],"yoast_head":"\nGoogle Analytics 4 Data in Microsoft Fabric - Syntera<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Google Analytics 4 Data in Microsoft Fabric - Syntera\" \/>\n<meta property=\"og:description\" content=\"Google Analytics 4 data is essential for understanding website performance and user behavior. The native dashboard is great for initial analysis but there are other use cases where it is just not quite enough. What if you want to export the data to combine it with other sources? Or give new users the required permissions […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/\" \/>\n<meta property=\"og:site_name\" content=\"Syntera\" \/>\n<meta property=\"article:published_time\" content=\"2024-10-22T06:58:45+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-10-22T06:58:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2024\/10\/title-image.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"455\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Dominic Schranz\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Dominic Schranz\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/\",\"url\":\"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/\",\"name\":\"Google Analytics 4 Data in Microsoft Fabric - Syntera\",\"isPartOf\":{\"@id\":\"https:\/\/www.syntera.ch\/blog\/#website\"},\"datePublished\":\"2024-10-22T06:58:45+00:00\",\"dateModified\":\"2024-10-22T06:58:46+00:00\",\"author\":{\"@id\":\"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/e038d1dcebeca2193c5503f78783988d\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.syntera.ch\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Google Analytics 4 Data in Microsoft Fabric\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/#website\",\"url\":\"https:\/\/www.syntera.ch\/blog\/\",\"name\":\"Syntera\",\"description\":\"translating data into business value.\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.syntera.ch\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/e038d1dcebeca2193c5503f78783988d\",\"name\":\"Dominic Schranz\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/bd3589b8a6ccc62e37c3cee8f8e06c73a4085816337ad7d6c154e30dc60cc4c1?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/bd3589b8a6ccc62e37c3cee8f8e06c73a4085816337ad7d6c154e30dc60cc4c1?s=96&d=mm&r=g\",\"caption\":\"Dominic Schranz\"},\"url\":\"https:\/\/www.syntera.ch\/blog\/author\/domi\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Google Analytics 4 Data in Microsoft Fabric - Syntera","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/","og_locale":"en_US","og_type":"article","og_title":"Google Analytics 4 Data in Microsoft Fabric - Syntera","og_description":"Google Analytics 4 data is essential for understanding website performance and user behavior. The native dashboard is great for initial analysis but there are other use cases where it is just not quite enough. What if you want to export the data to combine it with other sources? Or give new users the required permissions […]","og_url":"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/","og_site_name":"Syntera","article_published_time":"2024-10-22T06:58:45+00:00","article_modified_time":"2024-10-22T06:58:46+00:00","og_image":[{"width":1280,"height":455,"url":"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2024\/10\/title-image.png","type":"image\/png"}],"author":"Dominic Schranz","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Dominic Schranz","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/","url":"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/","name":"Google Analytics 4 Data in Microsoft Fabric - Syntera","isPartOf":{"@id":"https:\/\/www.syntera.ch\/blog\/#website"},"datePublished":"2024-10-22T06:58:45+00:00","dateModified":"2024-10-22T06:58:46+00:00","author":{"@id":"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/e038d1dcebeca2193c5503f78783988d"},"breadcrumb":{"@id":"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.syntera.ch\/blog\/2024\/10\/22\/google-analytics-4-data-in-microsoft-fabric\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.syntera.ch\/blog\/"},{"@type":"ListItem","position":2,"name":"Google Analytics 4 Data in Microsoft Fabric"}]},{"@type":"WebSite","@id":"https:\/\/www.syntera.ch\/blog\/#website","url":"https:\/\/www.syntera.ch\/blog\/","name":"Syntera","description":"translating data into business value.","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.syntera.ch\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/e038d1dcebeca2193c5503f78783988d","name":"Dominic Schranz","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/bd3589b8a6ccc62e37c3cee8f8e06c73a4085816337ad7d6c154e30dc60cc4c1?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/bd3589b8a6ccc62e37c3cee8f8e06c73a4085816337ad7d6c154e30dc60cc4c1?s=96&d=mm&r=g","caption":"Dominic Schranz"},"url":"https:\/\/www.syntera.ch\/blog\/author\/domi\/"}]}},"_links":{"self":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts\/754","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/comments?post=754"}],"version-history":[{"count":11,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts\/754\/revisions"}],"predecessor-version":[{"id":796,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts\/754\/revisions\/796"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/media\/773"}],"wp:attachment":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/media?parent=754"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/categories?post=754"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/tags?post=754"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}