How to Recreate Universal Analytics Metrics in Google Looker Studio
As Universal Analytics sunsets, marketers and analysts face the challenge of maintaining key insights in Google Looker Studio. Transitioning to Google Analytics 4 (GA4) opens up new ways to analyze data, but some favorite metrics and dimensions need to be recreated. This guide outlines step-by-step instructions to help you rebuild essential metrics in Looker Studio.
Getting Started: Setting Up Your Data Source
Before diving into custom metrics, ensure your GA4 data is connected to Looker Studio:
- Go to Looker Studio.
- Click on Create and select Data Source.
- Choose Google Analytics and connect your GA4 property.
With this setup complete, you’re ready to recreate Universal Analytics metrics.
Step 1: Page Path Levels
Breaking down URLs into meaningful sections is vital for understanding site structure and user navigation. Here’s how you can create custom fields for page path levels:
Page Path Level 1
Formula:
sqlCopyEditREGEXP_EXTRACT(Full page URL, '^(.+?/)')
Explanation:
Extracts the first section of the URL structure, such as https://example.com/section/. This helps identify broad categories or site sections.
Page Path Level 2
Formula:
sqlCopyEditREGEXP_EXTRACT(Page path, '^(.+?/)')
Explanation:
Pulls the second level of the URL, such as /section/subsection/. This provides more detailed insights into your site’s content hierarchy.
Page Path Level 3
Formula:
sqlCopyEditREGEXP_EXTRACT(Page path, '/.*/(.*/)')
Explanation:
Captures the third level of the URL structure, enabling precise tracking of specific subcategories or pages. This is particularly useful for detailed content analysis.
Step 2: Shopping Behavior
Mapping the ecommerce funnel is crucial for understanding user activity across the customer journey. Use this custom formula to categorize key steps:
Formula:
sqlCopyEditCASE WHEN Event name="session_start" THEN "1) Sessions"
WHEN Event name="view_item" THEN "2) Product View"
WHEN Event name="add_to_cart" THEN "3) Add To Cart"
WHEN Event name="begin_checkout" THEN "4) Checkout"
WHEN Event name="purchase" THEN "5) Transaction" END
Explanation:
This formula helps visualize the user journey through the following stages:
- Sessions: Users starting a session on your site.
- Product View: Viewing a product page.
- Add To Cart: Adding a product to the cart.
- Checkout: Beginning the checkout process.
- Transaction: Completing a purchase.
This overview provides clarity on where users drop off and where improvements are needed.
Step 3: User Type
Differentiating between new and returning users is vital for evaluating customer loyalty and retention strategies. Here’s the custom formula:
Formula:
sqlCopyEditCASE WHEN First session date=Date THEN "New Visitor" ELSE "Returning Visitor" END
Explanation:
- New Visitor: Users visiting the site for the first time.
- Returning Visitor: Users who have visited the site before.
This metric helps identify trends in user engagement and retention.
Advanced Metrics
To further enhance your analysis, create these advanced metrics in Looker Studio:
1. Avg. Time on Page
Formula:
sqlCopyEditUser engagement / Total users
Explanation:
Calculates the average time users spend on each page, providing insights into content quality and engagement.
2. Avg. Event Value
Formula:
sqlCopyEditEvent value / Event count
Explanation:
Tracks the average value of user actions, such as clicks or form submissions, helping measure the effectiveness of interactions.
3. Avg. Price
Formula:
sqlCopyEditItem revenue / Item purchase quantity
Explanation:
Helps analyze the average price of purchased items, useful for pricing strategies and revenue insights.
4. Cart Abandonment
Formula:
sqlCopyEditAdd-to-carts - Checkouts
Explanation:
Identifies the number of users who added items to their cart but didn’t proceed to checkout, helping diagnose issues in the purchase funnel.
5. Check-Out Abandonment
Formula:
sqlCopyEditCheckouts - Transactions
Explanation:
Tracks users who began the checkout process but didn’t complete a purchase, highlighting potential barriers in the checkout flow.
6. No Shopping Activity
Formula:
sqlCopyEditSessions - Item views
Explanation:
Shows the number of sessions where no products were viewed, offering insights into user engagement and navigation.
7. Pages / Sessions
Formula:
sqlCopyEditViews / Sessions
Explanation:
Calculates the average number of pages viewed per session, a useful metric for tracking engagement and content depth.
Creating Abandonment Tables
Use the following metrics to create detailed abandonment reports for deeper insights:
- No Shopping Activity:
Formula:Sessions - Item Views
Insight: Identifies sessions where users didn’t engage with products. - Cart Abandonment:
Formula:Add-to-carts - Checkouts
Insight: Tracks users who added items to the cart but didn’t initiate checkout. - Check-Out Abandonment:
Formula:Checkouts - Transactions
Insight: Measures drop-offs during the checkout process.
Segment these metrics by user type or device to uncover specific trends and improve the user experience.
Recreating Universal Analytics metrics in Google Looker Studio ensures continuity in your data analysis as you transition to GA4. By following these steps, you can maintain actionable insights and build a robust reporting framework.
If this guide was helpful, feel free to bookmark it, share it with your team, or comment on what metrics you found most valuable. For more analytics tips and updates, keep following us!