Power BI with Google Trends Analysis

How to Integrate SERP API with Power BI for Keyword Trend Analysis

Analyzing keyword trends is crucial for businesses and marketers to understand what people are searching for and how these searches vary over time. With the SERP API, you can access Google Trends data directly from Power BI, offering a comprehensive view of keyword performance across different metrics. In this blog, we’ll explore how to set up this integration, run queries, and visualize the data effectively.

Table of Contents

1. Introduction to SERP API and Google Trends
2. Setting Up SERP API for Power BI
3. Power BI Code Samples for Keyword Analysis
4. Visualizing Keyword Trends in Power BI
5. Conclusion
6. FAQs

Introduction to SERP API and Google Trends

SERP API is a tool that allows developers to access search engine results programmatically. It provides various data endpoints, including Google Trends, enabling users to gather insights into keyword popularity, regional interest, and related searches.

Google Trends is a public web facility that analyzes the popularity of top search queries in Google Search across various regions and languages. It provides insights into trending searches and how they evolve over time.

By integrating SERP API with Power BI, users can automate the process of fetching Google Trends data and building dynamic reports to analyze keyword performance effectively.

Setting Up SERP API for Power BI

Before we begin writing code in Power BI, you need to set up an account with SERP API and obtain an API key. Here’s how you can do it:

Step 1: Sign Up for SERP API

1. Visit the SERP API website: https://serpapi.com/.
2. Create an account: Click on “Sign Up” and provide the necessary details.
3. Verify your email: Confirm your account registration by clicking on the verification link sent to your email.

Step 2: Retrieve Your API Key

1. Log in to your SERP API account.
2. Navigate to the “Account” section.
3. Locate your API Key: You’ll find the API key under the “API Key” section. Copy this key for later use.

Power BI Code Samples for Keyword Analysis

Once you have your API key, you can start integrating SERP API with Power BI. Below are several M-code samples that illustrate how to fetch Google Trends data for different analyses, such as geographical breakdown, time-series analysis, and related keyword trends.

Code Sample 1: Geographical Breakdown by Region

This code fetches data on keyword performance based on geographical regions.

let
    // Define the API endpoint
    apiUrl = "https://serpapi.com/search.json",

    // Define the parameters
    queryParams = [
        engine = "google_trends",
        q = "Data Analyst,The Developer,Jobs,India,Power BI",
        data_type = "GEO_MAP",
        date = "today 5-y",
        tz = "-330",
        api_key = "Paste your Key here"
    ],

    // Combine the endpoint and parameters
    fullUrl = apiUrl & "?" & Uri.BuildQueryString(queryParams),

    // Make the HTTP request
    response = Web.Contents(fullUrl),

    // Parse the JSON response
    jsonResponse = Json.Document(response),

    // Convert the response to a table
    dataTable = Table.FromRecords({jsonResponse}),

    // Extract the relevant data
    comparedBreakdownByRegion = dataTable{0}[compared_breakdown_by_region]
in
    comparedBreakdownByRegion

Explanation:

• API Endpoint: We define the endpoint as https://serpapi.com/search.json.
• Query Parameters: The parameters include engine, q (keywords), data_type, date, tz (timezone), and api_key.
• Full URL: Combines the endpoint with query parameters to form a complete request URL.
• HTTP Request: The Web.Contents function is used to send the request to the API.
• JSON Parsing: Json.Document parses the response into a JSON object.
• Table Conversion: The JSON response is converted into a Power BI table for analysis.
• Data Extraction: Extracts the geographical breakdown of keyword performance.

Code Sample 2: Time-Series Analysis of Keyword Performance

This code retrieves time-series data for keyword performance over all available dates.

