CODEWITHSUNDEEP
google-bigquery
Simon Breton
Simon Breton

Reputation: 2876

Grouping and pivoting custom dimensions

I'm working for a client with 4 website in 4 country. Some of our blog posts are translated and published on the 4 website. So what happen is that I have one unique id for each post with visit in four different country.

I would like to be able to query the number of visit for each post (so each post id) for each country.

I've run the following query but it doesn't work :

SELECT * FROM (
SELECT hits.customDimensions.value AS postid
FROM [storied-toolbox-115472:1127229124.ga_sessions_20161108]
WHERE hits.customDimensions.index=10), (SELECT hits.customDimensions.value AS Country,
count(totals.visits) as visit,
FROM [storied-toolbox-115472:1127229124.ga_sessions_20161108]
WHERE hits.customDimensions.index=1
group by
Country)

The query run correctly but in the result is I have the post id the country is referenced as null. If I have the country the post id is referenced as null.

Upvotes: 0

Views: 451

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173056

Don't have chance to do any testing so below is mostly "blind" shot, but I am usually good at this. Don't judge hard if this is wrong one :o) 

SELECT
  postid,
  country,
  SUM(visits) AS visits
FROM (
  SELECT 
    visitid, 
    hits.customDimensions.value AS postid
  FROM [storied-toolbox-115472:1127229124.ga_sessions_20161108]
  WHERE hits.customDimensions.index = 10
) AS posts
JOIN (
  SELECT 
    visitid, 
    hits.customDimensions.value AS country,
    SUM(totals.visits) AS visits
  FROM [storied-toolbox-115472:1127229124.ga_sessions_20161108]
  WHERE hits.customDimensions.index = 1
  GROUP BY visitid, country
) AS stats
ON posts.visitid = stats.visitid 
GROUP BY postid, country

Upvotes: 1

Elliott Brossard
Elliott Brossard

Reputation: 33755

I may have misunderstood the question, but here's a query that should give you all of the postids (as an array) and also the sum of visits on a per-country basis. You will need to enable standard SQL to run it.

SELECT
  ARRAY_AGG((SELECT value FROM UNNEST(hits.customDimensions)
             WHERE index = 10)) AS postids,
  (SELECT value FROM UNNEST(hits.customDimensions) WHERE index = 1) AS Country,
  SUM(totals.visits) AS visits
FROM `storied-toolbox-115472.1127229124.ga_sessions_20161108` AS t
  CROSS JOIN UNNEST(hits) AS hits
WHERE EXISTS (SELECT value FROM UNNEST(hits.customDimensions)
              WHERE index = 10)
GROUP BY Country;

Upvotes: 2

Related Questions