Guide to Exporting Universal Analytics Data to BigQuery Before the 2024 Deadline

Getting Started: Importance of Exporting Universal Analytics Data Before July 1, 2024

In this article, I would like to show how to solve the data transfer issue that arises with Universal Analytics. Many people who use Universal Analytics received a message on a popup screen:

"This property is no longer processing new data. Save your property data before it's deleted. Finish migrating to Google Analytics 4 (GA4) before July 1, 2024, using the Setup Assistant, if you haven't already done so. To maintain access to data from this property, you should download your data now."

Brief Overview of the Upcoming Google Policy Change

Google is phasing out Universal Analytics in favor of Google Analytics 4 (GA4). As of July 1, 2024, Universal Analytics properties will stop processing new data. To avoid losing valuable historical data, it's crucial to export your data before this deadline.

Importance of Preserving Historical Data for Future Analytics

Historical data is invaluable for understanding trends, making informed decisions, and creating future strategies. Losing this data would mean losing years of insights into customer behavior, website performance, and marketing effectiveness. Therefore, preserving this data by exporting it to a secure and accessible location, like BigQuery, is essential.

Overview of the Process

There are several ways to export data from your Universal Analytics property:

• Export individual reports into formats like CSV, TSV, Excel (XLSX), Google Sheets, and PDF.
• Google Analytics 360 customers can also export data to BigQuery.

At Dot Analytics, we use Google Sheets for aggregated daily data and a custom BigQuery schema to collect more granular data, including native UA dimensions like source, medium, campaign, age, gender, country, city, item name, etc.

If you do not save your data, you will lose the ability to analyze it. The UA interface will no longer have access to this data. The amount of historical data for normally working businesses can span several years. Losing a few years of data is not something you want to happen.

Summary of the Steps Involved in Exporting Universal Analytics Data to BigQuery

The process of exporting Universal Analytics data to BigQuery depends on the granularity you want to preserve and the format in which you wish to store this information. Aggregated data is easier to save and takes up less space, whereas non-aggregated data is more space-consuming.

Here are the prerequisites and steps for exporting data from Universal Analytics to BigQuery:

Prerequisites:

• ???Access to Universal Analytics

• ???Google Cloud Project (Trial or connected to billing)

• ???Service Account for Measurement API

• ???Script that saves data to BigQuery

By following these steps, you can ensure that your historical data is preserved and accessible for future analysis, helping you make informed decisions and maintain valuable insights from your past performance.

Why Export Universal Analytics Data to BigQuery?

Data Preservation

Preserving historical data is essential for maintaining long-term insights and performing trend analysis. By exporting your data from Universal Analytics to BigQuery, you ensure that your valuable historical data is not lost when Google phases out Universal Analytics. This historical data helps you understand trends over time, track the success of past campaigns, and make informed decisions for future strategies. Losing this data could mean losing years of valuable information about customer behavior, website performance, and marketing effectiveness.

Improved Analytics Capabilities

Exporting data to BigQuery offers significant advantages due to its powerful data processing capabilities. While data can be exported to other mediums like Google Sheets or Looker Studio, BigQuery stands out for several reasons:

1. Handling Large Datasets: BigQuery is designed to handle large volumes of data efficiently, making it ideal for storing and analyzing extensive datasets from Universal Analytics.
2. Combining Data Sources: You can seamlessly combine historical data from Universal Analytics with new data from Google Analytics 4, enabling comprehensive and continuous analysis.
3. Advanced Querying: BigQuery supports custom SQL queries, allowing you to perform complex analyses and extract deep insights. You can use aggregation functions like SUM, COUNT, AVERAGE, and MEDIAN to summarize data. Additionally, BigQuery offers advanced analytics functions and window functions, such as ROW_NUMBER, LEAD, LAG, and NTH_VALUE, providing a robust toolkit for data analysis.
4. Machine Learning Capabilities: With BigQuery ML, you can apply statistical algorithms like regression directly within BigQuery, enabling predictive analytics and advanced modeling without needing to move your data elsewhere.

By leveraging BigQuery's capabilities, you can not only preserve your historical data but also enhance your analytical capabilities, gaining deeper insights and making more informed decisions for your business. Exporting Universal Analytics data to BigQuery is a crucial step in ensuring that your data remains accessible and useful for future analysis.

Prerequisites for Exporting Data

