WITH

 ---added user_id compared to the query from
        flat_user_properties AS (
        select
            user_pseudo_id,
            event_timestamp,
            event_name,
            user_id,
            user_properties.key,
            user_properties.value.string_value,
            user_properties.value.int_value,
            user_properties.value.float_value,
            user_properties.value.double_value,
            user_properties.value.set_timestamp_micros,
            concat(user_pseudo_id,event_timestamp,event_name,dedup_id) as join_key
        from (
            select
                row_number() over(partition by user_pseudo_id, event_timestamp, event_name) as dedup_id,
                *
            from
                -- change this to your google analytics 4 export location in bigquery
                `uptimerobot-001.analytics_220800152.events_*`
            where
            -- define static and/or dynamic start and end date
            _table_suffix between '20220701' and '20220809'),
            unnest(user_properties) as user_properties
            ),


/*First payment of all customers in Lifetime*/
CustomerFirstPayment as (
  SELECT
    p.userID,
    MAX(paymentDateTime) as paymentDateTime,
    MAX(firstPaymentDateTime) as firstPaymentDateTime,
    paymentPeriod,
    paymentProcessor,
    paymentCurrency,
    MAX(paymentVATRatio) as paymentVATRatio,
    MAX(productID) as productID,
    MAX(
    (CASE
      WHEN paymentCurrency = "EUR"  THEN paymentAmount * 1.07
      ELSE paymentAmount
    END)) as paymentAmountUSD,
    MAX (paymentAmount) as realPaymentAmountPaid,
    ROUND(SAFE_DIVIDE(paymentAmount,paymentPeriod),0) as MRR

  FROM
    `uptimerobot-001.ur.payments` p
    LEFT JOIN
        (SELECT
          userID,
          MIN(paymentDateTime) as firstPaymentDateTime
        FROM
        `uptimerobot-001.ur.payments`
        WHERE
          paymentType = 2 AND
          paymentStatus = 1

        GROUP BY
          userID
        ) fp
    ON
      p.userID = fp.userID and p.paymentDateTime = fp.firstPaymentDateTime

    WHERE
      firstPaymentDateTime IS NOT NULL
      AND
      -- define static and/or dynamic start and end date - align with GA4 data reporting date range
          paymentDateTime > '2022-07-01 00:00:01' AND paymentDateTime < '2022-08-09 23:59:59'

    GROUP BY
      userID,
      paymentPeriod,
      paymentProcessor,
      paymentCurrency,
      MRR

    ORDER BY
        MRR DESC),

/*A/B test dimension value by userID*/
  ABtestAsigned as (
   SELECT
    flat_user_properties.user_id,
    key,
    string_value as ABTestVariant

  FROM
    flat_user_properties
  WHERE
     key = "ABTest"

  GROUP BY
    user_id,
    key,
    ABTestVariant
  ),

/*userID's with multiple values in A/B test dimension*/
 multipleABTestVariant as (SELECT
   user_id,
   count(user_id) userIDcount

  FROM
    ABtestAsigned
  GROUP BY (user_id)
  HAVING userIDcount > 1),

/*A/B test dimension value by userID w/o userID's with multiple values in A/B test dimension*/
  withoutMultipleABTestVariant as (
    SELECT
      *
    FROM
      ABtestAsigned
    WHERE
      user_ID NOT IN (SELECT user_ID FROM multipleABTestVariant)
    ORDER BY user_id),

/*G4 by userID*/
  G4 as (
    SELECT
      user_id,
      event_name

    FROM
      flat_user_properties
    WHERE
      event_name LIKE ("G4_First_Subscription_Paid")
    GROUP BY
      user_id,
      event_name
  ),

/*UserID with A/B test dimension Connected with users who completed G4*/

  ABtest as (
    SELECT
      withoutMultipleABTestVariant.user_id as user_id,
      key,
      ABTestVariant,
      (CASE
        WHEN event_name IS NULL THEN "NotConvertedInGA4"
        WHEN event_name LIKE ("G4_First_Subscription_Paid") THEN "G4_First_Subscription_Paid"
        ELSE "WTF"
      END) as convertedinGA4


    FROM
      withoutMultipleABTestVariant
    LEFT JOIN
      G4
    ON withoutMultipleABTestVariant.user_id = G4.user_id

  ),