let
    // Define the base URL for the API call
    BaseUrl = "https://serpapi.com/search",

    // Define the query parameters with engine, terms, data type, date, and time zone
    QueryParams = [
        engine = "google_trends",
        q = "Data Analyst,The Developer,Jobs,India,Power BI",
        data_type = "TIMESERIES",
        date = "all",
        tz = "-330",
        api_key = "Paste your Key here"
    ],

    // Generate the full URL with query parameters
    UrlWithParams = BaseUrl & "?" & Text.Combine(List.Transform(Record.FieldNames(QueryParams), 
        each _ & "=" & Uri.EscapeDataString(Record.Field(QueryParams, _))), "&"),

    // Fetch data from the API
    JsonResponse = Json.Document(Web.Contents(UrlWithParams)),

    // Extract the "interest_over_time" part from the JSON response
    InterestOverTime = JsonResponse[#"interest_over_time"]
in
    InterestOverTime

Explanation:

• Base URL: Defined as https://serpapi.com/search.
• Query Parameters: Includes engine, q, data_type, date, tz, and api_key.
• URL Generation: Constructs the full URL using query parameters.
• Data Fetching: Sends the API request and retrieves the JSON response.
• Interest Over Time: Extracts time-series data to analyze keyword trends.

Code Sample 3: Past 7 Days Keywords Performance Data

This code sample focuses on fetching keyword performance data for the last 7 days.

let
    // Define the base URL for the API call
    BaseUrl = "https://serpapi.com/search",

    // Define the query parameters with engine, terms, data type, date, and time zone
    QueryParams = [
        engine="google_trends",
        q="Data Analyst,The Developer,Jobs,India,Power BI",
        data_type = "TIMESERIES",
        date = "now 7-d",
        tz = "-330",
        api_key = "Paste your key here"
    ],

    // Generate the full URL with query parameters
    UrlWithParams = BaseUrl & "?" & Text.Combine(List.Transform(Record.FieldNames(QueryParams), 
        each _ & "=" & Uri.EscapeDataString(Record.Field(QueryParams, _))), "&"),

    // Fetch data from the API
    JsonResponse = Json.Document(Web.Contents(UrlWithParams)),

    // Extract the "interest_over_time" part from the JSON response
    InterestOverTime = JsonResponse[#"interest_over_time"]
in
    InterestOverTime

Explanation:

• Date Parameter: Set to "now 7-d" to retrieve data for the past 7 days.
• Interest Over Time: Extracts recent trends, allowing you to analyze short-term keyword performance.

Code Sample 4: Related Keywords Analysis

This code helps identify rising and top related keywords for a given search term.

let
    // Define the API endpoint
    apiUrl = "https://serpapi.com/search.json",

    // Define the parameters
    queryParams = [
        engine = "google_trends",
        q = "The Developer",
        data_type = "RELATED_TOPICS",
        api_key = "Paste your Key"
    ],

    // Combine the endpoint and parameters to create the full URL
    fullUrl = apiUrl & "?" & Uri.BuildQueryString(queryParams),

    // Make the HTTP request and get the response
    response = Web.Contents(fullUrl),

    // Parse the JSON response
    jsonResponse = Json.Document(response)
in
    jsonResponse

Explanation:

• Related Topics: Retrieves related keywords categorized into “Rising Keywords” and “Top Keywords.”
• Single Keyword: Note that only one keyword is allowed for this type of query.

Visualizing Keyword Trends in Power BI

Watch Tutorial:

Click Here to Watch

Once you’ve fetched the data using the above M-code samples, the next step is to visualize it effectively in Power BI. Here are some tips on how to create compelling visualizations:

Step 1: Load the Data into Power BI

1. Open Power BI Desktop.
2. Click on “Get Data” and choose “Blank Query.”
3. Paste the M-code into the advanced editor.
4. Load the data and create a new table.

Step 2: Create Visualizations

• Line Charts: Use line charts to visualize time-series data for keyword trends. This allows you to see how keyword popularity changes over time.
• Bar Charts: Compare geographical keyword performance with bar charts to highlight which regions show the most interest in your keywords.
• Maps: Use maps to visualize geographical data, providing a clear visual representation of where your keywords are trending.
• Tables: Display related keywords in a table format, categorizing them into rising and top keywords.

Step 3: Customize Visuals

• Add Data Labels: Enhance your charts by adding data labels to highlight specific values.
• Adjust Colors: Use color coding to differentiate between various keyword categories or time periods.
• Interactive Slicers: Enable users to filter data dynamically by adding slicers to your report.

Conclusion

Integrating SERP API with Power BI offers powerful insights into keyword trends, helping you make data-driven decisions. By following the steps outlined in this guide, you can set up your Power BI reports to fetch and analyze Google Trends data seamlessly. Whether you’re tracking keyword performance over time, analyzing geographical interest, or exploring related topics, these techniques provide a comprehensive approach to keyword analysis.

FAQs

1. What is SERP API?

SERP API is a tool that allows developers to access search engine results programmatically. It provides various data endpoints, including Google Trends, enabling users to gather insights into keyword popularity, regional interest, and related searches.

2. How many keywords can be analyzed at once?

You can analyze up to 5 keywords simultaneously for most queries. For related keywords analysis, only one keyword is allowed per request.

3. Can I use SERP API for free?

SERP API offers a free tier with limited usage. For more extensive access, you may need to upgrade to a paid plan.

4. What data types are available in Google Trends?

Google Trends provides data types such as GEO_MAP, TIMESERIES, and RELATED_TOPICS, among others. These data types allow for various analyses, including geographical interest and keyword trends over time.

5. How do I visualize the data in Power BI?

Once the data is loaded into Power BI, you can create visualizations using line charts, bar charts, maps, and tables to represent keyword trends and performance. Customize these visuals with data labels, color coding, and interactive slicers for a more dynamic report.