Writing a Complex Funnel

Creating a funnel query requires a structured approach to ensure clarity and proper formatting. Since funnel visualisations rely on well-organised datasets, it is important to follow best practices in query writing.

Key Guidelines for Writing Funnel Queries

• Use multiple sentences to break down complex requests.
• Be explicit in defining the required output.
• Maintain proper structure and punctuation to ensure accurate query interpretation.
• Format the data logically to align with funnel or other visualisation requirements.

Breaking Down a Funnel Query Example

Example Complex Query Request:

create 5 subqueries and the expected result is an output of 5 rows and 2 columns with each row containing the count and the label of each subquery. 1. list total registered customers, 2. list player count where lifetime deposit count >= 0, 3. list player count where lifetime deposit count >=1, 4. list player count where lifetime deposit count >=2, 5. list player count where lifetime deposit count >=3. Where acquisition source [organic]

⚠️ Text enclosed within [ ] is not interpreted as a keyword, ensuring it does not affect query processing.

We need to create five subqueries, with the expected output of five rows and two columns. Each row should contain:

1. The count of players that meet the specific criteria.
2. A label describing the criteria applied.

Query Breakdown:

1. Total Registered Customers – Retrieves the count of all registered customers.
2. Players with at Least 0 Deposits – Lists the number of players where lifetime deposit count >= 0.
3. Players with at Least 1 Deposit – Lists the number of players where lifetime deposit count >= 1.
4. Players with at Least 2 Deposits – Lists the number of players where lifetime deposit count >= 2.
5. Players with at Least 3 Deposits – Lists the number of players where lifetime deposit count >= 3.

Each subset of data is filtered to include only customers acquired through organic acquisition sources.

Why This Structure Matters

1. Clear Output Specification

   • The request explicitly states: "Create 5 subqueries and the expected result is an output of 5 rows and 2 columns."
   • This helps the system return results in a well-structured format.

2. Logical Segmentation of Data

   • The query defines incremental deposit counts, ensuring a natural progression in the dataset.
   • By structuring the query in a stepwise manner, each row logically builds on the previous one.

3. Use of Numbered Points and Commas

   • The numbered breakdown clarifies the query’s intent.
   • Proper punctuation improves readability and ensures the model interprets the request accurately.

By following this structured approach, funnel queries can be effectively built to support insightful visualisations. A well-organized query ensures that the data is meaningful, correctly formatted, and easy to interpret, making it suitable for funnels or any other analytical breakdowns.

The query:

The result: