GA4 to BigQuery Mapping Tutorial

GA4 to BigQuery Mapping Tutorial provides the key to translating all the relevant GA4 dimensions and metrics to their corresponding BigQuery dimensions and metrics.

I have successfully mapped around 90% of all available GA4 dimensions and metrics to their corresponding fields in BigQuery.

It is very important to remember that not every GA4 dimension/metric has its corresponding BigQuery field name.

In fact, most GA4 dimensions and metrics lack corresponding BigQuery field names.

Most of them need to be calculated from SQL or derived from your GA4/GTM tracking setup.

You will encounter BigQuery Field Names (Formulas) that are conceptual and assume that you have fields in your BigQuery dataset that directly correspond to these metrics.

If these fields are not directly available, you may need to create them using custom calculations or ensure your GA4 data collection setup captures this data.

I have added a note wherever you encounter conceptual fields.

#1 GA4 Attribution Dimensions to BigQuery Mapping.

By mapping GA4 attribution dimensions to BigQuery fields, you can see the various touchpoints (e.g., organic search, social media ads, email marketing) that led users to a conversion event (e.g., purchase, sign-up).

This breakdown allows you to identify the most effective channels and campaigns to drive conversions.

Here’s a breakdown of GA4 Attribution Dimensions and their corresponding BigQuery fields:

GA4 Attribution Dimension	What it is	BigQuery Field Name (Formula)
Campaign / Campaign ID	Represents the name and ID of a promotion or marketing campaign that led to a key event.	traffic_source.campaign AS campaign_name, traffic_source.campaign_id AS campaign_id
Default channel group	Channel groupings are rule-based definitions of your traffic sources.	traffic_source.medium AS default_channel_group (Note: This is a simplification and might not accurately reflect actual channel grouping logic.)
Manual campaign ID	Displays the ID of a specific campaign or promotion from the click URL or campaign_details event.	traffic_source.manual_campaign_id AS manual_campaign_id
Manual campaign name	Shows the specific campaign or promotion name from the click URL or campaign_details event.	traffic_source.manual_campaign_name AS manual_campaign_name
Manual ad content	Indicates the creative content used from the click URL or campaign_details event.	traffic_source.manual_ad_content AS manual_ad_content
Manual medium	Represents the marketing medium from the click URL or campaign_details event.	traffic_source.manual_medium AS manual_medium
Manual source	Identifies the referrer from the click URL or campaign_details event.	traffic_source.manual_source AS manual_source
Manual source / medium	Combines the source and medium from the click URL or campaign_details event.	CONCAT(traffic_source.manual_source, ' / ', traffic_source.manual_medium) AS manual_source_medium
Manual source platform	Shows the source platform, noted as ‘Manual’, from the click URL or campaign_details event.	traffic_source.manual_source AS manual_source_platform (Note: This might not accurately represent a platform.)
Manual term	Displays the paid keyword from the click URL or campaign_details event.	traffic_source.manual_term AS manual_term
Medium	Describes the method of acquiring users.	traffic_source.medium AS medium
Source	A representation of where traffic originates.	traffic_source.source AS source
Source / medium	Details the source and medium that led a user to your website or application.	CONCAT(traffic_source.source, ' / ', traffic_source.medium) AS source_medium
Source platform	Identifies the platform managing buying activity.	traffic_source.source AS source_platform (Note: This might not accurately represent a platform.)

Some fields, such as ‘Manual source platform’ and ‘Source platform’, do not have a direct corresponding field in the BigQuery export schema.

These dimensions are typically derived from other fields or a combination of fields. 

The ‘Default channel group’ is not a direct field; it can be derived from ‘traffic_source.medium’ and other traffic source fields using the rules defined for channel groupings.

---

#2 GA4 Google Ads Dimensions to BigQuery Mapping.

By mapping GA4 Google Ads Dimensions to BigQuery fields, you can analyze campaign performance in great detail by directly accessing metrics like clicks, impressions, conversions, and cost within BigQuery.

In BigQuery, you can combine Google Ads data with website/app data from GA4 to understand how your ads interact with other user touchpoints.

This holistic view helps you evaluate the overall effectiveness of your marketing efforts.

Here’s a breakdown of GA4 Google Ads Dimensions and their corresponding BigQuery fields:

GA4 Google Ads Dimensions	What it is	BigQuery Field Name (Formula)
Google Ads account name	The account name associated with the Google Ads campaign.	traffic_source.google_ads_account_name AS google_ads_account_name
Google Ads ad group ID	The ad group ID associated with the Google Ads campaign.	traffic_source.google_ads_ad_group_id AS google_ads_ad_group_id
Google Ads ad group name	The ad group name associated with the Google Ads campaign.	traffic_source.google_ads_ad_group_name AS google_ads_ad_group_name
Google Ads ad network type	The ad network type associated with the Google Ads campaign.	traffic_source.google_ads_ad_network_type AS google_ads_ad_network_type
Google Ads campaign	The campaign associated with the Google Ads campaign.	traffic_source.google_ads_campaign AS google_ads_campaign
Google Ads customer ID	The customer ID associated with the Google Ads campaign.	traffic_source.google_ads_customer_id AS google_ads_customer_id
Google Ads keyword text	The keyword associated with the Google Ads campaign.	traffic_source.google_ads_keyword_text AS google_ads_keyword_text
Google Ads query	The search query associated with the Google Ads campaign.	traffic_source.google_ads_query AS google_ads_query

---

#3 GA4 Demographic Dimensions to BigQuery Mapping.

By mapping GA4 Demographic Dimensions to BigQuery fields, you can analyze available demographic data (e.g., gender distribution) to identify general trends within your user base.

If you obtain user consent, you can create custom user properties in GA4 to capture additional demographic information (e.g., location, interests). These would have separate BigQuery fields.

Here’s a breakdown of GA4 Demographic dimensions and their corresponding BigQuery fields:

GA4 Demographics Dimensions	What it is	BigQuery Field Name (Formula)
Age	Categorizes the user’s age into groups such as ’18-24′, ’25-34′, etc.	user_demographics.age_group AS age_group
Gender	Identifies the user’s gender, typically classified as ‘Male’ or ‘Female’.	user_demographics.gender AS gender
Interests	Describes the user’s interests in various categories like Arts & Entertainment, Games, Sports.	user_properties.value.string_value AS interests

---

#4 GA4 Ecommerce Dimensions to BigQuery Mapping.

You can access detailed information about individual products within BigQuery by mapping GA4 Ecommerce Dimensions to BigQuery fields, including the product ID, name, variant, category, price, and more. 

GA4 ecommerce dimensions to BigQuery mapping allows you to identify top-selling products, analyze product performance across categories, and understand user behaviour related to specific items within BigQuery.

Here’s a breakdown of GA4 Ecommerce dimensions and their corresponding BigQuery fields:

GA4 Ecommerce Dimensions	What it is	BigQuery Field Name (Formula)
Currency	The type of currency used in a transaction.	MAX(ecommerce.currency) AS currency
Item affiliation	Identifies the affiliate or partner associated with an item sold.	MAX(items.affiliation) AS item_affiliation
Item brand	Indicates the brand associated with a specific item.	MAX(items.item_brand) AS item_brand
Item category	The primary category under which an item is classified.	MAX(items.category) AS item_category
Item category 2	The secondary category for classifying an item.	MAX(items.category2) AS item_category2
Item category 3	The third level in an item’s hierarchical category structure.	MAX(items.category3) AS item_category3
Item category 4	Represents the fourth tier in an item’s classification.	MAX(items.category4) AS item_category4
Item category 5	The fifth and most specific classification tier for an item.	MAX(items.category5) AS item_category5
Item coupon	The coupon code used for purchasing an item.	MAX(items.promotion_id) AS item_coupon
Item ID	A unique identifier assigned to an item.	MAX(items.item_id) AS item_id
Item list ID	A unique identifier given to a list of items.	MAX(items.item_list_id) AS item_list_id
Item list name	The name given to a list of items.	MAX(items.item_list_name) AS item_list_name
Item list position	Specifies the position of an item within a list.	MAX(items.item_list_position) AS item_list_position
Item location ID	An identifier for where an item or promotion is located.	MAX(items.location_id) AS item_location_id
Item name	The official name of an item.	MAX(items.item_name) AS item_name
Item promotion creative name	The designated name for a creative aspect of a promotion.	MAX(items.promotion_creative_name) AS item_promotion_creative_name
Item promotion creative slot	Identifies the specific placement or slot of a promotional creative.	MAX(items.promotion_creative_slot) AS item_promotion_creative_slot
Item promotion ID	A unique identifier for a promotional campaign or offer.	MAX(items.promotion_id) AS item_promotion_id
Item promotion name	The name associated with a specific promotion.	MAX(items.promotion_name) AS item_promotion_name
Item variant	Describes variations of an item, such as size or color.	MAX(items.item_variant) AS item_variant
Local item price	The price of an item adjusted for local currency.	MAX(items.price) AS local_item_price
Order coupon	A coupon code associated with a specific order.	MAX(purchase.coupon) AS order_coupon
Shipping tier	The level of shipping service chosen for delivering an item.	MAX(purchase.shipping_tier) AS shipping_tier
Transaction ID	An identifier used to track and manage transactions within ecommerce platforms.	MAX(transaction_id) AS transaction_id

