Gated Content Download Rate

{
  "pageInstanceID": "resources-cloud-security-benchmark-report-production",
  "page": {
    "pageInfo": {
      "pageID": "resources/cloud-security-benchmark-report",
      "pageName": "Resources | Cloud Security Benchmark Report",
      "destinationURL": "https://www.example.com/resources/cloud-security-benchmark-report",
      "referringURL": "https://www.linkedin.com/",
      "sysEnv": "desktop",
      "language": "en-US"
    },
    "category": {
      "primaryCategory": "resources",
      "subCategory1": "analyst_report",
      "pageType": "gated_resource"
    },
    "attributes": {
      "contentGroup": "gated_resource",
      "contentAccess": "gated",
      "contentID": "asset-12345"
    }
  },
  "component": [
    {
      "componentInfo": {
        "componentID": "asset-12345",
        "componentName": "Cloud Security Benchmark Report"
      },
      "category": {
        "primaryCategory": "gated_content",
        "subCategory1": "analyst_report",
        "componentType": "pdf"
      },
      "attributes": {
        "contentTopic": "cloud security",
        "contentFormat": "pdf",
        "contentAccess": "gated",
        "gateType": "lead_form",
        "deliveryMethod": "direct_download",
        "formID": "form-67890",
        "formName": "Gated Content Lead Form",
        "formSubmissionStatus": "accepted",
        "fileName": "cloud-security-benchmark-report.pdf",
        "fileExtension": "pdf"
      }
    }
  ],
  "event": [
    {
      "eventInfo": {
        "eventName": "file_download",
        "eventAction": "download",
        "type": "conversion"
      },
      "category": {
        "primaryCategory": "gated_content"
      },
      "attributes": {
        "contentID": "asset-12345",
        "contentGroup": "gated_resource",
        "gateType": "lead_form",
        "deliveryMethod": "direct_download",
        "formID": "form-67890",
        "formSubmissionStatus": "accepted"
      }
    }
  ],
  "version": "1.0"
}

{
  "event": "file_download",
  "content_group": "gated_resource",
  "content_access": "gated",
  "content_id": "asset-12345",
  "content_name": "Cloud Security Benchmark Report",
  "content_type": "analyst_report",
  "content_topic": "cloud security",
  "content_format": "pdf",
  "gate_type": "lead_form",
  "delivery_method": "direct_download",
  "form_id": "form-67890",
  "form_name": "Gated Content Lead Form",
  "form_submission_status": "accepted",
  "file_name": "cloud-security-benchmark-report.pdf",
  "file_extension": "pdf",
  "link_url": "https://www.example.com/assets/cloud-security-benchmark-report.pdf",
  "landing_page_view": {
    "event": "page_view",
    "page_type": "gated_resource",
    "content_group": "gated_resource",
    "content_access": "gated",
    "content_id": "asset-12345",
    "content_name": "Cloud Security Benchmark Report",
    "content_type": "analyst_report",
    "content_topic": "cloud security",
    "content_format": "pdf",
    "gate_type": "lead_form",
    "form_id": "form-67890"
  }
}

{
  "event": "file_download",
  "eventInfo": {
    "linkName": "file_download",
    "linkType": "download",
    "linkURL": "https://www.example.com/assets/cloud-security-benchmark-report.pdf"
  },
  "page": {
    "pageName": "Resources | Cloud Security Benchmark Report",
    "pageURL": "https://www.example.com/resources/cloud-security-benchmark-report",
    "siteSection": "resources",
    "pageType": "gated_resource",
    "contentGroup": "gated_resource"
  },
  "content": {
    "id": "asset-12345",
    "name": "Cloud Security Benchmark Report",
    "type": "analyst_report",
    "topic": "cloud security",
    "format": "pdf",
    "access": "gated",
    "gateType": "lead_form",
    "deliveryMethod": "direct_download",
    "fileName": "cloud-security-benchmark-report.pdf",
    "fileExtension": "pdf"
  },
  "form": {
    "id": "form-67890",
    "name": "Gated Content Lead Form",
    "submissionStatus": "accepted"
  },
  "landing_page_view": {
    "event": "page_view",
    "page": {
      "pageName": "Resources | Cloud Security Benchmark Report",
      "pageURL": "https://www.example.com/resources/cloud-security-benchmark-report",
      "siteSection": "resources",
      "pageType": "gated_resource",
      "contentGroup": "gated_resource"
    },
    "content": {
      "id": "asset-12345",
      "name": "Cloud Security Benchmark Report",
      "type": "analyst_report",
      "topic": "cloud security",
      "format": "pdf",
      "access": "gated",
      "gateType": "lead_form"
    },
    "form": {
      "id": "form-67890",
      "name": "Gated Content Lead Form"
    }
  }
}

