overview
what's new
getting started
articles
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 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. Table 2: table public.rep_daily_session_agg
Field Name | Field type |
calendar_date | Date |
device_type | Text |
session_type | Text |
session_count | Bigint |
session_duration | Double precision |
households | Bigint |
devices | bigint |
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.
Table 3: table public.rep_household_device_base_agg
Field Name | Field type |
num_households | Bigint |
num_devices | bigint |
Table 4: table public.rep_devices_agg
Field Name | Field type |
viewing_date | Date |
time_period | Text |
device_type | Text |
device_order | Bigint |
households_distinct | Bigint |
devices_distint | bigint |
duration_distinct | numeric |
Table 5: table public.rep_daily_device_agg
Field Name | Field type |
calendar_date | Date |
device_type | Text |
device_type_detail | Text |
session_count | Bigint |
session_duration | Bigint |
Households | Bigint |
devices | bigint |
Table 6: table public.rep_daily_device_7day_base_agg
Field Name | Field type |
viewing_date | Date |
device_type | Text |
devices_7day | Bigint |
households_7day | bigint |
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.
Table 7: table public.rep_daily_content_agg
Field Name | Field type |
calendar_date | Date |
device_type | Text |
session_type | Text |
channel | Text |
program_title | Text |
program_genres | Text |
session_count | Bigint |
session_duration | Double precision |
households | Bigint |
devices | bigint |
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).
Table 8: table public.rep_top_channel_device_agg
Field Name | Field Type |
viewing_date | Date |
timeperiod | Text |
device_type | Text |
session_type | Text |
channel | Text |
channel_rank | Text |
devices | bigint |
Table 9: table public.rep_channelhour_agg
Field Name | Field Type |
calendar_date | Date |
device_type | Text |
session_type | Text |
sessionhour | Double precision |
channel_name | Text |
session_count | Bigint |
session_duration | Bigint |
households | Bigint |
devices | bigint |
