Measuring User Retention with Hadoop and Hive

daily-events-aggregate.hql

select
    eventsAggregate.publicationId,
    eventsAggregate.userId,
    eventsAggregate.deviceModel,
    eventsAggregate.eventType,
    eventsAggregate.eventDate,
    to_date(subs.activationTime) as activationDate,
    datediff(eventsAggregate.eventDate, to_date(subs.activationTime)) as eventDay,
    eventsAggregate.count
from (
    select
        publicationId,
        userId,
        deviceModel,
        eventType,
        from_unixtime(eventTimestamp, 'yyyy-MM-dd') as eventDate,
        count(*) as count
    from
        events
    where
        dateBucket >= '%(starting_partition)s'
        and dateBucket < '%(ending_partition)s'
    group by
        publicationId,
        userId,
        deviceModel,
        eventType,
        from_unixtime(eventTimestamp, 'yyyy-MM-dd')
) eventsAggregate
join
    subs
on
    eventsAggregate.userId = subs.userId
    and eventsAggregate.publicationId = subs.publicationId;

events.hql

CREATE EXTERNAL TABLE IF NOT EXISTS events (
    uuid string,
    publicationId string,
    userId string,
    deviceModel string,
        .
        .  (metadata fields)
        .
    eventType string,
    eventTimestamp int,
        .
        .  (event-specific fields)
        .
)
PARTITIONED BY (`dateBucket` string)
LOCATION '/user/hive/warehouse/events/';

retention-aggregate.hql

select
    dailyEvents.publicationId,
    dailyEvents.deviceType,
    dailyEvents.eventDay,
    count(distinct dailyEvents.userId) as count
from
    subscriberDailyEvents dailyEvents
where
    dailyEvents.eventDay < %(num_days)d
    and dailyEvents.activationDate >= '%(first_activation)s'
    and dailyEvents.activationDate < '%(last_activation)s'
    and dailyEvents.eventType = 'appOpen'
group by
    dailyEvents.publicationId,
    dailyEvents.deviceModel,
    dailyEvents.eventDay;

subs.hql

CREATE EXTERNAL TABLE IF NOT EXISTS subs (
    publicationId string,
    userId string,
    activationTime string
        .
        .  (additional fields)
        .
)
PARTITIONED BY (`dateBucket` string)
LOCATION '/user/hive/warehouse/subs/';