Points to Consider:

item_coupon: While ‘MAX(items.promotion_id)’ might capture the ID of a promotion associated with an item, it might not directly represent the coupon code used by the customer. Consider using ‘CASE WHEN’ statements or exploring other fields within ‘ecommerce.promotions’ schema if you specifically need the coupon code.

local_item_price: There might not be a dedicated field for “local item price” within the standard GA4 ecommerce schema. You might need to perform calculations based on ‘price’ and currency conversion rates if you want to analyze price in a specific local currency.

order_coupon: The ‘purchase.coupon’ field likely captures the order-level coupon code, which might differ from an item-specific coupon (‘item_coupon’).

Transaction ID: While ‘MAX(transaction_id)’ retrieves a transaction ID, it might not necessarily be unique across your entire BigQuery dataset. Consider using appropriate filters or concatenating with other identifiers for uniqueness.

Consider using ‘CASE WHEN’ statements within your BigQuery queries to handle scenarios where a single field might not perfectly capture the desired data element.

---

#5 GA4 Event-Scoped Ecommerce Metrics to BigQuery Mapping.

By mapping GA4 Event-Scoped Ecommerce Metrics to BigQuery fields, you can better understand user behaviour within your ecommerce funnel and optimize conversion rates.

Here’s a breakdown of GA4 event-scoped ecommerce metrics and their corresponding BigQuery fields:

GA4 Event Scoped Ecommerce Metrics	What it is	BigQuery Field Name (Formula)
Add to carts	Tracks the total instances of items being added to shopping carts.	COUNTIF(event_name = 'add_to_cart')
Checkouts	Counts the instances where users initiated the checkout process.	COUNTIF(event_name = 'begin_checkout')
Ecommerce quantity	Represents the total number of items involved in ecommerce transactions.	SUM((SELECT SUM(quantity) FROM UNNEST(items)))
Gross purchase revenue	Total revenue generated from all types of purchases, before refunds.	SUM(ecommerce.purchase_revenue)
Item-list click events	Measures how often users click on items listed in any formatted list.	COUNTIF(event_name = 'select_item')
Item-list view events	Counts how frequently lists of items are viewed by users.	COUNTIF(event_name = 'view_item_list')
Item view events	Tracks each time an item is viewed.	COUNTIF(event_name = 'view_item')
Promotion clicks	The total number of times users have clicked on promotional items.	COUNTIF(event_name = 'select_promotion')
Promotion views	Counts the views of promotional content.	COUNTIF(event_name = 'view_promotion')
Purchase revenue	The net revenue from all purchases made, after accounting for refunds.	SUM(ecommerce.purchase_revenue) - SUM(ecommerce.refund_value)
Purchases	The total count of purchases completed.	COUNTIF(event_name = 'purchase')
Quantity	The total units involved in ecommerce events.	SUM((SELECT SUM(quantity) FROM UNNEST(items)))
Refund amount	The total monetary value of all refunds processed.	SUM(ecommerce.refund_value)
Refunds	Counts the total number of refunds issued.	COUNTIF(event_name = 'refund')
Shipping amount	The total cost associated with shipping for transactions.	SUM(ecommerce.shipping)
Tax amount	The total amount of tax charged in transactions.	SUM(ecommerce.tax)
Transactions	The total number of completed transactions or purchases.	COUNTIF(event_name = 'purchase')
Transactions per purchaser	The average number of transactions made by each buyer within a timeframe.	AVG(COUNTIF(event_name = 'purchase') GROUP BY user_pseudo_id)

---

#6 GA4 Item-Scoped Ecommerce Metrics to BigQuery Mapping.

By mapping GA4 item-scoped e-commerce metrics to BigQuery fields, you can effectively analyze detailed ecommerce activities and gain insights into user interactions with your products. 

Analyzing metrics such as item sales, views, and additions to cart can help you understand how individual products perform. 

This can help you identify the top-performing products and those that may require promotional efforts or pricing adjustments.

Efficiently tracking item quantities sold and checked out allows for better inventory management, accurate demand prediction, and a reduction in overstock or stockout situations.

Understanding which items capture more attention in promotions or are frequently added to carts but not purchased can guide adjustments in marketing tactics.

Here’s a breakdown of GA4 item-scoped ecommerce metrics and their corresponding BigQuery fields:

GA4 Item Scoped Ecommerce Metrics	What it is	BigQuery Field Name (Formula)
Add to carts	Tracks the total instances of items being added to shopping carts.	COUNTIF(event_name = 'add_to_cart' AND items IS NOT NULL)
Checkouts	Counts the instances where users initiated the checkout process.	COUNTIF(event_name = 'begin_checkout')
Ecommerce quantity	Represents the total number of items involved in ecommerce transactions.	SUM((SELECT SUM(quantity) FROM UNNEST(items)))
Gross purchase revenue	Total revenue generated from all types of purchases, before refunds.	SUM(ecommerce.purchase_revenue)
Item-list click events	Measures how often users click on items listed in any formatted list.	COUNTIF(event_name = 'select_item')
Item-list view events	Counts how frequently lists of items are viewed by users.	COUNTIF(event_name = 'view_item_list')
Item view events	Tracks each time an item is viewed.	COUNTIF(event_name = 'view_item')
Promotion clicks	The total number of times users have clicked on promotional items.	COUNTIF(event_name = 'select_promotion')
Promotion views	Counts the views of promotional content.	COUNTIF(event_name = 'view_promotion')
Purchase revenue	The net revenue from all purchases made, after accounting for refunds.	SUM(ecommerce.purchase_revenue)
Purchases	The total count of purchases completed.	COUNTIF(event_name = 'purchase')
Quantity	The total units involved in ecommerce events.	SUM((SELECT SUM(quantity) FROM UNNEST(items)))
Refund amount	The total monetary value of all refunds processed.	SUM(ecommerce.refund_value)
Refunds	Counts the total number of refunds issued.	COUNTIF(event_name = 'refund')
Shipping amount	The total cost associated with shipping for transactions.	SUM(ecommerce.shipping)
Tax amount	The total amount of tax charged in transactions.	SUM(ecommerce.tax)
Transactions	The total number of completed transactions or purchases.	COUNTIF(event_name = 'purchase')
Transactions per purchaser	The average number of transactions made by each buyer within a timeframe.	SUM((SELECT COUNT(*) FROM UNNEST(event_params) WHERE key = 'purchase_count')) / COUNT(DISTINCT user_pseudo_id)

---

#7 GA4 Revenue Metrics to BigQuery Mapping.

By mapping GA4 Revenue metrics to BigQuery fields, you can:

#1 Track the total revenue generated from various sources, such as purchases, in-app transactions, and subscriptions. 

#2 Pinpoint which products, services, or campaigns are the main revenue drivers. This knowledge is invaluable for optimizing marketing strategies and resource allocation.

#3 Calculate profitability by subtracting costs such as refunds, discounts, and operational expenses from total revenue.

#4 Analyze how changes in pricing affect revenue and make data-driven decisions to adjust pricing strategies for maximum profitability.

#5 Use historical revenue data to forecast future trends and prepare better for upcoming demands and market changes.

Here’s a breakdown of GA4 Revenue metrics and their corresponding BigQuery fields:

GA4 Revenue Metrics	What it is	BigQuery Field Name Formula
ARPPU	Measures the average revenue generated by each user who has made a purchase.	SUM(ecommerce.purchase_revenue) / COUNT(DISTINCT user_pseudo_id WHERE event_name = 'purchase')
ARPU	Represents the average amount of revenue generated by each active user.	SUM(ecommerce.purchase_revenue) / COUNT(DISTINCT user_pseudo_id)
Average daily revenue	The average amount of revenue collected each day within a specified period.	SUM(ecommerce.purchase_revenue) / COUNT(DISTINCT event_date)
Average purchase revenue	The average revenue obtained from purchases over a specified time frame.	SUM(ecommerce.purchase_revenue) / COUNT(event_name = 'purchase')
Average purchase revenue per user	The total revenue obtained from purchases divided by the number of purchasing users.	AVG(ecommerce.purchase_revenue)
Max daily revenue	The highest revenue recorded in a single day during the selected time frame.	MAX(SUM(ecommerce.purchase_revenue) GROUP BY event_date)
Min daily revenue	The lowest revenue recorded in a single day during the selected time frame.	MIN(SUM(ecommerce.purchase_revenue) GROUP BY event_date)
Total revenue	The combined total of all revenue from purchases, adjusted for any refunds.	SUM(ecommerce.purchase_revenue) - SUM(ecommerce.refund_value)

