Blog 2026-09-25

Why did paid installs drop? Query the rollup, check the coverage.

“Why did paid installs drop yesterday?” is a reasonable question to ask an AI connected to your database. But the first query should not rank campaigns. It should check whether yesterday's data is ready to compare.

A partial attribution export can look like a performance decline. A missing country can look like zero installs. And averaging campaign conversion percentages can make a clean-looking summary wrong. A useful AI analytics workflow checks coverage, compares consistent slices, and calculates rates from their underlying counts.

Here is a fictional Postgres walkthrough: an incomplete import initially suggests a 31.25% install decline. Once the import is complete, the actual recorded decline is 18.75%. SQL can show where that difference sits. It cannot, by itself, prove why people stopped installing.

Keep large attribution exports in SQL, not in the prompt

You do not need to paste 100,000 event rows into an AI conversation to compare two days. Keep the source events in your data pipeline, maintain a rollup at a useful grain, and ask for small, inspectable query results. For this example, the grain is day × campaign × country.

The rollup stores click and install counts, not just precomputed percentages. A separate coverage table records whether the pipeline considers each slice complete. That status must come from ingestion checks and the source's reporting rules—not from an agent deciding that a number looks plausible.

Bufflehead supplies read-only access to existing Postgres data. Loading exports, deduplicating events, creating rollups, and maintaining coverage metadata happen in your pipeline, outside Bufflehead.

Define the comparison before running it

  • Scope: September 22 versus September 23, 2026, in UTC; campaigns alpha and beta; countries US and GB.
  • Metric: deduplicated paid installs assigned to a click cohort using one fixed attribution rule. Clicks are the corresponding eligible clicks for that same cohort.
  • Maturity: both cohorts have completed the example's fictional 24-hour attribution window and the source's required reporting delay. Later real-world backfills would require a revised snapshot.
  • Missing versus zero: a complete slice with no activity has an explicit zero row. An absent row is unknown, not zero.

This is a controlled fixture, not a recommended attribution model. Real providers may group installs by install date instead of click date, include view-through attribution, or revise historical results. Do not combine an install-date numerator with an unrelated click-date denominator and call it conversion. Confirm the source definition and compare equally mature cohorts.

A small dataset with one incomplete slice

Run this setup in a disposable Postgres database using an ordinary SQL client, not through Bufflehead. For subsequent AI queries, connect with a database role restricted to SELECT on the intended tables.

CREATE SCHEMA installs_demo;

CREATE TABLE installs_demo.daily (
  day date NOT NULL,
  campaign text NOT NULL,
  country text NOT NULL,
  clicks integer NOT NULL CHECK (clicks >= 0),
  installs integer NOT NULL CHECK (installs >= 0),
  PRIMARY KEY (day, campaign, country)
);

CREATE TABLE installs_demo.coverage (
  day date NOT NULL,
  campaign text NOT NULL,
  country text NOT NULL,
  is_complete boolean NOT NULL,
  PRIMARY KEY (day, campaign, country)
);

INSERT INTO installs_demo.daily VALUES
  ('2026-09-22', 'alpha', 'US', 1000, 100),
  ('2026-09-22', 'alpha', 'GB',  100,  20),
  ('2026-09-22', 'beta',  'US',  300,  30),
  ('2026-09-22', 'beta',  'GB',  100,  10),
  ('2026-09-23', 'alpha', 'US', 1000,  70),
  ('2026-09-23', 'alpha', 'GB',  100,  20),
  ('2026-09-23', 'beta',  'US',  100,  10),
  ('2026-09-23', 'beta',  'GB',  100,  10);

INSERT INTO installs_demo.coverage
SELECT day, campaign, country,
       NOT (day = DATE '2026-09-23'
            AND campaign = 'beta' AND country = 'US')
FROM installs_demo.daily;

September 22 contains 160 installs. September 23 currently contains 110, which looks like a 31.25% decline. But beta/US is only partially loaded. The primary keys prevent duplicate rollup slices; they do not deduplicate source events for you.

1. Check expected coverage, including absent rows

Start from the slices you expect, then left-join the actual data. Checking only rows that arrived cannot reveal a slice that is missing entirely. The Postgres table-expression documentation explains how a LEFT JOIN retains unmatched rows from its left input.

WITH expected AS (
  SELECT d.day, c.campaign, g.country
  FROM (VALUES (DATE '2026-09-22'),
               (DATE '2026-09-23')) AS d(day)
  CROSS JOIN (VALUES ('alpha'), ('beta')) AS c(campaign)
  CROSS JOIN (VALUES ('US'), ('GB')) AS g(country)
), checked AS (
  SELECT e.*, r.clicks, r.installs,
         (c.is_complete IS TRUE AND r.day IS NOT NULL) AS ready
  FROM expected e
  LEFT JOIN installs_demo.coverage c USING (day, campaign, country)
  LEFT JOIN installs_demo.daily r USING (day, campaign, country)
)
SELECT day, campaign, country
FROM checked
WHERE NOT ready
ORDER BY day, campaign, country;

Expected result: September 23, beta, US. Until that is resolved, report the coverage problem rather than an overall performance conclusion.

These eight combinations are deliberately explicit. In production, derive expected slices from an authoritative, effective-dated campaign scope or import manifest, not from the same incomplete fact table. Not every campaign operates in every country. A bad expected set can either conceal missing data or demand combinations that should not exist.

2. Gate the campaign comparison on complete coverage

The following query checks coverage within the same statement and returns a campaign-country comparison only when all eight expected slices are ready. It repeats the small scope definition so it can run independently.

