N-Gram Analysis of your PPC Data will give you insights to make actions that will save you tons of budget!

Become a PPC Super Hero by using Data Science Approaches for PPC Problems. N-Gram analysis will bring you tons of insights and actions that were hidden before.

On this page

    What are N-Grams and how to create them on search terms?

    N-Grams are used to analyze large texts. They are also great for analyzing thousands of very small texts, like Query or Keyword related Reports from your PPC Accounts.

    Consider you have a window of N co-occurring words within the Query and move that window word by word forward – this is the way N-Grams are created. “N” can be 1,2,3 and so on. Choosing N=1 will give you 1-Grams, N=2 are called 2-Grams. No rocket science.

    EXAMPLE-QUERY: “book hotel in berlin”

    1-Grams: book, hotel, in, berlin
    2-Grams: book hotel, hotel in, in berlin
    3-Grams: book hotel in, hotel in berlin

    Why and where should you use N-Grams in PPC?

    As mentioned in the beginning, you will save a lot of money by using poor performing N-Grams as Negative Keywords within your PPC Account. Those poor search patterns were hidden before by just looking at complete search terms.

    On Query Level, you will have trouble to judge performance as you do not have sufficient sample sizes available. N-Grams increase observations drastically and it is then possible to make decisions based on numbers.


    But you can also do it in the opposite way:

    Detect new well performing search patterns you can use for creating new keywords. Once you are familiar with the concept you will find more use cases easily.

    Step by Step Guide: From Keyword/Query Reports to N-Gram Reports

    Step 1: Choose the PPC Report you want to analyze

    The most important use cases will be the Google Ads Keyword Performance and the Search Term Performance Report (of course you can apply it to the equivalent Amazon PPC or Bing reports). We consider the Query or Keyword as text (yes, it is very short) we want to create n-Grams on. Because just counting on word occurrences will give you limited insights, so you should also consider some metrics in your report.

    Step 2: Select useful KPIs

    The most important metrics should be:

    • Impressions
    • Clicks
    • Costs
    • Conversions
    • ConversionValue

    Maybe you are confused as I haven’t selected some calculated measures like Cost Per Conversion or Conversion Rate. The reason is simple: When we run the n-Gram transformations and to the mapping of KPIs with calculated metrics, you will end up in wrong numbers. We have to calculate all those metrics after the N-Gram mapping. Only use absolute numbers as input values – no ratios or averages!

    Step 3: Map all available KPIs to N-Grams

    First, you have to decide how deep you want to analyze your Queries. With deep I mean the value of “N”. I good way to start is using 1-Grams and 2-Grams. When you go higher, you will end up in low click numbers per N-Gram and you have trouble again to make actions out of that.

    Please also remember: The higher N is, the higher the required memory for processing is. Also the storage of the results takes more space. This can be a problem when you analyze Gigabytes of Search Query data.

    Step 4: Add calculated Measures to your N-Grams

    After the mapping process, all KPI sums are available on N-Gram Level. Now you can start adding calculated KPIs that are familiar to you when it comes to reviewing your PPC Account data: CPO, CR, ROI and more. Sorting by these KPIs will bring e.g. the poor search patterns on top of your list.

    When we put everything together the N-Gram Analysis Process looks like this:

    Simple Example of N-Gram Analysis: From Search Terms to N-Grams with mapped KPIs and calculated measures

    In my example, I only had 3 Queries in total and it was manageable by hand. What if we have 1 GB of Search Term data we have to analyze? This will be part of the next paragraph. I can already tell you: There is more than one way to do it. And even if you are not a programmer, you will be able to create a PPC N-Gram Analysis.

    Hands On: Create your own N-Gram Analysis on Search Term Data

    Free Online N-Gram Analyzer

    Free Pemavor N-Gram Analyzer

    If you are not a programmer or Google Ads Script or Python is not the first option for you, the quickest way to get a N-Gram Report is using our free n-Gram Analyzer. It is working not only for the Search Term Performance Report but also for every other report that contains queries or keywords and some KPIs you want to analyze. Just upload your CSV data and all n-Gram Transformations are done in seconds. At that point we only have the sums of the provided metrics. For adding calculated measures, you can download the CSV or just copy the result to the clipboard and paste to google sheets of Excel to add the missing calculations.

    Note: Due to processing limitations, we only calculate 1-Grams in this free tool.

    Create N-Grams with Big Query ML

    If you have stored your Search Term Data already in Google Big Query I have good news for you: You are just one SQL Select Statement away from your N-Gram Analysis. One of the Big Query ML Features are addressing N-Grams and it is available as build in function. In my Select Statement I used ML-NGRAM Function to create 1-Grams and 2-Grams, you can easily change this to your needs in the SQL Statement.

    SELECT
      Ngram,
      SUM(Impressions) AS Impressions,
      SUM(Clicks) AS Clicks,
      SUM(Costs) AS Costs,
      SUM(Conversions) AS Conversions,
      COUNT(1) Count,
      ROUND(SUM(Conversions)/SUM(Clicks),2) AS CR,
      ROUND(SUM(Costs)/SUM(Conversions),2) AS CPO
    FROM
      `sealyzer-data-science.test.T_Queryreport`,
      UNNEST(ML.NGRAMS(SPLIT(REGEXP_REPLACE(LOWER(Query), r'(\pP)', r' \1 '), ' '),
          [1,
          2],
          ' ')) AS Ngram # [1,2] means 1-Grams an 2-Grams are created. You can edit this to set your value for "N"
    GROUP BY
      Ngram

    After Running the Script you will get this output:

    1-Grams and 2-Grams generated with SQL within Big Query

    Note: This approach is very nice and scales well even on big data sets. The challenge for some PPC Managers will be to bring the data to Google Big Query. Because the Google Data Transfer Services for importing Google Ads Data can be very costly – we recommend to use Google Ads Script in Combination with Big Query API. If you are interested feel free to contact me.

    N-Gram Script in Python

    Processing Steps using Python Pandas to create N-Gram Analysis of Google Ads Search Terms

    There are some available modules that extract N-Grams from Texts. However, those modules do not cover the mapping of the KPIs, which is crucial for getting actionable insights. For that reason, I created my own solution based on Python Pandas, which is very flexible in changing Inputs. When you have changing file column names, the processing will still work.

    Here is the Python Script that creates 1-Grams for your PPC Reports.

    Note: With some small adjustments, you can create 2-Grams and 3-Grams as well. Contact me when you are interested.

    import pandas as pd
    from collections import defaultdict
    import numpy as np
    
    
    
    def explode_str(df, col, sep):
        s = df[col]
        i = np.arange(len(s)).repeat(s.str.count(sep) + 1)
        return df.iloc[i].assign(**{col: sep.join(s).split(sep)})
    
    # queries.csv contains your search term report with KPIs
    # Adjust delimiter by seeting the sep= parameter
    print("----------------------------------------------------------------------------------------------------")
    print("\tThe raw report")
    print("----------------------------------------------------------------------------------------------------")
    df = pd.read_csv("queries.csv",sep="\t")
    print(df)
    
    # Create 1-Grams by splitting 1 row to multiple rows
    # "Query" is the columnname where you want to apply the n-Gram analysis
    df = explode_str(df, 'Query', ' ')
    print("")
    print("----------------------------------------------------------------------------------------------------")
    print("\tSplitting into 1-Grams")
    print("----------------------------------------------------------------------------------------------------")
    print(df)
    
    
    # Group by N-Grams
    df = df.groupby('Query').sum()
    df = df.reset_index()
    print("")
    print("----------------------------------------------------------------------------------------------------")
    print("\tAggregated Metrics on 1-Grams")
    print("----------------------------------------------------------------------------------------------------")
    print(df)
    
    
    print("")
    print("----------------------------------------------------------------------------------------------------")
    print("\tAdded Calculated Measures")
    print("----------------------------------------------------------------------------------------------------")
    
    # Define your calculated measures here:
    df['CR'] = df['Conversions']/df['Clicks']
    df['CPO'] = df['Costs'] / df['Conversions']
    print(df)
    
    #Save to CSV
    df.to_csv("ngrams.csv",index=False)

    N-Gram Script in Google Ads

    There are some examples available to do the N-Gram Processing directly in Google Ads by using Google Ads Script. However, the processing of large data sets can be a problem, especially when you go deeper then 1-Grams. In this case:

    Depending on your use case, you have to adjust the Javascript Code to fit your desired output. We will soon share a running solution that is already built on Google Ads API Query Statements – the old n-Grams built on the soon outdated AdWords API – will stop working.

    Share
    Recent Posts

    Comments

    You must be logged in to post a comment.

    More similar posts

    Menu