Import Cludo data into Power BI

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.

Copy and paste code

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

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

Add custom parameters

You can customize how data is queried with parameters. Specific parameters may be required, while some are optional. Check the “Analytics query examples” section to see full details about parameters that can be added for each query type.

To add a new parameter, click Manage Parameters in the Home tab, and then click New in the parameter management window. Be sure to fill out specific parameter details such as the parameter name, data type, and required according to the documentation in the “Analytics query examples” section. Click OK to apply your new parameter to the query.

Apply query to your report

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

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

let
    
    // Try to get customer-defined parameters
    CustomerIdParam = try CludoCustomerIdParameter otherwise null,
    EngineIdParam = try CludoEngineIdParameter otherwise null,
    ApiKeyParam = try CludoApiKeyParameter otherwise null,
    DaysBackParam = try CludoSearchCountDaysBackParameter otherwise null,
    ShowUniqueSearchesParam = try CludoSearchCountUniqueSearchesParameter otherwise null,
    IncludePreviousPeriodParam = try CludoSearchCountPreviousPeriodParameter otherwise null,
    ToDateParam = try CludoSearchCountStartDateParameter otherwise null,
    FromDateParam = try CludoSearchCountEndDateParameter otherwise null,
    

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


    // Optional/derived params. Set defaults if not set.
    DaysBack = 
        if DaysBackParam = null
        then 7
        else DaysBackParam,
    ShowUniqueSearches = 
        if ShowUniqueSearchesParam = null
        then "false"
        else Text.From(ShowUniqueSearchesParam),
    IncludePreviousPeriod = 
        if IncludePreviousPeriodParam = null
        then "false"
        else Text.From(IncludePreviousPeriodParam),
    ToDate = 
        if ToDateParam = null
        then Date.ToText(DateTime.Date(DateTime.LocalNow()), "yyyy-MM-dd")
        else Date.ToText(Date.From(ToDateParam), "yyyy-MM-dd"),
    FromDate = 
        if FromDateParam = null
        then Date.ToText(Date.AddDays(Date.From(ToDate), 0-DaysBack), "yyyy-MM-dd")
        else Date.ToText(Date.From(FromDateParam), "yyyy-MM-dd"),
    PeriodLength = Duration.Days(Date.From(ToDate) - Date.From(FromDate)),
    PreviousToDate = Date.ToText(Date.AddDays(Date.From(FromDate), -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(CustomerId) >= 1000000 then "https://api-us1.cludo.com/api/v3/" else "https://api-eu1.cludo.com/api/v3/",
    EndpointDir = "/statistics/totalSearches",
    QueryParams = "?from=" & FromDate & "&to=" & ToDate & "&onlyIncludeUnique=" & ShowUniqueSearches & "&includePreviousPeriod=" & IncludePreviousPeriod,
    FullUrl = BaseDomain & CustomerId & "/" & EngineId & EndpointDir & QueryParams,


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


    // Format table columns
    ReplacedTable = Table.ReplaceValue(
        Table, 
        each [Name],
        each if [Name] = "withinCurrentPeriod" then FromDate & " to " & ToDate
             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 ShowUniqueSearches <> "true" then "Total searches" else "Unique searches"}}),
    DisplayTable = if IncludePreviousPeriod <> "true" then Table.RemoveLastN(RenamedTable, 1) else RenamedTable

in
    DisplayTable

Add parameters to customize the query

Parameter name	Required?	Data type	Notes
CludoCustomerIdParameter	X	Text	You Cludo customer ID found at https://my.cludo.com/install/api
CludoEngineIdParameter	X	Text	You Cludo engine ID found at https://my.cludo.com/install/api
CludoApiKeyParameter	X	Text	Your Cludo API key found at https://my.cludo.com/install/api
CludoSearchCountDaysBackParameter		Decimal Number	The number of days back from today for which search count data will be calculated. Default value: 7
CludoSearchCountUniqueSearchesParameter		True/False	Show unique search count instead of total search count. Unique searches exclude duplicate searches from one user in a single session. Default value: false
CludoSearchCountPreviousPeriodParameter		True/False	Show 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
CludoSearchCountStartDateParameter		Date	Optional alternative to setting the “Days back” parameter. 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.
CludoSearchCountEndDateParameter		Date	Optional alternative to setting the “Days back” parameter. 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.