Filters in ChurnIQ Studio are essential for data analysis, ensuring that the data sets that you want to display are narrowed down based on a specific criteria. Limiting the data presented in your dashboards makes it easier to analyze and derive proper insights, the ones that will be relevant to your audience.
There are multiple ways of using filters in ChurnIQ Studio as they are applicable to different measures or dimensions at various stages of report creation.
How to add filters?
The first thing you need to do is to extract the necessary information from the database (run a query) and enter the Explore section to add the filters. You can find the “Filter” tab in the upper bar.
Select the field that you want to filter and add a numeric/text condition (e.g. equals, contains).
Click the Run button to apply the filters and update the Results.
Use an advanced filtering option if you need to process the data further.
Use the filters combined with yes/no logic for more complex queries. These expressions allow comparisons and logical operators (AND, OR). The filter will determine if the condition is met (returns values) or not (returns nulls).
Choose date filtering to specify the period you want to analyze. Use the dropdown menu.
Use advanced text filtering to specify the conditions.
Important: Remember to rerun the query after each time you change the filters.
Example: You are a broadcaster and want to measure the data on your active subscribers in the last 3 months. What you have to do is to open your “Explore” based on the "Broadcasters" table. In the filter section, find the "is_active" field. Then click on the filter and choose "Yes" to only see broadcasters with active status. This will filter your Explore results to show only broadcasters who meet the Yes/No criteria based on your chosen approach. Next, you choose the data filtering and set “is in the last 3 months” to extract only data from a fixed time period. You can combine these filters with other filters in the Explore to further refine your results.
All the details concerning advanced filtering can be found in Looker Documentation.
How to create custom measures in ChurnIQ Studio?
Custom measures are user-defined metrics that allow for creating specific calculations and aggregations based on the existing fields in the data model. Unlike standard measures that come predefined, custom measures let you tailor calculations to your unique business requirements. They can be useful whenever you need to calculate a sum, average or ratio that isn’t available by default.
Follow the steps to create custom measures:
Go to Explore → Add → Custom measure
Define your measure. Select the field that you want to measure and choose the type of measure (e.g. sum, average, min, max, etc.)
Provide a name for your custom measure.
Click on field details to choose the formatting for displaying your data (percentage, currency symbols, etc.)
Save the new custom measure. Your table with the new custom measure is ready!
Important: remember to rerun the query each time you add custom parameters.
How to create a custom dimension in ChurnIQ Studio?
In ChurnIQ Studio dimension is a parameter that can be used to organize, group or filter the query results. It can represent an attribute or characteristics of your data, and provide the description of it.
Creating a custom dimension in ChurnIQ Studio allows for:
- Enhanced analysis. Custom dimensions enable creating specific groupings and categorisations not available in the default dataset, enabling more tailored analysis.
- Improved flexibility. You can adapt your data model to evolving requirements without changing the underlying database schema.
- Personalized reporting. Broadcasters can create dimensions that reflect their unique business logic or current reporting needs.
- Complex calculations. They facilitate complex calculations and transformations that standard dimensions may not cover.
Follow the steps to create your own custom dimensions:
Go to Explore →Custom Fields → Add → Custom dimension
The next step is to define your dimension. Enter the name of your custom dimension and write the formula or ML logic using LookML expression in the Expression editor.
Once you start typing, a list of available columns and options will appear on the left.
Save all your settings by pressing on the Save tab.
Your custom dimension is ready!
The complete documentation concerning LookML expressions can be found in Looker functions and operators.
How to create custom table calculation in ChurnIQ Studio?
A custom table calculation in ChurnIQ studio is a way to perform calculations on the data that is already present in Explore as a result of running a query. It allows you to create new fields based on the existing data using Looker’s expression language. These calculations are performed on the data after it has been fetched from the database, making it possible to add and manipulate without altering the underlying dataset or needing to modify SQL queries.
Follow the steps to create your own custom table calculation:
Select the desired fields and add them to your table.
Run a query.
Click on Custom fields and choose Add Table Calculation.
Define calculation: name your calculation and enter the formula using Looker’s table calculation functions and operators.
Click on Save button to create the calculation.
Example: A broadcaster wants to see the performance of his offers by measuring the revenue. To get the clear view, he decided to compare the recent revenue with the one from the previous period. The difference should be expressed using the percentage.
The steps to create the custom table calculation of revenue are as follows:
Run a query that includes the Revenue measure.
Add custom table calculation with the name Revenue Change.
Define the calculation.
You can use the offset function to calculate the revenue. Copy/paste the following expressions:
(${churniq_studio_transactions.publisher_revenue_publisher_currency} - offset(${churniq_studio_transactions.publisher_revenue_publisher_currency}, -1)) / offset(${churniq_studio_transactions.publisher_revenue_publisher_currency}, -1)
Save the calculation. The completed table will display the percentage of Revenue change compared to the previous month.