Points to Consider:

ARPU (Average Revenue Per User): While the formula calculates total revenue divided by the number of distinct users, it does not differentiate between active users. A more precise definition of ARPU considers only active users within a specific timeframe. To refine this metric, you might need to incorporate additional filters based on user activity data.

---

#8 GA4 Event Dimensions to BigQuery Mapping.

Here’s a breakdown of GA4 event dimensions and their corresponding BigQuery fields:

GA4 Event Dimensions	What it is	BigQuery Field Name (Formula)
Event name	This dimension captures the identifier of an event.	event_name
Is key event	Indicates whether an event has been designated as a key event.	IF(event_params.key = 'is_key_event' AND event_params.value.int_value = 1, TRUE, FALSE) AS is_key_event

Points to consider:

In the GA4 BigQuery export schema, there is no corresponding field that indicates whether an event has been designated as a key event. 

The concept of a “key event” is more of an analytical distinction made within the GA4 user interface, where certain events can be marked as key events for analysis and reporting.

However, this designation does not translate into a distinct field in the BigQuery export.

The formula provided for the “Is key event” dimension in the table above assumes that you have a custom event parameter named ‘is_key_event’ set up in your GA4 configuration.

To handle this situation in BigQuery, you must manually specify which events are considered key events based on your analysis needs. 

You could do this by creating a list of event names that you have designated as key events within your GA4 configuration and then using this list in your SQL queries to filter or flag these events accordingly.

For example, if you consider ‘purchase’ and ‘add_to_cart’ as key events, you could write a SQL query like the one below to identify them in your BigQuery data:

1 2 3 4 5 6 7 8

SELECT   event_name,   CASE     WHEN event_name IN ('purchase', 'add_to_cart') THEN TRUE     ELSE FALSE   END AS is_key_event FROM   `your_project.your_dataset.your_table`

---

#9 GA4 Event Metrics to BigQuery Mapping.

Here’s a breakdown of GA4 event metrics and their corresponding BigQuery fields:

GA4 Event Metrics	What it is	BigQuery Field Name
Key events	Measures how frequently users trigger events that have been designated as key events.	Not directly available in BigQuery export schema; requires custom logic based on event names designated as key events.
Event count	The total number of times any event is triggered by users across the platform.	COUNT(event_name)
Event count per user	Represents the average number of events each active user triggers.	COUNT(event_name) / COUNT(DISTINCT user_pseudo_id) WHERE /* Add filter based on user activity data for the timeframe */
Event value	This metric aggregates all value parameters associated with events, capturing both realized and potential contributions beyond direct purchases.	SUM(event_params.value.double_value) WHERE event_params.key = 'value'
Events per session	The average number of events that occur within a session, indicating user engagement per session.	COUNT(event_name) / COUNT(DISTINCT event_bundle_sequence_id)
First opens	Counts the number of times the application is opened for the first time by new users.	COUNTIF(event_name = 'first_open')
First visits	Tracks the number of first-time visits to your website by new users.	COUNTIF(event_name = 'first_visit')

Points to consider:

There is no corresponding field in the BigQuery export schema that flags an event as a key event. 

To calculate this metric, define which events are considered key events in your GA4 configuration, then count their occurrences.

---

#10 GA4 Geography Dimensions to BigQuery Mapping.

Mapping GA4 Geography Dimensions to their corresponding BigQuery fields allows you to analyze the geographical distribution of your user base. 

You can identify the countries, regions, or cities where your website or app garners the most interest. 

Don’t overlook the power of geographic data in targeting advertising campaigns. 

By focusing your ads on specific countries, regions, or cities, you can ensure they reach the most relevant audience, potentially boosting your campaign’s effectiveness.

Combine geographic data with other user attributes to create highly targeted customer segments based on location and demographics.

Here’s a breakdown of GA4 Geography dimensions and their corresponding BigQuery fields:

GA4 Geography Dimensions	What it is	BigQuery Field Name (Formula)
City	Identifies the city where user activities, such as website visits, are originating from.	SELECT geo.city FROM your_table_name
City ID	A unique identifier associated with the city from which user activities are conducted.	Not available in BigQuery schema
Continent	Specifies the continent from which a user is accessing your website or app.	SELECT geo.continent FROM your_table_name
Continent ID	A unique UN M49 code that corresponds to the continent where the user’s activity originates.	Not available in BigQuery schema
Country	Describes the country from which a user’s activity originates, helping in regional analysis.	SELECT geo.country FROM your_table_name
Country ID	The ISO 3166 code representing the country of the user’s activity origin.	SELECT geo.country_iso_code FROM your_table_name
Region	Indicates the broader geographic area, like a state or province, from which user activity comes.	SELECT geo.region FROM your_table_name
Region ID	A code identifying the specific region, such as a state or province code, linked to user activity.	Not available in BigQuery schema
Subcontinent	Details the subcontinental area from which the user’s activity is originating, providing more granular geographic data.	SELECT geo.sub_continent FROM your_table_name
Subcontinent ID	The UN M49 code that identifies the specific subcontinent where the user’s activity originates.	Not available in BigQuery schema

Points to consider:

City ID, Continent ID, Region ID, Subcontinent ID: These fields are not standard in the GA4 BigQuery export schema. If these identifiers are necessary, they would need to be derived or added through custom data processing or, if possible, during data collection.

---

#11 GA4 Link Dimensions to BigQuery Mapping.

Mapping GA4 Link Dimensions to their corresponding BigQuery fields provides valuable insights into how users interact with outbound links on your website or app. 

Here’s a breakdown of GA4 Link dimensions and their corresponding BigQuery fields:

GA4 Link Dimensions	What it is	BigQuery Field Name (Formula)
Link classes	Captures the HTML class attributes assigned to links.	ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_classes'), ', ') AS link_classes
Link domain	Specifies the domain to which an outbound link points.	(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_domain') AS link_domain
Link ID	Identifies the HTML ID attribute of a link.	(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_id') AS link_id
Link text	The visible text of a link that users click on.	(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_text') AS link_text
Link URL	The complete URL that an outbound link directs to.	(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_url') AS link_url
Outbound	Indicates whether a link directs to an external site.	(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'is_outbound') AS is_outbound

Example SQL Query:

Here’s how you might structure a query to retrieve these link dimensions for a specific event, assuming these parameters are tracked:

1 2 3 4 5 6 7 8 9 10 11 12

SELECT   event_name,   ARRAY_TO_STRING(ARRAY(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_classes'), ', ') AS link_classes,   (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_domain') AS link_domain,   (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_id') AS link_id,   (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_text') AS link_text,   (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'link_url') AS link_url,   (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'is_outbound') AS is_outbound FROM   `your_dataset.your_table` WHERE   event_name = 'your_specific_event'

---

#12 GA4 Page/Screen Dimensions to BigQuery Mapping.

The following are the main advantages of mapping GA4 Page/Screen Dimensions to their corresponding BigQuery fields:

1) By mapping page titles, paths, and referrers, you can visualize users’ paths through your website or app. Identify common entry and exit points, optimize navigation flow, and pinpoint areas where users drop off.

2) You can analyze which content groups, types, and specific pages resonate best with users based on metrics like page views, time spent, and engagement.

3) You can create highly targeted user segments by combining page/screen data with user demographics or behaviour. This can be a powerful tool for tailoring your marketing or product strategies to specific user groups. 

4) You can leverage page/screen data to understand user interests and preferences.

5) You can utilize page/screen data alongside other event data to build a more comprehensive picture of user touchpoints before a conversion. 

Here’s a breakdown of GA4 Page/Screen dimensions and their corresponding BigQuery fields:

GA4 Page/Screen Dimensions	What it is	BigQuery Field Name
Content group	Identifies the content group associated with a page or screen.	event_params.value.string_value WHERE event_params.key = 'content_group'
Content ID	A unique identifier assigned to a content group.	event_params.value.string_value WHERE event_params.key = 'content_id'
Content type	Describes the type of content associated with a content group.	event_params.value.string_value WHERE event_params.key = 'content_type'
Hostname	Specifies the subdomain and domain name of the URL users visit.	event_params.value.string_value WHERE event_params.key = 'hostname'
Landing page	Provides the page path and query string of the first pageview in a session.	event_params.value.string_value WHERE event_params.key = 'landing_page'
Page location	The complete URL of the webpage visited by a user.	event_params.value.string_value WHERE event_params.key = 'page_location'
Page path + query string	Captures the page path and query string of a URL.	event_params.value.string_value WHERE event_params.key = 'page_path_plus_query_string'
Page path and screen class	Combines the page path from a website URL with the screen class from a mobile application.	event_params.value.string_value WHERE event_params.key = 'page_path_and_screen_class'
Page referrer	Records the referring URL from which the user came.	event_params.value.string_value WHERE event_params.key = 'page_referrer'
Page title	The title set for a webpage.	event_params.value.string_value WHERE event_params.key = 'page_title'
Page title and screen class	Merges the page title from a website with the screen class from a mobile app.	event_params.value.string_value WHERE event_params.key = 'page_title_and_screen_class'
Page title and screen name	Pairs the page title from a website with the screen name set for a mobile app.	event_params.value.string_value WHERE event_params.key = 'page_title_and_screen_name'

