Subscription analytics for publishers: from anonymous reader to subscriber

A trial converts days later, in billing, where no pixel runs, so Google Ads and Meta never see the subscriptions those trials turn into. The pixel on the checkout page also misses every reader whose blocker stops it.

Linking a subscription to the reading behind it means joining billing's events with the browser's in one table, because that reading happened before the reader logged in.

TL;DR

• Your billing backend posts subscription_started and trial_converted to Jitsu server-to-server.
• Google Ads gets those two events and Meta gets them as StartTrial and Subscribe, each with three properties (order_id, value, currency) plus the click IDs, IP address and user agent used for matching.
• Without recorded advertising consent, a conversion reaches neither ad platform.
• The SQL on this page runs on the events table in your warehouse and shows how many articles new subscribers read on the web, how many paywalls they saw and what share joined a newsletter in the 30 days before they subscribed or started a trial.

What reaches the ad platforms

A function sits on the billing stream's Google Ads connection with AD_PLATFORM=google-ads and on its Meta Ads connection with AD_PLATFORM=meta. For each event:

1. Only subscription_started and trial_converted with ad_consent: true pass. Page views, engagement and video events stop here.
2. Conversions without a subscription ID, a numeric value or a three-letter currency stop with a warning in Live Events.
3. What passes leaves with three properties, order_id, value and currency, and without context.traits. The click IDs, IP address, user agent and page URL in context go along for matching; article IDs, sections and plan names stay in the warehouse.
4. On the Meta connection, the event takes Meta's standard event name: StartTrial for a trial start, Subscribe for a paid subscription (Meta Pixel reference).

Take a magazine that sells annual subscriptions with a 14-day trial. Both ad platforms hear about the trial on day one and about the first annual charge, with its value, on day 15.

Deploy the function with jitsu-cli. Destination setup: Google Ads docs, Meta Ads.

import { JitsuFunction } from "@jitsu/protocols/functions";
import { AnalyticsServerEvent } from "@jitsu/protocols/analytics";

export const config = {
  slug: "ad-conversions",
  name: "Subscription conversions for ad platforms",
  description: "Lets consented subscription conversions through to ad platforms with three properties and no traits",
};

type Props = {
  // "google-ads" or "meta": the destination on this connection
  AD_PLATFORM: string;
};

const CONVERSIONS = ["subscription_started", "trial_converted"];

const adConversions: JitsuFunction<AnalyticsServerEvent, Props> = async (event, { log, props }) => {
  // Only the two conversions reach an ad platform. Page views, video and engagement events stop here.
  if (event.type !== "track" || !CONVERSIONS.includes(event.event ?? "")) {
    return "drop";
  }
  const p = event.properties ?? {};
  // ad_consent is the advertising consent your backend recorded for this reader at checkout.
  if (p.ad_consent !== true) {
    return "drop";
  }
  const currency = typeof p.currency === "string" ? p.currency.toUpperCase() : "";
  const valid =
    typeof p.subscription_id === "string" &&
    p.subscription_id !== "" &&
    typeof p.value === "number" &&
    Number.isFinite(p.value) &&
    /^[A-Z]{3}$/.test(currency);
  if (!valid) {
    log.warn(`${event.event} ${event.messageId}: needs subscription_id, numeric value and currency, dropped`);
    return "drop";
  }
  // Only these three properties reach the ad platform. Articles, sections, plan names,
  // cancellation reasons and traits such as email stay in the warehouse. The order ID includes
  // the event name, so a trial start and its first paid charge do not share one.
  event.properties = { order_id: `${p.subscription_id}:${event.event}`, value: p.value, currency };
  if (event.context?.traits) {
    delete event.context.traits;
  }
  if (props.AD_PLATFORM === "meta") {
    event.event = event.event === "subscription_started" && p.trial === true ? "StartTrial" : "Subscribe";
  }
  return event;
};

export default adConversions;

What new subscribers did before signing up

The query joins through identify events, which carry both anonymous_id and user_id.