/*REDUNDANT list of users from GA4 completed G4 with ABTestVariant dimenssion
  ABTest2 as
    (SELECT
          user_id,
          event_name,
          key,
          string_value as ABTestVariant,
          event_timestamp

      FROM
          flat_user_properties
      WHERE
          key = "ABTest"
          AND event_name LIKE ("G4_First_Subscription_Paid")
    ),*/


  preparedData as (SELECT
      fp.userID,
      paymentDateTime,
      firstPaymentDateTime,
      paymentPeriod,
      paymentProcessor,
      productID,
      realPaymentAmountPaid,
      realPaymentAmountPaid/(1+(paymentVATRatio/100)) as realPaymentAmountPaidVATexcluded,
      paymentAmountUSD,
      paymentCurrency,
      ABTestVariant,
      /*REDUNDANT
      (CASE
        WHEN ABTestVariant = "early-bird-ab / EarlyBird" AND paymentPeriod = 12 THEN paymentAmountUSD / 0.7 / 12
        WHEN ABTestVariant = "early-bird-ab / EarlyBird" AND paymentPeriod = 1 THEN paymentAmountUSD / 0.5
        WHEN ABTestVariant = "early-bird-ab / EarlyBirdLowerDiscou" AND paymentPeriod = 12 THEN paymentAmountUSD / 0.9 / 12
        WHEN ABTestVariant = "early-bird-ab / EarlyBirdLowerDiscou" AND paymentPeriod = 1 THEN paymentAmountUSD / 0.9
        WHEN ABTestVariant = "early-bird-ab / NoEarlyBird" AND paymentPeriod = 12 THEN paymentAmountUSD / 12
        WHEN ABTestVariant = "early-bird-ab / NoEarlyBird" AND paymentPeriod = 1 THEN paymentAmountUSD
      END
      ) futureMRR,
    (CASE
        WHEN ABTestVariant = "early-bird-ab / EarlyBird" AND paymentPeriod = 12 THEN paymentAmountUSD / 0.7 * 0.3
        WHEN ABTestVariant = "early-bird-ab / EarlyBird" AND paymentPeriod = 1 THEN paymentAmountUSD / 0.5 * 0.5
        WHEN ABTestVariant = "early-bird-ab / EarlyBirdLowerDiscou" AND paymentPeriod = 12 THEN paymentAmountUSD / 0.9 * 0.1
        WHEN ABTestVariant = "early-bird-ab / EarlyBirdLowerDiscou" AND paymentPeriod = 1 THEN paymentAmountUSD / 0.9 * 0.1
        WHEN ABTestVariant = "early-bird-ab / NoEarlyBird" THEN 0
      END
      ) discountCost,*/
      convertedinGA4

    FROM
      CustomerFirstPayment fp

    LEFT JOIN
      ABTest abt

    ON TO_HEX(MD5(CONCAT(fp.userID,'honvo'))) = abt.user_id

   WHERE
      ABTestVariant IS NOT NULL),

  usersData as (
    SELECT
      pp.userID,
      paymentDateTime,
      firstPaymentDateTime,
      userRegisterDateTime,
      DATETIME_DIFF (CAST(firstPaymentDateTime AS DATETIME), CAST(userRegisterDateTime AS DATETIME), MINUTE)/60 as hoursToPayment,
      (CASE
        WHEN DATETIME_DIFF (CAST(firstPaymentDateTime AS DATETIME), CAST(userRegisterDateTime AS DATETIME), MINUTE)/60 > 1 THEN "more_than_hour"
        ELSE "less_than_hour"
      END
      ) as hoursToPaymentBucket,
      paymentPeriod,
      userIsProPlan,
      paymentProcessor,
      productID,
      realPaymentAmountPaid,
      realPaymentAmountPaidVATexcluded,
      paymentAmountUSD,
      paymentCurrency,
      ABTestVariant,
      /*REDUNDANT
      futureMRR,
      discountCost,*/
      convertedinGA4

    FROM
      preparedData pp

    LEFT JOIN
      `uptimerobot-001.ur.users` u
    ON pp.userID = u.userID

  WHERE
  userRegisterDateTime > '2022-07-01 00:00:01' AND paymentDateTime < '2022-08-09 23:59:59'

  ),

productData as (

  SELECT
      userID,
      paymentDateTime,
      firstPaymentDateTime,
      userRegisterDateTime,
      hoursToPayment,
      hoursToPaymentBucket,
      paymentPeriod,
      userIsProPlan,
      paymentProcessor,
      usersData.productID,
      `uptimerobot-001.ur.products`.productID as productID2,
      realPaymentAmountPaid,
      realPaymentAmountPaidVATexcluded,
      paymentAmountUSD,
      paymentCurrency,
      productValue,
      ABTestVariant,
      /* REDUNDANT
      futureMRR,
      discountCost,*/
      productMonthlyPrice,
      productAnnualPrice,
      (CASE
        WHEN paymentPeriod = 12 THEN productAnnualPrice
        WHEN paymentPeriod = 1 THEN productMonthlyPrice
        ELSE 99999999999999
      END) correctProductPrice,
      (CASE
        WHEN paymentPeriod = 12 THEN productAnnualPrice / 12
        WHEN paymentPeriod = 1 THEN productMonthlyPrice
        ELSE 99999999999999
      END) MRRProductPrice,
      convertedinGA4


FROM
  usersData

LEFT JOIN
  `uptimerobot-001.ur.products`

ON
  usersData.productID = `uptimerobot-001.ur.products`.productID

),

productData2 as (
  SELECT
      userID,
      paymentDateTime,
      firstPaymentDateTime,
      userRegisterDateTime,
      hoursToPayment,
      hoursToPaymentBucket,
      paymentPeriod,
      userIsProPlan,
      paymentProcessor,
      productID,
      realPaymentAmountPaid,
      realPaymentAmountPaidVATexcluded,
      paymentAmountUSD,
      paymentCurrency,
      productValue,
      ABTestVariant,
      ---futureMRR,
      ---discountCost,
      productMonthlyPrice,
      productAnnualPrice,
      MRRProductPrice,
      (CASE
        WHEN paymentCurrency IN ("EUR","eur")  THEN MRRProductPrice * 1.07
        ELSE MRRProductPrice
      END) as MRRProductPriceUSD,
      ---futureMRR - MRRProductPrice as futureMRRProductMonthlyPriceDiff,
      ROUND(realPaymentAmountPaidVATexcluded - correctProductPrice,2) as discountCost,
      (CASE
        WHEN ROUND(realPaymentAmountPaidVATexcluded - correctProductPrice,2) = 0 THEN "RealProductPrice"
        WHEN ROUND(realPaymentAmountPaidVATexcluded - correctProductPrice,2) > 0 THEN "MoreThanRealProductPrice"
        WHEN ROUND(realPaymentAmountPaidVATexcluded - correctProductPrice,2) < 0 THEN "LessThanRealProductPrice"
        ELSE "WTF"
        END
      ) paidLessMoreRealThanProductPrice,
      convertedinGA4

FROM
  productData
)

  SELECT
*

  FROM
    productData2