Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
This issue is really causing me a headache.
I'm pulling data from Google Analytics into my Power BI report. I have stripped this data down using:
Date
Sessions (I can strip this off, and still get same results)
Google Ads: Campaign
Revenue
Google Ads Campaign ID( I can strip this off, get same results)
The goal is to see campaign revenue by day, so I can filter specific campaigns with the later goal of linking these to other tables from my dataset, but for now, the problem lies in the initial data.
Here's my problem:
I select a specific date to check the data, let's say 5/21
When I go to Google Analytics, I look at Campaign X, and let's say it generated $2,100 in revenue on this day
When I go to my Power Query and filter 5/21, it agrees and says we generated $2,100 with this campaign
When I Load the query into my report, and I go to the Data Table View and filter 5/21 and look at campaign X, it tells me campaign X generated $840 (it's about 40% less than the actual number).
I have not yet made any data relationships, so it is a standalone table that I have not added anything to (no columns, measure, etc).
I had this happen once before to me, and the fix was that I had data from 2012 onward, and when I filted my power query to show only data from 2017 onward, the query and the data table showed the same data. I did that same thing for this query, but it did not fix the discrepancy.
Any help would be greatly appreciated! Thank you!
I'm continuing to have this issue with Google Analytics data. I'm looking now at sessions:
I can pull in my GA data, and look at:
1. Date
2. Default Channel Grouping
3. Transactions (# of transactions)
4. Sessions
and my data in my power query and my data table are correct
I then add:
5. Device Category
And now, my power query data is correct, but my data table/report data is wrong.
Can anyone else offer some help/suggestions?
Thanks!
Hi @Hazenm
Could you share screens here?
screens in Power Query, Data view?
Along with the code in Advanced editor?
Best Regards
Maggie
Yep! Sorry I should have done that to start.
I was starting to post screen shots when I realized something, and maybe this is the key to figuring this out - when I filter the power query for a specific date, it gives me the right results, which I knew before. But I then saw that if I applied that filter to my dataset, when I go into the data table view, the data is correct. Again, removing the filter at the power query level, and applying, then filtering at the data table level, the data table is incorrect.
Here is my advanced editor script:
----
let
Source = GoogleAnalytics.Accounts(),
#"3XXXX" = Source{[Id="3XXXX"]}[Data],
#"UA-3XXXX-1" = #"3XXXX"{[Id="UA-3XXXX-1"]}[Data],
#"4XXX" = #"UA-3XXXX-1"{[Id="4XXX"]}[Data],
#"Added Items" = Cube.Transform(#"4XXX",
{
{Cube.AddAndExpandDimensionColumn, "ga:adwordsCampaignID", {"ga:adwordsCampaignID"}, {"Google Ads Campaign ID"}},
{Cube.AddAndExpandDimensionColumn, "ga:date", {"ga:date"}, {"Date"}},
{Cube.AddAndExpandDimensionColumn, "ga:campaign", {"ga:campaign"}, {"Google Ads: Campaign"}},
{Cube.AddMeasureColumn, "Revenue", "ga:transactionRevenue"},
{Cube.AddMeasureColumn, "Sessions", "ga:sessions"}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each [Date] > #date(2017, 10, 30)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Date] = #date(2019, 5, 21)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Revenue", Order.Descending}})
in
#"Sorted Rows"
---
This is obviously with that filter on the power query level
First image is power query, second image is my Google Analytics view, Third image is the Data Table. So GA and Power query correct, Data table incorrect.
Hi @Hazenm I know this reply is very late, but were you able to find a solution? I used the GA native connector but had some issues with my data sometimes. As a workardoun, maybe you can try to test your connection with a 3rd party connector. I've tried windsor.ai, supemetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the GA connector in the data sources list:
After that, just grant access to your GA account using your credentials, then on preview and destination page you will see a preview of your GA fields:
There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |