-
Determine the reporting context — Check what day of the week it is
(Monday vs. Tuesday–Friday). This determines headline framing:
- Monday: the "prior day" comparisons cover Saturday and Sunday
individually. The headline summary emphasizes the WoW numbers. Daily
weekend breakdowns go in the thread.
- Tuesday–Friday: the "prior day" comparison is yesterday vs.
day-before-yesterday. The headline summary emphasizes the DoD numbers.
-
Identify TTS models — Query ClickHouse default.generations filtered by
api_type = 'tts' as the canonical source. Alternatively, query
analytics.dim_models for models where has(output_modalities, 'speech') is
true and use the resulting permaslug values; both approaches should produce
the same model set in practice. Do not hardcode the model list — re-derive it
every run.
-
Query weekly unique users — From default.generations filtered to TTS
(api_type = 'tts'), compute week-to-date unique users
(COUNT(DISTINCT clerk_user_id)) with at least one TTS generation, broken
down by model. If a prior complete week exists, compute WoW change
(absolute + %). Use toStartOfWeek(created_at, 1) for Monday-start weeks.
-
Query daily unique users — On Tuesday–Friday: compare yesterday vs.
the day before (unique users, absolute + % change). For yesterday, split
users into repeat vs. new by checking whether each user's earliest TTS
generation in default.generations falls on the reporting day. On
Monday: compute the same metrics for both Saturday and Sunday
individually (each compared to the prior day). For efficiency on the
new-vs-repeat split, pre-materialize a CTE / subquery of "users whose
first-ever TTS call was before the reporting day" rather than joining the
full default.generations table on itself — naive self-joins time out.
-
Query cost & revenue — From default.generations (TTS):
- Total generations, total revenue (
usage column), avg cost per generation
(overall, then by model)
- Duration by model: P50, P95, P99, and max using
quantile functions on
generation_time (Nullable Decimal, units are milliseconds — divide by
1000 to report seconds)
- On Tuesday–Friday: compare yesterday vs. day-before-yesterday with %
change
- On Monday: compute for both Saturday and Sunday individually
Known data gap (flag until fixed): as of the initial run,
generation_time is NULL on 100% of TTS rows in default.generations. When
all rows for the reporting day are NULL, do not emit fabricated 0.0
values — instead emit an explicit "duration metrics unavailable —
generation_time not populated by cfw-tts-api" line in the report and
continue. Stop flagging this once the column becomes populated (>50% non-null
on the reporting day).
-
Query model mix — From default.generations (TTS), compute each model's
share of total generations (count + %), with day-over-day change in share
(percentage points). On Monday, compute for both Saturday and Sunday.
Highlight in the headline any single-model shift of >= 10 percentage points
DoD — these typically reflect a single high-volume customer migrating models,
which is worth surfacing.
-
Query reliability from Datadog — TTS failures are emitted by
cfw-tts-api to Datadog logs. ClickHouse default.generations only contains
successful (completed) TTS generations.
Successes: count from ClickHouse default.generations (TTS).
Failures: query Datadog logs for the canonical one-per-failure event:
- Query:
service:api @script_name:tts-api "Unexpected error status returned"
- Time range: midnight-to-midnight UTC unix timestamps (seconds) for the
relevant day(s)
- This message is emitted once per failed TTS request, so each matching log
= 1 failed generation.
- Also count
status:error @script_name:tts-api separately — these are
Cloudflare-worker-level failures (memory limit, runtime crash) that do not
produce the "Unexpected error status returned" log.
Pagination: Datadog's get_logs API has a per-request cap of 1000
results. Paginate by splitting the time window into sub-windows and
re-querying any window that returns >= 1000 results, recursively, until every
leaf window returns fewer than the cap. Sum the leaf counts. Throttle between
calls to avoid Datadog 429s: get_logs/events/search tolerates short
sleeps, but the logs/analytics/aggregate endpoint is rate-limited far more
aggressively — allow at least 60 seconds between aggregate calls and prefer
one grouped aggregate call over many filtered ones. When calling the REST
API directly, build the request body with a real JSON serializer (e.g.
Python json.dumps); shell string interpolation of the query into a JSON
literal breaks once the query contains quotes.
Categorize failures by @extra.error_location:
tts.invoke -> provider-fault (upstream provider returned an error)tts.checkBans -> policy-block (OpenRouter correctly blocked a banned
user)canMakeGenerations -> budget/limit rejection (user exceeded budget or
rate limit)- status:error (worker-level) -> worker-fault (memory limit etc.)
- other / unknown -> other
Headline failure rate should be computed against provider-fault +
worker-fault only — excluding policy-block and budget/limit rejections,
which are the system working as designed. Report those counts separately so
they are visible but do not distort the reliability headline.
-
Compile the report — Format all sections into a Slack-readable message
using Slack mrkdwn. Use monospace-aligned tables in triple-backtick code
blocks, *bold* (never **bold**), _italic_, backtick inline code, and
<url|label> links (not [label](url)). Do not use #/## headers or
Markdown tables. Use clear visual separators between sections.
- Monday headline: Lead with the WoW comparison — prior complete week
totals vs. the week before. Include headline revenue and generation counts
for the full weekend (Sat+Sun combined). Do NOT lead with a single weekend
day's DoD numbers.
- Tuesday–Friday headline: Lead with yesterday's DoD comparison (active
users, generations, revenue, top model by volume).
- Always include "Day N since launch (2026-05-01)" in the headline
subtitle.
-
Query rankings SEO engagement — Query PostHog (project 90142) via the
PostHog MCP (HogQL, read-only) for the same reporting UTC day the rest of
the report covers, scoped to the rankings page /rankings/speech:
- Page traffic — count of
$pageview events whose $current_url /
pathname matches /rankings/speech (full-rate).
- Section engagement —
rankings_section_view grouped by the
section property, ranked by volume. This event is 10%-sampled, so
scale by the sample_rate property for any absolute figure and state
that it is sampled; use the raw counts to rank sections relative to each
other, not as exact totals.
- Deliberate navigation —
click_rankings_section_nav grouped by
section (full-rate).
- Framing — pair the page traffic against this report's own adoption
number (e.g. daily users / generations) to express the
traffic-vs-adoption signal ("lots of eyes on the page, little actual
usage"). Hardcode the pathname
/rankings/speech for this skill; do not
derive it from the skill name. TTS maps to /rankings/speech (not
/rankings/transcription) intentionally.
-
Slack report — Post exactly one top-level headline summary with the
native slack tool to channel C0BCV3YNZKK (#metrics-multi-modality).
Capture its returned ts, then post every detail section with the native
slack tool as a threaded reply using thread_ts set to that top-level
message's ts. Never post a second top-level message or re-post a reply.
If delivery is uncertain, inspect the thread with the native slack tool
instead. Keep each message within Slack's character limits (~4000 chars per
message). Post the rankings SEO section as one additional threaded reply
using the same thread_ts; do not add a second top-level message.
