I am trying to build a dashboard to assess performance of A/B test (test vs control group) for campaigns. This is how my data is :
How do I get % change between test and control groups of Campaign C1, C2 etc. Note that not every campaign has a test and control group.
I have sorted my data based on campaign and group. In simple English what I want to do is “if a campaign has test and control then calculate the % difference in the number of clicks between the two groups”
Any idea how to do this ?
campaign | group | clicks |
---|---|---|
C1 | test | 10 |
C1 | control | 2 |
C2 | control | 5 |
C3 | test | 20 |
C4 | test | 15 |
C4 | control | 25 |
Pivot your data by group column and then you can use pivot_where function to create difference in the measure
above or depending on how frequent this type of analysis is then you *could* join the view into the model twice filtering on it each time for test/control (if you always have only test and control). Will mean you can avoid pivoting for every analysis.
I got this working. I used table calcs as follows:
prev_campaign = if(row()=1, null, offset(${campaign_name},-1))
prev_group = if(row()=1, null, offset(${group},-1))
prev_clicks = if(row()=1, null, offset(${clicks},-1))
Create another table calc to compare current vs previous and if they match then calculate %change
if(${campaign} = ${prev_campaign} AND ${group} != ${prev_group},(${clicks}/${prev_clicks})-1,null)