Data collection for analytics

[franzaps]

Track app impressions so developers can see how their apps are discovered on Zapstore.

---

Database Schema (suggestion)

impressions_hourly (
  app_id      TEXT,       -- e.g. "com.vitorpamplona.amethyst"
  hour        TIMESTAMP,  -- truncated to hour
  source      TEXT,       -- "app", "web", "unknown"
  type        TEXT,       -- "feed", "detail", "search", "stack"
  count       INTEGER,
  
  PRIMARY KEY (app_id, hour, source, type)
)

On each qualifying event: UPSERT ... SET count = count + 1

---

Increment Rules

REQ Pattern	What to Increment
kind=32267, no #d filter, limit > 1	For each app_id returned: +1 to (app_id, hour, source, "feed")
kind=32267, #d filter present	For each app_id in filter: +1 to (app_id, hour, source, "detail")
kind=32267, search term present	For each app_id returned: +1 to (app_id, hour, source, "search")
kind=30267 (App Stack) returned	For each a tag referencing 32267: +1 to (referenced_app_id, hour, source, "stack")

---

Source Detection

Extract from subscription_id prefix in the REQ:

Prefix	Source Value
app-*	"app"
web-*	"web"
anything else	"unknown"

---

Rollup Strategy

Granularity	Retention	Rollup Schedule
Hourly	7 days	—
Daily	90 days	Aggregate hourly → daily after 7 days
Monthly	Forever	Aggregate daily → monthly after 90 days

---

Platform Health (Weekly Background Job)

Aggregate across all apps to get relay-wide metrics. Runs weekly, queries the impressions_* tables:

platform_weekly (
  week        DATE,       -- week start
  source      TEXT,
  impressions_total       INTEGER,
  impressions_by_type     JSONB,    -- {"feed": N, "detail": N, ...}
  unique_apps_viewed      INTEGER,
  downloads_total         INTEGER   -- from Blossom logs
)

Computes:

• Total impressions by source (app vs web)
• Breakdown by type (feed, detail, search, stack)
• Count of distinct apps that received impressions
• Download count from Blossom access logs

---

Social Metrics (Separate Data Source)

Pulled from social relays (not relay.zapstore.dev), aggregated periodically:

Metric	Source	How to Count
Comments	kind=1 or kind=1111 with a tag referencing 32267	Count events per app
Zaps	kind=9735 with a tag referencing 32267	Count events + sum amounts per app
Stack inclusions	kind=30267 with a tag referencing 32267	Count distinct stacks containing app

Store as:

social_weekly (
  app_id              TEXT,
  week                DATE,
  comments_count      INTEGER,
  zaps_count          INTEGER,
  zaps_total_sats     INTEGER,
  stacks_included_in  INTEGER,
  
  PRIMARY KEY (app_id, week)
)

---

What NOT to Store

• IP addresses
• Full subscription IDs
• Raw REQ logs
• Pubkeys making requests
• Individual timestamps

[franzaps]

@pippellia-btc here's a rough idea on what we need for analytics in terms of data gathering

[pippellia-btc]

I think this is very sound. I am curious of why doing the whole consolidation from hourly ---> daily --> monthly.
Having only daily stats to me seems enough for granularity and much simpler.

[franzaps]

Makes sense. Ignore hourly, and we can do monthly later.

So I guess an impressions_daily then

[pippellia-btc]

Before doing social metrics and platform health aggregations, I think it makes more sense to devise an analytics strategy for downloads. Downloads are really important in conjunction to impressions because they show you the "conversion rate", which is a really useful set of metrics for developers IMO.

I've come up with this structure

CREATE TABLE IF NOT EXISTS downloads (
  hash     TEXT NOT NULL,
  day      DATE NOT NULL,
  source   TEXT NOT NULL,
  count    INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (hash, day, source)
);

source is a nice have for the future, when downloads will come from Zapstore Android, Zapstore IOS, Zapstore CLI...
To track it, I suggest using a dedicated header like

X-Zapstore-Client: app|web|cli...

What do you think @franzaps ?

[pippellia-btc]

Ah @franzaps, I think there is an issue with the requirements of the impressions. As we know there can be different apps with the same app_id published by different pubkeys. So we should add an app_pubkey to the impression schema.

app_id + app_pubkey = unique, correct?

[franzaps]

Agree let's do it but remember client can be faked. Yep apps are unique by (app ID, pubkey)

[pippellia-btc]

Agree let's do it but remember client can be faked

Sure, but that's the same for the REQ id prefix app-, ... An attacker could only increase the number of impressions of from one source, instead of being considered from unknown.