Rollups

The questions the command line answers without anybody writing SQL.

rainlytics pageviews --last 7d

path         views
-----------  -----
/              412
/liju/         208
/grammar/       97

referrers, status-codes, cache-hit-ratio and searches are the others. Each takes the same --last, the same --path and --host, the same output formats and the same bot filter.

Each of them answers from the precomputed summaries a schedule wrote, at the cost of a GET per window. --query runs the question through Athena for a fresher answer, and reports what it scanned and what that cost. Reading a precomputed answer below has when each applies.

What each one counts

pageviews counts the pages people looked at. A pageview is a GET that answered HTML and succeeded, which is what separates a page from the images, stylesheets and fonts the same log records. A 304 counts, because a browser being told its copy is current is somebody looking at the page. The path is decoded, for the reason under The log is percent-encoded twice below.

referrers counts where people arrived from, by host. Requests carrying no referrer are left out, and so are the ones this site sent itself. Those are somebody moving around inside it. On the reference site an unfiltered version of this is topped by its own stylesheet.

status-codes counts every response, including the assets the pageview count leaves out. A stylesheet returning 404 is worth seeing and a rollup looking only at pages never would.

cache-hit-ratio counts over the requests the cache had a say in, being a Hit, a RefreshHit or a Miss. A redirect, an error and a response a CloudFront Function generated are requests the cache was never asked about, and counting them would move the ratio without the cache having changed.

The log is percent-encoded twice

CloudFront percent-encodes every value it writes into a log record, and a request URI reaches it already carrying the browser’s own encoding. A page at /words/好/ is requested as /words/%E5%A5%BD/ and recorded as /words/%25E5%25A5%25BD/.

pageviews decodes the path twice, so it reports the address a reader would recognise. One pass answers /words/%E5%A5%BD/. That is the URI the browser sent, and it reads no better than the record. A site whose addresses are all ASCII sees the same table either way.

Only pageviews reads a column carrying the encoding. referrers reads a referrer for its host, and a host is ASCII whatever the rest of the URL holds. A status code and a result type arrive plain. The crawler filter matches ASCII substrings of a user agent and reads an encoded one the same way.

Two limits are worth knowing. url_decode reads + as a space. That is right for a query string and wrong for a path, where + is a literal. Athena also raises over an escape naming no byte, such as %zz. A path carrying one that still answered HTML would fail the query outright. Both stayed theoretical across 137,000 records of real traffic.

Crawlers are most of the traffic

Every rollup leaves automated traffic out by default. That is a judgement, and here it is.

One hour of the reference site in August 2026 held 9,492 requests. 3,748 of them matched the bot filter, and 1,951 were a single crawler. Bots were 39% of the hour and the largest single user agent on the site. An unfiltered pageview count is not the raw number with the opinions taken out. It is a number that says more about crawlers than about anybody who reads the site.

The filter matches four substrings against a lowercased cs(User-Agent):

bot|crawl|spider|slurp

Substrings, because a crawler names itself ClaudeBot/1.0 with the token glued to a word, which a whole-word match would walk past. The cost is a device whose name happens to contain one, and the Cubot range of Android phones is the example. Count it both ways to see how much of the difference is yours:

rainlytics pageviews --last 7d --include-bots

status-codes is the one where --include-bots is usually what you want. Bots find the broken links first and in numbers.

--path and --host narrow the question

--path counts one section of a site, as a prefix of the address:

rainlytics pageviews --path /guides/ --last 30d

It matches the address a reader sees. The record holds it percent-encoded twice, and the filter decodes before comparing, so --path /词典/ finds the pages pageviews prints under that name. The text is taken literally. A path holding _ or % matches itself.

Give --path again for each section that belongs in one answer. A request counts when its address starts with any of them:

rainlytics pageviews --path /guides/ --path /tutorials/ --last 7d

Guides and tutorials are one section of a site to whoever writes them, and a site with search boxes at /words/search/ and /sentences/search/ has no prefix covering both. Each path becomes its own prefix test and the tests are joined by OR. One --path writes what it always wrote.

