2 Replies Latest reply on Apr 28, 2019 10:43 PM by Matt Lazarus

    Query Google Analytics working in Bigquery but not in Tableau

    Pitchstar Chernenko

      I want to get session from Google Analytic and display them in Tableau. Attached is my Standard SQL queries:

      with subQ_googleanalytic as(
      select parse_date("%Y%m%d",date) as GA_date
      ,COUNT(DISTINCT CONCAT(CAST(fullvisitorid AS string),CAST(visitid AS string))) AS no_sessions
      from `43864393.ga_sessions_*`
      , unnest(hits) as hits
      where _TABLE_SUFFIX between
      FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)) AND
      FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      and totals.visits = 1 --The value is null if there are no interaction events in the session.
      and hits.page.hostname is null
      group by 1)
      
      
      select *
      from subQ_googleanalytic
      

      The query run successfully in Bigquery. However, i cannot paste the query into my Tableau. Tableau said:

      An error occurred while communicating with the data source.

       

       

       

       

      The Google BigQuery service was unable to compile the query.

      Syntax error: Expected ")" but got end of statement at [1:643]

      I am using tableau desktop 2018.2. How do i resolve this?

      Many thanks!

       

      Message was edited by: Pitchstar Chernenko - Fix the title

        • 1. Re: Query Google Analytics working in Bigquery but not in Tableau
          patrick.byrne.0

          Hello Pitchstar,

           

          Please try connecting to the Google Analytics data source

           

          + Google Analytics - Tableau

           

          Hopefully this helps!

           

          Cheers,

          Byrne

          • 2. Re: Query Google Analytics working in Bigquery but not in Tableau
            Matt Lazarus

            Hi Pitchstar,

             

            Are you pasting the query into the Custom SQL part of Tableau data connection window? If so, ensure:

             

            -You are using the latest version of Tableau Desktop

            -You haven't checked "Legacy SQL"

             

            Screen Shot 2019-04-29 at 2.49.35 pm.png

             

            Also, I would consider removing the WITH part of your query so that your query looks something like this:

             

            select parse_date("%Y%m%d",date) as GA_date 

            ,COUNT(DISTINCT CONCAT(CAST(fullvisitorid AS string),CAST(visitid AS string))) AS no_sessions 

            from `43864393.ga_sessions_*` 

            , unnest(hits) as hits 

            where _TABLE_SUFFIX between 

            FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)) AND 

            FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) 

            and totals.visits = 1 --The value is null if there are no interaction events in the session. 

            and hits.page.hostname is null 

            group by 1

             

            In saying this, Custom SQL can lead to performance issues. I would recommend saving your SQL query as a BigQuery View and then connecting to said View instead.

             

            Hope this helps!