Data partially matched retrieving blank values

[a_yatsenko]

Greetings! I have just started to investigate into capabilities of Power BI, but have encountered an issue that hampers our use of the application. I would appreciate any assistance!

I am trying to match 2 tables:

- Google Analytics export, of lets say Product data, with unique identifier

- Sale data per platform which contains that unique identifier (clickout_id) which is used as a foreign key to match two tables.

After cleaning the data from possible dublicates I am able to match two subsets of data with Many to One relationship.

So, this step works just fine. Consider the task would be to tell which channel brings more money, so I'd build a table that aggregates Medium and adds Commission_eur. Crossing this two values will retrieve this table - http://prntscr.com/bkdpmp, however, a huge amount of data does not seem to match and is leftover. The data I exported was for the same time range of relatively short time period, and it should match.

I would appeciate your help!

[v-qiuyu-msft]

Hi @a_yatsenko,

In your scenario, please check if you have added any filter condition on Visual Filters, Page Filters or Report Filters. Besides, I have used some sample dataset but not reproduced the issue. Please see the attached .PBIX file.

It would be better if you could share the sample report which can indicator the issue. So that we can analyze the issue directly and accurately.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[a_yatsenko]

@v-qiuyu-msft Thanks for spending time with developing that test case. I guess the problem lies in the data that is extracted from Analytics, but that's a different issue. For testing purposes, I tried to replicate the same use case as I described above, but in Excel

1) Extract all the data with primary key (clickout) for a particular period of time from GA manually (export CSV function)

2) Add sales data that contains the same primary key (clickout)

3) match the values with VLOOKUP

4) The result will deliver blank values, as in my use case, some clicks could have occured in previous month, but the purchase took place in May for instance. Thus, I won't be able to match the data with 100% accuracy.

But there is another issue I spotted:

1) Once extract data from GA API in preview I edit and filter query by date to select data for a specific time only - (http://prntscr.com/bmlt8h)

2) But when I want to make sure that the clickout from preview exists in that data subset that I extracted manually, I cannot find any record in my Excel table - http://prntscr.com/bmlu4s

Is there any limitation to Google Core API?

[Vicky_Song · Accepted solution]

@a_yatsenko, looks like that your issue is more related to data which is exported from Google Analytics.

1). You said that the blank values is caused by clicks happen time is different from purchase time. Do you get any other column or table with which you get build up correct relationships between these two tables?

2). For your second issue, please be sure that you're checking data in the same time range. As I saw that the 1st image shows data in 2014 while in 2015 in the 2rd image.

3). Additionally, I think you can also considering consult GA for help. Go here.

[a_yatsenko]

@Vicky_Song thanks for following-up

1) Indeed, click time and purchase time different of course, but I would not foresee such a great dispersion

2) I guess that the problem lies in GA API, as for one of the websites I could extract data only for 2015, not 2016, thus the values could not be matched.

I guess I won't relly on Analytics integration then, as in my case it imposes some limitations. The best option would be to build a custom app that will store traffic data and then cross it with the sales data.

Thanks again!