3 Crucial Tips for optimizing Google Analytics with Microsoft Power BI

Background

It’s estimated that 55% of all the worlds websites have Google Analytics [GA] installed on them. This number dwarfs the next largest competitor, Adobe. Due to its sheer size, scale, ease of use, and robust API’s – pretty much everyone supports it, including Microsoft PowerBI. PowerBI’s integration with GA is longstanding, pretty solid, and largely dependable. However, there are still potential issues with slow user experience [UX] and data accuracy arising from the nature of the data (high cardinality, long text fields, GA sampling limits). But all is not lost; this article will showcase 3 tips on restoring performance and dramatically increasing accuracy via some easy steps. It is worthy to note that although this article is geared toward the free version of GA, it can be applied to the Premium [360] version and other data sources as well.

So, without further ado – here are three tips on speeding up PowerBI and making your data more accurate:

Tip #1 – Speed up Refresh time and improve your data quality by fracturing your GA query

When loading GA data into PowerBI, the temptation is just to select the entire date range for their property. This is a huge mistake on several points: 1) It reloads all [unchanging] historical data, every time you refresh, 2) If you have a lot of traffic, your data quality will suffer hugely due to the sampling, and 3) The sampling will decrease resolution and prevent you from going granular with the data.

To resolve this, I recommend breaking your GA data load down in to bite-sized chunks of data. Essentially, creating an array of identical query-loads, but each covers a given date range. With PowerQuery in PowerBI you can then stitch together [Append] unlimited numbers of queries into a larger dataset. The overarching process is fairly quick, provided you don’t abuse calculated columns in the output dataset (more on that later). The process of converting from a single GA Query to an array of queries is pretty straightforward, although you will need to move your measures and calculated columns to the new [roll-up] table after implementing.

  1.  Take your existing baseline query and make copies of it, for each period you want to capture. You will need to adjust the date filter for each query. You will need to make the determination on frequency here based on your overall traffic and number of dimensions/metrics in your query. I don’t want to get ‘into the weeds’ here with representative sample percentages and target frequencies here because it varies depending on your data. What I will say is that – select a frequency you feel gives you adequate resolution. Generally, that’s once a month for a site with strong traffic and maybe every 3-6 months for a small site. Lastly, If you have user level data in GA that you need to expose – you will need to remove all sampling and may have to go to daily pulls.load_split
  2. Create a new query using the “append queries as new..” option and load the data once. After that you will need to select the ‘Three or more tables..” option and include all the new fractured tables. This will run (briefly) and you will notice a new query listed. It will have one step with “= Table.Combine({..” syntax in it. Btw: don’t use merge as that will slow down the process as it scrubs every record in insert. Just make sure you’re selecting date ranges (in #1 above) that DON’T OVERLAP. append
  3. After you have performed your initial data load – you wont need to re-fetch the historical data as Its just a waste of time and CPU. So, PowerQuery has included an option to exclude the query in future report refreshes. Go to all your historical queries (e.g. last month, the month before, and so on..), right click, and UNCHECK the “Include in Report Refresh” option. PowerBI will remember the original data, but wont bother asking GA for updates.refresh

NOTE: Some follow-up notes on fragmenting your data: Fragmenting will increase the physical data size. Although PowerBI’s compressed, columnar data store is very quick – the sheer cardinality of the text data (especially dimensions like Page and Source/Medium) can choke the PowerBI/SSAS engine. But don’t fret just yet, we have the solution coming up next…

 

Tip #2 – Simplify your data before its in the Database..

loadingDo your reports take many seconds to refresh upon interacting with them? Are you intimately familiar with the spinning dots? Most users will experience a considerable degradation of performance in PowerBI after laying in all seven dimensions and metrics, calculated columns, and measures. You must first understand that the columnar database in PowerBI likes its data tall and skinny (e.g. lots of rows, but being minimalist on columns) to be as productive as possible. This is in contrast to a traditional SQL DB which prefers the opposite.  It also benefits PowerBI to not have large text fields with unique data. So, how do we address this?

  1. Its inevitable we will need to pull in [Page] or [Page Title] dimensions when querying GA. But part of the problem is all the ‘crap’ in the querystring. We look at this as superfluous junk but PowerBI sees it as something unique. The best way to address this is to scrub these fields down to just their relevant data DURING THE IMPORT and NOT to add additional calculated columns later. If you have campaign or tracking codes in the querystring you need to interrogate: see if there is a way to simplify that at data load time. As you can see by this example below – we have the same page with a boatload of unique querystrings relating to some Chinese bot farm we don’t care about. Bringing this data into PowerBI is a crime and you should try to simplify as much during load. From here, I would split the first column at the ‘?’ and then delete the 2nd half of the split. Since we also have a lot of cardinality in the 6th column, I would develop a filter (find/replace) of the primary offenders (again, Chinese Bot Farms)bot_traffic
  2. Since most PowerBI reports are constantly evolving and changing, routinely audit your tables for unused, calculated columns and remove them. They make the data fat, slow and hamper your load times. I keep a document with all the functions I’ve ever created in case I need to re-add it at some point.
  3. Determine if calculated columns would be better suited as a Measure. I will admit when I first was learning DAX – it was less challenging to use calculated columns to process data. but it slows the system down.

 

Tip #3 – Keep it simple [and fast] with Calculated Tables

Another way to approach this is to use a calculated table, based on your loaded data. You can limit the size (columns, etc) at this point much more effectively. The Calculated table is an independent table based on the original data from one or more tables. The major benefit is that you can SUMMARIZE data here (e.g. get rid of all the useless data you don’t care about for the query). Sure, it will use additional memory – but nothing good in life is free.

calc_table

 

Final Notes

I think you will be very pleased with the outcomes here on your reporting. The important thing is that we want to get all the cutting and prep work off the table before PowerBI has to crunch data. Failure to do this will lead to a reporting experience that will get slower and slower with each passing month.

If you have any questions – feel free to email me or post a comment. Best of luck and I hope this was useful.

Article by David

Your host and an experienced analyst dedicated to evangelizing the quantifiable, data-driven measurement of business with an emphasis on the customer relationship.

199 Comments


  1. My wife and i ended up being now satisfied when Chris managed to round up his investigations through the precious recommendations he made when using the blog. It’s not at all simplistic to just continually be making a gift of information and facts which usually other folks may have been trying to sell. And we discover we’ve got the blog owner to be grateful to for this. The specific explanations you made, the straightforward web site menu, the friendships you can make it easier to create – it is all unbelievable, and it’s leading our son in addition to our family understand the topic is enjoyable, which is wonderfully vital. Many thanks for the whole lot!

  2. I got this web page from my friend who shared with me on the topic of this website and at the moment this time I am visiting this web site and reading very informative articles or reviews here.

  3. you’re really a excellent webmaster. The website loading velocity is incredible. It seems that you’re doing any distinctive trick. Also, The contents are masterwork. you have performed a wonderful activity on this topic!

  4. I’m really impressed with your writing skills as well aas with the layout on your weblog.
    Is this a paid theme or did you customize it yourself? Anyway keep
    up the nkce quality writing, it’s rare to see a great blog like this
    one today.

  5. Thank you a bunch for sharing this with all of us you really recognize what you’re speaking approximately!Bookmarked. Kindly also talk over with my web site =).We will have a link exchange arrangement among us

  6. Thanks a lot for sharing this with all of us you actually know what you’re talking about! Bookmarked. Please also visit my site =). We could have a link exchange arrangement between us!

  7. Thanks for every other wonderful article. The place else could anybody get that type of info in such an ideal manner of writing? I’ve a presentation next week, and I am on the look for such info.

  8. I simply want to tell you that I am new to blogging and site-building and actually loved your page. Most likely I’m planning to bookmark your website . You amazingly have remarkable articles and reviews. Thanks a bunch for sharing with us your web-site.

  9. There are certainly a number of particulars like that to take into consideration. That is a great level to convey up. I supply the ideas above as normal inspiration but clearly there are questions like the one you deliver up where an important thing will likely be working in honest good faith. I don?t know if finest practices have emerged around issues like that, however I am sure that your job is clearly identified as a fair game. Both boys and girls really feel the affect of only a second’s pleasure, for the rest of their lives. Miss Verlene

  10. I want to blog about issues I don’t want associating with my ‘work’ self. How do I choose a blog host that has a good anonymity record, in other words, I don’t want it to be easy for people to match up my IP address or access my personal details.. Also, having set the blog up, how do you encourage high volume visitors?. I’ve only used LJ before and I want to try something a bit different, but anonymity is the biggest issue for me. Would welcome suggestions of good free blog hosts..

  11. I am looking to start my own blog, but I want to make sure it is on a popular site where people will read it. I plan on discussing sports, video games and whatever else is interesting at the time. What are the best/most popular sites to blog on?.

  12. I would like to thank you for the efforts you’ve put in writing this site.
    I am hoping to view the same high-grade content from you later on as well.
    In fact, your creative writing abilities has encouraged me to get
    my own, personal blog now 😉

  13. Hi there, 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 mad so any help is very much
    appreciated.

  14. I keep several blogs & discussion forums and also allow individuals to create their talk about them. I examine frequently and also get rid of any swear words, etc. I additionally ask commentors not to leave any type of disparaging terms on the blogs/forums. Nonetheless, that stated, am i still legitimately in charge of the content on my blog/forums if they write a maligning remark or abusive comments?. Lots of many thanks.

  15. yjewfuigmx Yeezy 350,Thanks a lot for providing us with this recipe of Cranberry Brisket. I’ve been wanting to make this for a long time but I couldn’t find the right recipe. Thanks to your help here, I can now make this dish easily.

  16. magnificent put up, very informative. I’m wondering why the other specialists of this sector don’t understand this. You must proceed your writing. I’m sure, you’ve a great readers’ base already!

  17. Dalio said that he became a capitalist when he was 12 years old, when he earned his first salary by sending newspapers, mowing lawns and helping people with golf clubs, and in the stock market in the 1960s.

  18. I’m really enjoying the design and layout of your blog.
    It’s a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often. Did you
    hire out a designer to create your theme? Exceptional work!

  19. I am aiding some pals get their store on the map and also discovered. They have a web site. One of the ways in my study to maximize a web site is to use a blog site. I am not blog savvy as well as neither are they so need a little aid as to great websites to place there blog site to make sure that they obtain discovered.

  20. My spouse and I stumbled over here different web address
    and thought I might as well check things out. I like what I see so
    now i am following you. Look forward to looking at your web page yet again.

  21. Hey just wanted to give you a brief heads up and let you know a few of the pictures aren’t loading correctly.

    I’m not sure why but I think its a linking issue. I’ve tried it in two different
    web browsers and both show the same results.

  22. This is not a crazy idea in my opinion. We may enter the end of the ten-year bull market, we The volatility seen in the past six months is very different from the past 10 years.” Earlier this week,

  23. This is not a crazy idea in my opinion. We may enter the end of the ten-year bull market, we The volatility seen in the past six months is very different from the past 10 years.” Earlier this week,

  24. I precisely needed to appreciate you yet again. I do not know what I would have carried out in the absence of the actual tricks provided by you concerning this situation. Entirely was a very intimidating crisis for me, nevertheless coming across your expert strategy you dealt with that made me to jump with fulfillment. I am grateful for the information and even hope that you are aware of an amazing job that you are accomplishing instructing many people all through your site. Probably you have never got to know all of us.

  25. Tinker Hatfield created them, Michael Jordan seared them in the general public’s collective memory with a scintillating shot against the Cleveland Cavaliers in the 1989 NBA Playoffs, and in the 30 years since.

  26. I precisely needed to say thanks all over again. I am not sure the things I would have made to happen without the type of ideas discussed by you relating to that theme. It became an absolute terrifying condition for me, however , taking note of the very specialized technique you handled that forced me to weep for gladness. I’m just happier for the information and thus hope you recognize what a great job you are providing training most people through your websites. Most probably you’ve never come across all of us.

  27. Analysts say recovery flight and recovery delivery 737 MAX is expected to take months rather than weeks. Boeing CEO Murenberg said in a statement: “We are temporarily adjusting the 737’s production system to accommodate the temporary suspension of the aircraft, which allows us to prioritize additional resources to focus on software certification. And let the 737 MAX resume flight.”

  28. I wish to convey my appreciation for your kind-heartedness giving support to people who actually need assistance with this one subject matter. Your very own commitment to passing the solution all through came to be particularly practical and has frequently permitted most people just like me to reach their goals. Your amazing invaluable publication implies a whole lot a person like me and additionally to my office workers. Regards; from everyone of us.

  29. I truly wanted to post a remark in order to thank you for all the marvelous concepts you are posting here. My time-consuming internet research has at the end been compensated with good quality details to write about with my guests. I ‘d say that we visitors actually are very much lucky to dwell in a perfect network with very many lovely individuals with interesting hints. I feel somewhat happy to have encountered the website and look forward to so many more entertaining moments reading here. Thank you again for everything.

  30. Can I just say such a relief to find somebody that really knows what theyre dealing with on-line. You actually know how to bring a difficulty to light making it critical. The best way to really need to look at this and understand why side with the story. I cant think youre less well-liked when you certainly hold the gift.

  31. Hello, i think that i saw you visited my web site so i came to
    “return the favor”.I’m trying to find things to improve my website!I suppose
    its ok to use a few of your ideas!!

  32. I’ve been exploring for a bit for any high quality articles or blog posts on this sort of area . Exploring in Yahoo I at last stumbled upon this site. Reading this info So i’m satisfied to convey that I’ve an incredibly good uncanny feeling I came upon just what I needed. I most surely will make sure to don’t overlook this website and give it a look regularly.

  33. Hello, i think that i saw you visited my blog so i came to
    “return the favor”.I am trying to find things to improve my website!I suppose its ok to use some
    of your ideas!!

  34. I simply needed to thank you so much all over again. I am not sure what I might have done in the absence of the type of suggestions discussed by you directly on this question. It had been an absolute depressing crisis in my opinion, but noticing your expert approach you handled the issue made me to weep with delight. I’m thankful for the support and pray you realize what an amazing job that you’re carrying out instructing many people via your webblog. Most likely you haven’t met all of us.

  35. Everyone loves what you guys tend to be up too. This type of clever work and coverage!
    Keep up the great works guys I’ve you guys to
    blogroll.

  36. I’d have to examine with you here. Which isn’t one thing I often do! I take pleasure in studying a submit that can make folks think. Additionally, thanks for permitting me to comment!

  37. And Lillard did just that Tuesday night, scoring 50 points on 17-of-33 shooting (10-of-18 on threes) and hitting a series-clinching, 37-foot 3-pointer with no time remaining to beat the Thunder 118-115 and eliminate them in five games.

  38. I as well as my pals were found to be reviewing the great techniques on your web page and so suddenly came up with a horrible suspicion I never thanked the blog owner for those strategies. All of the young boys had been absolutely warmed to study them and have in effect in reality been taking pleasure in them. We appreciate you indeed being indeed considerate as well as for considering some superb things millions of individuals are really eager to know about. Our honest regret for not saying thanks to earlier.

  39. Taylor Swift is back with a new era in full force. In a pastel paradise that’s perfect for spring and highlights the glowing positivity from her latest release, the queen is here to take over once again.

  40. Unpredictability is essential in comedy, and the best part of Horrible Bosses is how it can remain one step ahead of you while still giving plenty to laugh about.

  41. My wife and i ended up being thrilled when Peter managed to do his investigations from the ideas he discovered in your web pages. It is now and again perplexing to just always be offering helpful hints that other people could have been selling. So we remember we now have the website owner to appreciate for that. The specific illustrations you’ve made, the straightforward site navigation, the relationships you assist to create – it’s many impressive, and it’s really aiding our son in addition to the family feel that the subject is excellent, which is certainly extraordinarily pressing. Thank you for everything!

  42. My spouse and i were absolutely ecstatic Jordan managed to conclude his basic research by way of the ideas he obtained when using the blog. It is now and again perplexing to simply find yourself handing out secrets and techniques which often many others might have been selling. We take into account we now have the website owner to be grateful to because of that. The main illustrations you made, the simple web site navigation, the relationships your site give support to instill – it’s got many overwhelming, and it’s assisting our son in addition to our family consider that that theme is awesome, and that is extraordinarily important. Thank you for all!

  43. A dog’s face can very easily give away their guilt. It doesn’t help when they are standing directly in front of the mess they just created. But admit it, you stay mad for about 10 seconds and then you have to laugh at the situation standing before you.

  44. I want to express some appreciation to the writer just for rescuing me from such a circumstance. Just after scouting through the the web and seeing opinions that were not helpful, I was thinking my life was over. Living minus the approaches to the problems you have resolved by way of your blog post is a serious case, and the kind that would have badly affected my entire career if I hadn’t come across your web site. Your actual training and kindness in handling a lot of stuff was precious. I’m not sure what I would’ve done if I hadn’t encountered such a step like this. I’m able to at this time relish my future. Thanks very much for your expert and sensible help. I will not be reluctant to suggest your web page to anyone who needs to have care about this subject matter.

  45. This site definitely has all the information and facts I needed concerning this subject and didn’t know who to ask.

  46. What’s Taking place i’m new to this, I stumbled upon this I’ve discovered It absolutely helpful and it has aided me out loads.

    I am hoping to give a contribution & help other customers like its helped me.
    Great job.

  47. 5/11/2019 In my view, fountainanalytics.com does a great job of dealing with subject matter of this kind. Even if frequently intentionally controversial, the posts are in the main well-written and stimulating.

  48. Thank you for the auspicious writeup. It in reality was a entertainment account it.

    Look advanced to more delivered agreeable from you! By the
    way, how can we keep in touch?

  49. I’m not certain the place you’re getting your info, however good topic.
    I must spend a while studying much more or understanding more.
    Thank you for magnificent info I used to be searching for this information for my mission.

  50. Infotogel.live dan Togelmbah.com adalah situs yang berisikan prediksi togel jitu dan akurat serta memberikan data hasil keluar togel 4D hari ini secara cepat dan terupdate. prediksi pengeluaran hk sgp sdy

  51. You actually make it seem so easy along with your presentation but
    I in finding this matter to be actually something which I think I’d never understand.
    It kind of feels too complicated and extremely vast for me.

    I’m taking a look forward on your next post, I will try to get the hold of it!

  52. I wish to show thanks to you just for bailing me out of this particular issue. Right after researching through the internet and obtaining ways that were not productive, I figured my entire life was over. Existing without the presence of strategies to the problems you’ve solved by means of this article is a serious case, and those which may have badly damaged my career if I had not come across your web blog. Your good natural talent and kindness in handling all things was very helpful. I am not sure what I would have done if I had not encountered such a solution like this. I’m able to at this moment look forward to my future. Thank you so much for your impressive and sensible guide. I will not hesitate to propose your web blog to anybody who requires support on this subject.

  53. I am transforming organizing companies and would love to move my WordPress Blogs over. Does any individual recognize of an easy way to achieve this?.

  54. Nice blog here! Also your web site loads up very fast!
    What host are you using? Can I get your affiliate link to your host?
    I wish my site loaded up as fast as yours lol

  55. Howdy! Someone in my Myspace group shared this website with us so I came to check it out.
    I’m definitely loving the information. I’m bookmarking
    and will be tweeting this to my followers! Outstanding blog and wonderful style and design.

  56. Hello would you mind stating which blog platform you’re using?

    I’m planning to start my own blog in the near future but I’m
    having a difficult time deciding between BlogEngine/Wordpress/B2evolution and Drupal.
    The reason I ask is because your design seems different then most blogs and I’m looking for something unique.

    P.S Apologies for being off-topic but I had to ask!

  57. Hello to every body, it’s my first go to see of this
    weblog; this webpage contains remarkable
    and genuinely fine information in support of visitors.

  58. I wish to show my appreciation for your generosity supporting those people who must have guidance on that issue. Your real dedication to passing the solution around had become exceptionally useful and has regularly helped people just like me to attain their goals. The useful publication entails so much a person like me and extremely more to my mates. Many thanks; from everyone of us.

  59. I precisely had to say thanks yet again. I’m not certain what I might have undertaken in the absence of the entire pointers contributed by you over such a area. It seemed to be a horrifying problem in my circumstances, nevertheless taking note of the very skilled form you dealt with it made me to weep for contentment. I am grateful for this information and then wish you recognize what a great job you are always getting into educating men and women with the aid of your webblog. I am sure you have never got to know all of us.

  60. I have to thank you for the efforts you have put in penning this site.

    I’m hoping to view the same high-grade content by you later on as well.

    In truth, your creative writing abilities has encouraged me to get my own, personal website now 😉

  61. Hi there! Someone in my Myspace group shared this website
    with us so I came to take a look. I’m definitely enjoying the information. I’m book-marking and will be
    tweeting this to my followers! Terrific blog and outstanding
    style and design.

  62. Do you have a spam problem on this site; I also am a blogger, and
    I was wanting to know your situation; many of us have developed some nice procedures and
    we are looking to trade methods with other folks, why not shoot me an email if
    interested.

  63. It’s the best time to make a few plans for the future
    and it is time to be happy. I have read this publish and
    if I may just I wish to counsel you some interesting things or tips.

    Perhaps you could write next articles relating to this
    article. I wish to read even more issues about it!

  64. Please let me know if you’re looking for a article writer for your blog.
    You have some really good posts and I feel I would be a
    good asset. If you ever want to take some of the load off, I’d love to
    write some content for your blog in exchange for a link back to mine.
    Please shoot me an e-mail if interested. Kudos!

  65. I see you don’t monetize fountainanalytics.com, don’t waste your traffic, you can earn extra cash every month with
    new monetization method. This is the best adsense alternative for
    any type of website (they approve all sites), for more details simply search in gooogle: murgrabia’s tools

  66. Hmm it seems like your blog ate my first comment (it was super long) so I guess I’ll
    just sum it up what I had written and say, I’m thoroughly enjoying
    your blog. I as well am an aspiring blog writer but I’m still new to everything.
    Do you have any points for novice blog writers?

    I’d really appreciate it.

  67. You could definitely see your expertise within the article you write.

    The world hopes for more passionate writers such as you who aren’t
    afraid to say how they believe. At all times follow your heart.

  68. I used to be recommended this blog by means of my cousin. I’m now not positive whether or not
    this post is written through him as no one else know such certain about my difficulty.
    You’re amazing! Thank you!

Leave a Reply

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