WITH expected AS (
  SELECT d.day, c.campaign, g.country
  FROM (VALUES (DATE '2026-09-22'),
               (DATE '2026-09-23')) AS d(day)
  CROSS JOIN (VALUES ('alpha'), ('beta')) AS c(campaign)
  CROSS JOIN (VALUES ('US'), ('GB')) AS g(country)
), checked AS (
  SELECT e.*, r.clicks, r.installs,
         (c.is_complete IS TRUE AND r.day IS NOT NULL) AS ready
  FROM expected e
  LEFT JOIN installs_demo.coverage c USING (day, campaign, country)
  LEFT JOIN installs_demo.daily r USING (day, campaign, country)
), changes AS (
  SELECT campaign, country,
    SUM(installs) FILTER (WHERE day = DATE '2026-09-22') AS before_installs,
    SUM(installs) FILTER (WHERE day = DATE '2026-09-23') AS after_installs
  FROM checked
  WHERE (SELECT BOOL_AND(ready) FROM checked)
  GROUP BY campaign, country
)
SELECT campaign, country, before_installs, after_installs,
       after_installs - before_installs AS install_change
FROM changes
ORDER BY install_change, campaign, country;

Before the backfill: no rows. This means “comparison withheld,” not “no change.” Use the coverage query to explain the blocker. Do not silently drop the incomplete slice and present the remainder as the full population.

To simulate the pipeline finishing its import, run the following separately in the ordinary SQL client. The transaction updates the counts and completion marker together; this is fixture maintenance, not an AI tool action.

BEGIN;
UPDATE installs_demo.daily
SET clicks = 300, installs = 30
WHERE day = DATE '2026-09-23' AND campaign = 'beta' AND country = 'US';
UPDATE installs_demo.coverage
SET is_complete = true
WHERE day = DATE '2026-09-23' AND campaign = 'beta' AND country = 'US';
COMMIT;

Rerun coverage: it should return no missing slices. Rerun the comparison: alpha/US falls from 100 to 70 installs; alpha/GB stays at 20, beta/US at 30, and beta/GB at 10. The completed total is 130 versus 160: down 30 installs, or 18.75%.

The missing 20 installs explained part of the apparent drop. The remaining 30-install decline is concentrated in alpha/US. That is a decomposition of the observed result, not proof that a creative change, audience shift, or bidding decision caused it.

3. Recompute rates from counts

Keep the same coverage gate when calculating the overall rate. Use the sum of attributed installs divided by the sum of corresponding clicks—not the average of slice percentages.

WITH expected AS (
  SELECT d.day, c.campaign, g.country
  FROM (VALUES (DATE '2026-09-22'),
               (DATE '2026-09-23')) AS d(day)
  CROSS JOIN (VALUES ('alpha'), ('beta')) AS c(campaign)
  CROSS JOIN (VALUES ('US'), ('GB')) AS g(country)
), checked AS (
  SELECT e.*, r.clicks, r.installs,
         (c.is_complete IS TRUE AND r.day IS NOT NULL) AS ready
  FROM expected e
  LEFT JOIN installs_demo.coverage c USING (day, campaign, country)
  LEFT JOIN installs_demo.daily r USING (day, campaign, country)
)
SELECT day, SUM(clicks) AS clicks, SUM(installs) AS installs,
       ROUND(100.0 * SUM(installs) / NULLIF(SUM(clicks), 0), 2)
         AS attributed_install_rate_pct
FROM checked
WHERE (SELECT BOOL_AND(ready) FROM checked)
GROUP BY day
ORDER BY day;

After completion: September 22 has 1,500 clicks, 160 installs, and a 10.67% rate. September 23 has 1,500 clicks, 130 installs, and an 8.67% rate. The rate falls by 2 percentage points. Before completion, this query also withholds its result.

Averaging the four September 22 slice rates would give 12.50%, not 10.67%, because it gives a 100-click slice the same weight as a 1,000-click slice. NULLIF leaves a zero-denominator rate undefined instead of dividing by zero. PostgreSQL's aggregate-function documentation also notes that SUM over no rows returns NULL; do not automatically turn missing data into a measured zero.

Give the agent a coverage-first instruction

Compare paid installs for September 22 and 23 in UTC, for alpha and beta in US and GB, using the agreed click-cohort attribution definition. First check all expected slices and their maturity. If any are incomplete or missing, name them and withhold the full comparison. Otherwise, return totals, campaign-country changes, and rates calculated from summed counts. Show the SQL. Separate observed changes from causal hypotheses.

Supply those definitions in the AI tool's context. Bufflehead does not create or maintain that context, certify import completeness, or guarantee model compliance. Review the generated SQL and its interpretation.

We executed the reference queries against this fictional fixture in PostgreSQL 14, with the analytical queries in read-only transactions. Checks covered the partial import, the completed backfill, missing coverage and rollup rows, and a zero-click denominator. These validate the example SQL, not an end-to-end AI run.

What a useful answer looks like

For the complete September 22–23 UTC click cohorts in the agreed campaign-country scope, recorded paid installs fell from 160 to 130 (18.75%). Alpha/US accounts for the 30-install decrease. Total eligible clicks were unchanged at 1,500; the attributed install rate fell from 10.67% to 8.67%. The initial 110-install total was incomplete. These results locate the change but do not establish its cause.

Next, request a focused drill-down into alpha/US using dimensions your data actually contains. If investigating device, creative, or placement differences requires raw events, query that subset instead of dumping the full export into the conversation. Keep the date, attribution rule, and completeness checks attached to every comparison.

Download Bufflehead to connect AI tools to existing data through read-only access. For related checks, read Your AI ran valid SQL. Did it answer the question? and Ask before you query.

Try it on your own data.

Native binary, no Docker, no cloud egress. Free for local files and direct database connections — request a demo for the AWS SSM path.

Download free Request a demo