Points to consider:

Content ID:

While the formula event_params.value.string_value WHERE event_params.key = ‘content_id’ attempts to retrieve a content ID, it’s important to note that GA4 doesn’t inherently provide a unique identifier for content groups.

The ‘content_id’ parameter might be populated with custom IDs you have defined, but it is not a guaranteed field in every content group event.

Page location, Page path + query string, Page path and screen class, Page title and screen class, Page title and screen name:

These fields all use the same formula structure (event_params.value.string_value with key filters).

However,

It is essential to understand that GA4 might not provide all these specific parameters within every pageview event. Their presence depends on whether the relevant data was collected during the event.

Here’s a breakdown of the potentially missing data:

1. page_location: This might not be available for all events if GA4 wasn’t able to determine the full URL.
2. page_path_plus_query_string: Similar to ‘page_location‘, this combined field might be absent if some data wasn’t collected.
3. Mobile app data (screen class names): The screen class related parameters (page_path_and_screen_class, page_title_and_screen_class, page_title_and_screen_name) won’t be populated for events originating from websites.

---

#13 GA4 Page/Screen Metrics to BigQuery Mapping.

Mapping GA4 Page/Screen Metrics to their corresponding BigQuery fields allows analysing metrics like views, time spent, and entrances/exits to gauge user engagement with specific pages or screens.

You can understand which content resonates most with users.

You can understand how deeply users navigate your website or app by analysing views per user and time spent on pages. 

Here’s a breakdown of GA4 Page/Screen metrics and their corresponding BigQuery fields:

GA4 Page/Screen Metrics	What it is	BigQuery Field Name (Formula)
Elapsed time from last page request	The average time, in seconds, between each event loading, calculated from the previous user-initiated page request.	AVG(TIMESTAMP_DIFF(timestamp, PRIOR VALUE(timestamp) OVER (PARTITION BY user_pseudo_id ORDER BY timestamp), SECOND)) AS perceived_page_load_time_sec
Entrances	The count of sessions that began with a particular page or screen as the first recorded event.	COUNTIF(event_name = 'session_start') AS entrances
Exits	The number of sessions that ended with a particular page or screen as the final recorded event.	COUNTIF(event_name = 'session_end') AS exits
Views	The total count of screens or web pages viewed by users, including repeated views of the same page or screen.	COUNTIF(event_name IN ('page_view', 'screen_view')) AS views
Views per user	The average number of screens or web pages viewed per user, indicating overall engagement.	AVG(view_count) OVER (PARTITION BY user_pseudo_id) AS views_per_user

---

#14 GA4 Platform/Device Dimensions to BigQuery Mapping.

By mapping GA4 Platform/Device Dimensions to their corresponding BigQuery fields, you can:

1. Analyze the distribution of users across different device categories (desktop, mobile, tablet) to understand how users prefer to access your website/app.
2. Track the operating systems (OS) used by your audience to ensure your website/app functions well on popular OS versions.
3. Leverage the language data to understand the primary languages used by your user base. This can help you personalize your content and user experience by offering language options or translating content strategically.

Here’s a breakdown of GA4 Platform/Device dimensions and their corresponding BigQuery fields:

GA4 Platform/Device Dimensions	What it is	BigQuery Field Name (Formula)
App store	Indicates the store from which a mobile app was downloaded and installed.	Require custom implementation or tracking as custom parameters.
App version	The version name for Android or the short bundle version for iOS of the mobile app.	SELECT app_info.version FROM your_table_name
Browser	The browser from which user activities originate.	SELECT device.web_info.browser FROM your_table_name
Browser version	The version of the browser used by the user.	SELECT device.web_info.browser_version FROM your_table_name
Device	Identifies the mobile device from which the user’s activity originated.	SELECT device.category FROM your_table_name
Device brand	Specifies the brand name of the mobile device.	SELECT device.mobile_info.brand_name FROM your_table_name
Device category	The type of device from which activities originate.	SELECT device.device_category FROM your_table_name
Device model	The model name of the mobile device.	SELECT device.mobile_info.model_name FROM your_table_name
Language	The language of the user’s browser or device.	SELECT device.language FROM your_table_name
Language code	The ISO 639 code representing the language setting.	Custom implementation or SELECT device.language FROM your_table_name
Mobile model	Specifies the model name of the mobile device.	SELECT device.mobile_info.model_name FROM your_table_name
Operating system	The OS used by visitors on your website or app.	SELECT device.operating_system FROM your_table_name
OS version	The version of the operating system used by the visitors.	SELECT device.operating_system_version FROM your_table_name
Platform	Indicates the method by which users access your website or application.	SELECT platform FROM your_table_name
Screen resolution	The width and height of the screen from which user activities originate.	Require custom implementation or tracking as custom parameters.
Stream ID	The ID of the stream from which the user’s activity originated.	SELECT stream_id FROM your_table_name
Stream name	The name of the stream from which the user’s activity originated.	Require custom implementation or tracking as custom parameters.

---

#15 GA4 User-Scoped Traffic Dimensions to BigQuery Mapping.

Here’s a breakdown of GA4 User-Scoped Traffic Dimensions and their corresponding BigQuery fields:

GA4 User-Scoped Traffic Dimensions	What it is	BigQuery Field Name (Formula)
First user campaign	The initial campaign that led to the acquisition of a user.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_campaign') AS first_user_campaign
First user campaign ID	The ID associated with the campaign that first acquired the user.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_campaign_id') AS first_user_campaign_id
First user default channel group	The default channel group responsible for acquiring the user initially.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_default_channel_group') AS first_user_default_channel_group
First user manual ad content	The ad content associated with the user’s first acquisition.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_manual_ad_content') AS first_user_manual_ad_content
First user manual campaign ID	The ID of the campaign used for the user’s initial acquisition.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_manual_campaign_id') AS first_user_manual_campaign_id
First user manual campaign name	The campaign used for acquiring the user initially.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_manual_campaign_name') AS first_user_manual_campaign_name
First user manual medium	The medium associated with the start of the user’s first session.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_manual_medium') AS first_user_manual_medium
First user manual source	The source associated with the start of the user’s first session.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_manual_source') AS first_user_manual_source
First user manual source / medium	The source and medium associated with the start of the user’s first session.	CONCAT((SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_manual_source'), ' / ', (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_manual_medium')) AS first_user_manual_source_medium
First user manual source platform	The platform used to first acquire the user.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_manual_source_platform') AS first_user_manual_source_platform
First user manual term	The term associated with the user’s first acquisition, typically from a search or marketing campaign.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_manual_term') AS first_user_manual_term
First user medium	The medium by which the user was first acquired.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_medium') AS first_user_medium
First user source	The source by which the user was first acquired.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_source') AS first_user_source
First user source / medium	The source and medium that initially acquired the user.	CONCAT((SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_source'), ' / ', (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_medium')) AS first_user_source_medium
First user source platform	The platform responsible for initially acquiring the user.	(SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'first_user_source_platform') AS first_user_source_platform

This approach ensures that each dimension is correctly extracted from the user_properties array, assuming these properties are being tracked and named as specified in your GA4 configuration.

Adjust the dataset and table references based on your specific setup.

---

#16 GA4 Session-Scoped Traffic Dimensions to BigQuery Mapping.

Here’s a breakdown of GA4 Session-Scoped Traffic Dimensions and their corresponding BigQuery fields:

GA4 Session-Scoped Traffic Dimensions	What it is	BigQuery Field Name (Formula)
Session campaign	The campaign associated with the beginning of a session.	SELECT traffic_source.campaign AS session_campaign FROM your_dataset.your_table_name
Session campaign ID	The ID of the campaign associated with the start of a session.	SELECT traffic_source.campaign_id AS session_campaign_id FROM your_dataset.your_table_name
Session default channel group	The default channel group linked to the start of a session.	SELECT CASE WHEN traffic_source.medium = 'organic' THEN 'Organic Search' WHEN traffic_source.medium = 'none' THEN 'Direct' ELSE 'Other' END AS session_default_channel_group FROM your_dataset.your_table_name
Session manual ad content	The ad content tied to the session’s start.	SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'ad_content') AS session_manual_ad_content FROM your_dataset.your_table_name
Session manual campaign ID	The ID of the campaign associated with the session’s start.	SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign_id') AS session_manual_campaign_id FROM your_dataset.your_table_name
Session manual campaign name	The campaign name associated with the session’s start.	SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS session_manual_campaign_name FROM your_dataset.your_table_name
Session manual medium	The medium associated with the session’s start.	SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS session_manual_medium FROM your_dataset.your_table_name
Session manual source	The source tied to the session’s start.	SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS session_manual_source FROM your_dataset.your_table_name
Session manual source / medium	The source and medium associated with the session’s start.	SELECT CONCAT((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source'), ' / ', (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium')) AS session_manual_source_medium FROM your_dataset.your_table_name
Session manual source platform	The platform responsible for the session’s start.	SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source_platform') AS session_manual_source_platform FROM your_dataset.your_table_name
Session manual term	The term associated with the session’s start.	SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'term') AS session_manual_term FROM your_dataset.your_table_name
Session medium	The medium associated with the session’s start.	SELECT traffic_source.medium AS session_medium FROM your_dataset.your_table_name

