An example Power BI report for the existing Google Merchandise Store online shop https://shop.merch.google/ On individual tabs, we have an overview of the shop's website traffic, sales analysis, and user website usage analysis. I would like to draw attention to typical e-commerce KPIs such as the number of engaged sessions, average duration, or user engagement rate %. 

On the second tab, let's focus on individualized measures, e.g., average revenue per active user or rolling sales for the last 14 days. On the last tab, the ribbon visualization clearly shows how shopping intensity and conversion levels increased during the Christmas 2020 period. We also have a typical marketing funnel chart here, as well as the distribution of individual sessions depending on duration and revenue per session.     

Unlike the other projects, this one is based on real data from the Google store. In it, I wanted to show how, using business intelligence tools, we can deal with the problem of lacking an individualized report in Google Analytics. For example, based on data from this source, we could create customized measures using different definitions than in GA, such as average revenue per session, active users in the last 14 days, or modify the calculation of standard measures such as average session duration, average revenue per user (ARPU), or even specific visualizations, such as the distribution of sessions depending on duration and revenue. 

In this project, I used the public dataset bigquery-public-data.ga4_obfuscated_sample_ecommerce, which has an open source license and allows practicing data exploration in the format normally provided by the Google Analytics 4 API for the existing Google Merchandise Store online shop (https://shop.merch.google/). Due to limitations in using the GA4 API, I used this data source via the Google Cloud Big Query API, performing direct SQL queries within that service.  

This project is strongly rooted in the Google ecosystem; I mainly used Google Analytics 4, Google Cloud Platform (BigQuery studio, BigQuery API). Additionally: AI Copilot for testing SQL queries in BigQuery and DAX measures, Power BI Desktop (main environment), Power BI Service (management), Power Query (modeling) 

The biggest problem was the data format stored by BigQuery, typical for Google Analytics, as well as their large size. I had to optimize the queries in BigQuery in such a way as to simultaneously select interesting data, expand nested data (SQL UNNEST), and avoid overloading Power BI in the later stages of report building. Thanks to this, I didn't have to perform too many transformations in Power Query.

I also spent some time learning about the Google Analytics platform itself and reading the documentation on the standard measures used by GA and how this platform cooperates with BigQuery. Reporting via Google Analytics is focused around events on the site and built around numerous standard measures and dimensions, such that, for example, a quarterly dataset can have even several dozen million rows of records. For this report, I selected what I considered to be the most interesting dimensions (items, traffic, device) in order to showcase the possibilities of business intelligence. Optimizing the performance of the visualizations, building customized measures in DAX, configuring interactions between visualizations, optimizing navigation between report pages, and preparing the mobile version also posed some difficulties.

Example photos from work on the report below: