What is the most efficient & Convenient way to pull Adobe analytics data in to Power BI?

[vinay_potlu]

Hello all, 

Can anyone guide the most efficient & Convenient ways to pull AA data into PBI weekly? What are some of the data discrepancies/variations that we can observe by pulling the data from various sources below? What are the data/size limitations while exporting the data for each of the ways below? 

1) Report Builder 
2) Datawarehouse
3) Adobe Analytics connector in Power BI
4) Exporting directing from the Analytics workspace.

We always observe data variations from Report Builder metrics, Analytics connector metrics & AA workspace metrics. Can anyone assist with why we always have a difference while pulling data weekly or monthly? 

Thanks.

[Krishna_Musku · Accepted solution]

If you want to use AA connector in Power BI, I think you won't be able to get weekly granularity reports. You can find more info in below link:

https://learn.microsoft.com/en-us/power-query/connectors/adobe-analytics

The data variations are known between workspace and report builder because of they both use different reporting engine and to collect, process, and calculate data.

[jeff_bloomer]

@vinay_potlu,
It is actually possible to pull weekly data as long as you're using version 2.0 (Beta) of the Data Connector.  You just have to use WEEK as a Dimension when you pull the data in PowerBI when you create your data flow.   If you do this, you should see much better consistency in your data.

We have been doing this quite a lot in our reporting very successfully.  Hope this helps for you as well!

[Jennifer_Dungan]

While I don't use Power BI, I wanted to address the other part of your question: "What are the data/size limitations while exporting the data for each of the ways below?"

1) Report Builder 

This one dependent on a lot of factors... The pulls are pretty simple.. the way that you can pull data is rather limited, it works well for simple pulls... now, by default, if you were to pull by a Custom Traffic or Custom Conversion dimension, the rows defaults to Top 1-10.. you can change this, but I don't know what the limit actually is... however, since you are dealing with Excel, you need to make sure that you also take Excel's limitations into account.

If you don't mind me asking, what sort of data variations are you noticing? I've only seen minor differences... not enough to really worry about.. but I am wondering about your experience.

2) Datawarehouse

Data Warehouse is good for getting large files, but the data is flattened, and if you are pulling metrics like Visits or Unique Visitors then this is problematic, since you cannot de-duplicate the values... I've had pulls that have more rows than Excel can handle (luckily the files were being used by our DBA who imported the data into Temp tables so we didn't have issues.

3) Adobe Analytics connector in Power BI

Hopefully the others can address this

4) Exporting directing from the Analytics workspace.

I believe you can export up to 50,000 rows of data from Workspace, and I think you can only export one breakdown, so if you have nested values, I think you can only choose one level to export.

There are a few other other options:

1) API 2.0

You can use the Adobe API to extract the broken down data... I am not sure if this is subject to limitations, but you can replicate all your breakdowns:

Here's a trick, enable debugger mode in Workspace:

This will add a new icon to your tables:

When you click on this it will give you options for the data or the sparkline:

Then under that, you will see times for pulls that were made:

When you click on these, it will take you to a page that provides the API calls in multiple formats that you can copy, modify and use (workspace is all built on API 2.0, so this is a great way to learn how to form your own API calls)

2) The other option is the Raw Data Feed.... this will give you every single row of collected data... this is good, but it does require a lot of data processing to ensure it matches what Adobe shows... that is because there are calls that are flagged as "exclude" that you need to pull out... and you will have to import this data into databases where you will need to process the Visitor, Visit, etc data... this is a big undertaking though... and likely not a good option for you right now

[vinay_potlu]

Thanks, @Jennifer_Dungan  , @jeff_bloomer , @pradeep_07 
We use both Report Builder & Datawarehouse options to pull the data on a weekly & monthly basis.  They are also exploring options for using Abobe connectors and workspace options.

1) Report Builder: We observe variations of about 10% to 15% (Unique visitors, Visits). Around 6-8% for Page views for a weekly data pull between the report builder & Analytics workspace.  
Ex: UV from Adobe Workspace: 2000, Report Builder: 2300. 
      Visits from Adobe Workspace: 3000, Report Builder: 3310. 
2) Datawarehouse:  We observe data variations of about 35-40% (Unique visitors, Visits). Around 6-8% for Page views for a weekly data pull between Datawarehouse & Analytics workspace.  
Ex: UV from Adobe workspace: 1000, Datawarehouse:.2400
      Visits from Adobe Workspace: 2000, Report Builder:3200. 

Can you please elaborate on the data is flattened & de-duplicate the values, and how to overcome this problem? Thanks. 

[Jennifer_Dungan]

In both Report Builder and Data Warehouse, once the data is broken out to rows of data, there is no way to de-duplicate that data...

Now, that said, if you need to get de-duplicated totals, you will have to make additional calls that don't break the data out to various dimensions..

For example, I have a report that pulls PVs, Vs, UVs for desktop, mobile, tablet for all the sites in our global suite. Then total for each site, then I have a total for all sites by device type, and overall total.... (I have to do this for "last month", "same month last year", "YTD up to last month", and "YTD last month for the previous year"). I also have to pull totals for "groups of sites)

(note: I use server to identify each site)

I do the following requests for each of the 4 time frames (basically multiple this list by 4):

• Key metrics for desktop, broken down by server
• Key metrics for mobile, broken down by server
• Key metrics for tablet, broken down by server
• Key metrics no device segment, broken down by server
• Key metrics for desktop, Group A
• Key metrics for mobile, Group A
• Key metrics for tablet, Group A
• Key metrics no device segment, Group A
  • create duplicate requests for all "Group A" calls for each group
• Key metrics for desktop, no breakdown (Overall Total)
• Key metrics for mobile, no breakdown (Overall Total)
• Key metrics for tablet, no breakdown (Overall Total)
• Key metrics no device segment, no breakdown (Overall Total)

I made this list from memory, I think it covers my use (or at least gives you a flavour of it)...

Basically, I don't use Excel to add the values together...

PVs would be fine, but UVs and Vs would be inflated, so I have to make additional calls to get those totals at each site level, at site grouping level, and at a total level....

Because it can be sometimes a little harder to get all this data to pull in an look good directly from Report Builder, I usually pull this into a hidden "Raw Data" sheet, then use Excel formulas to pull each value that I need into a pretty, formatted version of the report, and then I also use Excel formulas to calculate Percent Change between the Last Month and This Month Last Year values, and between the YTD and YTD for previous year values for each breakdown....

Essentially, I don't use Excel to add up those metrics and get a total, I create additional requests for the breakdown I need totals for so that I can access de-duplicated values.... 

It's a lot more work, but it's the only way...

Depending on your report, if you need totals based on interactive filtering, that won't even work... but if it's simple, then you will likely have to do something similar to me.

[pradeep_07]

I have used Power BI with Adobe Analytics . Datawarehouse is the best option if you have Azure / Amazon S3bucket /SFTP to store the data and retrieve the data from there. You can automate it too.