Getting ga:socialNetwork dimension in BigQuery Export

Question

I'm trying to retrieve site referral data from social networks via BigQuery Export.

I've gotten the referral path from such sites, but what I cannot seem to find is the neatly categorized field that is available in Google Analytics.

i.e. ga:socialNetwork

Anyone know where to find this data?

So far, I've looked here: https://support.google.com/analytics/answer/3437719?hl=en

(and, in our data, of course)

Cheers!

Answer 1

Although the ga:socialNetwork dimension isn't currently available via BigQuery Export, as you mentioned, you can get the referral path using trafficSource.source.

You can see the difference between these two fields by running this query (against the Core Reporting API, which has both fields). You can then use the result as a lookup table for your data.

Answer 2

If anyone is interested here was 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 EACH BY Week, Medium, Full_URL, Referral_URL, SocialNetwork, Total_Sessions, Avg_Time_On_Site_in_Mins, Avg_Session_Page_Depth, Bounce_Rate, ORDER BY Total_Sessions DESC;

Answer 3

This is now available in BigQuery Export with the field name hits.social.socialNetwork.

The detailed documentation is here: https://support.google.com/analytics/answer/3437719?hl=en

Following this documentation I ran a sample query 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