Google Cloud Account Setup

To export Universal Analytics data to BigQuery, you need a Google Cloud account.

Follow these steps to set it up:

1. Go to Google Cloud and click on "Get Started for Free".
2. Sign in using your Google email (Gmail).
3. Once signed in, you'll be prompted to set up your billing account. You can start with the free $300 credit or connect a billing account for future usage.
4. After setting up billing, go to the Google Cloud Console.
5. Click on "New Project" and follow the instructions to create a new project. Give your project a name and select your billing account.
6. Once your project is created, you'll need to enable the BigQuery API. Go to the "API & Services" dashboard, click "Enable APIs and Services", and search for "BigQuery API". Enable it for your project.

Accessing Universal Analytics Data

To collect data from Universal Analytics, you need to ensure proper access:

1. Sign in to your Google Analytics account and navigate to the Admin section.
2. Make sure you have the necessary permissions to access the data you want to export. You should have at least "Read & Analyze" permissions for the relevant properties.
3. Create a service account in your Google Cloud project. This service account will be used to access your Universal Analytics data securely:

• In the Google Cloud Console, go to the "IAM & Admin" section.
• Click on "Service Accounts" and then "Create Service Account".
• Provide a name for your service account, and assign it the "Editor" role.
• Create a key for your service account, which will be downloaded as a JSON file. Keep this file safe, as you will need it to authenticate your data requests.

Understanding BigQuery Basics

BigQuery is a powerful data warehousing solution, and understanding its basics is crucial for exporting UA data to BigQuery:

• Tables and Datasets: In BigQuery, data is organized into tables, which are grouped into datasets. Create a dataset within your project to store your Universal Analytics data.
• SQL Queries: BigQuery uses standard SQL for querying data. Familiarize yourself with basic SQL commands such as SELECT, FROM, WHERE, and JOIN to perform data analysis.
• Loading Data: Data can be loaded into BigQuery tables using the web UI, the command line tool, or the BigQuery API. For exporting Universal Analytics data, you will typically use the BigQuery API or a custom script.

By following these steps, you'll be well-prepared for transitioning from Universal Analytics to BigQuery. Ensuring proper setup and access, along with a basic understanding of BigQuery, will make the data export process smooth and efficient. These data export techniques will help you in saving Google Analytics data effectively.

Step-by-Step Guide to Exporting Universal Analytics Data to BigQuery

Step 1: Create a BigQuery Project

To begin exporting Universal Analytics (UA) data to BigQuery, you'll first need to create a BigQuery project within Google Cloud.

Create a Google Cloud Project:

1. Go to Google Cloud and sign in with your Google email.
2. Click on "Get Started for Free" if you haven't set up billing yet. You can use the free $300 credit or link a billing account.
3. Once logged in, navigate to the Google Cloud Console.
4. Click on "New Project", provide a project name, and select your billing account. Follow the instructions to create your project.

Create a Dataset in BigQuery:

1. In the Google Cloud Console, go to the BigQuery section.
2. Click on your project name and then "Create Dataset".
3. Name your dataset, for example, "universal_analytics_data"

Step 2: Enable BigQuery API

To integrate your data, you need to enable the necessary APIs:

1. In the Google Cloud Console, go to the "API & Services" dashboard.
2. Click on "Enable APIs and Services".
3. Search for "BigQuery API" and click "Enable".

Step 3: Set Up Data Transfer from Universal Analytics to BigQuery

Next, configure the data transfer service to move UA data to BigQuery.

Create a Service Account:

1. Go to the "IAM & Admin" section in the Google Cloud Console.
2. Click on "Service Accounts" and then "Create Service Account".
3. Name your service account and assign it the "Editor" role.
4. Create a key for the service account, which will download as a JSON file. Keep this file safe, as it will be used for authentication

Configure Data Access:

1. Ensure you have "Read & Analyze" permissions for the relevant UA properties in your Google Analytics account.
2. Add your user to the User Management section in Universal Analytics to grant access.

Launch the Data Transfer Script:

1. Write or obtain a script to transfer data from UA to BigQuery. If you don't have a script, contact Dot Analytics for assistance.
2. Run the script, making sure to authenticate using the service account JSON file

Step 4: Define Your Data Schema

It's crucial to decide how to structure your data in BigQuery to ensure it aligns with your analysis needs.

