1311484 - FHRv4 cross sectional table: add simple derived columns

Status: RESOLVED WONTFIX

(Data Platform and Tools Graveyard :: Datasets: Cross-Sectional, defect, P3)

Description

[brendan c]

https://bugzilla.mozilla.org/show_bug.cgi?id=1286573#c13

1) add a number of very simple columns to the table that are the result of simple mathematical operation on the existing columns. For example, we'll want things like "active_hours_per_active_day", which is just active_hours_total/days_active.
There are a bunch of things like this, and even though they are super simple operations, we need a number of these rate metrics for the distribution viewer, and we don't want to push that logic onto that team (and more generally, it's preferable to have these measures pre-populated in one place for all consumers of the cross-sectional table rather than making people roll their own).

Comment 1

[brendan c]

Here are the columns we'll want along with the definitions. Please let me know if any of these specs are unclear or need more detail.

(note that I'm using the name change mentioned here:
https://bugzilla.mozilla.org/show_bug.cgi?id=1286573#c18
"session_hours_*" instead of "active_hours_*")

--per session hour--
number_of_pings_per_session_hour = number_of_pings/session_hours_total
search_counts_per_session_hour = search_counts_total/session_hours_total
sessions_per_session_hour = session_num_total/session_hours_total

--per active day--
session_hours_per_active_day = session_hours_total/days_active
number_of_pings_per_active_day = number_of_pings/days_active
search_counts_per_active_day = search_counts_total/days_active
sessions_per_active_day = session_num_total/days_active


--per possible day--
days_active_per_possible_day = days_active/days_possible
session_hours_per_possible_day = session_hours_total/days_possible
number_of_pings_per_possible_day = number_of_pings/days_possible
search_counts_per_possible_day = search_counts_total/days_possible
sessions_per_possible_day = session_num_total/days_possible

--days since events--
start_date_oldest_days_since = (days since start_date_oldest as of the snapshot)
start_date_newest_days_since = (days since start_date_newest as of the snapshot)
profile_creation_date_days_since = (days since profile_creation_date as of the snapshot)

Comment 2

[Daniel Thorn [:relud]]

cross_sectional has been deprecated