Adjust the dataset and table references (your_dataset.your_table_name) based on your specific setup.

Points to consider:

BigQuery exports of GA4 data primarily handle traffic dimensions at the user and event levels rather than session levels.

For now, if you need to analyse data at the session level in BigQuery, you might consider the following:

Custom Implementations: Tagging each event with session-specific parameters using Google Tag Manager. These parameters could include session ID, timestamp, or any relevant details about the session.

Using Start of Session Events: Identifying the start of new sessions using ‘session_start’ events and associating custom parameters with these events to simulate session-scoped dimensions. This method is limited to data captured at the session start. You won’t have information about user behaviour throughout the session.

Session manual ad content, session_manual_campaign_name, session_manual_source, session_manual_medium, session_manual_source_medium, session_manual_term: 

These fields all utilize UNNEST to extract values from event_params.

This approach is accurate, but remember that these parameters might not be present in every session event within GA4.

Their presence depends on whether the specific details (ad content, campaign name, etc.) were included in the initial user acquisition event.

---

#17 GA4 Session Metrics to BigQuery Mapping.

Here’s a breakdown of GA4 Session Metrics and their corresponding BigQuery fields:

GA4 Session Metrics	What it is	BigQuery Field Name (Formula)
Average session duration	The average length of time, in seconds, that users spend during a session.	AVG(session_duration_sec) AS average_session_duration
Bounce rate	The percentage of sessions that did not meet the engagement criteria.	100 * (COUNTIF(totals.bounces > 0) / COUNT(session_id)) AS bounce_rate
Engaged sessions	The number of sessions that lasted 10 seconds or more, included one or more key events, or had at least two page or screen views.	COUNTIF(engaged_time_msec > 10000 OR event_count > 1) AS engaged_sessions
Engaged sessions per user	The average number of engaged sessions per user.	AVG(COUNTIF(engaged_time_msec > 10000 OR event_count > 1) OVER (PARTITION BY user_pseudo_id)) AS engaged_sessions_per_user
Engagement rate	The percentage of sessions that were engaged sessions.	100 * (COUNTIF(engaged_time_msec > 10000 OR event_count > 1) / COUNT(session_id)) AS engagement_rate
Low engagement sessions	The count of sessions that did not meet the engagement criteria.	COUNTIF(engaged_time_msec <= 10000 AND event_count <= 1) AS low_engagement_sessions
Session key event rate	The percentage of sessions that converted, calculated as the ratio of sessions with key events to total sessions.	100 * (COUNTIF(key_event_occurred = 1) / COUNT(session_id)) AS session_key_event_rate
Sessions	The count of sessions that began on your website or app.	COUNT(DISTINCT session_id) AS sessions
Sessions per user	The average number of sessions per user.	AVG(COUNT(DISTINCT session_id) OVER (PARTITION BY user_pseudo_id)) AS sessions_per_user
Views per session	The number of app screens or web pages viewed per session.	AVG(page_views) OVER (PARTITION BY session_id) AS views_per_session

Points to consider:

• Average session duration: Assumes session_duration_sec is a calculated field representing the total duration of each session in seconds.
• Bounce rate: Assumes totals.bounces is a field indicating whether a session is a bounce. This field and calculation method might need to be adjusted based on your specific data structure.

• Engaged sessions: Assumes engaged_time_msec represents the total engagement time in milliseconds and event_count represents the number of events in a session. Adjust the criteria based on your engagement definition.
• Session key event rate: Assumes key_event_occurred is a flag indicating whether a key event occurred during the session. This might require custom logic to set based on your event data.

• Sessions per user and Views per session: These calculations use COUNT(DISTINCT session_id) and average page views per session, assuming page_views is a field or calculated metric representing the number of page views in each session.

---

#18 GA4 User Dimensions to BigQuery Mapping.

Here’s a breakdown of GA4 User Dimensions and their corresponding BigQuery fields:

GA4 User Dimensions	What it is	BigQuery Field Name (Formula)
Audience name	Indicates the name of an audience that users belong to during the selected date range.	Not directly available in BigQuery export schema; requires custom implementation or analysis.
New / established	Differentiates between new and established users who opened your app or visited your website within the last 7 days.	IF(DATE_DIFF(CURRENT_DATE(), DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)), DAY) <= 7, 'new', 'established') AS user_type
Signed in with user ID	Shows ‘yes’ when a user_id is collected, allowing you to associate different sessions to an individual user.	IF(user_id IS NOT NULL, 'yes', 'no') AS signed_in_with_user_id

Points to consider:

Audience name: The GA4 BigQuery export does not directly include audience names as a dimension. Audience membership is typically managed within GA4 and might not be exported directly to BigQuery. Analyzing audience membership over time would require custom implementation.

Solutions for Analyzing Audience Membership:

#1 Custom User Properties.

If you have configured audience membership as a custom user property in GA4, this data can be analyzed directly in BigQuery. Custom user properties are exported to BigQuery and can be queried like any other field.

To analyze audience membership:

1. Identify the User Property Key: Determine the key used for audience membership in your GA4 setup.
2. Query the User Property: Use an SQL query to extract and analyze this user property from the user_properties array in your BigQuery dataset.

1 2 3 4 5

SELECT   user_pseudo_id,   (SELECT value.string_value FROM UNNEST(user_properties) WHERE key = 'audience_membership') AS audience_name FROM   `your_project.your_dataset.your_table`

#2 Event Tagging

Configure your GA4 events to include a custom dimension that captures the audience a user belongs to.

Analyze events in BigQuery to see which audience members are performing specific actions.

1 2 3 4 5 6 7 8

SELECT   event_name,   (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'audience_name') AS audience_name,   COUNT(*) AS event_count FROM   `your_project.your_dataset.your_table` GROUP BY   event_name, audience_name

#3 Post-Processing Data

Consider post-processing your GA4 data outside of BigQuery to include audience information. This might involve exporting relevant data from GA4 and then joining it with audience data from your audience management tool based on the user ID or another common identifier.

---

#19 GA4 User Metrics to BigQuery Mapping.

Here’s a breakdown of GA4 User Metrics and their corresponding BigQuery fields:

GA4 User Metrics	What it is	BigQuery Field Name (Formula)
1-day repeat purchasers	The number of customers who made purchases on two consecutive days.	Requires custom calculation based on purchase events and timestamps.
2–7-day repeat purchasers	Customers who made a purchase and then again between 2 and 7 days later.	Requires custom calculation based on purchase events and timestamps.
30-day paid active users	Customers who made one or more purchases within the last 30 days.	Requires custom calculation based on purchase events within the time frame.
31–90-day repeat purchasers	Customers who made a purchase and then again between 31 and 90 days later.	Requires custom calculation based on purchase events and timestamps.
7-day paid active users	Customers who made one or more purchases within the last 7 days.	Requires custom calculation based on purchase events within the time frame.
8–30-day repeat purchasers	Customers who made a purchase and then again between 8 and 30 days later.	Requires custom calculation based on purchase events and timestamps.
90-day paid active users	Customers who made one or more purchases within the last 90 days.	Requires custom calculation based on purchase events within the time frame.
Active users	Distinct users who visited your website or app.	COUNT(DISTINCT user_pseudo_id) WHERE engaged_session = 1
Average daily purchasers	Average number of purchasers across all days in the selected time frame.	Requires custom calculation based on daily purchaser counts.
Average engagement time	Average time a website or app was in focus or foreground.	Requires custom calculation based on engagement time metrics.
Average engagement time per session	Average engagement time per session.	Requires custom calculation based on session engagement times.
DAU / MAU	Ratio of Daily Active Users to Monthly Active Users.	Requires custom calculation based on daily and monthly active user counts.
DAU / WAU	Ratio of Daily Active Users to Weekly Active Users.	Requires custom calculation based on daily and weekly active user counts.
First time purchasers	Users who made their first purchase in the selected time frame.	Requires custom calculation based on first purchase events.
First-time purchaser key event	Percentage of active users who made their first purchase.	Requires custom calculation based on first purchase events among active users.
First-time purchasers per new user	Average number of first-time purchasers per new user.	Requires custom calculation based on new user counts and first-time purchase events.
Max daily purchasers	Highest number of purchasers across all days in the selected time frame.	Requires custom calculation based on daily purchaser counts.
Min daily purchasers	Lowest number of purchasers across all days in the selected time frame.	Requires custom calculation based on daily purchaser counts.
New users	New unique user IDs that logged the first_open or first_visit event.	COUNT(DISTINCT user_pseudo_id) WHERE event_name IN ('first_open', 'first_visit')
PMAU / DAU	Ratio of Paying Monthly Active Users to Daily Active Users.	Requires custom calculation based on paying user counts and daily active user counts.
PWAU / DAU	Ratio of Paying Weekly Active Users to Daily Active Users.	Requires custom calculation based on paying user counts and daily active user counts.
Returning users	Users who have initiated at least one previous session.	Requires custom calculation based on session counts per user.
Total purchasers	Unique users who made at least one purchase.	COUNT(DISTINCT user_pseudo_id) WHERE event_name = 'purchase'
Total users	Unique user IDs that triggered any events.	COUNT(DISTINCT user_pseudo_id)
User key event rate	Percentage of users who converted, based on key events.	Requires custom calculation based on key event counts and total user counts.
User engagement	Time that your app screen or web page was in focus or foreground.	Requires custom calculation based on engagement time metrics.
WAU / MAU	Ratio of Weekly Active Users to Monthly Active Users.	Requires custom calculation based on weekly and monthly active user counts.

