Integrate with Power BI Analytics – Yext Help

Introduction

Prerequisites: This guide assumes you have a basic knowledge of Microsoft Power BI, have previously gained access to Microsoft Power BI Desktop, and have a workspace. You will also need a live Yext account with analytics data.

This doc will walk you through how to create a basic web API with a query string, to push Yext analytics to create a datasource in Microsoft Power BI by using Microsoft Power Query.

For more information, review this Microsoft guide Query Overview in Power BI Desktop.

Step 1: Set Up Your Yext App

This step will walk you through setting up a Yext App in order to generate the API Key needed for authenticating your web API.

Create an App in your Account

Navigate to the Developer Console. To do this, hover over your name in the top right corner and select Developer Console from the drop down.
Click +Add an App.
Create an app titled [Your Company - Power BI] and set a contact email. Select Create App.

Generate a Secure API Key

Move to the API Credentials tab to set your API permissions.
Click to Add an endpoint for your API. Select Management API > Analytics endpoint.
Set your API to have Read Only permissions. Read APIs simply pull and ingest information from a system - you will not need Write permissions since you will not need to push any updates to Yext.

Your API key will be generated beneath for you to copy and paste into Power BI.

power bi app directory page

Step 2: Create your Power BI Query

Now you are ready to get data from the web source - setting up the API call. Within Power BI Desktop, create a Blank Query within the Advanced Query Editor:

From Home in Power BI, navigate to Get Data and click the dropdown arrow.
Select Blank Query.

Blank Query data source in Power BI

Another window will pop up to start your query. Select Advanced Editor from the top ribbon.

Step 3: Choose the Query for your Solution

Depending on the Yext solution you have, you will want to pull in different metrics. See the Query Samples reference section below for packaged code. The queries include the request URL, API key, headers, and metrics and dimensions in the body content. Skip to the section that best fits how you are using Yext and copy and paste the corresponding code. Remember to replace [YOUR API KEY] with the key you just generated. Remove the default code in the Editor.

The codes provided are a starting point - you can customize the dataset however you need. Feel free to delete or reorder columns and rows as you see fit.

Note: You can combine metrics from multiple sets if you want to have data from across multiple solutions in one flow. Keep in mind that every metric cannot be paired with every dimension. See How to Add / Remove Metrics or Dimensions and How to Add Filters below. If you want to customize your API call even further, read the Yext Analytics API Documentation.

Once you have pasted your query and customized it, you will need to finish authorizing the API in Power BI.

A warning window will pop up that says "Please specify how to connect". From there:

Click Edit Credentials and select Anonymous. You will only have to do this the very first time you access the Yext Analytics API - your credentials will be stored for future refreshes.
Choose which level to apply the credentials to from the dropdown box. Set it to https://api.yextapis.com/v2/accounts/me/analytics/reports.

Click Connect.

This will automatically run your query and create a preview of the data returned. Create, customize, and manage your data however you see fit - just don't forget to save your work.

To save your work:

Select Close & Apply in the top left corner of the Editor.
Once changes are applied, save your file by selecting File > Save as. Save it as a .pbix file.

And just like that, your Yext data is flowing into Power BI!

Step 4: Next Steps

How to Add / Remove Metrics or Dimensions

The codes provided are a starting point. You can combine metrics from multiple sets if you want data from across multiple solutions in one flow. Keep in mind that every metric cannot be paired with every dimension.

Add Metrics or Dimensions - Add any metric/dimension names to the body array, for example:

[metrics = {"ANSWERS_CLICKS", "ANSWERS_SEARCHES"}, dimensions = {"DAYS"}]

Remove Metrics or Dimensions - Remove unwanted items from the line beginning with "body". Remember to remove the entire name and its surrounding quotes, like "ANSWERS_SEARCHES",.

How to Add Filters

To add filters, append them to the end of the body array. For example, to filter by date range:

