How to Match Visits to Adobe Analytics Reports for a Specific Country

How to Match Visits to Analytics Reports for a Specific Country

When working with web analytics, it's common to want to segment your data by specific countries in order to get more granular insights. For businesses operating internationally, understanding how visitors from different countries interact with their website can help in tailoring marketing campaigns, improving user experience, and optimizing content for specific regions.

In this post, we’ll look at SQL queries that can be used to extract country-specific data and analyze visits and events from analytics reports.

Matching Visits to Analytics Reports by Country

To match visits to Analytics reports for a specific country, you can use the following query. In this case, we’ll focus on Brazil (country code 'BRA'). The query pulls relevant data about visits, page events, and post events based on certain conditions.

Query for Country-Specific Analytics (Brazil)

SELECT COUNT(DISTINCT(CONCAT(post_visid_high, post_visid_low, visit_num))),
       pagename, 
       page_event, 
       post_event_list 
FROM test12345 
WHERE exclude_hit = '0' 
  AND geo_country = 'BRA' 
  AND hit_source = '1' 
  AND bot_id = '0';

Explanation:

• COUNT(DISTINCT(CONCAT(post_visid_high, post_visid_low, visit_num))): This part of the query counts the number of distinct visits by concatenating the post_visid_high, post_visid_low, and visit_num. This ensures each visit is counted once, regardless of how many pages are visited.

• pagename: The query retrieves the page name from the reports.

• page_event: Retrieves data about page-specific events.

• post_event_list: This lists any post-visit events associated with the visit.

• test12345: This is the table where the analytics data is stored.

• exclude_hit = '0': Excludes any hits marked as invalid or irrelevant.

• geo_country = 'BRA': Filters the data for Brazil (country code 'BRA').

• hit_source = '1': Filters the hits to ensure only legitimate hits are included.

• bot_id = '0': Ensures that data from bots is excluded.

By running this query, you can isolate all visits from Brazil, providing insights into user engagement, page events, and other interactions.

General Query for Visit Data

If you want to retrieve visit data without focusing on a specific country, you can run a more general query like the one below:

SELECT COUNT(DISTINCT(CONCAT(post_visid_high, post_visid_low, visit_num))),
       pagename, 
       page_event, 
       post_event_list 
FROM test12345 
WHERE exclude_hit = '0' 
  AND hit_source = '1' 
  AND bot_id = '0';

Key Differences:

• This query does not filter by the geo_country field, meaning it retrieves data for all countries.

• The rest of the conditions (excluding hits, bot data, etc.) remain the same to ensure clean, valid data.

Use Cases for These Queries

1. Geo-Specific Analysis: By running the country-specific query, you can focus on how users from a particular country interact with your website.

2. Event Tracking: Tracking page events and post-visit events allows you to understand how users engage with your content after visiting.

3. Filtering Invalid Hits: The use of exclude_hit = '0' and bot_id = '0' ensures that your reports are not skewed by irrelevant data, such as bot traffic or test hits.

Conclusion

Using these queries, you can segment your analytics data by country and gain deeper insights into how users from specific regions engage with your website. Whether you're optimizing your content for users in Brazil or another region, the ability to isolate data based on geography and user behavior is a powerful tool for improving your marketing efforts and user experience.

Let me know if you need further customization or help with SQL queries for other use cases!