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 covers the connection; this page covers the table.
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 communities produce 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:
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:
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:
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:
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 |
