Currently I am trying to get the total watch time and average view duration for a video (or for all the videos in the channel).
Total watch time is computed as the number of times a video is watched (Count the number of times a videoId occurs in videoViews table) multiplied by the video duration.
count(videoView.videoId) * video.videoDuration
I believe this is fine.
The problem is in computing the average view duration metric.
The definition of this metric from google’s developer docs :
The average length, in seconds, of video playbacks. In a playlist report, the metric indicates the average length, in seconds, of video playbacks that occurred in the context of a playlist.
I think we don’t have enough information to compute this metric because the videoView table is only updated after someone watches the complete video. If someone watches the video for lets’say 60% of the view duration, this information is stored in userVideoHistory, but the entry userVideoHistory is overwritten and the history data is lost.
Request : Store the information (maybe have a watch duration column in videoViews table) of video playbacks history even if the user does not watch the video in full.