How to query the GA realtime dataset using standard SQL

If you’re using GA360 and have setup the automated export to BiqQuery – you’ve probably noticed the realtime streaming option. Although it would outwardly seem like you can query these (.ga_realtime_sessions_yyyymmdd) tables like the regular (.ga_sessions_yyyymmdd) tables using standard or legacy sql – its not that simple. If you do – you will note data in which the counts are highly inflated. NOW, If you’re using legacy sql, google provides a view you can query – and you can skip the rest of this article. However, if you’re using standard sql, this view is not accessible (it will give you a compatibility error). In order to get accurate numbers, we need to filter to only the top visitkey value. One option is to create a new series of views (which I won’t cover). As an alternative – you can use the following query, which will deduplicate the data to the proper counts. Voila!

Note: this query is actually part of a larger view in which I “UNION ALL” about 25 tables. The consumer of which is PowerBI – but you can use it (or modify) it to your liking. Also note that I have some custom dimensions in here – those can be modified or removed to suit your implementation.

select
‘site name’ as SiteCode,
DATE_TRUNC(PARSE_DATE(“%E4Y%m%d”, date), DAY) AS Date,
REGEXP_REPLACE(hits.page.pagePath,r'(\?.*)’,”) AS PAGE, — Scrubs off the querystring values (if you haven’t already put a filter in GA)
(SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS ArticleID, — Custom Dimension
(SELECT MAX(IF(index=14, value, NULL)) FROM UNNEST(hits.customDimensions)) AS LogIn,  — Custom Dimension
(SELECT MAX(IF(index=5, value, NULL)) FROM UNNEST(hits.customDimensions)) AS PageType,  — Custom Dimension
device.deviceCategory as Mobile_Device_Type,
trafficSource.campaign AS TrackingCode,
trafficSource.source AS ReferringDomain,
trafficSource.medium AS Medium,
COUNT(hits.page.pagePath) AS PageViews — Use INSTEAD of SUM(totals.pageViews)
FROM
(SELECT
ARRAY_AGG(t ORDER BY exportTimeUsec DESC LIMIT 1)[OFFSET(0)].*
FROM `project.dataset.ga_realtime_sessions_20*` AS t
WHERE _TABLE_SUFFIX = FORMAT_DATE(“%y%m%d”, CURRENT_DATE())
GROUP BY t.visitKey), — SubQuery to de-dupe the realtime data
UNNEST(hits) as hits
WHERE
hits.type = “PAGE” — Removes events and such from results
GROUP by
Date,
PAGE,
ArticleID,
Login,
PageType,
Mobile_Device_Type,
TrackingCode,
ReferringDomain,
Medium

Article by David

It seems like this author has no description. Add your discription/bio at user profile or disable this widget in theme customizer if you dont want to use it.

304 Comments


  1. I like what you guys are up too. Such smart work and reporting! Carry on the excellent works guys I have incorporated you guys to my blogroll. I think it’ll improve the value of my website :).

  2. I happen to be writing to make you know of the beneficial experience my wife’s daughter found going through your web site. She came to understand a good number of things, with the inclusion of what it is like to possess a great giving character to get the mediocre ones completely fully understand certain impossible subject matter. You undoubtedly did more than our expected results. Many thanks for displaying those warm and friendly, healthy, explanatory and also easy thoughts on this topic to Jane.

  3. Недорогой отель Владимирский находится рядом с центром Нижнего Новгорода. Ближайшая станция метро Ленинская находится в 640 м от гостиницы, что позволяет попасть в центр города за 10-15 минут. Гостиница располагается в Нижнем Новгороде рядом с ЖД вокзалом.

    http://vladimir-otel.ru – Отель Нижний Новгород

  4. Please let me know if you’re looking for a article writer for your site.You have some really good articles and I feel I would be a good asset.If you ever want to take some of the load off, I’d really like to write somematerial for your blog in exchange for a linkback to mine. Please shoot me an e-mail if interested.Regards!

  5. I simply want to mention I’m newbie to blogging and actually savored your blog. Most likely I’m going to bookmark your blog post . You absolutely come with terrific writings. Appreciate it for sharing your blog site.

  6. We offer you the opportunity to advertise your products and services.
    Good day! Look at an interesting offer for you. I can help you with sending your commercial offers or messages through feedback forms. The advantage of this method is that the messages sent through the feedback forms are included in the white list. This method increases the chance that your message will be read. The same way as you received this message.

    Sending via Feedback Forms to any domain zones of the world. (more than 1000 domain zones.).
    The cost of sending 1 million messages for any domain zone of the world is $ 49 instead of $ 99.
    Domain zone .com – (12 million messages sent) – $399 instead of $699
    All domain zones in Europe- (8 million messages sent) – $ 299 instead of $599
    All sites in the world (25 million messages sent) – $499 instead of $999
    Domain zone .de – (2 million messages sent) – $99 instead of $199
    Domain zone .uk – (1.5 million messages sent) – $69 instead of $139
    Domain zone .nl – (700 000 sent messages) – $39 instead of $79

    Discounts are valid until March 25.

    In the process of sending messages, we do not violate the rules of GDRP.
    This message is created automatically use our contacts for communication.

    Contact us.
    Telegram – @FeedbackFormEU
    Skype – FeedbackForm2019
    Email – FeedbackForm2019@gmail.com

    Hope to hear from you soon.

  7. Hey there! I know this is somewhat off topic but I was wondering if you knew where I could get a captcha plugin for my comment form? I’m using the same blog platform as yours and I’m having trouble finding one? Thanks a lot!

  8. Thanks for the guidelines you have shared here. One more thing I would like to talk about is that laptop memory requirements generally increase along with other improvements in the technological innovation. For instance, whenever new generations of processor chips are introduced to the market, there’s usually a matching increase in the scale demands of both personal computer memory as well as hard drive space. This is because the program operated through these processor chips will inevitably boost in power to take advantage of the new know-how.

  9. Hey there! I just wanted to ask if you ever have any problems with hackers? My last blog (wordpress) was hacked and I ended up losing a few months of hard work due to no backup. Do you have any methods to protect against hackers?

  10. I blog frequently and I seriously appreciate your information. This article has truly peaked my interest. I’m going to take a note of your blog and keep checking for new details about once per week. I subscribed to your RSS feed as well.

  11. Интернет-магазин sbk-torg.ru предлагает купить автомобильные масела, антифроз, тосол, присодки, фильтры, автокосметику, автофильтры, незамерзайку. В наличии на складе в Нижнем Новгороде

    http://sbk-torg.ru – автомасла купить

  12. I just want to tell you that I’m newbie to weblog and actually enjoyed your page. Almost certainly I’m want to bookmark your website . You certainly have beneficial articles and reviews. Many thanks for revealing your blog.

  13. dwkaobpkg,Thanks for ones marvelous posting! I actually enjoyed reading it, you will be a great author.I will always bookmark your blog and will tbctll,come back from now on. I want to encourage that you continue your great writing, have a nice afternoon!

  14. Hi there I am so glad I found your weblog, I really found you by mistake, while I was looking on Bing for something else, Anyhow I am here now and would just like to say many thanks for a tremendous post and a all round interesting blog (I also love the theme/design), I don’t have time to read through it all at the minute but I have saved it and also included your RSS feeds, so when I have time I will be back to read a lot more, Please do keep up the great work.

  15. Hi, i read your blog from time to time and i own a similar one and i was just wondering if you get a lot of spam feedback? If so how do you reduce it, any plugin or anything you can recommend? I get so much lately it’s driving me insane so any assistance is very much appreciated.

  16. My name is Pete and I want to share a proven system with you that makes me money while I sleep! This system allows you to TRY the whole thing for F R E E for a whole 30 days! That’s right, you can finally change your future without giving up any sensitive information in advance! I signed up myself just a while ago and I’m already making a nice profit.

    In short, this is probably the BEST THING THAT EVER HAPPENED TO YOU IF YOU TAKE ACTION NOW!!!

    If you’re interested in knowing more about this system, go to http://globalviralmarketing.com/?ref=qkgWOPkN5RoC1NWh and try it out. Again, it’s FREE!

    You can thank me later

    /Pete

  17. I’m planning to write a literature blog, and I want to get as much readers as possible, even those who don’t like books much, to be able to encourage them to read. Suggestions? No bashing please!.

  18. There are incredibly a great deal of details this way to take into consideration. That’s a great specify start up. I provide thoughts above as general inspiration but clearly you’ll find questions like the one you mention in which the most significant thing will likely be in the honest good faith. I don?t know if best practices have emerged around items like that, but I am sure that your chosen job is clearly identified as a good game. Both youngsters feel the impact of only a moment’s pleasure, through-out their lives.

  19. You can certainly see your skills within the paintings you write. The arena hopes for even more passionate writers such as you who aren’t afraid to say how they believe. Always go after your heart. “What power has law where only money rules.” by Gaius Petronius.

  20. I am requesting my mother. She doesn’t necessarily intend to generate income off them, her objective is to utilize her blog site (when popular) and utilize it as references to potentially assist her obtain a newspaper article. She has a title for one called “Answers to Life’s Troubles”. Where can she upload blog sites and also they become preferred? She published it already on WordPress yet there are 3 million people uploading blog sites hers obtains shed in the mix. Any pointers?.

  21. Hello there,

    Hope you’re doing well.

    I am Raymond , Outreach Manager at a reputed Digital PR agency that helps brands getting featured on top publications and blogs.

    We work with hundreds of brands and agencies, and we think that your website will fit their requirements. Do you accept paid article deals on your blog?

    If yes, please let me know what will be the price for a guest post placement.

    Provide details here: https://forms.gle/ckfCCpChMomRRrJSA

    Looking forward to your details.

    Thanks & Regards,

    Raymond Parker

  22. Want to safely Share audio from any computer to mobile gadget? Check out SHAREit Share&Transfer App! It works on Windows PCs and Android phones, very easy in use and lighweight. Download SHAREit for PC from here: https://download-shareit.com and share files between your friends!

  23. Are you one of the millions of people in the USA that are struggling to make ends meet?

    So was I, until I found the Perfect TEAM that supports each other as if we all are Family.

    You have heard the old saying “No One Left Behind” This is as close as it gets. The only way
    you can not Succeed on our Team is to not put any effort into it or quit.

    I am personally placing new Team members under the members that join my TEAM.

    We have the answer that has helped Thousands and Thousands of people get more take-home pay,

    With One step, in our program, you will take home the monthly membership costs. While at the same time
    securing your financial needs.

    If you can Follow a few EASY Steps, we will show you how to get started on the road to Financial Freedom.

    You will find no affiliate links in my advertising, I work one on one with you. This way I can place you under the newest member. And I will do the same for you.

    You have to take the first step.

    Go here Now!!

    We look forward to working with you.

    Scott Caile .
    954-348-8522
    https://successteamnow.com/myecon

  24. An intriguing discussion is definitely worth comment. I do think that you ought to write more on this issue, it may not be a taboo matter but usually folks don’t discuss such subjects. To the next! Best wishes!!

  25. Hi, fountainanalytics.com

    I’ve been visiting your website a few times and decided to give you some positive feedback because I find it very useful. Well done.

    I was wondering if you as someone with experience of creating a useful website could help me out with my new site by giving some feedback about what I could improve?

    You can find my site by searching for “casino gorilla” in Google (it’s the gorilla themed online casino comparison).

    I would appreciate if you could check it out quickly and tell me what you think.

    casinogorilla.com

    Thank you for help and I wish you a great week!

  26. Pretty nice post. I just stumbled upon your weblog and wished to say that I’ve truly enjoyed browsing your blog posts. In any case I’ll be subscribing on your rss feed and I hope you write once more soon!

  27. You actually make it seem so easy along with your presentation but I find this matter to
    be actually one thing which I believe I might by no means understand.
    It kind of feels too complicated and extremely wide for me.
    I’m taking a look ahead on your next publish, I will attempt to get
    the hang of it!

  28. Normally I don’t read post on blogs, however I wish to say
    that this write-up very compelled me to
    try and do so! Your writing style has been amazed me. Thank you, quite nice post.

  29. I’m amazed, I have to admit. Rarely do I come across a blog
    that’s both equally educative and interesting,
    and let me tell you, you have hit the nail on the head.
    The issue is something which not enough people are speaking
    intelligently about. I’m very happy I stumbled across this in my search
    for something relating to this.

  30. Hello there,

    Hope you are well.

    I’m Simon, Outreach Manager at a leading Digital Marketing Agency. I came across your website and read some of your articles. They are so unique and informative.

    I would be happy to establish a cooperation with you. We would like to promote our client’s services on your website through multiple articles with a do follow link.

    Please let me know whether you accept this kind of advertisement and what will be the best cost for publishing a guest post on your website. As we’re here for a long-term relationship, we would expect the best pricing from you.

    Please provide your details here: https://forms.gle/1HzNQYg46p6pTPmX9

    If you run more potential sites, feel free to submit all your worthy sites.

    Looking forward to your details.

    Thanks & Regards,

    Simon Cooper
    Outreach Executive

  31. Cable companies are sticking to all of us! You’re paying way too much for your cable. Save your money! Pay only $39 a month. NO other fees. Over 5,000 Live HD TV Channels. Including local, Spanish & International Channels. Plus ALL premium movie channels: HBO, Showtime, Cinemax, Starz, & more. All sports channels: NFL ticket, NBA pass, MLB plus more. Every major news channels: MSNBC, CNN, FOX news, etc. Also all Children’s & family networks plus music channels. Watch on any screen device without down loading. NO contacts, activation fees or any other hidden fees. Watch it first for FREE! No credit card needed. Refer 4 others and your service is completely free. Nothing to lose except your high cable bill. Start saving today! Visit website or call 214-529-2088
    http://maxcable4lesscost.com

  32. hey there and thank you for your info – I have definitely picked up
    anything new from right here. I did however expertise a few technical issues
    using this web site, as I experienced to reload the site
    many times previous to I could get it to load correctly.
    I had been wondering if your web host is OK? Not that I’m complaining, but
    slow loading instances times will very frequently affect
    your placement in google and could damage your high-quality score if ads and
    marketing with Adwords. Anyway I am adding this RSS to my e-mail and can look out for much more of your
    respective fascinating content. Make sure you update this again very soon.

  33. Very good blog! Do you have any hints for aspiring writers?

    I’m hoping to start my own site soon but I’m a little lost on everything.
    Would you recommend starting with a free platform like WordPress or go
    for a paid option? There are so many choices out there that I’m totally confused
    .. Any suggestions? Bless you!

  34. If some one wishes to be updated with newest technologies therefore he must be visit this web site and be up to date everyday.

  35. I’m pleased with the way that fountainanalytics.com covers this kind of subject matter. Usually to the point, sometimes polemic, always thoughtful as well as stimulating.

  36. Howdy, i read your blog from time to time and i own a similar one and i was just curious if
    you get a lot of spam remarks? If so how do you protect against it, any
    plugin or anything you can suggest? I get so much lately it’s driving
    me crazy so any help is very much appreciated.

  37. Simply want to say your article is as surprising.
    The clarity in your post is simply spectacular and i
    can assume you are an expert on this subject.
    Fine with your permission allow me to grab your feed to keep up to
    date with forthcoming post. Thanks a million and please carry on the rewarding work.

  38. I have been browsing online more than three hours today, yet I never
    found any interesting article like yours. It’s pretty worth enough for me.
    Personally, if all website owners and bloggers
    made good content as you did, the internet will be much more useful than ever before.

  39. Just wish to share something. I need to submit my blogs to blog websites with numerous individuals reviewing blog sites. That’s what I need most.

  40. We absolutely love your blog and find nearly all of your post’s to be
    exactly what I’m looking for. Does one offer guest writers
    to write content for yourself? I wouldn’t mind writing a post or elaborating on many of the subjects
    you write related to here. Again, awesome site!

  41. Hello there,

    I am Anthony & I’m currently working on an online campaign with a client who would be interested to post an article on your website. The article would discuss topics relevant to your readership and include a natural link to my client’s website.

    If you’re interested in guest post collaboration, please provide your pricing information through the form below or get in touch for more information.

    Provide Your Website Details: https://forms.gle/dZDaMutecFpyehAZ6

    If you run more potential sites, feel free to submit all your worthy sites.

    Looking forward to hearing from you.

    Thanks & Regards

    Anthony Smith

    Outreach Executive

  42. Pingback: buy cialis
  43. Pingback: sildenafil
  44. Pingback: sildenafil 100mg
  45. Pingback: buy cialis online
  46. Pingback: sildenafil 20 mg
  47. Pingback: sildenafil citrate

Leave a Reply

Your email address will not be published. Required fields are marked *