Google Analytics (GA) is a web analytics service offered by Google that provides comprehensive insights into website traffic and user behavior. By tracking metrics such as page views, unique visitors, bounce rates, and conversion rates, GA allows users to understand how people interact with their websites.
This data is presented through user-friendly dashboards and reports, making it easy to identify trends, strengths, and weaknesses. Armed with this information, businesses can make informed decisions to optimize their online presence, enhance user experience, and ultimately achieve their digital goals.
Using the GA APIs, customers can build custom reports and dashboards, automating the process of gathering critical insights. They can request data on a wide range of dimensions and metrics, segment information to gain a deeper understanding of user behavior, specific marketing campaigns, measure e-commerce sales, or analyze user demographics.
This blog post is about one of our customer's GA reports. They wanted to include adContent dimension in their custom reports, namely traffic and transactions tables. However, there was a problem with the transaction table which I will mention in the following section.
Our adventure commenced with two pivotal GA tables: the Traffic Table and the Transactions Table. The objective was to augment these tables with the adContent dimension. While incorporating this field into the traffic table was straightforward and smooth, the transactions table posed two challenges:
To overcome these tough challenges and incorporate the adContent field, we came up with a clever plan. We segmented the query into two parts, each centered around a specific set of dimensions. The key to merging these two separate queries was finding a dimension with unique data that made linking them work well. After the investigation of data, this unique dimension emerged as the clientId. So, our queries turn out to be as follows by keeping the dimension limit in mind (which was 7):
Dimensions: source, medium, deviceCategory, date, channelGrouping, transactionId, clientId
Metric: transactionRevenue
Dimensions: source, medium, date, channelGrouping, adContent, clientId, campaign
Metric: transactionRevenue
While clientId is a unique property in our customer's GA data, it is alone not enough because clients may have multiple transactions on the same day with the same source but different campaigns or different channelGrouping. However, its fusion with the other dimensions and metrics paved the way for a comprehensive merging key. That’s why we used all common dimensions and the transactionRevenue metric together to obtain the join key of the above query results. The common fields are written in bold to make them easy to identify.
We compared GA data over a certain period of time and validated that our strategy is working well. Our imaginative approach of splitting the query and capitalizing on common dimensions resulted in a victory. Our efforts paid off, we satisfied our customer's requisites even though the constraints posed by the Google Universal Analytics API.
In the realm of data analysis and integration, challenges often serve as catalysts for innovation. This case study serves as a testament to the power of creative problem-solving in overcoming limitations and unleashing the full potential of data analytics. By thinking beyond convention and harnessing the capabilities of cloud technologies, we fulfilled our customer's expectations, even when the API initially presented seemingly insurmountable roadblocks.
Nothing is impossible, sometimes you may need to spend more time and effort. It is good to keep this in mind: changing the way you look at challenges can be incredibly beneficial from time to time.