Survey_satisfaction_ratings@fdk - I only see last 30 days data

[nnh47]

I am currently integrating Freshdesk data into Power BI using the Invantive Cloud OData-feed. I am able to access a broad range of data, however I am facing a challenge with the table survey_satisfaction_ratings.

In this table I only see data for the last 30 days: 37 rows. Through Freshdesk API I’m able to see 365 rows.

Can you advise please?

[nnh47]

After an upgrade I can see now 625 rows in the survey_satisfaction_ratings table, however the data is not complete.

I don’t see all the questions, only the default question.

This is an example of the same survey.

First screenshot is through Invantive:

The second using FreshDesk API directly:

Can you please help?

[forums]

Is it stated correctly that you are missing columns and their values, as in this sample:

Column Name	Value
question_47000081303	102
question_47000081304	103
question_47000081305	102

Can you help with what these values mean in the second screenshot?

[nnh47]

Yes, correct, I’m missing the above columns and their values. These are the individual survey questions, like how knowledgeable was the agent, how are you satisfied with the product, etc.

This is the M code I used for working with the API directly:

GetDataSurveys function:
let
  Source = (Per_Page as number, Page as number) => let
  URL = "https://acme.freshdesk.com/api/v2/surveys/satisfaction_ratings/?updated_since=2022-01-01&per_page=" & Number.ToText(Per_Page) & "&page=" & Number.ToText(Page),
  Source = Json.Document(Web.Contents(URL)),
  ConvertedToTable = try Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error) otherwise error "Failed to convert JSON to Table",
  ExpandedColumn1 = if Table.IsEmpty(ConvertedToTable) then error "No data available or wrong structure" else Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"id", "survey_id", "user_id", "agent_id", "feedback", "group_id", "ticket_id", "created_at", "updated_at", "ratings"}, {"id", "survey_id", "user_id", "agent_id", "feedback", "group_id", "ticket_id", "created_at", "updated_at", "ratings"}),
  ExpandedRatings = Table.ExpandRecordColumn(ExpandedColumn1, "ratings", {"default_question", "question_47000081303", "question_47000081304", "question_47000081305"}, {"default_question", "question_47000081303", "question_47000081304", "question_47000081305"})
in
  ExpandedRatings
in
  Source

SurveysSatisfaction_ratings query:

let
  GetAllPages = List.Generate(
        () => [Page = 1, Source = Json.Document(Web.Contents("https://XXXX.freshdesk.com/api/v2/surveys/satisfaction_ratings?created_since=2022-01-19T02:00:00Z" & "&per_page=30" & "&page=" & Number.ToText(1)))],
  each List.Count([Source]) > 0,
  each [Page = [Page] + 1, Source = Json.Document(Web.Contents("https://acme.freshdesk.com/api/v2/surveys/satisfaction_ratings?created_since=2022-01-19T02:00:00Z" & "&per_page=30" & "&page=" & Number.ToText([Page] + 1)))],
  each [Source]
    ),
  ConvertToTable = Table.FromList(GetAllPages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(ConvertToTable, "Column1"),
    ExpandColumn1 = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "survey_id", "user_id", "agent_id", "feedback", "group_id", "ticket_id", "created_at", "updated_at", "ratings"}, {"id", "survey_id", "user_id", "agent_id", "feedback", "group_id", "ticket_id", "created_at", "updated_at", "ratings"}),
  ExpandedRatings = Table.ExpandRecordColumn(ExpandColumn1, "ratings", {"default_question", "question_47000081303", "question_47000081304", "question_47000081305"}, {"default_question", "question_47000081303", "question_47000081304", "question_47000081305"}),
  #"Filtered Rows" = Table.SelectRows(ExpandedRatings, each [id] = 47001547786)
...

[forums]

Thanks for the addition.

An analysis was made. The documentation of the API is somewhat vague, but it seems that a pivoted list of answers is added with the key taken from the column id of survey_questions. Based upon internal documentation it seems there have been some changes over the years on surveys in Freshdesk. The area was never covered since at that time in around 2019 the team was unable to create a working survey.

The following changes have been made:

• Table survey_questions_accepted_ratings renamed to survey_question_accepted_ratings.
• Columns always empty in surveys were removed.
• Column metadata was corrected for survey_questions and survey_question_accepted_ratings, and now all columns contain their value.
• the pivoted answers are not yet changed into columns, but are available as JSON in the new column ratings.

Please add a reply in case processing the new column ratings to separate fields is too hard.

The new functionality will be available on Invantive Cloud somewhere around March 9, 2024, and at the same moment also in the then current 24.0-release for on-premises purposes.

[forums]

The new release has been taken into production on Invantive Cloud.