cabennett85/gittest — Issue #1345: Store initial feature level metrics for BroadCause
Author: cabennett85
Created: 2012-03-02 00:47:02+00:00
State: open

Let's do a first pass on feature level metrics, starting with simple counts of all the feature items (wishes, categories, locations, events, photos, contacts, newsletters sent, broadcasts), segmented by cause segment.

The queries will generally look like
{{{
SELECT
c.activity_level,
c.creation_method,
c.profile_completion_percentage,
COUNT(*) AS value
FROM
broadcause_metrics_cause_segments c INNER JOIN [table for feature X] t ON c.bnid = t.bnid
GROUP BY
activity_level,
creation_method,
profile_completion_percentage
}}}

e.g.

wishes
{{{
SELECT
c.activity_level,
c.creation_method,
c.profile_completion_percentage,
COUNT(*) AS num_wishes
FROM
broadcause_metrics_cause_segments c INNER JOIN broadcause_nonprofit_wish w ON c.bnid = w.bnid
GROUP BY
activity_level,
creation_method,
profile_completion_percentage
}}}

Then we'll save them into the broadcause_metrics table (more aptly named broadcause_cause_metrics since this table is for cause level metrics), where the activity_level, creation_method, and profile_completion_percentage columns correspond directly to the results of the query above, value corresponds to the count(*) result above, and name will be the plural name of the feature (e.g. wishes, categories, etc.).

We'd want to store them on a daily basis just like we do for the metric "causes". See implementation of that metric for reference.

For convenience I'll list the tables for the features here:
{{{
name | table
--------------------------------------------------
broadcasts | broadcause_nonprofit_broadcast
categories | broadcause_nonprofit_categories_map
contacts | broadcause_nonprofit_contact
events | broadcause_nonprofit_event
locations | broadcause_nonprofit_locations
newsletters_sent | broadcause_nonprofit_sent_email
photos | broadcause_nonprofit_photo
wishes | broadcause_nonprofit_wish
}}}

There is a special case for events, where the query will look different:

{{{
SELECT
c.activity_level,
c.creation_method,
c.profile_completion_percentage,
COUNT(*) AS num_events
FROM
(broadcause_nonprofit n
INNER JOIN broadcause_metrics_cause_segments c ON n.id = c.bnid)
INNER JOIN broadcause_nonprofit_event e ON n.ebuid = e.ebuid
GROUP BY
activity_level,
creation_method,
profile_completion_percentage
}}}