The Project | About Us | Contribute | Donations | License

HOME

In this section, we will explore how to use BigQuery to analyze web and app analytics data. This involves understanding the types of data typically collected, how to structure and query this data, and how to derive meaningful insights.

Key Concepts

Web and App Analytics Data:
- Page Views: Number of times a page is viewed.
- Sessions: A group of user interactions within a given time frame.
- Users: Unique visitors to the website or app.
- Events: Specific actions taken by users (e.g., clicks, form submissions).
Data Sources:
- Google Analytics: A popular tool for tracking web and app analytics.
- Firebase Analytics: Used for tracking app analytics.
Data Structure:
- Tables: Typically, data is stored in tables with columns representing different metrics and dimensions.
- Schemas: Define the structure of the data, including data types and relationships.

Practical Example

Step 1: Setting Up the Data

Assume you have a dataset named web_analytics with a table events that contains the following schema:

Column Name	Data Type	Description
event_id	STRING	Unique identifier for the event
user_id	STRING	Unique identifier for the user
event_name	STRING	Name of the event (e.g., page_view, click)
event_time	TIMESTAMP	Time when the event occurred
page_url	STRING	URL of the page where the event occurred
referrer	STRING	Referrer URL
device	STRING	Device type (e.g., mobile, desktop)

Step 2: Querying the Data

Example 1: Counting Page Views

To count the number of page views for each page, you can use the following SQL query:

SELECT 
  page_url, 
  COUNT(*) AS page_views
FROM 
  `project_id.web_analytics.events`
WHERE 
  event_name = 'page_view'
GROUP BY 
  page_url
ORDER BY 
  page_views DESC;

Explanation:

SELECT page_url, COUNT(*) AS page_views: Selects the page URL and counts the number of occurrences.
FROM project_id.web_analytics.events: Specifies the table to query.
WHERE event_name = 'page_view': Filters the events to only include page views.
GROUP BY page_url: Groups the results by page URL.
ORDER BY page_views DESC: Orders the results by the number of page views in descending order.

Example 2: Analyzing User Sessions

To analyze the number of sessions per user, you can use the following SQL query:

SELECT 
  user_id, 
  COUNT(DISTINCT session_id) AS sessions
FROM 
  `project_id.web_analytics.events`
GROUP BY 
  user_id
ORDER BY 
  sessions DESC;

Explanation:

SELECT user_id, COUNT(DISTINCT session_id) AS sessions: Selects the user ID and counts the distinct session IDs.
FROM project_id.web_analytics.events: Specifies the table to query.
GROUP BY user_id: Groups the results by user ID.
ORDER BY sessions DESC: Orders the results by the number of sessions in descending order.

Step 3: Deriving Insights

Example 3: Identifying Top Referrers

To identify the top referrers driving traffic to your site, you can use the following SQL query:

SELECT 
  referrer, 
  COUNT(*) AS referral_count
FROM 
  `project_id.web_analytics.events`
WHERE 
  event_name = 'page_view'
GROUP BY 
  referrer
ORDER BY 
  referral_count DESC
LIMIT 10;

Explanation:

SELECT referrer, COUNT(*) AS referral_count: Selects the referrer and counts the number of occurrences.
FROM project_id.web_analytics.events: Specifies the table to query.
WHERE event_name = 'page_view': Filters the events to only include page views.
GROUP BY referrer: Groups the results by referrer.
ORDER BY referral_count DESC: Orders the results by the number of referrals in descending order.
LIMIT 10: Limits the results to the top 10 referrers.

Practical Exercises

Exercise 1: Counting Click Events

Task: Write a query to count the number of click events for each page URL.

Solution:

SELECT 
  page_url, 
  COUNT(*) AS click_count
FROM 
  `project_id.web_analytics.events`
WHERE 
  event_name = 'click'
GROUP BY 
  page_url
ORDER BY 
  click_count DESC;

Exercise 2: Analyzing Device Usage

Task: Write a query to analyze the number of events by device type.

Solution:

SELECT 
  device, 
  COUNT(*) AS event_count
FROM 
  `project_id.web_analytics.events`
GROUP BY 
  device
ORDER BY 
  event_count DESC;

Exercise 3: Session Duration Analysis

Task: Write a query to calculate the average session duration for each user.

Solution:

WITH session_times AS (
  SELECT 
    user_id, 
    session_id, 
    MIN(event_time) AS session_start, 
    MAX(event_time) AS session_end
  FROM 
    `project_id.web_analytics.events`
  GROUP BY 
    user_id, session_id
)
SELECT 
  user_id, 
  AVG(TIMESTAMP_DIFF(session_end, session_start, SECOND)) AS avg_session_duration
FROM 
  session_times
GROUP BY 
  user_id
ORDER BY 
  avg_session_duration DESC;

Common Mistakes and Tips

Incorrect Filtering: Ensure you are filtering the correct event names (e.g., 'page_view', 'click').
Grouping Errors: Always group by the correct columns to avoid aggregation errors.
Time Zone Considerations: Be mindful of time zones when working with timestamps.

Conclusion

In this section, we covered how to analyze web and app analytics data using BigQuery. We discussed key concepts, provided practical examples, and included exercises to reinforce the learned concepts. By mastering these techniques, you can derive valuable insights from your web and app analytics data, helping you make informed decisions to improve user experience and engagement.

Analyzing Web and App Analytics

Key Concepts

Practical Example

Step 1: Setting Up the Data

Step 2: Querying the Data

Example 1: Counting Page Views

Example 2: Analyzing User Sessions

Step 3: Deriving Insights

Example 3: Identifying Top Referrers

Practical Exercises

Exercise 1: Counting Click Events

Exercise 2: Analyzing Device Usage

Exercise 3: Session Duration Analysis

Common Mistakes and Tips

Conclusion

BigQuery Course

Module 1: Introduction to BigQuery

Module 2: Basic SQL in BigQuery

Module 3: Intermediate SQL in BigQuery

Module 4: Advanced SQL in BigQuery

Module 5: BigQuery Data Management

Module 6: BigQuery Performance Optimization

Module 7: BigQuery Security and Compliance

Module 8: BigQuery Integration and Automation

Module 9: BigQuery Machine Learning (BQML)

Module 10: Real-World BigQuery Use Cases