Query Tool

The Query tool lets you access a wide range of data through a user friendly interface, expanding the kinds of reports you can put together using the dashboard. This functionality is available to admins, analysts, developers and users with read-only access. The tool includes data on: 

• Comments
• Customer manual reviews
• Customers
• Devices
• Disputes
• Locations
• Orders
• Order items
• Payment methods
• Transactions

In order to run a query, you will need to select the source or sources, date range and the fields you would like to see in the report. You can also add filters via the 'Add filters' button. The query tool filter supports both AND/OR filters. The option to apply an OR filter will appear once you have already applied a filter. You can select it from the drop down that will show 'AND' by default. Once you have selected OR, this condition will apply for every additional filter you add. 

 

Once you've successfully run a query, the results will be returned in a table which you can export as a  CSV. Please note, if the download exceeds our 1GB limit, you may need to adjust the date range or fields selected. 

 

Selecting multiple sources

You can select multiple data sources for your query. What you choose as a main source impacts the results of your query. Every row from the selected main source will be returned and additional data from the related source will be added where they match to an ID in the main source.

For example, if you selected ‘Comments’ as your main source and added ‘Customers’ as a related source and set the date range to last 7 days, this would return all comments made in the last 7 days as well as the additional customer data associated with any customers with a comment in the last 7 days.

If you selected ‘Customers’ as your main source and added ‘Comments’ as related source, you would get a table with data showing all customers from the last 7 days as well as any comment data associated with any of those customers.

Due to the underlying structure of the data, not every source is possible to combine. 

 

Aggregate queries 

Aggregate queries allow you to easily build complex queries which perform calculations on your data. This allows you to build queries to explore, analyse and summarise your data in new ways - without any SQL experience.

Aggregate queries support the functions: count all, count unique, sum, average, minimum and maximum.

You can segment the results by various time ranges, including by hour, and by any other field from the source tables. Aggregate queries use the existing real-time sources, which provide an up-to-the-second view of your data.

Aggregate queries work with related sources, allowing you to segment on fields which aren't available in the main source.

Here are a few examples:

Example 1: Customer recommendation counts

This example shows how to build a query which will count all the customers that were given a recommendation of ALLOW, REVIEW or PREVENT for the last week.

1. Click the “Select source” button and select “Customers” as the main source
2. Click the “Select fields” button to edit the fields that will be included in the query
   1. For Query Type select “Aggregate Query”
   2. For Aggregate Fields select “Customer ID” and “Count All”.
   3. For Segment By Field select “Recommendation”
   4. For Date Range Field select “Updated At”
   5. For Segment By Time select “Total”, which means the results will not be segmented by any time period
3. Click the “Select date” button to specify the date range over which to run the query. Select “Last 7 days”
4. Click “Run query” to retrieve the results

Example 2: Customer Score Distribution

1. Click the “Select source” button and select “Customers” as the main source
2. Click the “Select fields” button to edit the fields that will be included in the query
   1. For Query Type select “Aggregate Query”
   2. For Aggregate Fields select “Customer ID” and “Count Unique”.
   3. For Segment By Field select “Score”
   4. For Date Range Field select “Updated At”
   5. For Segment By Time select “Total”, which means the results will not be segmented by any time period
3. Click the “Select date” button to specify the date range over which to run the query. Select “Yesterday”
4. Click “Run query” to retrieve the results
5. Click the heading of the Score column to sort the results by score.

The results will show the number of customers which received each score. Scores are in the range 0 - 100. A score of 100 means the customer is highly likely to be fraudulent.

You can export the results as a CSV file and use a spreadsheet application to plot the results as a bar chart to see the distribution of scores.

Example 3: Count ATO reviews by reviewer

This example shows how to build a query that will show the number of times each team member manually reviewed an account as ATO in the last 30 days.

1. Click the “Select source” button and select “ATO Reviews” as the main source
2. Click the “Select fields” button to edit the fields that will be included in the query
   1. For Query Type select “Aggregate Query”
   2. For Aggregate fields select “Customer ID” and “Count All”
   3. For Segment by Field select “Reviewer Name”
   4. For Segment By Time select “Total”, which means the results will not be segmented by any time period
3. Click the “Select date” button to specify the date range over which to run the query. Select “Last 30 days”
4. Click “Run query” to retrieve the results

Graphs

The graph feature automatically generates a graph from the results of an aggregate query, making it easier and faster to discover trends in your data. To hide the graph, click the ‘hide graph’ button.

The graph can be customised to display absolute values as an overlay or stacked graph, and clicking "Percentage" will display the results as a percentage of the total.

Please note you will only be able to graph queries with a maximum of 1,000 results. If the query returns more results, you need to narrow your query parameters to use the graph feature.

Save and share queries 

You can save queries for yourself, your team or a specific group of users for regular reporting.

 

Clicking on 'save query' will display a popup that will prompt you to add a query name and description and set who has visibility of the query.

 

Type in the usernames you wish to share the query with into the 'share with' search bar. You can then choose whether these users have 'Editor' or 'Viewer' permission by selecting from the drop-down box. If you later wish to remove a users access to a query, you can change their permission to 'No Access'.

 

These advanced sharing options are exclusively available to admin users and not yet accessible for Developers, Analysts, or Read-Only users. Users with these permission levels can still share queries via URL.

 

Once shared, the query will appear at the top of the 'Shared' tab for the selected users.

 

 

Preset queries

Preset queries are pre-populated in the query tool by Ravelin. This useful set of queries helps you benefit from the query tool faster by kickstarting your analysis and can help educate new users on the types of reporting and analytics the query tool is capable of.

You can find preset queries under the presets tab in the saved queries side bar. Clicking on a preset query will automatically populate the inputs in the tabs at the top. You only need to click 'run query' to generate the results. Please note if you make any changes to the query, you must run it again for them to be reflected in the results.

If you want to make changes to a preset query, simply change the parameters and click save query. Then enter a query name and select visibility of "only me" or "my team" from the save query window. This will create a brand new query shared with the users you specified.

The underlying data 

The data within the Query tool is in near real-time for all of the data sources. 

Query limits 

There is a total query limit per client of 10TB per month. If you exceed this limit, you will not be able to run any more queries until the first day of the next month. Additionally, there is also a limit in place to stop single queries which are too expensive from running. If you hit these limits you should see an error message.