{
  "eventType": "web.webinteraction.linkClicks",
  "web": {
    "webInteraction": {
      "name": "file_download",
      "type": "download",
      "URL": "https://www.example.com/assets/cloud-security-benchmark-report.pdf",
      "linkClicks": {
        "value": 1
      }
    },
    "webPageDetails": {
      "name": "Resources | Cloud Security Benchmark Report",
      "URL": "https://www.example.com/resources/cloud-security-benchmark-report",
      "siteSection": "resources"
    }
  },
  "_tenant": {
    "content": {
      "id": "asset-12345",
      "name": "Cloud Security Benchmark Report",
      "type": "analyst_report",
      "topic": "cloud security",
      "format": "pdf",
      "access": "gated",
      "contentGroup": "gated_resource",
      "gateType": "lead_form",
      "deliveryMethod": "direct_download",
      "fileName": "cloud-security-benchmark-report.pdf"
    },
    "form": {
      "id": "form-67890",
      "name": "Gated Content Lead Form",
      "submissionStatus": "accepted"
    }
  },
  "landing_page_view": {
    "eventType": "web.webpagedetails.pageViews",
    "web": {
      "webPageDetails": {
        "name": "Resources | Cloud Security Benchmark Report",
        "URL": "https://www.example.com/resources/cloud-security-benchmark-report",
        "siteSection": "resources",
        "pageViews": {
          "value": 1
        }
      }
    },
    "_tenant": {
      "content": {
        "id": "asset-12345",
        "name": "Cloud Security Benchmark Report",
        "type": "analyst_report",
        "topic": "cloud security",
        "format": "pdf",
        "access": "gated",
        "contentGroup": "gated_resource",
        "gateType": "lead_form"
      },
      "form": {
        "id": "form-67890",
        "name": "Gated Content Lead Form"
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Gated Content Download Rate = session x asset pairs with an accepted gated asset download / all session x asset pairs with a gated landing page view
WITH events AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    event_name,
    CONCAT(user_pseudo_id, '.', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING)) AS session_key,
    device.category AS device_category,
    device.web_info.hostname AS hostname,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_group') AS content_group,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_access') AS content_access,
    (SELECT COALESCE(value.string_value, CAST(value.int_value AS STRING)) FROM UNNEST(event_params) WHERE key = 'content_id') AS content_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_name') AS content_name,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_type') AS content_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'content_format') AS content_format,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'gate_type') AS gate_type,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'form_id') AS form_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'form_submission_status') AS form_submission_status,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type') AS traffic_type
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
),
scoped AS (
  -- Session start date over all events of the session, before any event filter
  SELECT
    *,
    MIN(event_date) OVER (PARTITION BY session_key) AS session_start_date
  FROM events
  WHERE session_key IS NOT NULL
),
filtered AS (
  SELECT *
  FROM scoped
  WHERE event_name IN ('page_view', 'file_download')
    -- Internal traffic: GA4 internal traffic rule (office IP ranges) sets traffic_type = 'internal'
    AND IFNULL(traffic_type, '') != 'internal'
    -- Staging and preview hostnames
    AND NOT REGEXP_CONTAINS(LOWER(IFNULL(hostname, '')), r'staging|preview')
),
landing AS (
  -- Denominator: every session x gated asset with a landing page view
  -- content_group / content_access / content_id come from the landing page page_view push (ga4_data_layer.landing_page_view)
  SELECT
    session_key,
    content_id,
    ANY_VALUE(session_start_date) AS session_start_date,
    ANY_VALUE(device_category) AS device_category,
    MAX(content_name) AS content_name,
    MAX(content_type) AS content_type,
    MAX(content_format) AS content_format,
    MAX(gate_type) AS gate_type
  FROM filtered
  WHERE event_name = 'page_view'
    AND content_group = 'gated_resource'
    AND IFNULL(content_access, 'gated') = 'gated'
    AND content_id IS NOT NULL
  GROUP BY session_key, content_id
),
conversions AS (
  -- Numerator: accepted gated asset downloads in the same session
  SELECT DISTINCT
    session_key,
    content_id
  FROM filtered
  WHERE event_name = 'file_download'
    AND content_group = 'gated_resource'
    AND content_id IS NOT NULL
    AND form_submission_status = 'accepted'
),
qa_pairs AS (
  -- Sandbox / QA form IDs: drop the whole session x asset pair from numerator and denominator (list your own sandbox and QA form IDs)
  SELECT DISTINCT
    session_key,
    content_id
  FROM scoped
  WHERE form_id IN ('form-sandbox-001', 'form-qa-test-001')
    AND content_id IS NOT NULL
),
daily AS (
  SELECT
    l.session_start_date AS event_date,
    l.content_id,
    ANY_VALUE(l.content_name) AS content_name,
    ANY_VALUE(l.content_type) AS content_type,
    ANY_VALUE(l.content_format) AS content_format,
    ANY_VALUE(l.gate_type) AS gate_type,
    l.device_category,
    COUNT(DISTINCT CONCAT(l.session_key, '|', l.content_id)) AS gated_landing_page_sessions,
    COUNT(DISTINCT IF(c.session_key IS NOT NULL, CONCAT(l.session_key, '|', l.content_id), NULL)) AS successful_download_sessions
  FROM landing AS l
  LEFT JOIN conversions AS c
    ON c.session_key = l.session_key
   AND c.content_id = l.content_id
  LEFT JOIN qa_pairs AS q
    ON q.session_key = l.session_key
   AND q.content_id = l.content_id
  WHERE q.session_key IS NULL
  GROUP BY l.session_start_date, l.content_id, l.device_category
)
SELECT
  event_date,
  content_id,
  content_name,
  content_type,
  content_format,
  gate_type,
  device_category,
  gated_landing_page_sessions,
  successful_download_sessions,
  ROUND(SAFE_DIVIDE(successful_download_sessions, gated_landing_page_sessions) * 100, 2) AS gated_content_download_rate,
  -- Low volume: fewer than 100 landing page sessions for the asset across the whole window
  SUM(gated_landing_page_sessions) OVER (PARTITION BY content_id) < 100 AS low_volume_flag