1-day repeat purchasers, 2–7-day repeat purchasers, 31–90-day repeat purchasers, 8–30-day repeat purchasers

There are no direct BigQuery fields for these metrics; they must be calculated by analyzing purchase event timestamps for each user.

Here is a general approach to calculating these metrics:

Step 1: Identify Purchase Events.

First, identify the purchase events in your dataset. This involves filtering your events table for events that represent a purchase. The event name for purchases in GA4 is typically ‘purchase'.

Step 2: Calculate Repeat Purchasers.

For each of the specified time intervals, calculate the number of users who made repeat purchases within those intervals. This involves comparing the timestamps of purchase events for each user. Here’s an example approach for calculating 1-day repeat purchasers:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

WITH Purchases AS (   SELECT     user_pseudo_id,     event_date,     MIN(TIMESTAMP_MICROS(event_timestamp)) AS first_purchase_timestamp   FROM     `your_project.your_dataset.events_*`   WHERE     event_name = 'purchase'     AND _TABLE_SUFFIX BETWEEN 'start_date' AND 'end_date'   GROUP BY     user_pseudo_id, event_date ),   RepeatPurchases AS (   SELECT     a.user_pseudo_id,     a.event_date AS first_purchase_date,     b.event_date AS repeat_purchase_date   FROM     Purchases a   JOIN     Purchases b   ON     a.user_pseudo_id = b.user_pseudo_id     AND DATE_DIFF(PARSE_DATE('%Y%m%d', b.event_date), PARSE_DATE('%Y%m%d', a.event_date), DAY) = 1 )   SELECT   COUNT(DISTINCT user_pseudo_id) AS one_day_repeat_purchasers FROM   RepeatPurchases

Explanation:

• Purchases CTE: This Common Table Expression (CTE) identifies the first purchase event for each user on each day.
• RepeatPurchases CTE: This CTE finds pairs of purchase events for the same user where the second purchase occurs exactly 1 day after the first purchase.
• The final SELECT statement counts the unique users who made repeat purchases exactly 1 day apart.

Adjustments for other time intervals:

• For 2–7-day repeat purchasers, adjust the DATE_DIFF condition to check for differences between 2 and 7 days.
• For 8–30-day repeat purchasers, adjust the DATE_DIFF condition to check for differences between 8 and 30 days.
• For 31–90-day repeat purchasers, adjust the DATE_DIFF condition to check for differences between 31 and 90 days.

Note:

• Replace your_project.your_dataset.events_* with your actual dataset path.
• Adjust 'start_date' and 'end_date' to your specific analysis period.
• Ensure that the event name for purchases ('purchase') matches how purchases are tracked in your GA4 setup.

30-day paid active users, 7-day paid active users, 90-day paid active users

These metrics count users who made purchases within the last 30, 7, and 90 days, respectively. Calculating these metrics involves filtering purchase events within the specified time frames and counting unique users.

Here is a general approach to calculating each of these metrics:

1. Identify Purchase Events: Filter your dataset for events that represent a purchase. In GA4, the event name for purchases is typically ‘purchase'.
2. Define Time Frames: For each metric, define the time frame relative to the current date (or another reference date) — last 7 days, last 30 days, and last 90 days.
3. Count Unique Users: For each time frame, count the unique user_pseudo_id or user_id (if available) associated with purchase events.

Example SQL Query – 7-Day Paid Active Users

1 2 3 4 5 6 7

SELECT   COUNT(DISTINCT user_pseudo_id) AS paid_active_users_last_7_days FROM   `your_project.your_dataset.events_*` WHERE   event_name = 'purchase'   AND event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

Example SQL Query – 30-Day Paid Active Users

1 2 3 4 5 6 7

SELECT   COUNT(DISTINCT user_pseudo_id) AS paid_active_users_last_30_days FROM   `your_project.your_dataset.events_*` WHERE   event_name = 'purchase'   AND event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)

Example SQL Query – 90-Day Paid Active Users

1 2 3 4 5 6 7

SELECT   COUNT(DISTINCT user_pseudo_id) AS paid_active_users_last_90_days FROM   `your_project.your_dataset.events_*` WHERE   event_name = 'purchase'   AND event_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)

Notes:

• These queries use CURRENT_TIMESTAMP() to define the current date and time, and TIMESTAMP_SUB to calculate the time frames for the last 7, 30, and 90 days.
• If you’re tracking user_id and prefer to use it for identifying unique users, replace user_pseudo_id with user_id in the queries.

Average daily purchasers, Max daily purchasers, Min daily purchasers.

These metrics require aggregating purchase events by day and then calculating average, maximum, and minimum values. 

Here is an example SQL query that demonstrates how to calculate these metrics:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

WITH DailyPurchases AS (   SELECT     FORMAT_DATE('%Y-%m-%d', TIMESTAMP_MICROS(event_timestamp)) AS purchase_date,     COUNT(DISTINCT user_pseudo_id) AS daily_purchasers   FROM     `your_project.your_dataset.events_*`   WHERE     event_name = 'purchase'     AND _TABLE_SUFFIX BETWEEN 'start_date' AND 'end_date'   GROUP BY     purchase_date )   SELECT   AVG(daily_purchasers) AS average_daily_purchasers,   MAX(daily_purchasers) AS max_daily_purchasers,   MIN(daily_purchasers) AS min_daily_purchasers FROM   DailyPurchases;

Explanation:

• DailyPurchases CTE: This Common Table Expression (CTE) calculates the number of unique purchasers for each day. It groups the data by the date of the event and counts distinct user_pseudo_id for purchase events.
• Main SELECT Statement: This part of the query calculates the average, maximum, and minimum number of daily purchasers using the CTE results.

Notes:

• Replace your_project.your_dataset.events_* with the actual path to your GA4 BigQuery dataset.
• Adjust 'start_date' and 'end_date' to your specific analysis period.

Average engagement time, Average engagement time per session.

Calculating these metrics involves aggregating engagement time (e.g., engagement_time_msec) across events or sessions and then averaging.

The ‘Average Engagement Time’ metric can be calculated by summing all engagement times and dividing by the number of sessions. Here’s how you can structure the query:

1 2 3 4 5 6

SELECT   SUM(engagement_time_msec) / COUNT(DISTINCT session_id) AS average_engagement_time FROM   `your_project.your_dataset.events_*` WHERE   _TABLE_SUFFIX BETWEEN 'start_date' AND 'end_date'

‘Average Engagement Time Per Session’ metric calculates the average engagement time for each session and then averages those across all sessions:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

WITH SessionEngagement AS (   SELECT     session_id,     SUM(engagement_time_msec) AS total_engagement_time_per_session   FROM     `your_project.your_dataset.events_*`   WHERE     _TABLE_SUFFIX BETWEEN 'start_date' AND 'end_date'   GROUP BY     session_id )   SELECT   AVG(total_engagement_time_per_session) AS average_engagement_time_per_session FROM   SessionEngagement

DAU/MAU, DAU/WAU, PMAU/DAU, PWAU/DAU, WAU/MAU.

These metrics involve aggregating unique user counts over daily, weekly, and monthly periods and then computing the ratios of these aggregates.

Here’s how you can approach these calculations using GA4 data in BigQuery:

Step-1: Define active and paying users.

Active Users: Users who have initiated at least one session within the specified time frame.

Paying Users: Users who have made at least one purchase within the specified time frame.

Step-2: Calculate unique user counts

