Standard Activity Metrics Library

Based on activity_event tag Asemic will generate activity table, one row per active user at the time of their first event. Table is implicitly available with the definition equivalent to:

Event Definition

table_name: analytics.activity
columns:
  user:
    data_type: STRING
    tags: [entity_id_column]
  _time:
    data_type: DATETIME
    tags: [event_timestamp_column]
  date:
    data_type: DATE
    tags: [event_date_column]

Properties

properties:
  # Daily Activity Indicators
  dau_active:
    data_type: INTEGER
    can_filter: true
    can_group_by: false
    event_property:
      source_event: activity
      select: '1'
      aggregate_function: none
      default_value: 0

  # Time-based Properties
  last_active_date:
    data_type: DATE
    can_filter: true
    can_group_by: true
    lifetime_property:
      source_event_property:
        source_event: activity
        select: "DATE({_time})"
        aggregate_function: max

  days_since_last_active:
    data_type: INTEGER
    can_filter: true
    can_group_by: true
    computed_property:
      select: "DATE_DIFF({date}, {last_active_date}, DAY)"

  previous_days_since_active:
    data_type: INTEGER
    can_filter: false
    can_group_by: false
    sliding_window_property: 
      source_property: days_since_last_active
      window_function: max
      relative_days_from: -1
      relative_days_to: -1

  # Rolling Window Activity
  wau_active:
    data_type: INTEGER
    can_filter: false
    can_group_by: false
    sliding_window_property: 
      source_property: dau_active
      window_function: max
      relative_days_from: -6
      relative_days_to: 0

  mau_active:
    data_type: INTEGER
    can_filter: false
    can_group_by: false
    sliding_window_property: 
      source_property: dau_active
      window_function: max
      relative_days_from: -27
      relative_days_to: 0

  days_active_last_7:
    data_type: INTEGER
    can_filter: true
    can_group_by: false
    sliding_window_property:
      source_property: dau_active
      window_function: sum
      relative_days_from: -6
      relative_days_to: 0

  days_active_last_28:
    data_type: INTEGER
    can_filter: true
    can_group_by: false
    sliding_window_property:
      source_property: dau_active
      window_function: sum
      relative_days_from: -27
      relative_days_to: 0

  # Churn related
  mau_lost:
    label: "MAU Lost"
    data_type: INTEGER
    can_filter: true
    can_group_by: false
    computed_property: 
      select: "if({previous_days_since_active} = 28, 1, 0)"

  mau_reactivated:
    label: "MAU Reactivated"
    data_type: INTEGER
    can_filter: true
    can_group_by: false
    computed_property: 
      select: "if({previous_days_since_active} = 28, and {dau_active} = 1, 1, 0)"

KPIs

# activity_kpis.yml
kpis:
  # Core Activity Metrics
  dau:
    label: "DAU"
    select: "SUM(property.dau_active)"
    x_axis:
      date: {total_function: avg}
      cohort_day: {}

  wau:
    label: "Weekly Active Users"
    select: "SUM(property.wau_active)"
    x_axis:
      date: {total_function: avg}
      cohort_day: {}

  mau:
    label: "Monthly Active Users"
    select: "sum(property.mau_active)"
    x_axis:
      date: {total_function: avg}
      cohort_day: {}

  # Engagement Metrics
  stickiness:
    label: "Stickiness"
    description: "DAU/MAU Ratio"
    select: "SAFE_DIVIDE({kpi.dau}, 
            {kpi.mau}) * 100"
    unit: {symbol: '%', is_prefix: false}
    x_axis:
      date: {}
      cohort_day: {}

  # Retention Metrics
  retention:
    label: "Retention"
    select: "SAFE_DIVIDE({kpi.dau}, 
        sum({property.cohort_size})) * 100"
    unit: {symbol: '%', is_prefix: false}
    x_axis:
      # no date axis for this metric
      cohort_day: {}

  retention_d{}:
    label: "Retention D{}"
    select: "SAFE_DIVIDE({kpi.dau}, 
        sum({property.cohort_size})) * 100"
    where: '{property.cohort_day} = {}'
    unit: {symbol: '%', is_prefix: false}
    x_axis:
      date: {}
      # no cohort day axis for this metric
    template: [1,3,7,14,30]

  # Churn Metrics
  mau_lost:
    label: "MAU Lost"
    select: 'sum({property.mau_lost})
    x_axis:
      date: {}
      cohort_day: {}

  churn_rate:
    label: "Churn Rate"
    select: "SAFE_DIVIDE({kpi.mau_lost}, {kpi.mau}) * 100"
    unit: {symbol: '%', is_prefix: false}
    x_axis:
      date: {}
      cohort_day: {}

  mau_lost:
    label: "MAU Reactivated"
    select: 'sum({property.mau_reactivated})
    x_axis:
      date: {}
      cohort_day: {}

  resurrection_rate:
    label: "Resurrection Rate"
    select: "SAFE_DIVIDE({kpi.mau_reactivated}, {kpi.mau}) * 100"
    unit: {symbol: '%', is_prefix: false}
    x_axis:
      date: {}
      cohort_day: {}

Additional Time-Based Analysis

Asemic has built-in features for tracking a trend of any metric, so no need defining these explicitly.

Most metrics are available both for day-to-day tracking and for cohort analysis, as well.

---

These metrics provide a comprehensive framework for analyzing user activity and engagement. Would you like me to add any specific variations or additional categories?