Warehouse sync

Warehouse sync drops your calls, conversions, attribution and pipeline into files you load into your own data warehouse. That is the point: once the data is in BigQuery, Snowflake, Redshift or anything else you use, you can join it to everything else you hold — subscription revenue, support tickets, your sales team's activity — which is a join only you can do.

Setting one up

Under Data export, add a destination: give it a name, choose which datasets you want, and copy the signing secret. The secret is shown once. That is deliberate — it means nobody who gets into your account later can read it either. If you lose it, rotate it, which invalidates any URL already sitting in a scheduler.

A destination produces a run on a schedule you set, defaulting to daily. Each run writes one CSV per dataset plus a manifest.json, and you fetch them with signed URLs.

Why you pull rather than us pushing

We do not hold a credential for your warehouse. There is no service-account key of yours in our database, so a breach of ours cannot reach your data platform. Instead you fetch the files with a URL we sign, which reaches exactly one file of one run and stops working within hours.

The trade-off is real and worth saying out loud: you schedule the fetch, so a broken pipeline on your side looks like missing data from us. If your table stops updating, check your loader before you check with us — and the run list in the dashboard shows whether a run was produced and whether anything ever downloaded it.

Signed URLs

Press Get download links on a run and you get one URL per file. Each looks like:

https://api.proofbell.com/v1/warehouse/exports/wxr_…/files/calls.csv?expires=…&signature=v1:…

• They expire. One hour by default, seven days at most. A permanent URL to your bulk call data is a credential that cannot be rotated without somebody noticing, which is the state in which it never is.
• The expiry is inside the signature. Editing it in the URL does not extend it — the request is refused.
• One signature fetches one file. A link for conversions.csv will not fetch calls.csv, so you can give a pipeline only the dataset it needs.
• A wrong or expired signature answers 404, not 403. That is on purpose: a distinct answer would let anyone with a run id confirm it exists.

The exception is a URL whose signature is valid but past its expiry — that answers 403 with a plain message, because anyone holding a valid signature has already proved they have the secret, and telling them the link is stale saves the afternoon otherwise spent regenerating one that was correct all along.

Loading it, without duplicating rows

Data export → your destination → Load statements generates the exact CREATE TABLE and MERGE for your destination's current shape. Generated rather than printed here, because the shape depends on your settings and a copied statement from a documentation page would be for somebody else's.

Do not load an export with schema autodetect. This is the one instruction here that will cost you money if you skip it. Autodetect sees a column of digits and is entitled to choose a floating-point type for it — and a money column stored as a float loses pennies on large amounts, silently, in every table and every chart built on top of it. Nothing errors and nothing looks wrong; the totals simply stop agreeing with your invoices by amounts too small to notice and too large to ignore once somebody does.

The generated statement declares every money column as INT64 for exactly that reason. It is also why a window in which a money column happens to be empty for every row is dangerous under autodetect: with nothing to infer from, the column can arrive as STRING, and the first real value then fails to load rather than rounding — the better of the two failures, but only because it is loud.

Money is in minor units. Every _minor column is whole pence, never pounds — 4500 is £45.00. The column descriptions in the generated schema say so and they survive into your warehouse's schema view. Charting one as currency without dividing is out by a factor of a hundred.

The contract, which is the same for every dataset:

• row_key is unique per logical fact and stable for ever. Re-exporting the same fact produces the same key.
• row_version is a non-decreasing integer for that key. Only overwrite a stored row when the incoming version is greater than or equal to the stored one — two runs can be in flight at once, so an older file must not move a figure backwards.
• exported_at is when the file was made. It is not a business date, and reporting on it produces a chart of when our scheduler ran.

Load into a staging table, then merge:

MERGE proofbell.conversions T
USING (
  -- Newest version wins, in case the staging load holds a re-export.
  SELECT * EXCEPT(_rank) FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY row_key ORDER BY row_version DESC, exported_at DESC
    ) AS _rank FROM proofbell.stg_conversions
  ) WHERE _rank = 1
) S
ON T.row_key = S.row_key
WHEN MATCHED AND S.row_version >= T.row_version THEN UPDATE SET …
WHEN NOT MATCHED THEN INSERT …

The types in the generated statements are words Snowflake accepts too, so they are close to portable. What is not portable is the ROW_NUMBER subquery above — Snowflake writes that as QUALIFY.

Attribution needs a delete first

attribution.csv is the one dataset where a bare MERGE is not enough. Its key is conversion + model + channel, and the channels credited for a conversion can change — a session that arrived late can shift credit from one channel to another. The old channel's row is not in the new file, so a merge leaves it behind and the conversion appears to have been credited twice. Delete the conversions present in the file, then insert:

DELETE FROM proofbell.attribution
 WHERE conversion_id IN (SELECT DISTINCT conversion_id FROM proofbell.stg_attribution);
INSERT INTO proofbell.attribution SELECT * FROM proofbell.stg_attribution;

