Getting the value ga: socialNetwork in BigQuery Export
I am trying to get referral site data from social media via BigQuery Export.
I got the referral path from sites like this, but what I can't seem to find is a neatly classified field available in Google Analytics.
i.e. ha: SocialNetwork
Does anyone know where to find this data?
So far I've looked here: https://support.google.com/analytics/answer/3437719?hl=en
(and, according to our data, of course)
Hooray!
source to share
Although the size ga:socialNetwork
is not currently available through BigQuery Export, as you mentioned, you can get the referral path using trafficSource.source
.
You can see the difference between these two fields by executing this request (versus Core Reporting API which has both fields). Then you can use the result as a lookup table for your data.
source to share
If anyone is interested in my solution, based on Andy's answer:
SELECT
Week,
IF (SocialNetwork IS NULL, Medium, "social" ) AS Medium,
Referral_URL,
SocialNetwork,
Total_Sessions,
Avg_Time_On_Site_in_Mins,
Avg_Session_Page_Depth,
Bounce_Rate,
FROM (
SELECT
Week,
Medium,
Referral_HostName,
Referral_URL,
SocialNetworks.socialNetwork AS SocialNetwork,
Total_Sessions,
Avg_Time_On_Site_in_Mins,
Avg_Session_Page_Depth,
Bounce_Rate,
FROM
[zzzzzzz.ga_sessions_20141223] AS All_Sessions
LEFT JOIN EACH [GA_API.SocialNetworks] AS SocialNetworks
ON ALL_Sessions.Referral_HostName = SocialNetworks.Source
GROUP EACH BY Week, Medium, Full_URL, Referral_HostName, Referral_URL, SocialNetwork, Total_Sessions, Avg_Time_On_Site_in_Mins, Avg_Session_Page_Depth, Bounce_Rate,
ORDER BY Total_Sessions DESC )
GROUP EVERY Week, Average, Full_URL, Referral_URL, SocialNetwork, Total_Sessions, Avg_Time_On_Site_in_Mins, Avg_Session_Page_Depth, Bounce_Rate, ORDER BY Total_Sessions DESC;
source to share
This is now available in BigQuery Export with the field name hits.social.socialNetwork
.
Detailed documentation is here: https://support.google.com/analytics/answer/3437719?hl=en
Following this documentation, I ran a sample request which worked fine
SELECT
COUNT(totals.visits),
hits.social.socialNetwork
FROM
[project:dataset.ga_sessions_20161101]
GROUP BY
hits.social.socialNetwork
ORDER BY
1 DESC
source to share