title: Difference in statistics from Google Analytics Report and BigQuery Data in Hive table
body: <p>I have a Google Analytics premium account set up to monitor the user activity of a website and mobile application. </p>
  
  <p>Raw data from GA is being stored in BigQuery tables.</p>
  
  <p>However, I noticed that the statistics that I see in a GA report are quite different the statistics that I see when querying the BigQuery tables.</p>
  
  <p>I understand that GA reports show aggregated data and possibly, sampled data. And that the raw data in Bigquery tables is session/hit-level data. </p>
  
  <p>But I am still not sure if I understand the reason why the statistics could be different.</p>
  
  <p>Would really appreciate it if someone clarified this for me.</p>
  
  <p>Thanks in advance.</p>
  
  <p><strong>UPDATE 1:</strong></p>
  
  <p>I exported the raw data from Bigquery into my Hadoop cluster. The data is stored in a hive table. I flattened all the nested and repeated fields before exporting.</p>
  
  <p>Here is the hive query that I ran on the raw data in the Hive table:</p>
  
  <pre><code>SELECT
     date as VisitDate,
     count(distinct fullvisitorid) as CountVisitors,
     SUM(totals_visits) as SumVisits,
     SUM(totals_pageviews) AS PVs
  FROM
     bigquerydata 
  WHERE 
     fullvisitorid IS NOT NULL
  GROUP BY 
     date
  ORDER BY
     VisitDate DESC
  </code></pre>
  
  <p>A) Taking February 9th as the VisitDate, I get the following results from this query:</p>
  
  <pre><code>i) CountVisitors= 1,074,323
  ii) SumVisits= 48,990,198
  iii) PVs= 1,122,841,424 
  </code></pre>
  
  <p>Vs</p>
  
  <p>B) Taking the same VisitDate and obtaining the same statistics from the GA report:</p>
  
  <pre><code>i) Users count = 1,549,757
  ii) Number of pageviews = 11,604,449 (Huge difference when compared to A(iii))  
  </code></pre>
  
  <p>In the hive query above, am I using any wrong fields or processing the fields in a wrong way? Just trying to figure out why I have this difference in numbers.</p>
  
  <p><strong>UPDATE 2 (following @Felipe Hoffa 's suggestion):</strong></p>
  
  <p>This is how I am flattening the tables in my Python code before exporting the result to GCS and then to Hadoop cluster:</p>
  
  <pre><code>queryString = 'SELECT * FROM flatten(flatten(flatten(flatten(flatten(flatten([' + TABLE_NAME + '],hits),hits.product),hits.promotion),hits.customVariables), hits.customDimensions), hits.customMetrics)'
  </code></pre>
  
  <p>I understand what you are saying about flattening causing repeated pageviews and each repetition getting into the final wrong addition. </p>
  
  <p>I tried the same query (from Update1) on Bigquery table instead of my Hive table. The numbers matched with those on the Google Analytics Dashboard.</p>
  
  <p>However, assuming that the Hive table is all I have and it has those repeated fields due to flattening.. BUT Is there still anyway that I can fix my hive query to match the stats from Google Analytics dashboard? </p>
  
  <p>Logically speaking, if the repeated fields came up due to flattening.. can't I reverse the same thing in my Hive table? If you think that I can reverse, do you have any suggestion as to how I can proceed on it?</p>
  
  <p>Thank you so much in advance!</p>