For each time frame (daily, weekly, monthly), calculate the count of unique users. You can use the user_pseudo_id to identify unique users.

Example SQL Query – Daily Active Users (DAU).

1 2 3 4 5 6 7

SELECT   FORMAT_DATE('%Y%m%d', DATE(TIMESTAMP_MICROS(event_timestamp))) AS day,   COUNT(DISTINCT user_pseudo_id) AS dau FROM   `your_project.your_dataset.events_*` GROUP BY   day

Example SQL Query – Weekly Active Users (WAU).

To calculate WAU, you want to count unique users active within a week. You can define a week in various ways, but a common approach is to use ISO week numbers or simply group by the year and week number.

1 2 3 4 5 6 7 8 9

SELECT   FORMAT_DATE('%G-W%V', DATE(TIMESTAMP_MICROS(event_timestamp))) AS week,   COUNT(DISTINCT user_pseudo_id) AS wau FROM   `your_project.your_dataset.events_*` WHERE   event_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND CURRENT_TIMESTAMP() GROUP BY   week

This query calculates the WAU for the last 7 days from the current timestamp. Adjust the WHERE clause as needed for your specific reporting requirements.

Example SQL Query – Monthly Active Users (MAU).

For MAU, count unique users active within a month. You can group by the year and month to get this metric.

1 2 3 4 5 6 7 8 9

SELECT   FORMAT_DATE('%Y-%m', DATE(TIMESTAMP_MICROS(event_timestamp))) AS month,   COUNT(DISTINCT user_pseudo_id) AS mau FROM   `your_project.your_dataset.events_*` WHERE   event_timestamp BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND CURRENT_TIMESTAMP() GROUP BY   month

This query calculates the MAU for the last 30 days from the current timestamp. You might adjust the WHERE clause to align with calendar months or other specific monthly periods you are analyzing.

Example SQL Query – Paying Users.

1. Identify and filter the events that represent purchases. In GA4, purchase events are typically named purchase.
2. Specify the time frames for which you want to count paying users. This could be daily, weekly, monthly, or any custom time period.
3. For each defined time frame, count the unique users who have made purchases. This involves using the COUNT(DISTINCT user_pseudo_id) function in SQL to ensure each user is counted only once per time frame.

1 2 3 4 5 6 7 8 9 10

SELECT   FORMAT_DATE('%Y-%m', DATE(TIMESTAMP_MICROS(event_timestamp))) AS month,   COUNT(DISTINCT user_pseudo_id) AS paying_users FROM   `your_project.your_dataset.events_*` WHERE   event_name = 'purchase'   AND event_timestamp BETWEEN TIMESTAMP('2024-01-01') AND TIMESTAMP('2024-01-31') GROUP BY   month

This query provides the count of unique paying users for January 2024. Adjust the event_timestamp in the WHERE clause to match the specific time frames you are analyzing.

Step-3: Calculate ratios.

To calculate the ratios of DAU (Daily Active Users), WAU (Weekly Active Users), MAU (Monthly Active Users), and their respective paying user counterparts (PDAU, PWAU, PMAU) once you have their counts, you can either join these counts from different queries or calculate them within the same query period.

Here’s how you can approach both methods using SQL in BigQuery:

Method 1: Calculating ratios within the same query.

If you have a single query that can capture DAU, WAU, MAU, and their paying counterparts over the same period, you can calculate the ratios directly. Here’s an example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

WITH UserCounts AS (   SELECT     DATE(event_timestamp) AS date,     COUNT(DISTINCT IF(event_name = 'session_start', user_pseudo_id, NULL)) AS DAU,     COUNT(DISTINCT IF(event_name = 'purchase', user_pseudo_id, NULL)) AS PDAU,     COUNT(DISTINCT IF(DATE_DIFF(CURRENT_DATE(), DATE(event_timestamp), DAY) <= 7, user_pseudo_id, NULL)) AS WAU,     COUNT(DISTINCT IF(DATE_DIFF(CURRENT_DATE(), DATE(event_timestamp), DAY) <= 7 AND event_name = 'purchase', user_pseudo_id, NULL)) AS PWAU,     COUNT(DISTINCT IF(DATE_DIFF(CURRENT_DATE(), DATE(event_timestamp), DAY) <= 30, user_pseudo_id, NULL)) AS MAU,     COUNT(DISTINCT IF(DATE_DIFF(CURRENT_DATE(), DATE(event_timestamp), DAY) <= 30 AND event_name = 'purchase', user_pseudo_id, NULL)) AS PMAU   FROM     `your_project.your_dataset.events_*`   WHERE     _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())   GROUP BY     date )   SELECT   date,   DAU,   WAU,   MAU,   PDAU,   PWAU,   PMAU,   DAU / MAU AS DAU_MAU_Ratio,   DAU / WAU AS DAU_WAU_Ratio,   PDAU / DAU AS PDAU_DAU_Ratio,   PWAU / WAU AS PWAU_WAU_Ratio,   PMAU / MAU AS PMAU_MAU_Ratio FROM   UserCounts ORDER BY   date DESC

Method 2: Joining counts from different queries.

If you calculate DAU, WAU, MAU, and their paying counterparts in separate queries, you can join these results based on the date or another common identifier to calculate the ratios:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

WITH Daily AS (   SELECT     DATE(event_timestamp) AS date,     COUNT(DISTINCT user_pseudo_id) AS DAU,     COUNT(DISTINCT IF(event_name = 'purchase', user_pseudo_id, NULL)) AS PDAU   FROM     `your_project.your_dataset.events_*`   GROUP BY     date ), Weekly AS (   SELECT     DATE_TRUNC(DATE(event_timestamp), WEEK(MONDAY)) AS week,     COUNT(DISTINCT user_pseudo_id) AS WAU,     COUNT(DISTINCT IF(event_name = 'purchase', user_pseudo_id, NULL)) AS PWAU   FROM     `your_project.your_dataset.events_*`   GROUP BY     week ), Monthly AS (   SELECT     DATE_TRUNC(DATE(event_timestamp), MONTH) AS month,     COUNT(DISTINCT user_pseudo_id) AS MAU,     COUNT(DISTINCT IF(event_name = 'purchase', user_pseudo_id, NULL)) AS PMAU   FROM     `your_project.your_dataset.events_*`   GROUP BY     month )   SELECT   d.date,   d.DAU,   w.WAU,   m.MAU,   d.PDAU,   w.PWAU,   m.PMAU,   d.DAU / m.MAU AS DAU_MAU_Ratio,   d.DAU / w.WAU AS DAU_WAU_Ratio,   d.PDAU / d.DAU AS PDAU_DAU_Ratio,   w.PWAU / w.WAU AS PWAU_WAU_Ratio,   m.PMAU / m.MAU AS PMAU_MAU_Ratio FROM   Daily d JOIN   Weekly w ON d.date = w.week JOIN   Monthly m ON d.date = m.month ORDER BY   d.date DESC

First time purchasers, First-time purchaser key event, First-time purchasers per new user:

1. To identify first-time purchasers, you need to filter your dataset for events that represent a purchase. 
2. After filtering purchase events, you need to determine which purchases are first-time purchases for each user by sorting purchases by date for each user and identifying the earliest purchase event.
3. Once you have identified first-time purchases, count these events to understand the volume of first-time purchasers within your selected time frame. 
4. Identifying key events for first-time purchasers (such as making a second purchase) involves tracking their subsequent actions after their first purchase.
5. To calculate the average number of first-time purchasers per new user, divide the total number of first-time purchasers by the total number of new users acquired in the same time frame. 

1. First Time Purchasers

To identify first-time purchasers, use a query that selects the earliest purchase date for each customer and filters those whose first purchase falls within a specific time frame:

1 2 3 4 5 6 7 8 9

SELECT   user_id,   MIN(purchase_date) AS first_purchase_date FROM   purchases GROUP BY   user_id HAVING   MIN(purchase_date) = 'YYYY-MM-DD'; -- Specify the date of interest

This query groups purchases by ‘user_id’, finds the minimum (earliest) purchase_date for each user, and filters to include only those whose first purchase date matches a specific date.

2. First-time Purchaser Key Event.

The SQL query below uses a Common Table Expression (CTE) to identify each user’s first purchase date. It then joins this CTE with the original purchases table to filter for those first purchases that match a specified key event type.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

WITH FirstPurchases AS (   SELECT     user_id,     MIN(purchase_date) AS first_purchase_date   FROM     purchases   GROUP BY     user_id ) SELECT   p.user_id,   p.purchase_date,   p.event_type FROM   purchases p JOIN   FirstPurchases fp ON p.user_id = fp.user_id AND p.purchase_date = fp.first_purchase_date WHERE   p.event_type = 'KeyEventType'; -- Specify the key event type

3. First-time Purchasers Per New User.

To calculate the ratio of first-time purchasers to new users (assuming new users are identified by a first_visit_date in a users table):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