An answer counting several sections together says nothing about which of them a row came from. searches names the section on every row when it is given more than one, and a rollup of your own gets the same column from matchedPath.

--host counts one of the sites a single distribution serves:

rainlytics status-codes --host docs.example.com --last 7d

That one matches in full. A site and its www name are two hosts, and folding them together is a decision for whoever runs them rather than a default. x-host-header is in the delivered field set for exactly this, and nothing else in a record says which site was asked for.

Neither option changes what a query costs. --last has already decided which partitions are read, and these two narrow rows that are paid for either way. Narrowing to one section of a busy site gives a shorter answer for the same money.

--last decides what the question costs

A range becomes partition predicates rather than a filter on the record’s own timestamp. --last 7d over a year of logs reads seven days of objects. The same range written WHERE timestamp_ms > ... answers identically and reads the year to do it, which is the mistake the whole partition layout exists to prevent.

rainlytics pageviews --last 24h
rainlytics referrers --last 2w
rainlytics status-codes --last 4w --include-bots

Whole hours, days or weeks. There is no month, because a month is not a fixed length and a range that quietly meant thirty days would be worse than one nobody could ask for.

The predicate a range builds names each partition key separately:

WHERE year IN ('2026')
  AND month IN ('08', '09')
  AND day IN ('28', '29', '30', '31', '01', '02', '03')
  AND cast(timestamp_ms AS bigint) BETWEEN 1787875200000 AND 1788436800000

Those are a cross product. A week spanning a month boundary asks for seven days in two months and reads fourteen partitions, and the timestamp condition after them is what keeps the answer exact. The alternative is one predicate per day joined by OR, which reads exactly the seven and which Athena plans more slowly the longer the range gets. Fourteen partitions against a year of them is still the difference the layout exists to make.

The same SQL, saved in the console

The RollupQueries construct saves each rollup as an Athena named query, written by the same builder the command writes with:

import { RollupQueries } from "@kensio/rainlytics/cdk";

new RollupQueries(this, "RainlyticsRollups", { table, workgroup });

Somebody in the console can then read what rainlytics pageviews counts, run it, and edit it into a question of their own without reading this repository.

The saved copies cover the current month. There is no span to compute at deploy time, and dates baked in then would be the dates of whoever last deployed and would change the template on every deploy. They ask Athena what month it is:

WHERE year = date_format(current_date, '%Y')
  AND month = date_format(current_date, '%m')

Narrowing a saved copy

Everything else a command takes is settled per rollup, by requests:

new RollupQueries(this, "RainlyticsRollups", {
  table,
  workgroup,
  requests: {
    searches: { paths: ["/search/"], param: "term" },
  },
});

searches is why this is here. It reads one query-string parameter on one page. A saved copy left to the defaults counts every query string on the distribution, while its own description tells the reader to name the search page with --path. The parameter defaults to q. A site whose box calls it something else gets a saved query that answers with an empty table.

Per rollup, and not one set of options across all five. /search/ is the search page to searches and one directory of a site to pageviews. A shared set would save rainlytics-pageviews as a query counting the search page under a name promising the whole site. That is the same fault the other way round. A rollup left out of requests takes the defaults a command starts from.

An entry takes what a rollup command takes, apart from --last. The range is always the current month for the reason above, and the database comes from the table:

requests: {
  "status-codes": { includeBots: true },
  searches: {
    host: "docs.example.com",
    paths: ["/search/"],
    param: "term",
    redirectStatuses: ["301", "302"],
  },
}

paths is the list --path collects when a command is given it more than once. A site with a search box under two sections names both, and the saved copy counts them together.

redirectStatuses is --redirect-status, and it is where a site says what its own search page answers with. The three a search counts by default are 302, 303 and 307, and searches covers why 301 and 308 are left out. A site whose exact match answers 301 puts it here, and the saved query reads a redirected column that is right for it.

