In the rapidly evolving landscape of digital analytics, the preservation of historical data from Universal Analytics is paramount for organizations seeking to maintain valuable insights and continuity in their analytics endeavors. As Universal Analytics approaches retirement, transitioning to newer platforms like Google Analytics 4 (GA4) becomes inevitable. However, the significance of historical data cannot be overstated, as it provides crucial context and trends that inform strategic decision-making.

Against this backdrop, comprehending the strategies and limitations associated with preserving historical data from Universal Analytics becomes imperative for organizations aiming to derive maximum value from their analytics initiatives. This blog delves into various preservation methods, including manual export, data export to BigQuery, and utilization of the Google Analytics Reporting API. These approaches facilitate the seamless preservation and integration of historical data for advanced analysis and reporting.

Moreover, leveraging tools such as the Google Analytics Spreadsheet Add-On provides a user-friendly means to manipulate historical data directly within Google Sheets. While these methods offer undeniable benefits, it's crucial to recognize their inherent limitations, underscoring the importance of meticulous planning and strategic execution in the preservation process.

Why Preserving Historical Data is Essential

With the retirement of Universal Analytics in the coming days, Google Analytics 4 (GA4) has become the industry standard. This transition underscores the importance of promptly importing Universal Analytics data into GA4 to preserve your historical data. Google strongly advises users to export historical data from Universal Analytics to ensure no valuable insights are lost.

Exporting all relevant user interaction and event data assures that no information is overlooked in the transition process. 

Preserving historical data from Universal Analytics (GUA) is vital for various reasons:

• • Reference for Past Performance: GUA historical data provides a reference point for past performance metrics, including website traffic, user engagement, conversion rates, and marketing campaign effectiveness. By analyzing this historical data, organizations can identify trends, patterns, and insights that can inform future strategies and decision-making.

• • Comparison with GA4 Data: Comparing historical GUA data with data collected in Google Analytics 4 (GA4) allows organizations to evaluate the impact of the transition on their analytics metrics. This comparison can reveal any discrepancies or differences between the two platforms and help ensure data integrity during the migration process.

• • Long-term Trend Analysis: Historical GUA data enables long-term trend analysis, allowing organizations to identify seasonal trends, recurring patterns, and shifts in user behavior over time. This long-term perspective is essential for understanding the evolution of digital trends and making informed predictions about future performance.

• • Support for Reporting and Analysis: GUA historical data serves as a valuable resource for reporting and analysis purposes, providing a comprehensive record of past performance metrics. This data can be used to generate reports, dashboards, and visualizations that track historical trends and performance indicators, helping stakeholders understand past performance and make data-driven decisions.

Restoring Historical Data from Universal Analytics:

There are four methods available for preserving historical data from Universal Analytics. Let's examine each of them in depth.

