Skip to navigation

Dynamic Tag Tables

Find the SQL views that hold your dynamic tag scores and join them with your dataset's tables for advanced analytics.

When a tag group finishes scoring on a dataset, its scores are available as SQL views. Explore and analyze them in the Queries tab. To check whether scoring is complete, see Check Scoring Progress.

Dynamic tag views

Every dynamic tag group has four views, one for each level. They appear in the output of SHOW TABLES.

ViewLevel
dt_[dynamic tag group name]_videoVideo
dt_[dynamic tag group name]_shotShot
dt_[dynamic tag group name]_visualKeyframe
dt_[dynamic tag group name]_transcriptTranscript segment

Replace [dynamic tag group name] with your group’s name in lowercase, with spaces replaced by underscores. For example, a group named Casual Social Celebrations has the view dt_casual_social_celebrations_video.

In every view, tag_name is stored in lowercase (for example, pickup trucks), even when the UI shows title case. Filter on the lowercase value.

Threshold sources

Each view has a threshold_decision column that is 1 when the tag applies and 0 when it doesn’t. The threshold it uses depends on the level:

ViewThresholdDefault
_videoThe threshold set in Review videos0.5
_visualThe Visual threshold on the tag page0.5
_transcriptThe Transcript threshold on the tag page0.5
_shotThe Combined threshold on the tag page0.5

To apply a different cutoff in SQL, filter on score instead of threshold_decision.

dt_[dynamic tag group name]_video

Join to coactive_table_video on coactive_video_id for metadata.

ColumnDescriptionFormat
coactive_video_idCoactive video identifierstring
tag_nameName of the dynamic tag applied to the video, in lowercasestring
scoreRelevance score representing the likelihood the tag applies to the videofloat (0.0–1.0)
threshold_decisionBinary indicator for whether the tag applies, based on the threshold set in Review videos (default = 0.5)integer (0 or 1)

dt_[dynamic tag group name]_shot

This view has no video ID or timestamps. Join coactive_shot_id to coactive_table_slice.id to get video_id, start_time_ms, and end_time_ms, then join video_id to coactive_table_video.coactive_video_id for metadata.

ColumnDescriptionFormat
coactive_shot_idCoactive shot identifierstring
tag_nameName of the dynamic tag applied to the shot, in lowercasestring
scoreRelevance score representing the likelihood the tag applies to the shotfloat (0.0–1.0)
threshold_decisionBinary indicator for whether the tag applies, based on the Combined threshold on the tag page (default = 0.5)integer (0 or 1)

dt_[dynamic tag group name]_visual

This view is keyframe-level and has no video ID. Join to coactive_table on coactive_image_id to get coactive_video_id and metadata. The view reflects the latest version of each tag.

ColumnDescriptionFormat
coactive_image_idCoactive identifier of the image or keyframestring
tag_nameName of the dynamic tag applied to the image or keyframe, in lowercasestring
scoreRelevance score representing the likelihood the tag applies to the image or keyframefloat (0.0–1.0)
threshold_decisionBinary indicator for whether the tag applies, based on the Visual threshold on the tag page (default = 0.5)integer (0 or 1)

dt_[dynamic tag group name]_transcript

Join coactive_transcript_id to coactive_table_transcription.id to get the segment’s video_id, text, start_time, and end_time.

ColumnDescriptionFormat
coactive_transcript_idCoactive identifier of the transcript segmentstring
tag_nameName of the dynamic tag applied to the transcript segment, in lowercasestring
scoreRelevance score representing the likelihood the tag applies to the transcript segmentfloat (0.0–1.0)
threshold_decisionBinary indicator for whether the tag applies, based on the Transcript threshold on the tag page (default = 0.5)integer (0 or 1)

Join for advanced analytics

Join the dynamic tag views with the core tables to aggregate scores by video, filter by keyframe timestamp (keyframe_time_ms), or add file metadata to your results. For the columns in each core table, see SQL Table Reference.

Example query

This query finds keyframes where the basketball tag applies and adds show metadata from each keyframe’s video. The metadata column is a map<string,string>, so read each value with metadata['key']. Keys depend on the metadata ingested with your videos. To list them, see Read metadata values, then replace SeriesName and EpisodeName with your own keys.

SELECT
dt.coactive_image_id,
t.coactive_video_id,
t.keyframe_time_ms,
dt.score,
v.metadata['SeriesName'] AS series_name,
v.metadata['EpisodeName'] AS episode_name
FROM dt_sports_visual dt
JOIN coactive_table t
ON dt.coactive_image_id = t.coactive_image_id
LEFT JOIN coactive_table_video v
ON t.coactive_video_id = v.coactive_video_id
WHERE dt.tag_name = 'basketball' -- tag_name is stored in lowercase
AND dt.threshold_decision = 1 -- uses the Visual threshold on the tag page
ORDER BY dt.score DESC;

For more query patterns, such as tag distribution and the top tags for each image or video, see Analyzing Dynamic Tags with SQL.