In today’s data-driven world, businesses rely heavily on analytics to understand their customers, optimise their marketing campaigns, and drive informed decisions. Universal Analytics (UA) provides a wealth of data on website traffic and user behaviour.

On the 1st of July of 2024 users won’t be able to access any UA properties or the API, and all its data will be deleted. Google is currently prompting Analytics users to export their data before this date through pop-ups in properties and via email. The deadline to save historic website data is the same for both standard and GA 360 users.

Storing and analysing large volumes of UA data can be challenging, and this is where data warehousing comes into play. As well as avoiding the loss of essential information for reporting, the process of storing and transforming Analytics data alongside other data sources can be seen as an opportunity for creating a centralised data environment that can have long-term benefits for measurement and insights.

What is UA data Warehousing?

UA data warehousing involves the extraction, transformation, and loading (ETL) of UA data into a central repository, known as a data warehouse. This warehouse also usually consolidates and organises data from various sources, enabling businesses to analyse it more efficiently and effectively.

Unlike traditional databases, which are designed for operational purposes, data warehouses are optimised for analytical queries and reporting.

Why warehouse UA data?

There are several key benefits to warehousing UA data:

• Long-term data storage: UA data is typically stored for a limited period in UA’s standard reporting interface. Data warehousing allows businesses to archive and access historical data for future analysis and insights.
• Enhanced reporting and analysis: Data warehouses enable businesses to create custom reports and perform advanced data analysis. This allows for deeper insights into customer behaviour, campaign performance, and other metrics.
• Data centralisation: By consolidating data from various sources (e.g., UA, CRM, ad platforms), data warehouses eliminate data silos and help work towards a single source of truth for analysis.
• Improved data quality: ETL processes involved in data warehousing can help clean, transform, and validate data, ensuring its accuracy and consistency.
• AI and ML projects: Centralising current and historic website and business data in a clean and structured way is often an essential first steps in preparing for experimenting with new machine learning and AI tools and platforms specifically tailored to your business needs.

How do I implement a data warehouse?

Implementing a data warehouse involves several key steps:

Data source identification: Identify all data sources that will contribute to the data warehouse.

Data extraction: Extract data from the source systems using appropriate tools and techniques. Some platforms will have connections to your chosen data warehouse built-in, others will need to use third-party data pipeline tools.

Data transformation: Clean, transform, and integrate data from different sources to ensure consistency and accuracy.

Data loading: Load the transformed data into the data warehouse.

Data modelling: Create a data model that defines the structure and relationships within the data warehouse.

Data access and reporting: Develop tools and interfaces to allow users to access and analyse data – e.g. connecting your cleaned source of data from all platforms to a business intelligence tool or visualisation service.

What costs are involved?

The cost of UA data warehousing varies depending on the chosen solution, the volume of data, and the number of users.

For example, BigQuery (Google’s own cloud data warehouse) employs a usage-based pricing model, where you pay for the storage and processing of your data. Space & Time offer data warehousing support via BigQuery with access to the final data tables/project for you to either connect to a BI tool of your choosing or have a dashboard created to interface with the data.

Prices depend on your UA data storage needs and visualisation requirements and any other sources that may be involved in wider storage, transformation and reporting projects.

What best practices for data storage need to be considered?

To ensure the success of a data warehouse implementation, it is essential to adhere to best practices such as:

Define clear business objectives: Clearly define the goals and objectives of the data warehouse to ensure it aligns with business needs. As mentioned in a previous post, collecting unnecessary data may lead to multiple problems. Similarly, storing unnecessary data can lead to higher prices and cluttering.

Use a data governance framework: Implement a data governance framework to ensure data quality, consistency, and security.

Involve stakeholders: Engage all relevant stakeholders throughout the implementation process to ensure buy-in and adoption.

Monitor and maintain: Continuously monitor and maintain the data warehouse to ensure it remains up-to-date and meets the evolving needs of the business.

How much UA data should I keep?

The recommended retention period for UA data depends on the specific business requirements. However, it’s generally advisable to retain data for at least 2 years to gain meaningful insights and identify trends.

How much historic data is in UA?

This will depend on a few factors. In terms of what can be exported, this will depend on your data retention setting – which can be anywhere between 14 months to unlimited (the default is 26 months). The latest date your Universal Analytics has data for will be subject to when it stopped collecting data when UA sunsetted in favour of GA4.

Can data be exported manually from UA?

This is possible into Google Sheets or Excel, but bear in mind there is a limit of 5,000 rows per export, which can soon be reached if breaking data down by multiple dimensions such as data, page, source and medium. There will also be potential issues with merging or connecting data to other sources, especially if you have a high volume of data that is spread over multiple exports.

A single export into a data warehouse (e.g BigQuery) would make it easier to view and analyse data – examples of this would be collecting combinations of products into common product group names or grouping landing pages together to see data by business area or geographic regions (whatever your page structure allows for).

Conclusion

UA data warehousing is essential for businesses looking to unlock deeper, longer-term insights from their website traffic and user behaviour. By storing and analysing UA data in a centralised repository, businesses can improve their reporting and analysis capabilities, enhance data quality, and gain a competitive advantage in the data-driven marketplace.

Space & Time offers a variety of packages ranging from simple UA data warehousing through to broader data architecture and visualisation support. If storing and analysing data is in your business interests, get in touch with [email protected] to talk about how we can help you take a step in the right direction.