Identify Key Dimensions and Metrics:

1. Determine which dimensions and metrics are essential for your analysis. Remember that UA allows only up to 9 metrics or dimensions per API request.
2. Plan your data schema accordingly, ensuring all critical data points are included. This step involves schema configuration for analytics data, which is vital for accurate analysis.

Create Tables:

1. In your BigQuery dataset, create tables to store the data. For example, you might have tables for sessions, users, and events, each with relevant dimensions and metrics.
2. Preconfigure your tables to match the structure of your existing dashboards to maintain continuity in your reports. This ensures that managing BigQuery projects remains efficient and organized.

Step 5: Schedule and Automate Data Transfers

While automating the data transfer from UA to BigQuery might not be necessary for historical data, setting up automation can be beneficial for ongoing data from Google Analytics 4 (GA4).

Set Up Scheduled Queries:

1. In BigQuery, you can set up scheduled queries to automate data transfers. This ensures your BigQuery tables are updated regularly without manual intervention.
2. Go to the "Scheduled Queries" section in BigQuery, create a new scheduled query, and set the frequency of updates. This is crucial for an automated data sync to BigQuery.

Integrate GA4 Data:

1. As you transition from Universal Analytics to BigQuery, start integrating GA4 data to keep your datasets up-to-date with the latest information.
2. Configure data transfer services to include GA4 data, ensuring a seamless transition and continuous data flow. This involves setting up data transfers to BigQuery for new GA4 data while you backfill UA data in BigQuery.

By following these steps, you can effectively export UA data to BigQuery, preserving your historical data and leveraging BigQuery's powerful analytics capabilities. This structured approach ensures your data remains accessible and useful, facilitating better decision-making and insights. Implementing API integration for data export and automating the process helps in maintaining an efficient data flow.

Best Practices for Universal Analytics Data Export to BigQuery

Data Validation and Verification

Ensuring the integrity of your exported data is crucial for accurate analysis. Here are some tips to validate and verify your data, ensuring data integrity post-transfer:

1. Compare Metrics and Dimensions: After exporting your UA data to BigQuery, use SQL queries to compare the metrics and dimensions in BigQuery with those in Universal Analytics. Ensure that what you see in Universal Analytics matches what you see in BigQuery.
2. Row Counts and Sums: Calculate the number of rows and the sums of key performance indicators (KPIs) for each date. This helps ensure that the data export process has captured all the necessary information without discrepancies.
3. Data Cleaning: Use the opportunity to clean your data in BigQuery. Fill in missing values or replace incorrect ones to improve the quality of your datasets.

By following these practices, you can be confident that your data is accurately represented in BigQuery, enabling reliable analysis and reporting. This helps in troubleshooting BigQuery imports and ensuring smooth data operations.

Cost Management in BigQuery

Managing costs in BigQuery is essential to ensure that your data storage and processing remain economical. Here are some strategies for cost-effective BigQuery usage:

1. Understand BigQuery Pricing: BigQuery charges for both storage and data usage. Familiarize yourself with the pricing model to better manage your costs. Storage costs are based on the amount of data stored, while data usage costs depend on the amount of data processed by your queries.
2. Optimize Queries: Write efficient SQL queries to minimize the amount of data processed. Use techniques such as partitioning and clustering your tables to reduce query costs.
3. Monitor Usage: Regularly monitor your BigQuery usage through the Google Cloud Console. Set up budget alerts to notify you when you approach your spending limits, helping you to avoid unexpected costs.
4. Use Scheduled Queries Wisely: Don’t run the scheduled queries too often.

Implementing these cost management strategies will help you make the most of BigQuery's powerful capabilities while keeping expenses under control. By following these best practices, you can efficiently manage your Universal Analytics data export to BigQuery, ensuring data integrity post-transfer and cost-effective BigQuery usage.

Common Challenges and Solutions

Troubleshooting Common Issues

When exporting Universal Analytics data to BigQuery, you may encounter several challenges. Here are some common issues and their solutions:

