1

Select, groupby and sort function together to recalculate GA rates

Mourad Betkaoui

April 28, 2016 14:49

Answered

Follow

Hi,

I have an issue using nested functions.
I have a GA datasource where I get some metrics and dimension, including ga:date.

I want to display a dimension sorted by a metrics filtred on ga:date.
I use
- GROUPBY to calculate the sum of the metric based on the dimension
- SORT for sorting the result

and a SELECT to filter the data

GA datasource structure:

A: ga:date / B: fullReferrer / C: ga:users / D: ga:sessions / E: ga:bounces

The wanted result is the bounceRate by fullRefferer filtred on date between startDate and endDate, all sorted by numbers of users

Here is my query, but it's return empty result.

SELECT (
     SORT (
            GROUPBY ( SLICE(B:B) , SLICE(C:C) ) , descending numeric , GROUPBY ( SLICE ( B:B ) , SUM (  SLICE(E:E) /  SUM(SLICE( D:D ))  ) )
)

, BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) )

What I did wrong ?

Thanks for your help.
Mourad

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Keely Davison April 28, 2016 18:16

  For this formula you need to select the data based on the date range and then group it using GROUPBY.  Also the GROUPBY takes care of summing the data souse it to SUM bounces and sessions for the date range and do the division on the summed total. 

  The formula will look something like

   

  SORT (
              GROUPBY ( SELECT( SLICE(B:B), BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) ) ,

                                SELECT( SLICE(C:C) , BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) )) ,

  descending numeric ,

                              (GROUPBY ( SELECT( SLICE(B:B), BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) ) ,

                              SELECT( SLICE(E:E) , BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) ) /  

                             

                               GROUPBY ( SELECT( SLICE(B:B), BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) ) ,

                              SELECT( SLICE(D:D) , BETWEEN( DATE(A:A,"yyyyMMdd") , startDate , endDate ) )

   ) )
  )

• 0
  
  Mourad Betkaoui April 29, 2016 06:53

  Excellent !

  Thanks you very much, it works perfectly.