[metrics = {"ANSWERS_CLICKS", "ANSWERS_SEARCHES"}, dimensions = {"DAYS"}, filters = [startDate = #date(2015,01,01)]]

For dynamic date ranges such as Last 7 Days, Last 30 Days, or Last 90 Days, use this snippet. The example below shows Last 7 Days, where startDate is today minus 8 days and endDate is today minus 1 day:

startDate = #date(Date.Year(Date.AddDays(DateTime.LocalNow(),-8)),Date.Month(Date.AddDays(DateTime.LocalNow(),-8)), Date.Day(Date.AddDays(DateTime.LocalNow(),-8))), endDate = #date(Date.Year(Date.AddDays(DateTime.LocalNow(),-1)),Date.Month(Date.AddDays(DateTime.LocalNow(),-1)), Date.Day(Date.AddDays(DateTime.LocalNow(),-1)))

For a full list of metrics, dimensions, and filter options read the Yext Analytics API Documentation.

Data Refresh

When you need to refresh your data this is a manual process. You can also configure Incremental Refresh within Power BI Desktop if you have it set up for other data sources.

Refresh your Data Manually:

Open your .pbix file and find the name of the query in the left Fields sidebar.
Hover over to the right of your query name and three dots will appear.
Click and select Refresh data.

Blank Query data source in Power BI

For more information, review this Microsoft guide Refresh data screen.

Reference: Query Samples

Copy and paste the code for the solution that best fits how you are using Yext. Remember to replace [YOUR API KEY] with the key you generated in Step 1. Remove the default code in the Editor before pasting.

Site Search Analytics

let
  url
    = "https://api.yextapis.com/v2/accounts/me/analytics/reports?&v=20220310&api_key=[YOUR API KEY]",
  header = [#"Content-Type" = "application/json", #"Accept" = "application/json"],
  body = Text.FromBinary(
    Json.FromValue(
      [
        metrics = {
          "ANSWERS_CLICK_THROUGH_RATE",
          "ANSWERS_CLICKS",
          "ANSWERS_CLUSTERS",
          "ANSWERS_KG_RESULT_RATE",
          "ANSWERS_PERCENTAGE_OF_TOTAL_SEARCHES",
          "ANSWERS_SEARCH_TERMS",
          "ANSWERS_SEARCHES",
          "ANSWERS_SEARCHES_WITH_CLICKS",
          "ANSWERS_SEARCHES_WITH_KG_RESULTS",
          "ANSWERS_SESSIONS",
          "ANSWERS_THUMBS_DOWN_EVENTS",
          "ANSWERS_THUMBS_UP_EVENTS",
          "ANSWERS_VOICE_SEARCHES"
        },
        dimensions = {"ENTITY_IDS", "DAYS"}
      ]
    )
  ),
  Source = Json.Document(Web.Contents(url, [Headers = header, Content = Text.ToBinary(body)]))
in
  Source

Support Search Analytics

let
  url
    = "https://api.yextapis.com/v2/accounts/me/analytics/reports?&v=20220310&api_key=[YOUR API KEY]",
  header = [#"Content-Type" = "application/json", #"Accept" = "application/json"],
  body = Text.FromBinary(
    Json.FromValue(
      [
        metrics = {
          "ANSWERS_CASE_DEFLECTION_RATE",
          "ANSWERS_CASE_DEFLECTIONS",
          "ANSWERS_CASE_STARTS",
          "ANSWERS_CASE_SUBMITS",
          "ANSWERS_CLICK_THROUGH_RATE",
          "ANSWERS_CLICKS",
          "ANSWERS_CLUSTERS",
          "ANSWERS_KG_RESULT_RATE",
          "ANSWERS_PERCENTAGE_OF_TOTAL_SEARCHES",
          "ANSWERS_SEARCH_TERMS",
          "ANSWERS_SEARCHES",
          "ANSWERS_SEARCHES_WITH_CLICKS",
          "ANSWERS_SEARCHES_WITH_KG_RESULTS",
          "ANSWERS_SESSIONS",
          "ANSWERS_THUMBS_DOWN_EVENTS",
          "ANSWERS_THUMBS_UP_EVENTS",
          "ANSWERS_VOICE_SEARCHES"
        },
        dimensions = {"DAYS"}
      ]
    )
  ),
  Source = Json.Document(Web.Contents(url, [Headers = header, Content = Text.ToBinary(body)]))
in
  Source

Listings Management Analytics

let
  url
    = "https://api.yextapis.com/v2/accounts/me/analytics/reports?&v=20220310&api_key=[YOUR API KEY]",
  header = [#"Content-Type" = "application/json", #"Accept" = "application/json"],
  body = Text.FromBinary(
    Json.FromValue(
      [
        metrics = {
          "TICKET_CLICKS",
          "DUPLICATES_DETECTED",
          "DUPLICATES_SUPPRESSED",
          "LISTINGS_LIVE",
          "CONVERSION_COUNT",
          "TOTAL_CONVERSION_VALUE",
          "AVERAGE_CONVERSION_VALUE",
          "CLICK_COUNT"
        },
        dimensions = {"DAYS", "ENTITY_IDS"}
      ]
    )
  ),
  Source = Json.Document(Web.Contents(url, [Headers = header, Content = Text.ToBinary(body)]))
in
  Source

Listings Management - Social Analytics

let
  url
    = "https://api.yextapis.com/v2/accounts/me/analytics/reports?&v=20220310&api_key=[YOUR API KEY]",
  header = [#"Content-Type" = "application/json", #"Accept" = "application/json"],
  body = Text.FromBinary(
    Json.FromValue(
      [
        metrics = {
          "FACEBOOK_CHECKINS",
          "FACEBOOK_CTA_CLICKS",
          "FACEBOOK_IMPRESSIONS",
          "FACEBOOK_LIKES",
          "FACEBOOK_PAGE_VIEWS",
          "FACEBOOK_POST_IMPRESSIONS",
          "FACEBOOK_TALKING_ABOUT",
          "GOOGLE_USER_PHOTOS",
          "SOCIAL_ACTIVITIES",
          "CONVERSION_COUNT",
          "TOTAL_CONVERSION_VALUE",
          "AVERAGE_CONVERSION_VALUE",
          "CLICK_COUNT"
        },
        dimensions = {"DAYS"}
      ]
    )
  ),
  Source = Json.Document(Web.Contents(url, [Headers = header, Content = Text.ToBinary(body)]))
in
  Source

Listings Management - Search Tracking Analytics

let
  url
    = "https://api.yextapis.com/v2/accounts/me/analytics/reports?&v=20220310&api_key=[YOUR API KEY]",
  header = [#"Content-Type" = "application/json", #"Accept" = "application/json"],
  body = Text.FromBinary(
    Json.FromValue(
      [
        metrics = {
          "IST_AD_MATCHES",
          "IST_AD_PRESENCE",
          "IST_AVERAGE_FIRST_LOCAL_PACK_MATCH_POSITION",
          "IST_AVERAGE_FIRST_MATCH_POSITION",
          "IST_AVERAGE_FIRST_ORGANIC_MATCH_POSITION",
          "IST_AVERAGE_LOCAL_PACK_NUMBER_OF_RESULTS",
          "IST_AVERAGE_LOCAL_PACK_POSITION",
          "IST_LOCAL_PACK_EXISTED",
          "IST_KNOWLEDGE_CARD_EXISTED",
          "IST_LOCAL_PACK_PRESENCE",
          "IST_LOCAL_PACK_SHARE_OF_SEARCH",
          "IST_MATCHES_PER_SEARCH",
          "IST_ORGANIC_SHARE_OF_SEARCH",
          "IST_SEARCH_REQUESTS",
          "IST_SHARE_OF_INTELLIGENT_SEARCH",
          "IST_TOTAL_MATCHES"
        },
        dimensions = {
          "DAYS",
          "IST_KEYWORD",
          "IST_QUERY_TEMPLATE",
          "IST_SEARCH_ENGINE",
          "ENTITY_IDS"
        }
      ]
    )
  ),
  Source = Json.Document(Web.Contents(url, [Headers = header, Content = Text.ToBinary(body)]))
in
  Source

Reputation Management - Review Response Analytics

let
  url
    = "https://api.yextapis.com/v2/accounts/me/analytics/reports?v=20220310&api_key=[YOUR API KEY]",
  header = [#"Content-Type" = "application/json", #"Accept" = "application/json"],
  body = Text.FromBinary(
    Json.FromValue(
      [
        metrics = {
          "AVERAGE_RATING",
          "NEW_REVIEWS",
          "RESPONSE_COUNT",
          "RESPONSE_RATE",
          "RESPONSE_TIME"
        },
        dimensions = {"DAYS", "REVIEW_KEYWORDS", "RATINGS"}
      ]
    )
  ),
  Source = Json.Document(Web.Contents(url, [Headers = header, Content = Text.ToBinary(body)]))
in
  Source

Reputation Management - Review Sentiment Analytics

let
  url
    = "https://api.yextapis.com/v2/accounts/me/analytics/reports?&v=20220310&api_key=[YOUR API KEY]",
  header = [#"Content-Type" = "application/json", #"Accept" = "application/json"],
  body = Text.FromBinary(
    Json.FromValue(
      [
        metrics    = {"AVERAGE_RATING", "NEW_REVIEWS", "KEYWORD_MENTIONS", "KEYWORD_SENTIMENT"},
        dimensions = {"DAYS", "RATINGS", "SENTIMENT_COLLECTION", "PARTNERS"}
      ]
    )
  ),
  Source = Json.Document(Web.Contents(url, [Headers = header, Content = Text.ToBinary(body)]))
in
  Source

Location Pages Analytics

let
  url
    = "https://api.yextapis.com/v2/accounts/me/analytics/reports?&v=20220310&api_key=[YOUR API KEY]",
  header = [#"Content-Type" = "application/json", #"Accept" = "application/json"],
  body = Text.FromBinary(
    Json.FromValue(
      [
        metrics = {
          "STOREPAGES_CALLTOACTIONCLICKS",
          "STOREPAGES_PHONECALLS",
          "STOREPAGES_CLICKSTOWEBSITE",
          "STOREPAGES_DRIVINGDIRECTIONS",
          "GSC_CLICKS",
          "GSC_CTR",
          "GSC_IMPRESSIONS",
          "GSC_POSITION",
          "STOREPAGES_PAGEVIEWS",
          "STOREPAGES_SESSIONS"
        },
        dimensions = {"STOREPAGES_PAGE_URL", "DAYS"}
      ]
    )
  ),
  Source = Json.Document(Web.Contents(url, [Headers = header, Content = Text.ToBinary(body)]))
in
  Source

Pages Analytics

let
  url
    = "https://api.yextapis.com/v2/accounts/me/analytics/reports?&v=20220310&api_key=[YOUR API KEY]",
  header = [#"Content-Type" = "application/json", #"Accept" = "application/json"],
  body = Text.FromBinary(
    Json.FromValue(
      [
        metrics = {
          "STOREPAGES_CALLTOACTIONCLICKS",
          "STOREPAGES_PHONECALLS",
          "STOREPAGES_CLICKSTOWEBSITE",
          "STOREPAGES_DRIVINGDIRECTIONS",
          "GSC_CLICKS",
          "GSC_CTR",
          "GSC_IMPRESSIONS",
          "GSC_POSITION",
          "STOREPAGES_PAGEVIEWS",
          "STOREPAGES_SESSIONS",
          "CONVERSION_COUNT",
          "TOTAL_CONVERSION_VALUE",
          "AVERAGE_CONVERSION_VALUE",
          "CLICK_COUNT"
        },
        dimensions = {"DAYS"}
      ]
    )
  ),
  Source = Json.Document(Web.Contents(url, [Headers = header, Content = Text.ToBinary(body)]))
in
  Source