Deep Dive: Click-Through PowerApps Analytics

[ThatAPIGuy]

Introduction

This blog post will walk through a simple approach to create your own click-through analytics. Extend your reporting options to include statistics which will raise visibility into how your app consumers navigate your applications, enabling you to use data to validate assumptions on app usage and design.

I have attached the MSAPP files and the datasource sample(excel spreadsheet) so you can play around with the app to understand it better and then implement the same approach in your apps.

Workflow overview -

To explore the app, follow these steps -

Step 1: Install the app from the MSAPP file that I have attached below.

Step 2: Save the excel spreadsheet "AnalyticsDemo.xlsx" in your OneDrive and add it as a data source in the app using the OneDrive connector.

Step 3: After saving and publishing the app, open the app and click on some icons and navigate around the app. Check whether the data gets collected in the Excel Spreadsheet. (Note: Sometimes it may take a few seconds before the data gets updated so you might have to close and open the spreadsheet once)

Step 4: Use the OneDrive excel spreadsheet as a data source in a PowerBI report and create different graphs/statistics based on what you would like to measure. Below is an example to get you started -

Capturing Interaction points-

The main functions that I have used to capture the user interaction are the Patch and Collect functions. 

To start with, you would probably like to collect some basic user information before you capture any further interaction points of that user. To do this, I used the below formula in the OnStart property of the app- 

Collect(AnalyticsDemo,{UserName:Office365Users.MyProfile().DisplayName,UserEmail:Office365Users.MyProfile().Mail,LoginDate:Today(),LoginTime:Text( Now(), "[$-en-US]hh:mm:ss" ),Latitude:Location.Latitude,Longitude:Location.Longitude})

This will create a new record in the Excel data source and because we are using the OnStart property, the basic user information will get collected only once, i.e., when the app is opened by the user. For capturing user details, I have used the Office365users connector.

For any further interaction points / clicks , I used the Patch function to update data in the record that was created above. Below is an example of the Patch function used in the OnVisible property of the Products screen -  

Patch(AnalyticsDemo,Last(AnalyticsDemo),{ProductScreen:"Y"})

where 

• AnalyticsDemo is the excel data source
• Last(AnalyticsDemo) is the last record created in the data source ( which will be the one that was created on the OnStart property)
• ProductScreen: "Y" updates the ProductScreen column of the data source, which tells you that the Products screen was opened by the user.

This way you can capture any interaction point by using the Patch function as mentioned above in either the OnSelect property of different controls or OnVisible property of different screens and updating a column field in the data source accordingly.

Main takeways from implementing Custom Click-through Analytics - 

