Log weekly Segment data volume to Sheets with Slack alerts

Every Monday at 8am, log my Segment ingestion volume to a Google Sheet and post a summary to Slack, so I can see cost trends long before the invoice lands. Build this as a deterministic code workflow: fetch, format, append, post. Every step runs over structured numbers and no step requires judgement.

Start with the Segment operation Get Daily Per-Source API Calls Usage to pull daily API call counts per Source for the previous seven full days, meaning the Monday through Sunday window that ended yesterday. Do not include today, because the current day is still partial and would understate the count. Note that the Segment Public API is only available on Team and Business tier workspaces, so surface a clear error if the credential comes back unauthorized.

Append one row per source per day to a tracking tab in a Google Sheet I will specify, using Append Values. Each row has exactly three columns: the date in YYYY-MM-DD format, the source name, and the API call count. Use the Source name from the usage response and fall back to the Source ID when no name is present. If the tracking tab is empty, write a header row of Date, Source, API Calls first. Order the rows by date ascending and then by source name so the sheet reads chronologically.

Before appending, read the existing rows with Get Values and skip any date and source combination that is already present, so a re-run never double counts. This sheet is the permanent history that Segment's own usage screen does not retain, so never overwrite, clear, or reorder existing rows. Only ever append.

Then compute the comparison from the rows already in the sheet, not from a second Segment call. Read the tracking tab and total the seven day window immediately before the one just written. Calculate the total API calls for the new week, the total for the prior week, and the percentage change between them, and do the same per source.

Post a message to my operations channel in Slack using Send a Message. Include the weekly total with its percentage change against the prior week, then a per source breakdown ranked from highest to lowest volume where each line shows the source name, its call count, and its own percentage change. Finish with a flagged list of any single source whose volume grew more than 25 percent week over week, called out explicitly as a likely runaway client side event firing in a loop, since that is usually the cause. The 25 percent flag is a fixed arithmetic comparison, not a reasoning step.

Handle the edge cases deterministically. On the first run there is no prior week in the sheet, so report the totals and state that there is no baseline yet instead of printing percentages. A source with volume this week but none in the prior week is labelled new rather than infinite percent growth. A source that reported last week but is at zero this week is listed at zero and flagged as stopped, because silent ingestion loss matters as much as a spike. Round every percentage to one decimal place and format call counts with thousands separators.