Solved

How can i calculate avg ratings from incremental avg ratings returned by API?

  • 3 October 2023
  • 8 replies
  • 120 views
E
Rank
Badge

Hello to everyone,

I am facing an issue while try to calculate the average ratings of an app using the following fields (returned by the API calls):

  • one_star_incremental    
  • two_star_incremental    
  • three_star_incremental    
  • four_star_incremental  
  • five_star_incremental  
  • total_count_incremental

In order to calculate the average ratings of an app for period X, i would use the following formula (for all the record in that period):


sum(one_star_incremental+ 2* two_star_incremental+ 3* three_star_incremental+4*four_star_incremental  + 5*five_star_incremental) / sum(total_count_incremental)

This formula returns wrong data compared to the dashboard I created on data.ai portal. 

Can someone help me?

Thank you!

icon

Best answer by Alex Williams 18 October 2023, 21:57

View original

8 replies

Alex Williams
Userlevel 5
Badge +5

Hi @edoardo ,

Apologies for the delayed response; Our team was looking into this.

Great question. data.ai has the App Store endpoint API for retrieving average values of ratings for a period of time. I am including the link here for reference:

https://helpcenter.data.ai/community/s/article/API-Definitions-App-Store-API-2-0

The output of this API should cater to all the values you're requesting.

 

Two calls are required to generate a report:

Expected Response:
"one_star_incremental": 3,
                    "two_star_incremental": 4,
                    "three_star_incremental": 8,
                    "four_star_incremental": 23,
                    "five_star_incremental": 62,
                    "total_count_incremental": 100,
                    "average_star_incremental": 4.37,
                    "favorable_ratings_percentage_incremental": 0.85,
                    "neutral_ratings_percentage_incremental": 0.08,
                    "critical_ratings_percentage_incremental": 0.07

 

Let us know if this help!

Connecting the people who level up the digital world.
E
Rank
Badge

Thank you Alex, I appreciate your response! However, my issue persists because the solution provided returns the average_star_incremental rating with daily granularity for just one day, as indeed indicated in the parameters.

My goal is to extract the average_star_incremental rating for a specified period (more than one day), defined by start_time and end_time parameters, without applying any granularity.

I ask you this since I tried to calculate average_star_incremental rating from API data with daily granularity (as the weighted average of fields (one,two,three,four,five)_incremental) but it returned incorrect data for Android Ratings.

I’ll give you an example if it could help understand my issue. I retrieved data from API using request below (Ufficio Postale App, period 1-4th October 2023):

https://api.data.ai/v2.0/portfolio/app-store?product_id=20600001823031%2C781421802&granularity=daily&start_date=2023-10-01&end_date=2023-10-04&countries=IT&devices=ios-all%2Candroid-all&bundles=all_supported
 

“sum weighted star / total count incr” is the weighted average of the ratings for each device:

 

Ratings on data.ai for the same time period: 

 

This question is related to another one in the community, I’m sorry if I opened almost similar questions but I took a bit before seeing the current one in the community.

Thank you again for your help!

Alex Williams
Userlevel 5
Badge +5

Thanks for the response, @edoardo. I’m going to connect with our team and try to get back to you ASAP.

Connecting the people who level up the digital world.
Alex Williams
Userlevel 5
Badge +5

Hi @edoardo! Thanks for your patience here! Our team looked into your response and has replied with this:

 

“We can average the daily cumulative ratings by AVG=SUM(average_star_incremental)/no of days in the time period.

An important note here would be to make sure that for weekly average, our start date falls on a Sunday and End date falls on the following Saturday, as shown in this screenshot:


For the following product link: https://www.data.ai/apps/ios/app/389801252/ratings-over-time?chart_total_ratings_c[…]_company_id_change)&gp-country-ratings-toast.country-ratings=!t
and following API Link: https://api.data.ai/v2.0/portfolio/app-store?product_id=389801252&granularity=dail[…]e=2023-09-02&countries=US&devices=ios-all&bundles=all_supported
We can see that:
Aug 27 = 4.71
Aug 28 = 4.79
Aug 29 = 4.77
Aug 30 = 4.74
Aug 31 = 4.63
Sept 01 = 4.73
Sept 02 = 4.72
So this is a week, and the average rating for this week is 4.71+4.79+4.77+4.74+0+4.63+4.73+4.72 / 7
= 4.72
And this is also the average rating as seen in the Product Page.”

 

Let us know if this helps or if you have any other questions. Thank you!

Connecting the people who level up the digital world.
E
Rank
Badge

Hi Alex,

thank you for your reply but this still doesn’t answer my question unfortunately.

I don’t know if I wasn’t clear enough, since my question was about Android ratings and the reply from your team is about iOS ratings.

From your example, I tried to apply the formula your team suggested but I still get wrong results.

I used this api link:https://api.data.ai/v2.0/portfolio/app-store?product_id=20600000290583&start_date=2023-08-27&end_date=2023-09-02&granularity=daily&countries=US&devices=android-all&bundles=all_supported

The api returned the following average star incremental ratings for each day:

4.66
4.79
4.88
4.96
4.57
4.65
4.94

The mean of this ratings is 4.78 which is different from the average ratings displayed on this kpi of the dashboard:

 

and also this value on the table of the same page that should correspond to the average ratings for the selected period (week 27/08-02/09):

 

Thank you for your help.

Edoardo

Alex Williams
Userlevel 5
Badge +5

Sorry about that, @edoardo! I’ll sync back with our team and respond as soon as I have another answer.

Connecting the people who level up the digital world.
Alex Williams
Userlevel 5
Badge +5

Hey @edoardo - Got some more information from our team. Let me know if this answers your question:

 

For calculating the average rating for the week of 27 Aug to 2nd Sep we have used the values below screenshot and kept them in the formula for the average calculation formula: 

sum(star_rating*star_count_incremental)/sum(Total_star_count_incremental).
 

Note: Negative and 0-star ratings we don’t take in the calculation.

To break this down with the example: There are Incremental Total Ratings this week(27-2) as follows:  44300 (2 stars), 72638 (3 stars), 132892(4 stars), 1058953( 5 stars). We will not use the 1-star rating because it is negative. In this case, the incremental Average Ratings for the week is (2 * 44300) + (3 * 72638) + (4 * 132892) + (5 * 1058953) / (44300+72638+132892+1058953) = 4.685915847 and then when rounded down, becomes 4.69.That is what we have received in our weekly Incremental average rating.

API link : https://api.data.ai/v2.0/portfolio/app-store?product_id=20600000290583&start_date=[…]ty=daily&countries=US&devices=android-all&bundles=all_supported

Product link : https://www.data.ai/apps/google-play/app/20600000290583/ratings-over-time?chart_to[…]_company_id_change)&gp-country-ratings-toast.country-ratings=!t

Connecting the people who level up the digital world.
E
Rank
Badge

Hi Alex! thank you again, I have no further questions! I really appreciated your help.

Edoardo

Reply


Terms of serviceCookie settings