1. 1. Manual Export: The simplest method involves accessing the desired reports in Universal Analytics. Customize these reports by applying filters, segments, or secondary dimensions to tailor them to your specific analysis needs. Once customized, navigate to the top right corner and click on "Export," then choose the appropriate file format for saving the data.
      

               

      Although manually exporting data from Universal Analytics (UA) is the simplest method, it has limitations. You can only use two dimensions for data analysis and are limited to 5,000 rows per report. If you use custom reports with more dimensions, your data is more likely to be sampled, especially if you're tracking thousands of hits per day. To make sure Google Analytics hasn't sampled your data, look for a green checkmark shield next to the report title. If you see it, your data hasn't been sampled.

      

            

   2.  Data Export to BigQuery: For proficient users adept with APIs, cloud storage presents opportunities to extract and process large datasets for future insights. Google BigQuery stands out as the top choice for many users due to its seamless integration with key products such as Google Ads, Looker (Data) Studio, and Sheets.
      
      Google Analytics 360 users can swiftly stream data into BigQuery by utilizing the built-in integration between these platforms. Moreover, within 24 hours of setting up a billing account in Google Cloud Platform (GCP) and creating a project, users gain access to both current and historical insights dating back 13 months (or 10 billion hits) from the integration date.
      
      This integration allows Google Analytics 360 users to accumulate data spanning up to 31 months before Universal Analytics (UA) data collection ceases. By proactively setting up this BigQuery export, users can securely store a more detailed and insight-rich perspective on Google servers. For non-enterprise users without an Analytics 360 account, third-party solutions may offer connectivity between Google Analytics and BigQuery, albeit without the ability to access historical data as seamlessly as native integration allows.
      
      
   3. Exporting data with the Google Analytics Reporting API: Organizations with data older than 13 months or without coverage from a third-party connector can manually export all Universal Analytics historical data in CSV format and leverage the Google Analytics Reporting API. Experienced developers capable of executing code within an application may be necessary to configure the process and extract historical data efficiently. This approach could ultimately save time when exporting to Excel/Google Spreadsheet or transferring to BigQuery. 
      
      Exporting data with the Google Analytics Reporting API in BigQuery (BQ) enables seamless transfer of Google Analytics (GA) data for advanced analysis. This involves querying GA data via the API and exporting it directly to BigQuery. Steps include configuring access, querying data, transforming it as needed, exporting to BigQuery, automating tasks, analyzing data, and visualizing results. This process empowers organizations to harness GA's analytics with BigQuery's powerful data processing capabilities for valuable insights.
      
      However, as with any solution utilizing an API, there are limitations:
      - You cannot retrieve user-session information.
      - By default, you cannot obtain client IDs for in-depth analysis (only available via custom dimensions if configured).
      - Generally, the API enables the extraction of essential reports, such as eCommerce, events, acquisition, etc. This is particularly useful for trend analysis based on historical data, especially if your GA4 property has not been configured yet.
      
      
   4. Exporting Google Analytics UA data to Google Sheets using Google add-on: You have the option to efficiently modify your Google Analytics historical data directly within an online document using either the API or the Spreadsheet Add-On. With the Google Analytics Spreadsheet Add-On, you can:
      
      • Develop custom calculations utilizing report data
      • Fetch data from numerous views
      • Schedule reports for automatic updates
      • Generate visualizations using provided tools and embed them on external websites
      • Handle privacy settings seamlessly
        

      Here are the steps you should follow to export Google Analytics UA data to Google Sheets using Google add-on:

      1. 1. Sign in to your Sheets account and initiate a new spreadsheet to synchronize with your data.
         2. Modify the table settings in the new spreadsheet to customize cell formatting for dates, numbers, and more when automatically importing data from Google Analytics. If default regional settings are in place and need adjustment, navigate to File and choose Settings.
         3. Change the regional setting  and click Save
         4. Next, you need to install the add-on. To do this, click Extensions > Add-ons > Get add-ons
            
         5. in the dialog box, select Google Analytics or use the search function to find it:
         6. Once you install the extension, select the account whose data you want to send to the Google Analytics app and share Google Analytics access with your Google account
         7. Then go to Extensions > Google Analytics > Create a new report
            
         8. Then specify the setting for the request in the menu; for example, create a download request
         9. Next, click Create report and go to the page with the request; after that, you can change the date range as well as apply different filters and sorting options
         10. Under the table with parameters, you’ll find a link to the parameters reference and other information on working with the extension
             
         11. Next, run the report to upload data to Google Sheets; to do this, select Extensions > Google Analytics > Run reports. In the end, you’ll see uploaded data, including specific parameters and indicators, in a new sheet within your document.
             

Limitations of the API and Add-On:

Dealing with the limitations of the API and Add-On involves understanding constraints such as restricted access to raw data and the need to combine dimensions and metrics in a single query. To address this, reports may need to separate user- and session-based data from other metrics and enrich specific datasets with attributes like traffic source or audience information. Additionally, since only nine dimensions and ten metrics are available in any single report, careful planning and strategic design of actions are essential for exporting previously collected data.

Looking for guidance on preserving your historical data effectively? Rest assured, Napkyn is here to provide expert guidance.

Feeling overwhelmed by the myriad of options for transferring data from UA? No need to fret. Our team of GA4 experts is at your service. Renowned for our GA4 migration services, we ensure the safeguarding of your historical data. Simply reach out to Napkyn, and we'll respond promptly. Together, we'll tailor the ideal solution to your requirements, whether through manual data extraction, leveraging third-party tools, or employing advanced data migration techniques. Napkyn has a strong track record of guiding clients through exporting historical data, ensuring safe transfers, and offering cost-effective solutions tailored to each client's needs, showcasing our expertise in this field.

Conclusion:

In conclusion, the transition from Universal Analytics to Google Analytics 4 (GA4) necessitates the preservation of historical data, ensuring valuable insights are not lost in the process. Through methods like manual export, data export to BigQuery, and leveraging the Google Analytics Reporting API, organizations can seamlessly integrate their historical data into GA4 for advanced analysis and reporting. However, it's important to acknowledge the limitations of these methods, including restricted access to certain data and the need for careful planning when designing data export strategies. By overcoming these challenges and leveraging the available tools and techniques, organizations can unlock actionable insights from their historical data, driving informed decision-making and maximizing the value of their analytics efforts in the ever-evolving digital landscape.

However, if you still need support extracting your data from Universal Analytics, we are happy to help! Reach out to our experts, and we’ll ensure that all of your data is safe and sound.

References:

• https://developers.google.com/admin-sdk/reports/v1/get-start/overview

• https://support.google.com/analytics/answer/1038573?hl=en

• https://developers.google.com/apps-script/add-ons/editors/sheets

• https://cloud.google.com/bigquery/docs/exporting-data

• https://support.google.com/docs/answer/9702507?hl=en