OpenChat users can send messages to each other containing tokens such as ICP. Users can upgrade to Diamond membership to have access to extra or enhanced features.

OpenChat is a chat application running end-to-end on the Internet Computer blockchain.

CHAT tokenholders own and govern the protocol.

github

twitter

website

Social

OpenChat

# OpenChat Active chats (monthly) Methodology

## 1. Overview

Monthly active chats measures the number of distinct chat conversations that had at least one activity (message, edit, reaction, or tip) within a rolling 30-day period for the OpenChat social platform. This metric tracks engagement depth by measuring how many unique conversations remain active over time, providing insights into community participation and platform stickiness. The calculation uses a rolling window approach where each day represents the count of distinct chats that were active during that day and the previous 29 days.

## 2. Data sources

- **OpenChat events database**: Timestamped user activity events from the OpenChat social platform including message sending, editing, reactions, and tipping activities
- **Internet Computer blockchain**: The underlying blockchain infrastructure where OpenChat operates, providing the execution environment for chat activities
- **OpenChat application activity tracking**: Event logging system that captures user interactions within the OpenChat platform, recording detailed payload information about each activity

## 3. Step-by-step calculation

### Component A: Event deduplication and filtering
- **Purpose**: Clean and filter raw OpenChat events to focus on chat activities
- **Logic**: Start with raw OpenChat events and remove duplicates by creating unique combinations of `timestamp`, `user`, `name`, `source`, and JSON-serialized `payload` content. Apply row numbering within these partition groups and keep only the first occurrence of each unique event combination. Filter the deduplicated events to include only chat activity types: `message_sent`, `message_edited`, `reaction_added`, and `message_tipped` events.
- **Key fields**: Event `name` for activity type classification, `user` for participant identification, `timestamp` for temporal ordering, `payload` containing chat metadata

### Component B: Chat activity extraction
- **Purpose**: Extract chat identifiers and standardize activity records
- **Logic**: Process filtered events to extract `chat_id` and `chat_type` from the JSON `payload` field using JSON value parsing. Truncate `timestamp` values to daily granularity and add standardized metadata fields including `timestamp_grain` set to 'day', `market_sector_id` and `business_segment_id` both set to 'social', `chain_id` set to 'internetcomputer', and `product_id` set to null. This creates a normalized view of all chat activities with consistent schema.
- **Key fields**: `chat_id` extracted from payload for conversation identification, `chat_type` for conversation categorization, daily truncated `timestamp` for temporal grouping

### Component C: Rolling window datespine construction  
- **Purpose**: Create complete time coverage with 30-day rolling windows
- **Logic**: Generate a complete daily timestamp series from the earliest chat activity date through yesterday to ensure no missing dates in the final output. For each day in this datespine, create a 30-day lookback window by generating timestamp arrays that span from 29 days prior through the current day. This creates a comprehensive grid where each day is associated with its corresponding 30-day window period, ensuring consistent window sizes even when some days have no activities.
- **Key fields**: `timestamp` for the reference date, `joinday` for each day within the 30-day window period

### Component D: Active chat aggregation
- **Purpose**: Count distinct chats active within each 30-day window
- **Logic**: Join the rolling window datespine with chat activity records where the `joinday` matches the activity `timestamp`. This creates a mapping of each reference date to all chat activities that occurred within its 30-day window. Group by reference `timestamp` and count distinct `chat_id` values to determine how many unique conversations had activity during each rolling period. This produces the core active chats monthly metric for each day.
- **Key fields**: Distinct `chat_id` count as the primary metric, reference `timestamp` for the measurement date

### Component E: Dimensional breakdowns
- **Purpose**: Calculate active chats across different analytical dimensions
- **Logic**: Create three separate dimensional views of the active chat counts. First, aggregate at the project level using 'project_dbt_tag' as the dimension with null values for `chain_id` and `product_id`. Second, aggregate by product level using 'product_id' as the dimension with null `chain_id`. Third, aggregate by chain level using 'chain' as the dimension with null `product_id`. Each breakdown maintains the same distinct `chat_id` counting logic but groups the results differently to support various analytical perspectives.
- **Key fields**: `dimensions` field indicating the breakdown type, dimensional identifier fields (`chain_id`, `product_id`), `active_chats_monthly` as the metric value

### Final aggregation
- **Combining components**: Union all dimensional breakdown results into a single comprehensive dataset containing project-level, product-level, and chain-level active chat metrics for each date
- **Schema standardization**: Apply the finalize metric macro to standardize column names, data types, and output format while implementing a 'fill_zeros' strategy to populate missing periods with zero values rather than null
- **Time aggregation**: Each record represents a daily measurement of the 30-day rolling active chat count, with the `timestamp` field indicating the end date of each rolling window period
- **Final calculation**: The final `active_chats_monthly` value represents the count of distinct chat conversations that had at least one activity (message, edit, reaction, or tip) during the 30-day period ending on the specified date

*This methodology was automatically generated by Token Terminal's methodology analyzer.*

Unique active chats over a rolling 30-day window.

OpenChatCHAT

Active chats (monthly)

Active chats (monthly)

Competitive landscape