FROM daily
ORDER BY event_date DESC, gated_landing_page_sessions DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Gated Content Download Rate = session x asset pairs with an accepted gated asset download / all session x asset pairs with a gated landing page view
WITH events AS (
  -- All experience events: sessionization runs before any event-type filter
  SELECT
    identityMap['ECID'][0].id AS ecid,
    timestamp,
    eventType,
    web.webInteraction.type AS interaction_type,
    web.webPageDetails.URL AS page_url,
    environment.ipV4 AS ip_v4,
    environment.browserDetails.userAgent AS user_agent,
    device.type AS device_type,
    _tenant.content.id AS content_id,
    _tenant.content.name AS content_name,
    _tenant.content.type AS content_type,
    _tenant.content.format AS content_format,
    _tenant.content.access AS content_access,
    _tenant.content.contentGroup AS content_group,
    _tenant.content.gateType AS gate_type,
    _tenant.form.id AS form_id,
    _tenant.form.submissionStatus AS form_submission_status
  FROM your_web_experience_event_dataset
  WHERE timestamp >= date_sub(current_date(), 90)
    AND timestamp < current_date()
    AND identityMap['ECID'][0].id IS NOT NULL
),
sessionized AS (
  SELECT
    *,
    SESS_TIMEOUT(timestamp, 60 * 30) OVER (
      PARTITION BY ecid
      ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session
  FROM events
),
scoped AS (
  SELECT
    *,
    CONCAT(ecid, '-', CAST(session.num AS STRING)) AS session_key,
    MIN(CAST(timestamp AS DATE)) OVER (PARTITION BY ecid, session.num) AS session_start_date
  FROM sessionized
),
filtered AS (
  SELECT *
  FROM scoped
  WHERE eventType IN ('web.webpagedetails.pageViews', 'web.webinteraction.linkClicks')
    -- Internal traffic: same office IP ranges as the GA4 internal traffic rule (use your own office IP ranges)
    AND COALESCE(ip_v4, '') NOT LIKE '203.0.113.%'
    -- Known bots and crawlers: Web SDK experience events are not bot-filtered by default
    AND NOT (LOWER(COALESCE(user_agent, '')) RLIKE 'bot|crawl|spider|headless')
    -- Staging and preview hostnames
    AND LOWER(COALESCE(page_url, '')) NOT LIKE '%staging%'
    AND LOWER(COALESCE(page_url, '')) NOT LIKE '%preview%'
),
landing AS (
  -- Denominator: every session x gated asset with a landing page view
  -- _tenant.content fields are populated on the landing page web.webpagedetails.pageViews event (xdm_mapping.landing_page_view)
  SELECT
    session_key,
    content_id,
    MIN(session_start_date) AS session_start_date,
    MAX(device_type) AS device_type,
    MAX(content_name) AS content_name,
    MAX(content_type) AS content_type,
    MAX(content_format) AS content_format,
    MAX(gate_type) AS gate_type
  FROM filtered
  WHERE eventType = 'web.webpagedetails.pageViews'
    AND content_group = 'gated_resource'
    AND COALESCE(content_access, 'gated') = 'gated'
    AND content_id IS NOT NULL
  GROUP BY session_key, content_id
),
conversions AS (
  -- Numerator: accepted gated asset downloads in the same session
  SELECT DISTINCT
    session_key,
    content_id
  FROM filtered
  WHERE eventType = 'web.webinteraction.linkClicks'
    AND interaction_type = 'download'
    AND content_group = 'gated_resource'
    AND content_id IS NOT NULL
    AND form_submission_status = 'accepted'
),
qa_pairs AS (
  -- Sandbox / QA form IDs: drop the whole session x asset pair from numerator and denominator (list your own sandbox and QA form IDs)
  SELECT DISTINCT
    session_key,
    content_id
  FROM scoped
  WHERE form_id IN ('form-sandbox-001', 'form-qa-test-001')
    AND content_id IS NOT NULL
),
daily AS (
  SELECT
    l.session_start_date AS event_date,
    l.content_id,
    MAX(l.content_name) AS content_name,
    MAX(l.content_type) AS content_type,
    MAX(l.content_format) AS content_format,
    MAX(l.gate_type) AS gate_type,
    l.device_type,
    COUNT(DISTINCT CONCAT(l.session_key, '|', l.content_id)) AS gated_landing_page_sessions,
    COUNT(DISTINCT CASE WHEN c.session_key IS NOT NULL THEN CONCAT(l.session_key, '|', l.content_id) END) AS successful_download_sessions
  FROM landing AS l
  LEFT JOIN conversions AS c
    ON c.session_key = l.session_key
   AND c.content_id = l.content_id
  LEFT JOIN qa_pairs AS q
    ON q.session_key = l.session_key
   AND q.content_id = l.content_id
  WHERE q.session_key IS NULL
  GROUP BY l.session_start_date, l.content_id, l.device_type
)
SELECT
  event_date,
  content_id,
  content_name,
  content_type,
  content_format,
  gate_type,
  device_type,
  gated_landing_page_sessions,
  successful_download_sessions,
  ROUND(successful_download_sessions * 100.0 / NULLIF(gated_landing_page_sessions, 0), 2) AS gated_content_download_rate,
  -- Low volume: fewer than 100 landing page sessions for the asset across the whole window
  CASE WHEN SUM(gated_landing_page_sessions) OVER (PARTITION BY content_id) < 100 THEN true ELSE false END AS low_volume_flag
FROM daily
ORDER BY event_date DESC, gated_landing_page_sessions DESC;