Skip to main content

ShowQL query for event analytics

Tim Funk

This page provides some great example queries to fetch analytics. But its missing (to me) how to paste that query into a call (or something else).

https://docs.api.showpad.com/docs/apis/reporting/content-reporting/example-queries#user-engagement-examples

I'd think I'd call something like this - But I've have no luck as well as this curl command is /events specific and the examples page also accesses other pages.

curl --request POST \
  --url https://$company.api.showpad.com/api/v3/exports/events \
  --header 'Authorization: Bearer $TOKEN' \
  --header 'Content-Type: application/json' \
  --data '{ "query": "$QUERY_HERE"}'

 

0

Comments

5 comments

Date Votes
  • Official comment
    Cedric Lemaire

    Hi Tim Funk,

    Your question was handled via a Support ticket, and I want to close the loop here as well, in case this information helps other developers. We will review the content on our Developer Portal to see if we need to add more clarification.

    The engineering team confirmed that the V3 API doesn't support the Showpad Query language, which is why you could not perform the calls provided above.

    Only some endpoints on the V4 API support the Showpad Query language, and since the Exports endpoint is only available on the V3 API, you could not perform these calls.
     
    The Export endpoints are solely aimed at pulling data in bulk and importing it into BI tools, where it can then be leveraged through SQL scripts, as per the documentation from our developer portal.

    Thanks again for bringing this to our attention.

    Best regards,

    -Cedric

  • Cedric Lemaire

    Hi Tim,

    Thank you for your question! It looks like you're trying to execute one of the SQL-like queries from the documentation page, but you're unsure how to make this work with the curl command for calling the API.

    The queries provided in the documentation are not directly executed via a curl command. Instead, these queries should be embedded within the query parameter of the POST request when calling the /exports endpoint.

    To clarify, here's how you can proceed.

    First, make sure your query is formatted as shown in the examples. For instance, if you want to get User Details, your query would look like this:

    SELECT
      User.userId AS 'Id',
      IF(User.isActive = 1, 'Active', 'Inactive') AS 'Status',
      User.emailAddress,
      User.firstName,
      User.lastName
    FROM Event
    LEFT JOIN User ON Event.userId = User.userId
    WHERE
      Event.type = 'app-opened' -- Session
      AND Event.startTime >= :startDate
      AND Event.startTime <= :endDate
      AND Event.endTime IS NOT NULL -- Exclude events with no end-time
    GROUP BY
      User.userId;

    Next, you'll need to create a JSON payload that includes this query. Here’s an example of how you can structure the curl command:

    curl --request POST \
      --url https://$company.api.showpad.com/api/v3/exports/events \
      --header 'Authorization: Bearer $TOKEN' \
      --header 'Content-Type: application/json' \
      --data '{
        "query": "SELECT User.userId AS '\''Id'\'', IF(User.isActive = 1, '\''Active'\'', '\''Inactive'\'') AS '\''Status'\'', User.emailAddress, User.firstName, User.lastName FROM Event LEFT JOIN User ON Event.userId = User.userId WHERE Event.type = '\''app-opened'\'' AND Event.startTime >= :startDate AND Event.startTime <= :endDate AND Event.endTime IS NOT NULL GROUP BY User.userId;",
        "startDate": "2024-01-01T00:00:00Z",
        "endDate": "2024-01-31T23:59:59Z"
      }'

    In this example:

    • Replace $company with your actual company identifier.
    • Replace $TOKEN with your actual access token.
    • Replace :startDate and :endDate with your desired date range.

    Some remarks:

    Ensure your query is correctly escaped in JSON. You might need to escape single quotes within the query by using \'. The dates provided in the startDate and endDate parameters should be in ISO 8601 format.

    Can you give that a try? Feel free to share the specific errors or responses you’re getting. 

    -Cedric

    0
  • Tim Funk

    Thanks ... But I'm still getting errors.

     

    Via $company.api.showpad.com - I get

    < HTTP/1.1 501 Not Implemented

    "The server does not support the functionality required to fulfill the request."

     

    Via $company.showpad.biz - I get 

    < HTTP/1.1 405 Method Not Allowed

    Could there be a licensing issue? Equally interesting is API calls work on showpad.biz but not showpad.com - for example

     

    OK: curl --url "https://$company.showpad.biz/api/v3/exports/events.json?startedAt=2024-08-21&endedAt=2024-08-22&limit=50"   --header "Authorization: Bearer $TOKEN"
    501 ERROR: curl --url "https://$company.api.showpad.com/api/v3/exports/events.json?startedAt=2024-08-21&endedAt=2024-08-22&limit=50"   --header "Authorization: Bearer $TOKEN"

     

     

    0
  • Cedric Lemaire
    • Edited

    Hi Tim Funk,

    Thanks for the additional information. First of all, you need the following:

    • The Showpad Ultimate package
    • Administrator access to Showpad to use the API

    Next, we learned from your post that we need to clarify the usage of .com and .biz on our Developer Portal. We will make sure to add that information soon.

    The confusion here stems from the difference in how API requests are handled based on the domain you're using.

    .com and .biz domains are handled the same:

    • When you use showpad.com or showpad.biz without the .api prefix, both domains work similarly and are essentially interchangeable for accessing the Showpad platform.

    The .api subdomain:

    • The domain api.showpad.com (with the .api prefix) is specifically reserved for accessing Showpad's newer style APIs, like V4.
    • However, this .api.showpad.com subdomain does not support the older V3 API endpoints. This is why you encountered issues when trying to make requests to V3 endpoints using the .api.showpad.com subdomain.

    Correct Usage:

    • If you're working with the V3 API, you should continue using showpad.com or showpad.biz (without the .api prefix).
    • If you're using the V4 API or any newer APIs, you should use the api.showpad.com domain.

    Practical Example:

    For V3 API Calls:

    • Use: https://$company.showpad.com/api/v3/exports/events
    • OR: https://$company.showpad.biz/api/v3/exports/events

    For V4 API Calls:

    • Use: https://$company.api.showpad.com/api/v4/exports/events

    Summary:

    To avoid the errors you've been experiencing, make sure you're using the correct domain based on the API version you're trying to access:

    • V3 API → Use showpad.com or showpad.biz (without .API).
    • V4 API → Use api.showpad.com.

    If you stick to these guidelines, your API calls should work as expected.

    0
  • Tim Funk

    This was helpful ... Still having issues so I have a support ticket open to clarify licensing and token usage

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post

Get the latest apps and email integrations

iOS App Store Google Play Store Microsoft Store