Fact Tables for Viewing Sessions
The focus of this section is only on the data in the Data Warehouse needed to understand viewing behavior across Linear and VOD content. This is the main table into which all the other dimensions feed into (join) to create the atomic entity of a viewing session. Defining a viewing session is a fundamental metric to understand viewing consumption. There is no industry standard to defining a view – it varies from 2 minutes (Netflix), 70% of content (most of the UK), 90% (for some Content Providers), Average minute audiences (National Panels). A viewing session captures all viewing at 1x speed with capping implemented based on event interaction and splitting this by % viewed of total event duration. Definition of viewing session is configurable.
Capping - Importance of capping and real viewing sessions
Capping is the software process to clean up unmatched viewing on the STB. This unmatched viewing occurs due to 2 main causes:
People turned off their TV but not their STB, so the viewing sessions continue to be captured.
People switched source on their smart TV to something else, so the viewing sessions continue to be captured. Most capping models today only account for the first reason and hence only late-night events tend to get capped for accurate viewing figures However with the rise of the Smart TV and the number of sources of content people have, the 2nd reason is also becoming a significant reason to cap. Synamedia has implemented a best-of-breed capping algorithm to ensure that the viewing consumption metrics you receive are an accurate reflection of viewing on your platform.
Fact_sessions – Identifiable Data
All the fields of the fact_sessions
Field Name | Description | Field Type |
uid | Transaction ID, generated by CCL unique per transaction/report from client to BE | Text |
day_id | Day of the transaction | Integer |
hour_id | Hour of the transaction | Integer |
user_id | Unique household identification, from Synamedia Go HouseholdID | Text |
device_id | Unique device identification | Text |
device_type | Unique identification of the device type, for example: STB, Mobile, Tablet, PC or CTV depending on the device the app if running on | Text |
device_desc | Device and Application details which are provided by client application. | Text |
country | Country reference from the transaction report. Synamedia converts the device’s public IP to a geographical location | Text |
state | State/Region reference from the transaction report. Synamedia converts the device’s public IP to a geographical location | Text |
city | City reference from the transaction report. Synamedia converts the device’s public IP to a geographical location | Text |
event_type | Video session action taken on the transaction record, for example: PLAY, BUFFERING, REQUEST_VIEWING | Text |
event_start | Program event (linear) or playback (VOD) start time. In UTC-0 reference | Timestamp |
event_end | Program event (linear) or playback (VOD) end time. In UTC-0 reference | Timestamp |
event_start_tz | Program event (linear) or playback (VOD) start time. In device’s time-zone reference | Timestamp |
event_end_tz | Program event (linear) or playback (VOD) end time. In device’s time-zone reference. | Timestamp |
local_timezone | Time-zone from the device originating the transaction | Text |
duration_seconds | Duration of the video session in seconds | Integer |
content_type | Source of the video session, for example: LIVE: Linear content watched live, CDVR: Personal recording, REPLAYTV: CatchupTV or StartoverTV | Text |
content | Program (linear) or content (VOD) title | Text |
content_id | Program (linear) or content (VOD) identification | Text |
content_details | Program (linear) or content (VOD) playback session details. endReason: This field represents the reason why the session ended. Play session: DESTROY, PROG_CHANGE, OTHER buffering: DESTROY, PLAYING, PLAYER_ERROR, STOPPED, OTHER Request_viewing: DESTROY, PLAYING, PLAYER_ERROR, STOPPED, OTHER audioTrack: app selected audio track subtitleTrack: app selected subtitle track | Json |
content_desc | Program (linear) or content (VOD) genre | Text |
content_desc_ex | For series content: Series info | Text |
content_source | Channel or VOD | Text |
content_source_desc | Channel Genre | Text |
ingest_timestamp | Date and time when the transaction record was sent from CCL to Clarissa Datawarehouse | Timestamp |
user_region | The physical location of the device user. This field is empty if the data is not received by the client app. | Text |
user_details | Text | |
cohort | Grouping of devices based on customer-defined criteria. This field is empty if no cohort is defined for the devices. | Text |
Understanding Viewing aggregation
Part of increasing the efficiency and utility of the data in the Data Warehouse lies in data aggregation. We have aggregations at a Session, Device, Content and Channel level which each serve a different metric.
Session Aggregations
Session Aggregates aggregate up to the total duration of viewing against each session and device type. You can use this table to understand total number of cVDR, Linear or VOD sessions against a device type for a given day.rep_daily_session_agg_export
Field Name | Description | Field Type |
calendar_date | Date used for data aggregation | Date |
device_type | Unique identification of the device type, for example: STB, Mobile, Tablet, PC or CTV depending on the device the app if running on | Text |
session_type | Distinct type of video session – same as CONTENT_TYPE field from transaction level, for example: LIVE: Linear content watched live, CDVR: Personal recording, REPLAYTV: CatchupTV or StartoverTV | Text |
session_count | Number of video sessions sharing the same base | Bigint |
session_duration | Number of the duration (in seconds) of all video sessions sharing the same base | Double precision |
households | Number of the duration (in seconds) of all video sessions sharing the same base | Bigint |
devices | Number of unique deviceIDs sharing the same base | Bigint |
Device Aggregations
Device aggregations ensure that the number of devices that are active are captured in a limited, fit-for-purpose, metric-driven utility. We aggregate this at a daily level to understand change in device reach from the previous day. The metrics we are deriving from this aggregation for are:
Number of monthly unique active households (calendar month to date) – Defined as a HH (any one device) having at least one viewing session in that calendar month.
Number of monthly unique active devices (calendar month to date) – Defined as a device that has at least one viewing session in that calendar month.
rep_household_device_base_agg_export
Field Name | Description | Field Type |
num_households | Number of unique householdIDs sharing the same number of unique devices. | Bigint |
num_devices | Number of unique devices under the same householdID | Bigint |
rep_devices_agg_export
Field Name | Description | Field Type |
viewing_date | Date used for data aggregation | Date |
time_period | Fixed time-period categories, for example: Last 1 day, Last 7 days, Last 30 days | Text |
device_type | Unique identification of the device type, for example: STB, Mobile, Tablet, PC or CTV depending on the device the app if running on | Text |
device_order | Device order based on the viewing hours for the household | Bigint |
households_distinct | Number of unique householdIDs sharing the same base. | Bigint |
devices_distint | Number of unique deviceIDs sharing the same base. | Bigint |
duration_distinct | Total viewing time | numeric |
rep_daily_device_agg_export
Field Name | Description | Field Type |
calendar_date | Date used for data aggregation | Date |
device_type | Unique identification of the device type, for example: STB, Mobile, Tablet, PC or CTV depending on the device the app if running on | Text |
device_type_detail | MAIN or COMPANION | Text |
session_count | Number of video sessions sharing the same base | Bigint |
session_duration | Total duration (in seconds) of all video sessions sharing the same base | Bigint |
Households | Number of unique householdIDs sharing the same base | Bigint |
devices | Number of unique deviceIDs sharing the same base. | Bigint |
rep_daily_device_7day_base_agg_export
Field Name | Description | Field Type |
viewing_date | Date used for data aggregation | Date |
device_type | Unique identification of the device type, for example: STB, Mobile, Tablet, PC or CTV depending on the device the app if running on | Text |
devices_7day | Number of unique householdIDs sharing the same base | Bigint |
households_7day | Number of unique deviceIDs sharing the same base | Bigint |
Content Aggregations
Content aggregations captures the popularity and viewing of content. It allows you to filter and understand the reach of a content asset or a channel for any given day.rep_daily_content_agg_details_export
Field Name | Description | Field Type |
calendar_date | Calendar date of the video session | Date |
device_type | Unique identification of the device type, for example: STB, Mobile, Tablet, PC or CTV depending on the device the app if running on | Text |
session_type | Distinct type of video session – same as CONTENT_TYPE field from transaction level, for example: LIVE: Linear content watched live, CDVR: Personal recording, REPLAYTV: CatchupTV or StartoverTV | Text |
channel | Channel name | Text |
program_title | Program title | Text |
program_genres | Program genre | Text |
session_count | Number of video sessions sharing the same base | Bigint |
session_duration | Total duration (in seconds) of all video sessions sharing the same base | Double precision |
households | Number of unique householdIDs sharing the same base. | Bigint |
devices | Number of unique deviceIDs sharing the same base | Bigint |
Channel Aggregations
Channel aggregations capture the popularity and viewing of channels. Since content viewing on linear is driven by the schedule we aggregate this at a weekly level to find patterns.
Total daily minutes on linear, VOD, OTT, PVR, Catch-up - to help understand changes in viewing behaviour and shifts from linear to VOD/OTT
Minutes spent on channels
Top performing channel based on HH reach (channel_rank)
rep_top_channel_device_agg_export
Field Name | Description | Field Type |
viewing_date | Calendar date of the video session. | Date |
timeperiod | Fixed time-period categories, sample: Last 1 day, Last 7 days, Last 30 days | Text |
device_type | Unique identification of the device type, for example: STB, Mobile, Tablet, PC or CTV depending on the device the app if running on | Text |
session_type | Distinct type of video session – same as CONTENT_TYPE field from transaction level, for example: LIVE: Linear content watched live, CDVR: Personal recording, REPLAYTV: CatchupTV or StartoverTV | Text |
channel | Channel name | Text |
channel_rank | Top 1 – most viewed channel Top 3 – between 2nd – 3rd most viewed channels Top 5 – between 3rd - 5th most viewed channel Top 10 - between 5th – 10th most viewed channels Above 10 – not in the first 10 most viewed channels | Text |
devices | Number of unique deviceIDs sharing the same base | bigint |
rep_channelhour_agg_export
Field Name | Description | Field Type |
calendar_date | Calendar date of the video session | Date |
device_type | Unique identification of the device type, for example: STB, Mobile, Tablet, PC or CTV depending on the device the app if running on | Text |
session_type | Distinct type of video session – same as CONTENT_TYPE field from transaction level, for example: LIVE: Linear content watched live, CDVR: Personal recording, REPLAYTV: CatchupTV or StartoverTV | Text |
sessionhour | Calendar hour of the video session | Double precision |
channel_name | Channel name | Text |
session_count | Number of video sessions sharing the same base. | Bigint |
session_duration | Total duration (in seconds) of all video sessions sharing the same base | Bigint |
households | Number of unique householdIDs sharing the same base | Bigint |
devices | Number of unique deviceIDs sharing the same base | Bigint |