An entry takes whatever RollupRequest carries, minus the range and the dataset. A field added to the request arrives here on its own.

A fact that belongs to every question, such as the host of one site on a distribution serving several, is a variable spread into each entry. Every key has to name a rollup being saved, and a mistyped one fails at synthesis. The alternative is a deployed query still counting whatever it counted before.

Each saved description says what its own copy covers. The console shows the narrowing to somebody who has read no SQL:

Count searches by the term somebody typed. What "rainlytics searches" runs.
Over the current month, on docs.example.com, under /search/, reading the "term"
parameter, counting 301 or 302 as redirected.

The statuses are named there only where a deployment chose its own. The three a search counts by default are in the rollup’s own description already, and a line repeating them on every copy says nothing about that copy.

--limit is the one option left out of that line. A row count decides how much of the answer is printed and leaves what was counted where it was. It sits on the last line of the SQL below.

Writing a rollup of your own

The rollups above are assembled from parts the package exports, and a site with a question of its own assembles another the same way. A rollup is a name, some help text and a function that writes the SQL for one request:

import {
  lastRange,
  qualifiedTableName,
  type Rollup,
  rollupRequest,
  rollupSql,
  rowsFor,
} from "@kensio/rainlytics";

const countries: Rollup = {
  name: "countries",
  summary: "Count views by country.",
  description: "Counts where readers were, most read from first.",
  isRanked: true,
  body: (request) =>
    [
      "SELECT c_country AS country, count(*) AS views",
      `  FROM ${qualifiedTableName(request.dataset)}`,
      rowsFor(request, ["sc_content_type LIKE 'text/html%'"]),
      "  GROUP BY 1",
      "  ORDER BY 2 DESC, 1",
      `  LIMIT ${String(request.limit)}`,
    ].join("\n"),
};

const sql = rollupSql(
  countries,
  rollupRequest({ range: lastRange("7d", new Date()) }),
);

rowsFor writes the whole WHERE clause. The partition predicate, the timestamp bounds, the crawler filter and the host and paths the request narrowed to all come out of it, and its second argument carries the conditions this one question adds. Writing that by hand puts a second copy of what a range costs and of the crawler filter in the site’s own repository, and the copy is the one that goes stale.

rollupSql hands back the text, and running it is the site’s own Athena client. Saving it in the workgroup is the other way round, and the way that needs no client.

countsVisitors: true puts a visitor count on the summaries a scheduled copy of the rollup writes. The count is over pageviews under the same narrowing, whatever this question counts, and Counting visitors has what it means and what it costs.

Reading a query-string parameter

decodedParameter writes the expression that takes one parameter out of a record and decodes it. A site counting the campaigns its inbound links name groups by that:

import {
  decodedParameter,
  qualifiedTableName,
  type Rollup,
  rowsFor,
} from "@kensio/rainlytics";

const campaign = decodedParameter("utm_campaign");

const campaigns: Rollup = {
  name: "campaigns",
  summary: "Count views by the campaign that sent them.",
  description: "Counts the campaigns inbound links named, most sent first.",
  isRanked: true,
  body: (request) =>
    [
      `SELECT ${campaign} AS campaign, count(*) AS views`,
      `  FROM ${qualifiedTableName(request.dataset)}`,
      rowsFor(request, ["cs_uri_query <> '-'", `${campaign} <> ''`]),
      "  GROUP BY 1",
      "  ORDER BY 2 DESC, 1",
      `  LIMIT ${String(request.limit)}`,
    ].join("\n"),
};

It names cs_uri_stem and cs_uri_query for itself. A record carries no whole URL, and those two columns are joined back together with the ? that was between them before CloudFront split them up. ('-' is what CloudFront writes where a field was empty. The first condition drops the requests that carried no query string.)