Money is an integer of minor units

Every column ending _minor is a whole number of the smallest unit of the currency in the currency column beside it. 402350 with GBP is £4,023.50.

Store the integer and divide in your reporting layer, not on the way in. The whole reason these files are CSV rather than JSON is that a JSON number becomes a floating-point double in every mainstream parser — including BigQuery's own JSON ingestion — and a large value loses its last digits with nothing erroring. Dividing by 100 before you store reintroduces exactly the problem the format avoids, and the symptom is a report that disagrees with your accounts by pennies per row.

A corrected deal changes a past period

If a deal closes in March for £10,000 and someone corrects it to £4,000 in July, March's figure becomes £4,000. The deal does not move to July. That matches what Google, Microsoft and Meta all do with an adjustment, and it is the only behaviour under which "what did January's spend produce" has one answer rather than one per month in which somebody happened to look.

What that means for your load: a row can change months after its occurred_at. The correction arrives as the same row_key at a higher row_version, so the MERGE is what keeps your table agreeing with ours. A window narrow enough to miss it would leave your warehouse holding the old figure indefinitely, which is why the default export window is 90 days rather than a week.

The datasets

Dataset	One row per	Keyed on
calls	Tracked call	Call id. Talk time, billability and disposition settle after the call, so a re-export corrects them.
conversions	Conversion	Conversion id, versioned by value_generation — a restated value arrives at a higher version.
attribution	Conversion × model × channel	All three. Not additive across models — always GROUP BY model.
opportunities	CRM deal	Opportunity id, versioned by generation.

Reading the conversions dataset

offline_source carries where a lead came from when you told us: one of tradeshow, webinar, sdr_outbound, instore, lead_form or sales_call. It comes from the lead source column of a CRM import — see importing deals.

Empty means two different things, and conversion_type tells them apart. Empty on an offline row means you stated no source, or stated one we would not place. Empty on a call or form row means the question does not apply. Treating both as a single “unknown” bucket buries your trade-show leads under every phone call you took, so filter on conversion_type = 'offline' before counting them.

Reading the attribution dataset

It carries two models — last non-direct (what the dashboard shows) and linear — because they disagree by construction and one number presented as truth is what makes attribution distrusted. Sum within a model, never across.

Two columns are worth understanding before you build anything on it:

• anchor is origin when the lookback window was measured back from the call that started the deal, and conversion when it was measured from the conversion's own date. It matters more than it sounds: a deal that took six months to close has its call outside a 90-day window measured from the close, so anchoring at the close loses its marketing entirely — with nothing erroring. Where we can identify the originating call, we anchor there and anchor_at tells you which date did the work.
• unattributed_reason separates no_touchpoints (we never saw this person click) from outside_lookback (we did, but before the window). They need different fixes: the first is a caller who never visited the site, the second means your lookback is too short.

attribution_degraded in the calls dataset

true means every tracking number was in use when that visitor arrived, so they saw a shared one. The call is real; the campaign beside it is a best guess. Filter on it before drawing a conclusion from a small difference between two channels.

What is never in an export

• Recordings and transcripts. Not as text, not as a URL, and not even a column saying one exists. A recording is a member of the public's voice; it is fetched from the API by a named person holding a permission, with an audit entry, and a file drop is the exact opposite of that.
• Hashed email and phone. This is the exclusion people query, because a hash reads as a pseudonym. For a phone number it is not one — there are fewer than a quadrillion possible numbers, so a hash of one is reversible by exhaustive search on a laptop. Exporting it would be exporting the number while appearing not to.
• The caller's number, unless you switch it on for that destination. Most joins do not need it, and we would rather the most sensitive column not travel to a destination that has no use for it.

The manifest

manifest.json is worth reading before the CSVs. It carries the declared type of every column, so your loader never has to guess — the guess is the failure mode, and a window in which a money column happens to be empty for every row is inferred as text, which breaks the next load. It also carries content_hash per file, so you can skip a file you have already loaded, and it states plainly what the export excludes.

Retention, and a limit

Export files stay fetchable for 14 days, then we delete them. Your warehouse is the durable copy by design — a bulk copy of your call data sitting on our storage indefinitely is a liability with no owner. Trigger a new run if you need one again.

There is a ceiling of 200,000 rows per dataset per run, and a run that exceeds it refuses and writes nothing rather than truncating. That is deliberate: a truncated file looks complete in your warehouse and is missing the oldest rows, so every period comparison ends up quietly wrong in the direction that flatters recent performance. If you hit it, narrow the window on the destination and run it more often.

If you open a CSV in a spreadsheet

Campaign names come from URL parameters on your own site, so they are whatever somebody typed. A value starting =, +, - or @ is treated as a formula by Excel and Google Sheets. We do not alter the value — the destination is a warehouse and changing a campaign name would break your join — but the manifest names any column in the file that contains such a value under spreadsheet_unsafe_columns. Import as text if you are opening one by hand.