Hi @Nick_Pyle !
Welcome to the Hitchhikers Community! This is a great additional use case for pulling data into Power BI 
This is possible but will take some tweaks.
First, add the Reviews Invitations endpoint permission to your existing Power BI app - Step 1 in the guide.
This query is going to be quite different from the other Yext analytics endpoints. This Yext Reviews endpoint will return all of the metrics available with the generic call, you dont have to sepcify every metric and dimension you want like you do for the other queries.
A few caveats:
-
The date will be returned as an epoch timestamp, I’m assuming you can transform this in PBI.
-
In order to get a sum of requests sent by location ID you will have to sum this in PBI as the API returns them all as individual objects. If you wanted, you could filter to 1 location ID at a time but this would just be more work creating queries up front.
If you want to filter you can do so with any of the options on this page. Just include them as query parameters by adding “&[filtername]=[value]” to the end of the URL after your API key. For example, https://api.yext.com/v2/accounts/me/analytics/reports?v=20220511&api_key=*****&folderIds=12345,56789
- In order to get the full set of data you will have to paginate through the data. To do this I recommend setting the limit parameter to 100 (in the URL as you will see below).
.
.
.
Here’s a quick summary of what you’ll be doing:
In order to pull in all of your invitation data you will need 2 queries. For a full explanation and step-by-step you can read more, here, but I’ve provided everything you need below!
-
Query 1) This is a function that hits the /reviewinvites endpoint. Be sure to rename this Query to be getPages as it is referenced in Query 2.
Note: the API key has been removed from the URL here and is authenticated as “Web API” in Step 3 - Edit Credentials.
-
Query 2) Returns the table full of data! Due to a large volume of data, this may take a few minutes to load.
.
.
.
Copy & Paste these queries into your Blank Query editor. Feel free to post any follow-up questions as this post is fairly long!
Best,
Adrienne
Query 1
let
Source = (offset as text) => let
url = "https://api.yext.com/v2/accounts/me/reviewinvites?v=20220901&limit=100&offset=" & offset,
Source = Json.Document(Web.Contents(url, [ApiKeyName="api_key"])),
response = Source[response],
invitations = response[invitations],
#"Converted to Table" = Table.FromList(invitations, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"firstName", "lastName", "contact", "templateId", "status", "entity", "type", "requested", "sent", "invitationUid", "feedbackUrl"}, {"Column1.firstName", "Column1.lastName", "Column1.contact", "Column1.templateId", "Column1.status", "Column1.entity", "Column1.type", "Column1.requested", "Column1.sent", "Column1.invitationUid", "Column1.feedbackUrl"})
in
#"Expanded Column1"
in
Source
Query 2
let
entitiesPerPage = 100,
Limit = "&limit=" & Text.From(entitiesPerPage),
url = "https://api.yext.com/v2/accounts/me/reviewinvites?v=20220901" & Limit,
Source = Json.Document(Web.Contents(url, [ApiKeyName="api_key"])),
response = Source[response],
countPages = Number.RoundUp(Value.Divide(response[countEmail], 100)),
textPages = Text.From(countPages),
List = List.Numbers(0, countPages, 100),
#"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "getPages", each getPages([Column1])),
#"Expanded getPages" = Table.ExpandTableColumn(#"Invoked Custom Function", "getPages", {"Column1.firstName", "Column1.lastName", "Column1.contact", "Column1.templateId", "Column1.status", "Column1.entity", "Column1.type", "Column1.requested", "Column1.sent", "Column1.invitationUid", "Column1.feedbackUrl"}, {"getPages.Column1.firstName", "getPages.Column1.lastName", "getPages.Column1.contact", "getPages.Column1.templateId", "getPages.Column1.status", "getPages.Column1.entity", "getPages.Column1.type", "getPages.Column1.requested", "getPages.Column1.sent", "getPages.Column1.invitationUid", "getPages.Column1.feedbackUrl"})
in
#"Expanded getPages"
