Hi team, we are using big query data source. I fou...
# announcementss
square-book-75964
02/22/2023, 8:25 PMHi team, we are using big query data source. I found that when I add a boolean denominator to a metric, it would cause the experiment display no data. Also, when I added activation metric to the experiment, it would said no data too.
Could anyone help on troubleshoot that?
square-book-75964
02/22/2023, 8:25 PMhttps://app.growthbook.io/experiment/exp_19g61oleg1il07
Copy code
-- revenue metric
SELECT session_id, timestamp, value
FROM `analytics_table`
WHERE
DATE(timestamp) >= '{{ startYear }}-{{ startMonth }}-{{ startDay }}' AND
DATE(timestamp) < '{{ endYear }}-{{ endMonth }}-{{ endDay }}' AND
event_type = "measure.performance";
-- result
session_id, timestamp, value
04109507985808136, 2023-02-22 04:08:12.968000 UTC, 100
15543816462148774, 2023-02-22 18:47:04.647000 UTC, 100
3077078075067108, 2023-02-22 19:17:07.746000 UTC, 80
-- boolean metric
SELECT session_id, timestamp
FROM `analytics_table`
WHERE
DATE(timestamp) >= '{{ startYear }}-{{ startMonth }}-{{ startDay }}' AND
DATE(timestamp) < '{{ endYear }}-{{ endMonth }}-{{ endDay }}' AND
event_type = "event.user_login";
-- result
session_id, timestamp
09394803480633995, 2023-02-16 05:21:27.067000 UTC
04109507985808136, 2023-02-22 04:08:12.971000 UTC
584359669554074, 2023-02-22 18:44:43.114000 UTC
8744928360643573, 2023-02-22 18:45:59.913000 UTC
15543816462148774, 2023-02-22 18:47:04.649000 UTC
024268435383898, 2023-02-22 18:49:12.402000 UTC
957748078153098, 2023-02-22 18:50:27.199000 UTC
3077078075067108, 2023-02-22 19:17:07.751000 UTCsquare-book-75964
02/22/2023, 9:47 PMcc: @fresh-football-47124 @future-teacher-7046
f
fresh-football-47124
02/22/2023, 9:51 PM
Hi Franky
s
square-book-75964
02/22/2023, 9:57 PMHi Graham, I don't know why I can see a chart with some data in metrics tab, but it would show no data in experiments tab. Also, as the data I captured above, we can find some session_id exists on both SQL
f
fresh-football-47124
02/22/2023, 9:59 PM
the metric page will show an overview from all your data, not specific to any experiment
fresh-football-47124
02/22/2023, 9:59 PM
on the experiment page, we join that data to the experiment exposure query using whatever user attribute you defined - typically user id
fresh-football-47124
02/22/2023, 10:00 PM
typically the conversion window is what causes the problem with 0 data showing up in this case
s
square-book-75964
02/22/2023, 10:03 PMWhat's that means? I'm selecting data from Jan 01, and those data was just created this morning
square-book-75964
02/22/2023, 10:04 PMsome metric with very similar SQL can show the result, but the one had denominator can't
f
fresh-football-47124
02/22/2023, 10:06 PM
are you able to run the full query SQL directly and remove the denominator part ?
s
square-book-75964
02/22/2023, 10:08 PMIf I remove the demoninator, the experiment works
f
fresh-football-47124
02/22/2023, 10:08 PM
from the UI or from the query? perhaps you can DM me the two queries and we can try to debug what’s happening?
s
square-book-75964
02/23/2023, 5:29 PMI'd sent that yesterday in DM
square-book-75964
02/23/2023, 7:45 PMAnyone else can follow up on this?
f
fresh-football-47124
02/23/2023, 7:45 PM
Hi @square-book-75964 - Sorry for the delay
fresh-football-47124
02/23/2023, 7:46 PM
let me take a look at the raw sql
s
square-book-75964
02/23/2023, 7:46 PMthank you so much 🙏
40 Views