https://www.growthbook.io/ logo
Powered by
c

curved-football-3891

07/07/2023, 4:11 PM
Hi Everyone, I'm new to Growth Book. Can anyone help me with SQL commands for some basic metrics for Google Analytics 4 via Big Query datasource? I'm interested in CVR, AOV and EPC. Any other sugestions of usefull metrics is more than welcome. Kind Regards, Mircea
f

fresh-football-47124

07/07/2023, 9:07 PM
Hi Mircea, we would be happy to help, let me DM you
p

prehistoric-beard-84272

09/14/2023, 9:58 AM
Hi @fresh-football-47124, I'm also struggling with this. I managed to setup AOV based on the metrics from the sample project, but I'm not quite sure about EPC.
My AOV query:
Copy code
SELECT
  user_id,
  user_pseudo_id as anonymous_id,
  TIMESTAMP_MICROS(event_timestamp) as timestamp,
  ecommerce.purchase_revenue_in_usd as value
FROM
  table_here
WHERE
  event_name = '{{eventName}}'
  AND _TABLE_SUFFIX BETWEEN '{{date startDateISO "yyyyMMdd"}}' AND '{{date endDateISO "yyyyMMdd"}}'
As denominator I use "count of purchases". This seems to work. I tried something similar for EPC, using the same query and "page_view" (i.e. all users who triggered the page_view event at least once - this should give all active users during that time) as denominator, but the values are basically the same as AOV. Could you give me a pointer where I'm going wrong?
f

fresh-football-47124

09/18/2023, 6:02 AM
Hi Sascha - Sorry, I didn’t see your message previously. Would you mind asking again in #getting-started?
p

prehistoric-beard-84272

09/18/2023, 10:49 AM
Thanks for following up, @fresh-football-47124 I was able to resolve it. Here's the summary for anyone who might run into similar issues: 1. I didn't see that the metrics only show the nominator, not taking into account the denominator. Therefore AOV and EPC had to be identical in the metrics preview 2. (this shouldn't be an issue anymore moving forward as the BQ query in GB was updated) I'm using BigQuery and hadn't included the the intraday table, so the metrics AOV and EPC where not populating. After including intraday data, everything worked just fine.
Powered by