1. Incompatible Dimensions: One common problem is combining dimensions that are not compatible, which causes the API request to return zero rows. To avoid this, ensure that the dimensions you select can be used together according to Google's documentation.
2. High Data Granularity: Using highly granular data, such as medium/source, can result in incomplete data exports, especially when dealing with long time periods. For instance, exporting data since 2015 might fail because of changes in Universal Analytics over time. To mitigate this, try breaking down your data into smaller time frames and aggregating data where possible.
3. API Limitations: The Universal Analytics API has limitations on the number of metrics and dimensions that can be requested at once (up to 9 metrics or dimensions). Plan your data requests accordingly to ensure they are within these limits. This is crucial for successful analytics data archiving.

Ensuring Data Completeness and Accuracy

To verify that all required data has been successfully exported and is accurate, follow these steps:

1. Compare Datasets: After exporting data to BigQuery, compare it with the data in the Universal Analytics interface. Calculate summary statistics, such as totals and averages, for both datasets to ensure they match. This step is essential for continuous data management.
2. Visualize Data: Visualization can help spot discrepancies and validate data integrity. Use tools like Google Data Studio to create visual reports that compare the data from Universal Analytics and BigQuery.
3. Check Row Counts and Sums: Count the number of rows and sum key metrics for specific time periods in both Universal Analytics and BigQuery. Any significant differences could indicate incomplete or inaccurate data exports.

By following these practices, you can troubleshoot common issues and ensure the completeness and accuracy of your data. Implementing these solutions will help you maintain data integrity post-transfer and achieve reliable, cost-effective BigQuery usage. Analytics data archiving and continuous data management are essential for preserving the value and usability of your historical data.

Leveraging BigQuery for Enhanced Analytics

Advanced Analytics with BigQuery

Now that the most tedious work is done, you can start to analyze your data using BigQuery’s advanced capabilities. While you can perform some analysis in the Universal Analytics interface, BigQuery offers powerful tools to enhance your insights.

With BigQuery, you can run statistical algorithms to show trends and make predictions based on historical data. For example, if you have several years of data collected, you can use BigQuery ML (Machine Learning) to apply various models:

• Linear Regression: Ideal for forecasting, such as predicting future sales of an item based on historical data.
• Logistic Regression: Useful for classification tasks, like categorizing customer values into low, medium, or high.
• K-means Clustering: Helps segment your data, for instance, identifying different customer segments based on behavior.
• Matrix Factorization: Perfect for creating recommendation systems, offering personalized product suggestions based on past customer interactions.
• Principal Component Analysis (PCA): Reduces data dimensions while preserving significant variations, useful for simplifying large datasets.
• Time Series Forecasting: Automatically handles anomalies and seasonality, making it easier to predict future trends.

These advanced analytics capabilities enable you to uncover deeper insights and make more informed decisions.

Integrating with Other Data Sources

BigQuery also allows you to combine Universal Analytics (UA) data with other data sources for richer insights. Here are some tips for integrating data:

• Merge Data Sources: Use BigQuery to merge your UA data with other datasets, such as CRM data, sales data, or third-party data sources. This can provide a more comprehensive view of your business.
• Create Unified Reports: Combine different data sources to create unified reports in tools like Google Data Studio. This helps in visualizing data from multiple perspectives and drawing more accurate conclusions.
• Leverage External APIs: Integrate data from external APIs directly into BigQuery. For instance, you can pull in social media data, weather data, or economic indicators to enrich your analysis.

By integrating various data sources within BigQuery, you can achieve a more holistic view of your data, enabling advanced analytics and deeper insights. This approach not only enhances your analytics capabilities but also supports continuous data management and more effective decision-making.

Final Thoughts on Universal Analytics Data Export

Recap of Key Steps and Benefits

Exporting your Universal Analytics data to BigQuery involves several important steps: creating a BigQuery project, enabling the BigQuery API, setting up data transfers, defining your data schema, and scheduling automated data syncs. By taking these steps, you can ensure your historical data is preserved, enabling advanced analytics and better decision-making. The benefits include maintaining data integrity, leveraging powerful analytics tools, and integrating various data sources for richer insights.

It's crucial to act swiftly as the deadline for transitioning from Universal Analytics to BigQuery is approaching. If you don't export your data before the deadline, you risk losing valuable historical insights that are essential for long-term trend analysis and strategic planning. Don't wait until it's too late to secure your data.

For personalized assistance with exporting your Universal Analytics data to BigQuery, contact Dot Analytics team. Our team is equipped to help streamline this transition with minimal disruption, ensuring your data is safe and ready for advanced analysis. Reach out today to ensure a smooth and efficient data export process.