WITH FirstPurchases AS (   SELECT     user_id,     MIN(purchase_date) AS first_purchase_date   FROM     purchases   GROUP BY     user_id ), NewUsers AS (   SELECT     user_id,     first_visit_date   FROM     users   WHERE     first_visit_date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' -- Specify the date range of interest ) SELECT   COUNT(DISTINCT fp.user_id) * 1.0 / COUNT(DISTINCT nu.user_id) AS first_time_purchasers_per_new_user FROM   NewUsers nu LEFT JOIN   FirstPurchases fp ON nu.user_id = fp.user_id;

This query calculates the number of first-time purchasers within a specified date range of new users and divides it by the total number of new users in the same period to get the ratio of first-time purchasers per new user.

Returning users.

The SQL query below counts the number of sessions per user and identifies those with more than one session, classifying them as returning users.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

WITH SessionCounts AS (   SELECT     user_pseudo_id,     COUNT(DISTINCT session_id) AS num_sessions   FROM     `your_project.your_dataset.events_*`   GROUP BY     user_pseudo_id )   SELECT   user_pseudo_id FROM   SessionCounts WHERE   num_sessions > 1

Note: The query does not specify a time frame, but you can add a WHERE clause in the CTE to analyze returning users within a specific period, such as WHERE event_date BETWEEN ‘YYYYMMDD’ AND ‘YYYYMMDD’.

User key event rate, User engagement.

The ‘User Key Event Rate’ metric calculates the proportion of users who performed specific key events compared to the total user base within a given time frame.

SQL Query for User Key Event Rate:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

WITH KeyEvents AS (   SELECT     user_pseudo_id,     COUNT(DISTINCT event_name) AS key_event_count   FROM     `your_project.your_dataset.events_*`   WHERE     event_name IN ('purchase', 'add_to_cart', 'sign_up') -- Define key events here   GROUP BY     user_pseudo_id ), TotalUsers AS (   SELECT     COUNT(DISTINCT user_pseudo_id) AS total_users   FROM     `your_project.your_dataset.events_*` )   SELECT   (SUM(key_event_count) / (SELECT total_users FROM TotalUsers)) * 100 AS user_key_event_rate FROM   KeyEvents

Explanation:

• KeyEvents CTE: This part of the query identifies users who performed any of the specified key events and counts these events for each user.
• TotalUsers CTE: Calculates the total number of unique users.
• Final SELECT: Calculates the percentage of key events performed by users relative to the total user base.

User Engagement metric measures the total engagement time users spend with your website or app, typically focusing on active engagement like page views or app interactions.

SQL Query for User Engagement:

1 2 3 4 5 6 7 8 9

SELECT   user_pseudo_id,   SUM(engagement_time_msec) AS total_engagement_time FROM   `your_project.your_dataset.events_*` WHERE   event_name = 'user_engagement' -- Assuming 'user_engagement' captures engagement time GROUP BY   user_pseudo_id

This query sums up the engagement_time_msec for the user_engagement event for each user, providing a total engagement time per user.

---

#20 GA4 Advertising Metrics to BigQuery Mapping.

Here’s a breakdown of GA4 Advertising Metrics and their corresponding BigQuery fields:

GA4 Advertising Metrics	What it is	BigQuery Field Name (Formula)
Google Ads clicks	The total number of times users have clicked on your ads via Google Ads.	SUM(google_ads_clicks)
Google Ads cost	The cumulative amount spent on your Google Ads campaigns.	SUM(google_ads_cost)
Google Ads cost per click	The average cost incurred for each click received through your Google Ads campaigns.	SUM(google_ads_cost) / SUM(google_ads_clicks) AS google_ads_cost_per_click
Google Ads impressions	The total number of views or exposures your Google Ads campaigns have received.	SUM(google_ads_impressions)
Google Ads video cost	The total expenditure on video advertising within Google Ads.	SUM(google_ads_video_cost)
Google Ads video views	The total count of views your video ads have garnered on the Google Ads platform.	SUM(google_ads_video_views)
Non-Google clicks	The total number of clicks on your advertising campaigns not running on Google Ads.	SUM(non_google_clicks)
Non-Google cost	The overall cost associated with your non-Google advertising campaigns.	SUM(non_google_cost)
Non-Google cost per click	The average cost for each click on your non-Google advertising campaigns.	SUM(non_google_cost) / SUM(non_google_clicks) AS non_google_cost_per_click
Non-Google cost per key event	The average cost incurred for each key event triggered through your non-Google advertising campaigns.	SUM(non_google_cost) / COUNT(non_google_key_events) AS non_google_cost_per_key_event
Non-Google impressions	The total number of times your non-Google ads were displayed to users.	SUM(non_google_impressions)
Return on non-Google ad spend	The revenue generated for every dollar spent on non-Google ads, considering all revenue streams against ad costs	SUM(non_google_revenue) / SUM(non_google_cost) AS return_on_non_google_ad_spend

Points to consider:

BigQuery Field Names (Formulas) provided above are conceptual and assume that you have fields in your BigQuery dataset that directly correspond to these metrics.

If these fields are not directly available, you may need to create them using custom calculations or ensure that your data collection setup in GA4 is configured to capture this data.

For example, the specific BigQuery field names provided, such as SUM(google_ads_clicks), SUM(google_ads_cost), etc., were illustrative and not directly pulled from the GA4 BigQuery export schema.

GA4’s BigQuery export does not directly provide fields named ‘google_ads_clicks‘, ‘google_ads_cost‘, ‘non_google_clicks‘, ‘non_google_cost‘, etc.

Instead, advertising metrics and costs are typically derived from the event data that GA4 exports to BigQuery, which requires parsing and aggregating specific event parameters related to advertising.

Let’s construct example SQL queries to calculate various Google Ads and non-Google advertising metrics.

These examples assume that you have linked your Google Ads account with GA4 and are utilizing BigQuery to analyze the exported data.

The metrics will be derived from custom fields or calculations based on the event data available in your GA4 BigQuery export.

Google Ads Clicks.

1 2 3 4

SELECT   SUM(metrics_clicks) AS google_ads_clicks FROM   `your_project.your_dataset.google_ads_table`

Google Ads Cost.

1 2 3 4

SELECT   SUM(metrics_cost_micros) / 1000000 AS google_ads_cost FROM   `your_project.your_dataset.google_ads_table`

Google Ads Impressions.

1 2 3 4

SELECT   SUM(metrics_impressions) AS google_ads_impressions FROM   `your_project.your_dataset.google_ads_table`

Google Ads Video Cost.

Assuming video cost is part of the cost metrics and needs to be filtered by video ad interactions:

1 2 3 4 5 6

SELECT   SUM(metrics_cost_micros) / 1000000 AS google_ads_video_cost FROM   `your_project.your_dataset.google_ads_table` WHERE   segments_ad_network_type = 'VIDEO'

Google Ads Video Views.

Assuming video views are tracked as a specific interaction type:

1 2 3 4 5 6

SELECT   SUM(metrics_interactions) AS google_ads_video_views FROM   `your_project.your_dataset.google_ads_table` WHERE   metrics_interaction_event_types = 'VIDEO_VIEW'

Non-Google Advertising Metrics.

For non-Google advertising metrics, let’s assume you are tracking these through custom events or parameters in GA4 that are then exported to BigQuery.

The specific field names and calculations might vary based on how you’ve set up tracking for these metrics.

Non-Google Clicks.

1 2 3 4 5 6

SELECT   COUNT(*) AS non_google_clicks FROM   `your_project.your_dataset.events_*` WHERE   event_name = 'non_google_ad_click'

Non-Google Key Events.

Assuming a key event is defined and tracked explicitly:

1 2 3 4 5 6

SELECT   COUNT(*) AS non_google_key_events FROM   `your_project.your_dataset.events_*` WHERE   event_name = 'non_google_key_event'

Non-Google Impressions.

1 2 3 4 5 6

SELECT   COUNT(*) AS non_google_impressions FROM   `your_project.your_dataset.events_*` WHERE   event_name = 'non_google_ad_impression'

Non-Google Revenue.

Assuming revenue from non-Google ads is tracked as part of event parameters:

1 2 3 4 5 6 7 8

SELECT   SUM(event_params.value.double_value) AS non_google_revenue FROM   `your_project.your_dataset.events_*`,   UNNEST(event_params) AS event_params WHERE   event_name = 'purchase' AND   event_params.key = 'non_google_ad_revenue'

Non-Google Cost.

Assuming the cost for non-Google ads is tracked similarly:

1 2 3 4 5 6 7

SELECT   SUM(event_params.value.double_value) AS non_google_cost FROM   `your_project.your_dataset.events_*`,   UNNEST(event_params) AS event_params WHERE   event_params.key = 'non_google_ad_cost'

Note: The specific calculations and field names might need adjustments based on your data schema and how you have configured tracking for Google Ads and non-Google advertising metrics.