Import Cludo data into Power BI

18 Feb, 2025 Analytics

Certain types of Cludo analytics data can be imported and visualized within Power BI. These instructions will get you started creating your own reports with Cludo data.

Add a Cludo query as a data source

Create a blank query

Cludo data can be imported using a custom Power Query. From the Home tab, click the Get data dropdown and select Blank query as your data source.

A screen capture of where the "blank query" option is in Power BI

Copy and paste code

In the Power Query Home tab, click into the Advanced Editor window

A screen capture of where the Power Query "advanced editor" can be found in Power BI

Pick an analytics type from the “Analytics query examples” section below. Copy the pre-written code snippet into the Advanced query editor and click Done

An example of query code in the Advanced Editor

Customize query with variables

In the copy and pasted code, you will notice a block for CUSTOMIZABLE QUERY OPTIONS. Here, you can edit the pre-filled variables to change how the query functions.

Required custom variables

Most queries will require some basic variables with information about your account and search engine. This information can all be found at https://my.cludo.com/install/api:

  • CustomerId: Your unique customer ID
  • EngineId: The unique ID for the engine you would like to query
  • ApiKey: Your API key for authenticating queries

Optional custom variables

Queries may also include some optional custom variables that can be edited to change how the query functions. In the Analytics query examples section, you’ll find more detailed information about the available optional variables for each query type.

Apply query to your report

After you’ve added the code and updated the custom variables for your query, click Close & Apply to apply it to you report.

A screen capture of where the "close and apply" button is found in Power BI

Analytics query examples

Search count

Get data on the number of searches that have been made over a particular time period.

Copy and paste code into a blank query