The value comes back decoded once, where pageviews decodes a column twice. url_extract_parameter decodes its own answer and one further pass finishes the job. A second pass would decode a term holding a percent sequence twice, and 50% typed into a search box is the case. That rule is what the function carries. A hand-written url_decode(url_extract_parameter(...)) in the site’s own repository carries the expression and leaves the rule behind.

decodedColumn is the other half of this, for a question grouping by a whole column rather than by one parameter. pageviews reads the path through it, and searches reads its term through decodedParameter.

Naming the section a row came from

A question narrowed to several paths counts them together, and one term or one country then holds rows from every one of them. matchedPath writes the prefix a row’s address started with, as a column the question selects and groups by:

import {
  matchedPath,
  qualifiedTableName,
  type Rollup,
  rowsFor,
} from "@kensio/rainlytics";

const countriesBySection: Rollup = {
  name: "countries-by-section",
  summary: "Count views by country and section.",
  description: "Counts where readers were, section by section.",
  isRanked: true,
  body: (request) =>
    [
      `SELECT ${matchedPath(request)} AS section,`,
      "  c_country AS country, count(*) AS views",
      `  FROM ${qualifiedTableName(request.dataset)}`,
      rowsFor(request, ["sc_content_type LIKE 'text/html%'"]),
      "  GROUP BY 1, 2",
      "  ORDER BY 3 DESC, 1, 2",
      `  LIMIT ${String(request.limit)}`,
    ].join("\n"),
};

It is a CASE over the same prefix tests rowsFor filters with, branch by branch in the order the request gave them. One definition of a prefix match covers both. A copy of the expression in a site’s own repository is a second definition, and the way those drift is a column that stops agreeing with the filter beside it.

What the column holds follows from how many paths a run was given:

• Several. The first one the address starts with. Where two overlap, /guides/ given alongside /guides/advanced/ reports a row under the second as /guides/. Every row is then in exactly one section, and a reader adding the rows up counts each of them once.
• One. That path, as a literal. Every row counted started with it, and a CASE there asks a question with one answer.
• None. CAST(NULL AS varchar). The whole distribution was counted and no prefix matched. An empty string would claim a prefix nobody asked for, and the cast gives the column a type in the result Athena hands back.

A rollup selects it however many paths it was given, and --path decides what comes back. searches is the built-in one that reads it, for a site with two search boxes.

The construct saves a site’s rollup in the console beside the built-in ones:

import { rollups } from "@kensio/rainlytics";
import { RollupQueries } from "@kensio/rainlytics/cdk";

new RollupQueries(this, "RainlyticsRollups", {
  table,
  workgroup,
  rollups: [...rollups, countries],
});

The saved copy covers the current month, as the built-in ones do. Its description says what it counts and stops there, since there is no rainlytics countries to point a reader at. It takes an entry in requests under its own name the way the built-in ones do.

A name is lowercase words joined by hyphens (cache-hit-ratio). It becomes a CDK logical id and an Athena query name, and assertRollupName refuses anything else at synthesis.

Adding a rollup of your own across windows

A range of a week is 29 stored windows, and the command adds them together before it prints anything. totals is where a rollup says how:

const countries: Rollup = {
  name: "countries",
  summary: "Count views by country.",
  description: "Counts where readers were, most read from first.",
  isRanked: true,
  totals: { added: ["views"] },
  body: (request) => /* ... */,
};

added names the columns holding counts. Every other column names a row, so two windows’ rows are matched on the country and their views add. The first count named is what a ranked answer is ordered by, matching the ORDER BY 2 DESC the query writes for one window.

A column worked out from the counts beside it is named under recomputed, and its function is handed the counts of one row once they have been added:

totals: {
  added: ["hits", "misses"],
  recomputed: {
    hit_percent: (added) => percentageOf(added["hits"], added["misses"]),
  },
},

cache-hit-ratio is the shipped question that needs it. A percentage averaged across windows is a figure about none of them, and the counts underneath it are what add.

A rollup with no totals answers from one stored window. A range covering several is reported as that, with --query offered for the span. That is the safe default for a question this package has never seen, and a wrong guess would report a percentage as its own sum.