-- BigQuery: what new subscribers did in the 30 days before subscribing
WITH subs AS (
  SELECT user_id, MIN(timestamp) AS subscribed_at
  FROM `your_dataset.events`
  WHERE type = 'track'
    AND event = 'subscription_started'
    AND timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  GROUP BY user_id
),
id_map AS (
  -- every anonymous ID a reader has identified from, on any plan
  SELECT DISTINCT user_id, anonymous_id
  FROM `your_dataset.events`
  WHERE type = 'identify'
    AND user_id IS NOT NULL
    AND anonymous_id IS NOT NULL
),
history AS (
  SELECT s.user_id, e.message_id, e.type, e.event, e.article_id
  FROM subs AS s
  JOIN id_map AS m ON m.user_id = s.user_id
  JOIN `your_dataset.events` AS e
    ON e.anonymous_id = m.anonymous_id
   AND e.timestamp BETWEEN TIMESTAMP_SUB(s.subscribed_at, INTERVAL 30 DAY) AND s.subscribed_at
),
per_reader AS (
  SELECT
    s.user_id,
    COUNT(DISTINCT IF(h.type = 'page', h.article_id, NULL)) AS articles_read,
    COUNT(DISTINCT IF(h.event = 'paywall_shown', h.message_id, NULL)) AS paywalls_seen,
    COUNT(DISTINCT IF(h.event = 'newsletter_subscribed', h.message_id, NULL)) AS newsletter_signups
  FROM subs AS s
  LEFT JOIN history AS h ON h.user_id = s.user_id
  GROUP BY s.user_id
)
SELECT
  COUNT(*) AS new_subscribers,
  APPROX_QUANTILES(articles_read, 2)[OFFSET(1)] AS median_articles_read,
  APPROX_QUANTILES(paywalls_seen, 2)[OFFSET(1)] AS median_paywalls_seen,
  ROUND(SAFE_DIVIDE(COUNTIF(newsletter_signups > 0), COUNT(*)), 3) AS share_with_newsletter_signup,
  ROUND(SAFE_DIVIDE(COUNTIF(articles_read = 0 AND paywalls_seen = 0), COUNT(*)), 3) AS share_with_no_history
FROM per_reader;

A high share_with_no_history means new subscribers with no web page views and no paywalls linked to them in the 30 days before: reading that never got linked, reading done only in the apps, or subscribers who came straight from an ad. Counting app reading as well means adding screen events next to page in the articles count. To keep it low, deploy the ID endpoint from the server-side tracking page, and identify readers at every login as shown under Sending the events, which links a second device.

Sending the events

The funnel uses ten events from the media tracking plan (CSV):

In the browser, identify the reader at sign-up and on every login with your internal user ID, and send funnel events with track.

jitsu.identify("u-1001", { plan: "registered" });
jitsu.track("paywall_shown", { article_id: "a-2231", paywall_type: "meter", meter_count: 5 });

Conversions come from your billing code, from the handler that receives your billing provider's webhooks or your own logic, with a server-to-server write key set up as on the server-side tracking page:

1. At checkout, store with the subscription: the reader's anonymous ID (the __eventn_id cookie), the advertising consent your consent platform recorded, Meta's _fbc and _fbp cookies, and the Google click ID (gclid, gbraid or wbraid) the reader arrived with.
2. Send subscription_started when billing starts a trial or a subscription, and trial_converted on the first paid charge after a trial.
3. Put the stored consent in properties.ad_consent as true or false. Your consent platform decides it; the function only reads it.
4. Include the reader's IP address and user agent in context.
5. Add the stored click IDs to context.clientIds and the checkout page URL to context.page.url; the Meta Ads destination sends that URL as event_source_url.

curl --location 'https://events.example.com/api/s/track' \
--header 'Content-Type: application/json' \
--header 'X-Write-Key: keyId:keySecret' \
--data-raw '{"type":"track","event":"trial_converted","userId":"u-1001","anonymousId":"7c1e9a52-0d4b-4a8e-9f3a-2b6f1c9d8e01","properties":{"subscription_id":"sub_8812","plan":"digital-annual","value":89,"currency":"EUR","ad_consent":true},"context":{"ip":"203.0.113.7","userAgent":"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)","clientIds":{"gclid":"EAIaIQobChMIexample","fbc":"fb.1.1792560000000.IwAR0example","fbp":"fb.1.1792560000000.1098765432"},"page":{"url":"https://www.example.com/subscribe"}}}'

Every event, cancellations and their reasons included, goes to the warehouse, and funnel events without email can also go to Mixpanel, Amplitude or PostHog.

Questions people ask

Should the pixel on the checkout page stay on? Report each subscription to an ad platform from one place. If the checkout pixel or Google tag already reports subscriptions, turn that conversion off in Meta and Google Ads when the server-side conversion goes live, so one subscription is not counted twice.

Can the anonymous reading be written onto the subscriber's user ID? Yes, in the warehouse, with identity stitching: Enterprise on Jitsu Cloud, any plan self-hosted. Once a reader identifies, their anonymous rows from up to 30 days before take the user ID in ClickHouse, BigQuery, PostgreSQL, Redshift, Snowflake or MySQL. The query on this page works without it.

Does sending no email hurt matching in Google Ads and Meta? Google Ads matches these conversions on the click ID stored at checkout, and Meta gets fbc, fbp and the page URL. For more matching, Jitsu's Google Ads destination also supports Enhanced Conversions, which match on hashed customer data such as an email address; that means letting a hashed email through on that one connection.

Get started with Jitsu

Test against your billing sandbox first: one trial_converted from a test charge shows up in Live Events before Google Ads or Meta are connected. Start a free Jitsu workspace to try it. The media and publishing guide links the full 34-event plan, reading and paywall events included.