Click to expand code snippet 
let


    /**
    ||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 
                   CUSTOMIZABLE QUERY OPTIONS
    */
        // Required
        CustomerId = "",
        EngineId = "",
        ApiKey = "",

        // Optional
        DaysBack = 7,
        ShowUniqueSearches = false,
        IncludePreviousPeriod = false,
        ToDate = null,      // Set in "yyyy-mm-dd" format
        FromDate = null,    // Set in "yyyy-mm-dd" format
    /**
    |||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 
    */


    // Required params. Throw error if null.
    CustomerIdParam =
        if CustomerId = null 
        then error Error.Record("Please add a valid variable 'CustomerId' with your Cludo customer ID in text format")
        else CustomerId,
    EngineIdParam =
        if EngineId = null 
        then error Error.Record("Please add a valid variable 'EngineId' with your Cludo engine ID in text format")
        else EngineId,
    ApiKeyParam = 
        if ApiKey = null
        then error Error.Record("Please add a valid variable 'ApiKey' with your Cludo API key in text format")
        else ApiKey,


    // Optional/derived params. Set defaults if not set.
    DaysBackParam = 
        if DaysBack = null
        then 7
        else DaysBack,
    ShowUniqueSearchesParam = 
        if ShowUniqueSearches = null
        then "false"
        else Text.From(ShowUniqueSearches),
    IncludePreviousPeriodParam = 
        if IncludePreviousPeriod = null
        then "false"
        else Text.From(IncludePreviousPeriod),
    ToDateParam = 
        if ToDate = null
        then Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")
        else Date.ToText(Date.From(ToDate), "yyyy-MM-dd"),
    FromDateParam = 
        if FromDate = null
        then Date.ToText(Date.AddDays(Date.From(ToDateParam), 0-DaysBack), "yyyy-MM-dd")
        else Date.ToText(Date.From(FromDate), "yyyy-MM-dd"),
    PeriodLength = Duration.Days(Date.From(ToDateParam) - Date.From(FromDateParam)),
    PreviousToDate = Date.ToText(Date.AddDays(Date.From(FromDateParam), -1), "yyyy-MM-dd"),
    PreviousFromDate = Date.ToText(Date.AddDays(Date.From(PreviousToDate), 0-PeriodLength), "yyyy-MM-dd"),


    // Construct search count API URL
    BaseDomain = if Number.FromText(CustomerIdParam) >= 1000000 then "https://api-us1.cludo.com/api/v3/" else "https://api-eu1.cludo.com/api/v3/",
    EndpointDir = "/statistics/totalSearches",
    QueryParams = "?from=" & FromDateParam & "&to=" & ToDateParam & "&onlyIncludeUnique=" & ShowUniqueSearchesParam & "&includePreviousPeriod=" & IncludePreviousPeriodParam,
    FullUrl = BaseDomain & CustomerIdParam & "/" & EngineIdParam & EndpointDir & QueryParams,


    // Call the API and get JSON data as table
    Source = Json.Document(Web.Contents(FullUrl, [Headers=[#"Authorization"="Basic " & Binary.ToText(Text.ToBinary(CustomerIdParam & ":" & ApiKeyParam), BinaryEncoding.Base64)]])),
    Table = Record.ToTable(Source),


    // Format table columns
    ReplacedTable = Table.ReplaceValue(
        Table, 
        each [Name],
        each if [Name] = "withinCurrentPeriod" then FromDateParam & " to " & ToDateParam
             else if [Name] = "withinPreviousPeriod" then PreviousFromDate & " to " & PreviousToDate
             else [Name],
        Replacer.ReplaceText,
        {"Name"}
    ),
    ReformattedTable = Table.TransformColumnTypes(ReplacedTable,{{"Value", Int64.Type}}),
    RenamedTable = Table.RenameColumns(ReformattedTable, {{"Name", "Time period"}, {"Value", if ShowUniqueSearchesParam <> "true" then "Total searches" else "Unique searches"}}),
    DisplayTable = if IncludePreviousPeriodParam <> "true" then Table.RemoveLastN(RenamedTable, 1) else RenamedTable

in
    DisplayTable

Edit variables in the code to customize the query

Variable nameData typeNotes
DaysBackNumberThe number of days back from today for which search count data will be calculated. Default value: 7
ShowUniqueSearchesTrue/FalseShow unique search count instead of total search count. Unique searches exclude duplicate searches from one user in a single session. Default value: false
IncludePreviousPeriodTrue/FalseShow search count from the previous period in addition to the latest period so you can compare how search volume is changing over time . Default value: false
FromDateTextOptional alternative to setting the “Days back” variable. Set a specific start date for the time period for which search count will be calculated. Dates should be set in YYYY-MM-DD format.
ToDateTextOptional alternative to setting the “Days back” variable. Set a specific end date for the time period for which search count will be calculated. Dates should be set in YYYY-MM-DD format.

Top queries

Get data on the most popular search queries over a particular time period

Copy and paste code into a blank query

Click to expand code snippet 
let


    /**
    ||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 
                   CUSTOMIZABLE QUERY OPTIONS
    */
        // Required
        CustomerId = "",
        EngineId = "",
        ApiKey = "",

        // Optional
        DaysBack = 7,
        QueriesCount = 10,
        ShowUniqueSearches = false,
        IncludePreviousPeriod = false,
        ToDate = null,      // Set in "yyyy-mm-dd" format
        FromDate = null,    // Set in "yyyy-mm-dd" format
    /**
    |||||||||||||||||||||||||||||||||||||||||||||||||||||||||| 
    */


    // Required params. Throw error if null.
    CustomerIdParam =
        if CustomerId = null or CustomerId = ""
        then error Error.Record("Please add a valid variable 'CustomerId' with your Cludo customer ID in text format")
        else CustomerId,
    EngineIdParam =
        if EngineId = null or EngineId = ""
        then error Error.Record("Please add a valid variable 'EngineId' with your Cludo engine ID in text format")
        else EngineId,
    ApiKeyParam = 
        if ApiKey = null or ApiKey = ""
        then error Error.Record("Please add a valid variable 'ApiKey' with your Cludo API key in text format")
        else ApiKey,


    // Optional/derived params. Set defaults if not set.
    DaysBackParam = 
        if DaysBack = null
        then 7
        else DaysBack,
    ShowUniqueSearchesParam = 
        if ShowUniqueSearches = null
        then false
        else ShowUniqueSearches,
    QueriesCountParam = 
        if QueriesCount = null
        then "10"
        else Text.From(QueriesCount),
    IncludePreviousPeriodParam = 
        if IncludePreviousPeriod = null
        then false
        else IncludePreviousPeriod,
    ToDateParam = 
        if ToDate = null
        then Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")
        else Date.ToText(Date.From(ToDate), "yyyy-MM-dd"),
    FromDateParam = 
        if FromDate = null
        then Date.ToText(Date.AddDays(Date.From(ToDateParam), 0-DaysBack), "yyyy-MM-dd")
        else Date.ToText(Date.From(FromDate), "yyyy-MM-dd"),
    PeriodLength = Duration.Days(Date.From(ToDateParam) - Date.From(FromDateParam)),
    PreviousToDate = Date.ToText(Date.AddDays(Date.From(FromDateParam), -1), "yyyy-MM-dd"),
    PreviousFromDate = Date.ToText(Date.AddDays(Date.From(PreviousToDate), 0-PeriodLength), "yyyy-MM-dd"),


    // Construct search count API URL
    BaseDomain = if Number.FromText(CustomerIdParam) >= 1000000 then "https://api-us1.cludo.com/api/v3/" else "https://api-eu1.cludo.com/api/v3/",
    EndpointDir = "/statistics/QueryLog",
    QueryParams = "?type=all&pageNumber=1&sortOrder=1&orderBy=" & (if ShowUniqueSearchesParam then "uniqueHits" else "hits") & "&from=" & FromDateParam & "&to=" & ToDateParam & "&limit=" & QueriesCountParam,
    FullUrl = BaseDomain & CustomerIdParam & "/" & EngineIdParam & EndpointDir & QueryParams,


    // Call the API and get JSON data as table
    Source = Json.Document(Web.Contents(FullUrl, [Headers=[#"Authorization"="Basic " & Binary.ToText(Text.ToBinary(CustomerIdParam & ":" & ApiKeyParam), BinaryEncoding.Base64)]])),
    Items = Source[items],
    ItemsTable = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // Add columns
    TableWithTerm = Table.AddColumn(ItemsTable, "Query", each Record.Field([Column1], "term"), type text),
    TableWithTimePeriod = Table.AddColumn(TableWithTerm, "Time period", each FromDateParam & " to " & ToDateParam, type text),
    TableWithHits = if ShowUniqueSearchesParam 
                    then Table.AddColumn(TableWithTimePeriod, "Unique searches", each Record.Field([Column1], "uniqueHits"), Int64.Type) 
                    else Table.AddColumn(TableWithTimePeriod, "Total searches", each Record.Field([Column1], "hits"), Int64.Type),
    TableWithPrevious = if IncludePreviousPeriodParam and ShowUniqueSearchesParam
                        then Table.AddColumn(TableWithHits, "Trend from previous period", each Record.Field([Column1], "uniqueTrending"), Int64.Type)
                        else if IncludePreviousPeriodParam
                        then Table.AddColumn(TableWithHits, "Trend from previous period", each Record.Field([Column1], "trending"), Int64.Type)
                        else TableWithHits,

    // Remove "Column1" source record
    DisplayTable = Table.RemoveColumns(TableWithPrevious, {"Column1"})

in
    DisplayTable

Edit variables in the code to customize the query

Variable nameData typeNotes
DaysBackNumberThe number of days back from today for which top query data will be calculated. Default value: 7
QueriesCountNumberThe number of top queries that should be returned. Default value: 10
ShowUniqueSearchesTrue/FalseShow unique search count instead of total search count. Unique searches exclude duplicate searches from one user in a single session. Default value: false
IncludePreviousPeriodTrue/FalseShow trend data from the previous period in addition to the latest period so you can compare how search volume is changing over time . Default value: false
FromDateTextOptional alternative to setting the “Days back” variable. Set a specific start date for the time period for which top query data will be calculated. Dates should be set in YYYY-MM-DD format.
ToDateTextOptional alternative to setting the “Days back” variable. Set a specific end date for the time period for which top query data will be calculated. Dates should be set in YYYY-MM-DD format.
Tags:  

Related Posts