getconfection

/* Use this query to answer the following questions: On which page did the event fire? How many times did it fire? What values are available? Where did the user who triggered the event come from? */
DECLARE __date_init DATE;
DECLARE __date_end DATE ;
DECLARE __event_name STRING ; 
DECLARE __domain STRING ;

/* Define the date range, event name, and relevant doamin(s). */
SET __date_init = '2023-09-01'; /* Return results that happened on this date or after ... */
SET __date_end = '2023-09-30'; /* .. and on this date or after. */
SET __event_name = 'eventName'; /* Replace eventName with your custom event name. You can also use Confection default events like loadtime, pageviewBatch, and purchasedEvent. */

getconfection

WITH raw_data AS (
  SELECT
    TO_JSON_STRING(JSON_EXTRACT(data, '$')) AS json_string
  FROM `projectname.confection.leads` /* Replace projectname with your project name. */
  WHERE JSON_EXTRACT_SCALAR(data, '$.domain') IN ('domain1.com', 'domain2.com', 'domain3.com') /* To return results from specific domains, enter one or more names here. */
    AND TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(data, '$.created') AS INT64))
      BETWEEN TIMESTAMP('2024-08-01') AND TIMESTAMP('2024-08-31') /* Set your timeframe here. */
)
SELECT
-- Get Cookie Values

getconfection

SELECT
  JSON_EXTRACT_SCALAR(parsed.value, '$.hubspotutk') AS hubspotutk,
  JSON_EXTRACT_SCALAR(parsed.value, '$.hsa_acc') AS hsa_acc,
  JSON_EXTRACT_SCALAR(parsed.value, '$.hsa_cam') AS hsa_cam,
  JSON_EXTRACT_SCALAR(parsed.value, '$.hsa_grp') AS hsa_grp,
  JSON_EXTRACT_SCALAR(parsed.value, '$.hsa_ad') AS hsa_ad,
  JSON_EXTRACT_SCALAR(parsed.value, '$.hsa_src') AS hsa_src,
  JSON_EXTRACT_SCALAR(parsed.value, '$.hsa_tgt') AS hsa_tgt,
  JSON_EXTRACT_SCALAR(parsed.value, '$.hsa_kw') AS hsa_kw,
  JSON_EXTRACT_SCALAR(parsed.value, '$.hsa_mt') AS hsa_mt,

getconfection

WITH raw_data AS (
  SELECT
    TO_JSON_STRING(JSON_EXTRACT(data, '$')) AS json_string
  FROM `projectname.confection.leads` /* Replace projectname with your project name. */
  WHERE JSON_EXTRACT_SCALAR(data, '$.domain') IN ('domain1.com', 'domain2.com', 'domain3.com')
    AND TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(data, '$.created') AS INT64))
      BETWEEN TIMESTAMP('2024-08-01') AND TIMESTAMP('2024-08-31') /* Set your timeframe here. */
)
SELECT
  -- Get Cookie Values

getconfection

WITH raw_data AS (
  SELECT
    TO_JSON_STRING(JSON_EXTRACT(data, '$')) AS json_string
  FROM `projectname.confection.leads` /* Replace projectname with your project name. */
  WHERE JSON_EXTRACT_SCALAR(data, '$.domain') IN ('domain1.com', 'domain2.com', 'domain3.com') /* To return results from specific domains, enter one or more names here. */
    AND TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(data, '$.created') AS INT64))
      BETWEEN TIMESTAMP('2024-08-01') AND TIMESTAMP('2024-08-31') /* Set your timeframe here. */
)
SELECT
-- Get Cookie Values

getconfection

/* We're using the following formula to determine a conversion rate: (total times an event fired / total number of UUIDs)*100 */
WITH EventFirings AS (
  SELECT
    COUNT(*) AS total_firings
  FROM `projectname.confection.leads` /* Replace projectname with your project name. */
  WHERE
    JSON_EXTRACT_SCALAR(data, '$.events.eventName.value') IS NOT NULL /* Replace eventName with your event name. */
    AND JSON_EXTRACT_SCALAR(data, '$.domain') IN ('domain1.com', 'domain2.com', 'domain3.com') /* To return results from specific domains, enter one or more names here. */
    AND TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(data, '$.events.eventName.created_time') AS INT64)) BETWEEN TIMESTAMP('2023-08-01') AND TIMESTAMP('2023-08-31') /* Set your date range here. */
),

getconfection

SELECT DISTINCT
  email_address AS Email,
  JSON_EXTRACT_SCALAR(data, '$.referrer') AS First_Referrer,
  JSON_EXTRACT_SCALAR(data, '$.utm_campaign') AS First_UTM_Campaign,
  JSON_EXTRACT_SCALAR(data, '$.utm_content') AS First_UTM_Content,
  JSON_EXTRACT_SCALAR(data, '$.utm_id') AS First_UTM_ID,
  JSON_EXTRACT_SCALAR(data, '$.utm_medium') AS First_UTM_Medium,
  JSON_EXTRACT_SCALAR(data, '$.utm_source') AS First_UTM_Source,
  JSON_EXTRACT_SCALAR(data, '$.utm_term') AS First_UTM_Term
FROM `projectname.confection.leads` /* Replace projectname with your project name. */

getconfection

WITH formtype_data AS (
  SELECT
    JSON_EXTRACT_SCALAR(data, '$.events.eventName.created_time') AS formType_created_time, /* Replace eventName with your event name. */
    JSON_EXTRACT_SCALAR(data, '$.events.eventName.value') AS formType_value /* Replace eventName with your event name. */
  FROM
    `projectname.confection.leads` /* Replace projectname with your project name. */
  WHERE
    JSON_EXTRACT_SCALAR(data, '$.events.eventName.value') IN ('value1', 'value2') /* Replace eventName with your event name. Replace value1 and value2 with event values. Add others (comma separated) as necessary. */
    AND JSON_EXTRACT_SCALAR(data, '$.domain') IN ('domain1.com', 'domain2.com', 'domain3.com') /* To return results from specific domains, enter one or more names here. */
)

getconfection

SELECT
  JSON_EXTRACT_SCALAR(data, '$.url') AS url,
  COUNT(*) AS count,
  email_address AS email
FROM `projectname.confection.leads` /* Replace projectname with your project name. */
CROSS JOIN UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(data), r'[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}')) AS email_address
WHERE JSON_EXTRACT_SCALAR(data, '$.domain') IN ('domain1.com', 'www.domain2.com', 'domain3.com') /* To return results from specific domains, enter one or more names here. Be sure to add www if your domain(s) include it. */
AND email_address NOT IN ('excluded1@example.com', 'excluded2@example.com', 'excluded3@example.com') /* Add any emails you want to exclude. */
AND TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(data, '$.created') AS INT64)) BETWEEN TIMESTAMP('2020-09-01') AND TIMESTAMP('2023-09-30') /* Set your timeframe here. */
GROUP BY url, email;

getconfection

SELECT JSON_EXTRACT_SCALAR(data, '$.url') AS URL,
       COUNT(*) AS Pageviews
FROM `projectname.confection.leads` /* Replace projectname with your project name. */
WHERE JSON_EXTRACT_SCALAR(data, '$.domain') IN ('domain1.com', 'domain2.com', 'domain3.com') /* To return results from specific domains, enter one or more names here. */
  AND TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(data, '$.created') AS INT64)) BETWEEN TIMESTAMP('2024-10-28') AND TIMESTAMP('2024-11-03') /* Replace with desired date range */
GROUP BY URL;