Broadcasting metrics calculations 1

[elsenorjose]

I am creating a report for a broadcast media client and some standard broadcast reporting metrics are CUME, Average Quarter Hour, and Time Spent Listening.

The view I am querying has the following structure (among other fields not necessary for the calculations):

Station ID
IP Address
ListeningDuration (in seconds)
Starttime
Endtime
Filename

How could I get the following information? I will include the appropriate GROUP BY clause to group by Station or File.

CUME: The number of different persons who tune in a particular station or network over a period of time. This number is used to show advertisers how many different people hear their message if it is aired at different times such as 7:00 P.M., 8:00 P.M., and 9:00 P.M.. If the total number of people available is 100 and 5 of them view at 7:00 and those five still view at 8:00 but 3 new people watch, and then two people turn the TV off but 4 new ones join the audience at 9:00, the cume would be 12 (5+3+4=12).

Average Quarter Hours: This calculation is based on the average number of people viewing a particular station (network, program) for at least five minutes during a fifteen-minute period. For example, if, out of 100 people, 10 view for at least five minutes between 7:00 and 7:15, 7 view between 7:15 and 7:30, 11 view between 7:30 and 7:45, and 4 view between 7:45 and 8:00, the AQH rating would be 8 (10+7+11+4=32/4=8).

Time Spent Listening: The number of quarter-hours average persons spend listening during a specific time period, usually expressed as hours and minutes. For instance, a station may be interested in knowing how long listeners are staying tuned during the eight- quarter hours between 9:00 p.m. and 11:00 p.m. so they'll have an idea of whether people are tuning in and out or sticking with the station.

TSL= # of quarter hours in a time period X AQH Cume persons.

Any thoughts? Thank you.

 

[rac2]

CUME.

SELECT COUNT( DISTINCT ListenerID )
FROM MyTable
WHERE SpotTime1 BETWEEN Starttime AND Endtime
   OR SpotTime2 BETWEEN Starttime AND Endtime
   OR SpotTime3 BETWEEN Starttime AND Endtime
   OR SpotTime4 BETWEEN Starttime AND Endtime

 

[elsenorjose]

Considering that there are 4, 15 minute intervals per hour per day, I'd need to write 48 'OR' statements for every file or network. That's a bit impractical I think. I'll see if I can modify this. Thanks.

 

[hvass]

Good point you dont want to be writing or statements as it would not be very flexible. A better approach is to generate a table with the time segments you are interested in
table aired
adid airedtime
001 9:00
002 10:00

Then join this table to your log file

select
count(*)
from log inner join aired
on(airedtime between starttime and endtime)

Could take the same approach to AQH a bit more complex

 

[elsenorjose]

Right. Why didn't I think of that? Thanks hvass.

 

[elsenorjose]

Hvass, follow up to this question. Would you have any idea how I could generate a table that has the following structure:

id starttime endtime
1 00:00 00:15
2 00:15 00:30
etc....


What I'm trying to do is create a table with 15 minute increments. I would need the fields to be formatted as TIME, not DATETIME. I'm not very good at data definition language and can't figure this out myself.

Thanks in advance!

 

[elsenorjose]

Never mind, I cheated and imported a .txt file
Thanks again.