Running a rollup of your own

The saved copy is what gives a site’s own question a command line:

rainlytics saved-query countries

rainlytics saved-query reads the queries saved in the workgroup and runs the one that matches, so nothing on this side loads the site’s code or asks for a build step. The name is the one Athena lists, with or without the rainlytics- prefix, and a name matching nothing is answered with the names that are saved there.

It takes --output, --workgroup and --region, and reports what the query scanned and what that cost the way the built-in commands do. It takes no --last and no --limit. The SQL Athena holds settled both when it was saved, which is why the range is the current month and the row count is the one requests was given. The command line page has the rest of it.

Replacing one of the built-in questions

A site whose searches answer differently from the shipped searches writes its own version and leaves the shipped one out of the list:

import { rollups } from "@kensio/rainlytics";
import { RollupQueries } from "@kensio/rainlytics/cdk";

new RollupQueries(this, "RainlyticsRollups", {
  table,
  workgroup,
  rollups: [
    ...rollups.filter((rollup) => rollup.name !== "searches"),
    mySearches,
  ],
});

rainlytics-searches in the console is then the site’s own question, and rainlytics saved-query searches runs it. The rainlytics searches command still runs the shipped one, since its command list is the questions the package ships. Two ways of asking, and the saved one is the site’s.

Passing both is refused at synthesis, since one saved query cannot answer two questions:

More than one rollup is called "searches", and each would be saved as
"rainlytics-searches". Where one of them replaces a built-in question, leave
the built-in out: rollups: [...rollups.filter((rollup) => rollup.name !==
"searches"), mySearches]

Output, and what happens next

--output json, csv or table, defaulting to a table at a terminal and to JSON when piped. Every value is a string, since every column in the log table is one.

rainlytics pageviews --last 7d --output csv > pages.csv
rainlytics referrers --last 7d | jq '.[0].referrer'

--limit takes the top rows of a ranked rollup, twenty by default. cache-hit-ratio answers with one row and has nothing to limit.

Reading a precomputed answer

rainlytics pageviews --last 7d reads what the summary schedule already counted. The bucket comes from --summaries or from RAINLYTICS_SUMMARY_BUCKET in the environment, and a range of a week is 29 objects and about a hundredth of a cent.

rainlytics pageviews --last 7d --summaries rainlytics-summaries-1a2b
rainlytics pageviews --last 7d --query

The rows are the same either way. Standard error is where the two differ, and it carries the span that answered and how old it is.

A summary answers the question it was computed with

--path, --host, --include-bots and --param each change the answer, and a schedule cannot count every combination of them. RollupSummaries computes the unfiltered form of each question, and requests is where a deployment adds a narrowed one under a name of its own.

A run whose filters no stored summary matches is told what was stored:

The stored pageviews summaries answer a different question.
  --path: asked for /guides/, computed with the whole distribution
A schedule computes the questions its deployment named, and the requests prop
on RollupSummaries is where a narrowed one is added. --query answers this run
from Athena at the cost a query reports.

--limit is the one option where the two only have to overlap. A summary computed with the top hundred paths holds the top twenty inside it. The reverse loses rows nobody counted, and it is refused.

Several windows add up, and the ranking is approximate

A week is 29 stored windows and the command adds them together. Counts add. A row that fell outside the stored rows of every window is missing from all of them, so a ranked answer assembled this way is approximate and standard error says so. --query ranks the whole span in one pass.

cache-hit-ratio adds its hits and its misses and works the percentage out again from the total. Averaging two windows’ percentages would answer a figure about neither.

A visitor count belongs to one window and never adds. The identifier takes a new salt every day, so two days’ counts added together count everybody who came back twice over. A command reading several windows says that it cannot give one.

A rollup of your own says how its rows combine with totals, and one that says nothing answers from a single stored window.

Anything else

rainlytics query takes SQL. These are the questions worth a name, and the log table holds a great many more.