# Query Genuin event data in BigQuery

Learn what lands in the genuin_data table, column by column, and run the first queries on engagement, campaigns, and searches.

Once a dataset is connected under **Settings** > **Data Sources** > **BigQuery**, Brand Control Center writes every member event to one table, `genuin_data`, on the schedule you chose. [Receive zero-party data in BigQuery](https://begenuin.com/developers/developer-resources/api/connect-bigquery-to-the-brand-control-center-to-receive-zero-party-data) covers the connection; this page covers the table.

![The BigQuery console with the genuin_data table selected under the connected dataset in the Explorer pane.](https://cdn.sanity.io/images/lnrtvsvr/development/0f5338217a6dbdea104fdf1d718ad1bc04e53b89-1650x747.jpg)
*The table appears under your dataset as soon as the connection succeeds.*

## How rows arrive

Each row is one event: a view, a search, a join, a post, a comment, a share, or an app action. Rows are appended in batches on the daily, weekly, or monthly schedule set in the BigQuery panel, and `_timestamp` is in UTC. The schema has 145 columns, all nullable; a column is empty when the event doesn't carry it. Genuin manages the schema and adds columns with product releases, so write queries that name the columns they need rather than `SELECT *` into fixed targets.

> **Note** event holds the event name. Run SELECT event, COUNT(\*) FROM genuin_data GROUP BY event to see which events your social experience produces before building reports on them.

## Columns

| Group | Columns | What they describe |
| --- | --- | --- |
| Event | _timestamp, event, brand_id, content_id, content_type, content_category, conv_type, category, channel, source, message, notification_type, parent_id, of_user_id, identity_id, country_code | What happened, when, to which piece of content, and from which brand and country. |
| App and device | context_app_name, context_app_namespace, context_app_version, context_app_build, build, version, context_device_manufacturer, context_device_model, context_device_name, context_os_name, context_os_version, context_user_agent, context_traits_ip, context_session_id, context_timezone, new_page_session, old_page_session, tab_name | The app or web client, device, session, and screen the event came from. |
| Member actions | keyword_searched, search_source, question, selected_option, topic_id, topic_ids, topic_selected, new_selected_topic, communities_selected, duration_selected, seconds_chosen, stop_recording_seconds, no_of_parts, upload_video_length, link_added, mentioned_user_id, from_username, handle, description, display_picture, hashtag_details, loop_ids, conversations, conversations_expiration | Choices members made: searches, answers, topics picked, recording and upload details, mentions. |
| Video playback | video_url, video_duration, video_length, video_view_length, length_at_video_paused, video_size, visible_in_feed, compress_mode | How long a video is and how much of it was watched. |
| Campaign | utm_source, utm_medium, utm_campaign, utm_data_utm_campaign, utm_data_from_username, campaign, media_source, embed_id | Which campaign, source, or embed brought the member to the event. |
| Video record | master_video_uuid, master_video_video_slug, master_video_content_keywords, master_video_ai_generated, master_video_video_sender_id, master_video_video_brand_id, master_video_video_created_at, master_video_community_uuid, master_video_video_conversation_uuid | The video the event refers to, joined from Genuin's records. |
| Community record | master_community_uuid, master_community_handle, master_community_name, master_community_description, master_community_slug, master_community_type, master_community_status, master_community_brand_id, master_community_ai_generated, master_community_created_at | The community the event refers to. |
| Group record | master_loops_uuid, master_loops_slug, master_loops_type, master_loops_status, master_loops_brand_id, master_loops_community_uuid, master_loops_country_code, master_loops_ai_generated, master_groups_uuid, master_groups_name, master_groups_description, master_groups_brand_id, master_groups_created_at | The group the event refers to, as both its conversation record (master_loops_) and its profile (master_groups_). |
| Brand record | master_brand_name, master_brand_slug, master_brand_subdomain | Your brand, for exports that mix several brands. |
| Comment and share records | master_comments_identifier, master_c_video_ (9 columns), master_c_user_uuid, master_c_user_status, master_c_community_ (11 columns), master_c_loops_ (8 columns), master_c_groups_ (5 columns), master_s_user_uuid | For comment events, the video, community, group, and member commented on; for share events, the member who shared. |

The `master_*` columns are joined from Genuin's own records when the row is written, so a filter such as `WHERE master_community_handle = 'trail-runners'` works without a second table.

## Starter queries

Replace `project_id.dataset_name` with the Dataset ID you connected.

Events per day for the past week:

```sql title="Events per day"
SELECT
  DATE(_timestamp) AS day,
  event,
  COUNT(*) AS events
FROM `project_id.dataset_name.genuin_data`
WHERE _timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY day, event
ORDER BY day DESC, events DESC;
```

Watch time per video, with the video's slug:

```sql title="Watch time per video"
SELECT
  master_video_uuid,
  ANY_VALUE(master_video_video_slug) AS slug,
  COUNT(*) AS views,
  ROUND(SUM(video_view_length), 1) AS seconds_watched
FROM `project_id.dataset_name.genuin_data`
WHERE video_view_length IS NOT NULL
  AND _timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY master_video_uuid
ORDER BY seconds_watched DESC
LIMIT 50;
```

Events by campaign:

```sql title="Events by campaign"
SELECT
  utm_source,
  utm_medium,
  utm_campaign,
  COUNT(*) AS events,
  COUNT(DISTINCT context_session_id) AS sessions
FROM `project_id.dataset_name.genuin_data`
WHERE utm_campaign IS NOT NULL
GROUP BY utm_source, utm_medium, utm_campaign
ORDER BY events DESC;
```

What members search for, by community:

```sql title="Searches by community"
SELECT
  master_community_handle,
  LOWER(keyword_searched) AS keyword,
  COUNT(*) AS searches
FROM `project_id.dataset_name.genuin_data`
WHERE keyword_searched IS NOT NULL
GROUP BY master_community_handle, keyword
ORDER BY searches DESC
LIMIT 100;
```

Join `master_video_uuid` or `master_community_uuid` to your own catalogue, or `utm_campaign` to your campaign table, to report Genuin engagement next to the rest of your business data.

## Limits

| Item | Limit |
| --- | --- |
| Datasets per brand | One |
| Table name | genuin_data, fixed |
| Columns | 145, all nullable, managed by Genuin |
| Delivery | Batch, on the daily, weekly, or monthly schedule |
| Timestamps | _timestamp in UTC |

## See also

- [Receive zero-party data in BigQuery](https://begenuin.com/developers/developer-resources/api/connect-bigquery-to-the-brand-control-center-to-receive-zero-party-data)
- [Push analytics data to Amazon S3](https://begenuin.com/developers/developer-resources/api/how-do-i-integrate-amazon-s3-bucket-with-brand-control-center-to-push-analytics-data)
- [Push analytics data to Mixpanel](https://begenuin.com/developers/developer-resources/api/integrate-mixpanel-with-brand-control-center)
- [How zero-party data powers your social experience and media revenue](https://begenuin.com/developers/browse-by-products/control/how-zero-party-data-powers-brand-communities-and-media-revenue-without-compromising-consumer-trust)