• Track business/team goals and app usage
• Identify potential app capabilities that need to be modified/upgraded
• Generate reports to emphasize the impact of PowerApps (esp. for your organization's leadership)

For more details, have a look at this webinar where I and @Audrie-MSFT talk about it in detail -

Thanks for reading!

Vivek

CustomAnalyticsDemo_20180418122804.zip

[Audrie-MSFT]

OUTSTANDING! True Champion value!

[DavesTechTips]

Excellent thank you @ThatAPIGuy, great article. We will definitely be using this!

[ThatAPIGuy]

@DavesTechTips glad to hear that!

[EricC]

Hi @ThatAPIGuy , 
This is a really great use of the patch function and I have implemented it into my app. Thank you so much for sharing this, it is really useful!

One question I have for you is this: When a user visits my app I collect some of their selections they select in drop downs. I can see there being an issue when they make their first round of selections, then do not close the app and make more selections a second, third time etc... I am unable to capture that data currently. 

Would you be able to explain a way that on these specific screens they may view more than once and make different selections how to make it start a new row in the excel sheet, rather than them needing to close the app(thus ending their session) and relaunching app to create a new row.

Thanks so much!

[ThatAPIGuy]

@EricC It's great to know that you find this useful and have implemented it in your app.

Regarding your question, you want to create a new row each time a user makes a new drop-down selection. You can add something like this to the OnChange property of the Dropwdown control -

Collect(AnalyticsExcelSheetName, { Selection:DropwdownName.Selected.Value,( other fields you want to collect)})

This way every time a new selection is made a new row will be created. However, any further information that you capture will be collected in this row instead of the first one. (as this second row will now be the record returned by the Last() function)

If you want to collect all the other info in the first row only and use the subsequent rows to collect only the dropwdown selection value and some related fields, what you can do is have a column say DropwdownSelectionNumber and patch the value 1 for first selection, 2 for second selection and so on and then use a Filter formula to acces the row with the 1st selection and then patch info to that row.

Hope this makes sense. Let me know if you have any questions.

[EricC]

@ThatAPIGuy

Exactly what I was looking for, thank you for your help!

One additional question for you, the write back to the excel workbook I am using via my personal onedrive for business account is working for me and is shared with everyone on my team, but when they use the app they get the below error and no data is written to the excel workbook even though they have edit rights. Any ideas what I might be doing wrong?

He can open up the workbook in onedrive and make changes no problem.

Also, he does get the same issue on your app/workbook that is also stored in onedrive for business and he has edit rights.

I don't have the excel workbook open or in edit mode when they are using it either, not sure what is going on.

[ThatAPIGuy]

@EricC

I am not sure what could be the reason. Can you share one of the Patch statements you are using in the app?

Also, I used excel for the demo so that you could test it out, however, I would recommend using Sharepoint list (would definitely work better than excel sheets) 

[EricC]

@ThatAPIGuy

I will try sharepoint as you suggested, I know you mentioned that in the webinar but wanted to see if I could get it working in excel first. Thank you.

And sure one such patch statement is : 

Patch(ELAnalyticsData,Last(ELAnalyticsData),{LetterType:LetterTypeDD.Selected.LetterType},{Office:OfficeDD.Selected.OfficeCode},{ClientResp:ClientRespCB.Selected.StaffName})

[EricC]

Just in case anyone else has the above issue like me. You need to share the parent level folder of the excel workbook as well as the workbook itself.

[ThatAPIGuy]

@EricC

Thanks for sharing!

[Anonymous]

Hi @vivekb , 

Thanks for sharing this Analytics Demo.  Our team used it successfully linking PowerApps to a SharedPoint list.  Initially it tracked perfectly.  But just recently stopped connecting. 

Do you have any suggestions on what the issue might be.  

Here are our collect & patch statements: 

Collect(Dm_Analytics, {UserName:User().FullName},{UserEmail:User().Email},{LoginDate:Today()},{LoginTime:Text(Now())} )


Patch(Dm_Analytics,Last(Dm_Analytics),{DetailScreen1:"Y"})

[ThatAPIGuy]

@Anonymous

Great to hear that you culd implement it.

Your code seems correct and I don't understand the reason behind it working it initially and then not. It's basically a sharepoint list data source that you are updating. The only troubleshooitng you could try is creating a separate sharpeoint list with the same permissions and try patching/collecting records in it. See if that works or not. If not, there could be some permissions error.

Let me know if you have more questions.

[bopra]

Thanks for sharing!

How would you do to implement Audit Trail eg. add User Action(edit, new, delete, print), OldValue and NewValue in a separate "Audit Trail" table? Something like this:

ID | DATETIME | USERNAME | FORMNAME | ACTION | FIELDNAME | OLDVALUE | NEWVALUE |

Old values must not be overwritten and I am kind of a stuck there because "patch" overwrites old values 

[Anonymous]

 @EricC could you succeffull your problem?? i can the same problem.

[CP153319]

I added this code to one of my existing apps and when one of my test users launched it he was prompted to give permisson to the OneDrive connector and then the app failed. It turns out that you must share the data source where you are keeping all this "analytical" data so keep that in mind.

You could use a SharePoint list but then I believe an end user could find that list (they have to have permission to access it) if they are knowledgable about SharePoint.

If you use OneDrive to host the Excel file which holds the data then any user you share it with who looks you up on Delve will be able to see and access the Excel file as well. Is that correct? 

[Anonymous]

@CP153319I tried giving access to the excel file onedrive, the problem is that I have about 200 users and I have to give them access one by one as there is no way to share massively onedrive, plus I have to remove the access warning message.

[CP153319]

@Anonymous - I have thousands of potentional users for one of my apps so I can't use this particular method. I wonder if there is a way to collect this usage data and store it without having the end user involved in the permissions. There is probably some method you could use with PowerApps and Flow where you could get the data, store it somewhere and then automatically move it somewhere else 

[Anonymous]

Great integration @ThatAPIGuy! Thanks for the demo.
I agree with @CP153319, these permission settings really aren't optimal yet with PowerApps and Sharepoint. There should be a functionality which allows the app to write data (as a user sort of) somewhere without the actual user having access to that database. @Audrie-MSFT any news on that? It will never be approved by Leadership otherwise.

[Audrie-MSFT]

@Anonymous You are correct, there is currently no easy way to impersonate another users permissions via PowerApps at this time. However, if you can trigger a Flow at the list level, then individual actions from within the workflow can run under a separate user account - other than the creator/modifier of the record.

Thank you,

Audrie

[CP153319]

@Audrie-MSFT- Wondering if you can answer one of my original questions (posted above... a while back):

Since you have to share the OneDrive folder with anyone you are collecting usage data from would they be able to see the actual Excel file (which you must also share with them) that collects the data if they look you up on Delve? 

[Audrie-MSFT]

@CP153319 Yes they would.

[CP153319]

So that's a bit of a "game-changer"... When you realize that using PowerApps with a file in OneDrive or SharePoint or a SharePoint list means that your end users can access the files or list and just do whatever they want with the data. It's hilarious when you consider that many of the demos of PowerApps show how you can restrict data access through the PowerApp depending on the user, or prevent editing certain fields - Yet they don't point out that the user can just find the file in Delve and delete it, or do whatever they want with it. LOL

What "back end" should "Citizen Developers" be using when we want to make a "real app" with secure data? Azure SQL?