How to Get the Most from Your Multichannel Funnel Reports

Multichannel funnel reports are incredibly popular because they answer critical business questions:

• Marketing Attribution: How do various marketing channels contribute to conversions?
• User Journeys: What paths do customers take before converting?
• ‍Optimization Opportunities: Where are the bottlenecks or inefficiencies in the sales funnel?

However, this article is not going to focus on the report itself. Oh, yes, we'll provide a SQL query, sure, but we would like also to discuss one more important topic: relationships between a data analyst and business users, reporting handoff, and the way we envision this should happen.

As we navigate these challenges, it becomes clear that the traditional ways of managing reports are not only inefficient but can hinder decision-making processes.

The Challenges with Traditional Reports

However, a report has a rigid structure that provokes further ad-hoc adjustments. Business users naturally get curious, with more questions that turn into follow-up requests to add more detailed data or view it from a different angle.

It's common, but it involves significant delays and time investment:

#1: Business users wait on data analysts to deliver data that will help answer questions and delay insights and decisions.

#2: Analysts spend significant time handling ad hoc requests for filtering, date range adjustments, or granularity changes.

There is a way to anticipate these ad-hoc questions and save significant time for both sides.

Here are some of the most frequent requests our data analytics team encounters when maintaining multichannel funnel reports for our clients:

• Date Range Adjustments: “Can we look at just last month instead of the last quarter?”
• Channel-Specific Filtering: “Can you show results for only organic and paid search?”
• Granularity Changes: “Can we get a detailed view of events instead of just aggregated numbers?”
• Geographic Focus: “Can we see the performance of a specific country or a region?”
• Metric Adjustments: “Can you add the product-level details to the report?”

Now, let’s explore how the new methodology implemented by OWOX streamlines operations and empowers business users.

The New Way of Reporting with OWOX

With OWOX Reports extension for Google Sheets, you, as a data analyst, can enable business users to answer such questions on their own, significantly improving the time required to make decisions.

This reduces the workload on the data analytics team and preserves their focus.

The best part? Business users don't even need to see or edit SQL or have access to a data warehouse (e.g. Google BigQuery) to be able to create, run, filter, or sort, and the schedule refreshes for the reports they need!

Plus, they will be able to visualize report data in just a few clicks.

Here's how you can achieve it:

Let's say you have a report like the one below.

For simplicity, we are using the BigQuery public dataset for GA4 BigQuery data. It's free to access, so feel free to use the SQL we provide to query it right from your Google Sheets with OWOX Reports if you want to experiment with the output.

1WITH
2  user_journeys AS (
3    SELECT
4      user_pseudo_id,
5      traffic_source.source AS channel,
6      traffic_source.medium AS medium,
7      geo.country AS country,
8      event_name,
9      event_timestamp,
10      ecommerce.purchase_revenue_in_usd AS revenue
11    FROM
12      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
13    WHERE
14      event_name IN ('page_view', 'add_to_cart', 'begin_checkout', 'purchase')
15  )
16SELECT
17  user_pseudo_id,
18  channel,
19  medium,
20  country,
21  event_name,
22  SUM(revenue) AS total_revenue
23FROM
24  user_journeys
25GROUP BY
26  user_pseudo_id, channel, medium, country, event_name
27ORDER BY
28  total_revenue DESC

‍

It provides the following output report structure:

• User ID;
• Channel (Traffic source);
• Medium (Traffic medium);
• Country;
• Event Name;
• Total Revenue.

It's already enough to create the basic multichannel report. Our public dataset only displays Google channels… However, this should be enough for our example. If you run the same query but replace the project_id and the dataset_id with your GA4 BigQuery Export data, you'll get the same report with your data.

We know that more clarifying questions will follow. To save valuable time for our business users (and data analysts as well), we will make the report's data customizable.

It only requires a couple of extra actions from our side.

Adding Parameters to Enable Business Users to Customize Their Reports

We may craft SQL to handle the ad-hoc requests to narrow or expand the report output by channel, country, event_name, or basically anything based on the most common questions that occur in your company.

For this, we will be using the Dynamic Parameters and Shared Data Marts. Both options are available when you use the OWOX Reports Extension.

The query below is the same query we've already shared above, but we included the Parameters syntax this time.

This will enable our business users to adjust the output of the query in the visual interface without any SQL knowledge:

1/** Required Parameters: -- required means the parameter will contain a pre-defined/prefilled value upon data export
2    {PurchaseDateFrom default="2021-01-01" type="datetime"} -- type "datetime" enables a calendar picker
3    
4    Optional Parameters:
5    {Channel default="" type="select" values=",(direct),Other,google,shop.googlemerchandisestore"} -- type "select" provides the user with a dropdown selector
6    {Medium default="" type="input"} -- type "input" is free input to filter on keywords
7    {Country default="" type="input"}
8**/
9
10WITH
11  user_journeys AS (
12    SELECT
13      user_pseudo_id,
14      traffic_source.source AS channel,
15      traffic_source.medium AS medium,
16      geo.country AS country,
17      event_name,
18      TIMESTAMP_MICROS(event_timestamp) AS event_timestamp,
19      ecommerce.purchase_revenue_in_usd AS revenue
20    FROM
21      `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
22    WHERE
23      event_name IN ('page_view', 'add_to_cart', 'begin_checkout', 'purchase')
24      AND event_timestamp >= UNIX_MICROS(TIMESTAMP('{PurchaseDateFrom}')) -- date range parameter
25      AND (TRIM('{Channel}') = '' OR traffic_source.source = '{Channel}') -- enables users to specify channel(s) in the output
26      AND (TRIM('{Medium}') = '' OR traffic_source.medium = '{Medium}') -- same for medium
27      AND (TRIM('{Country}') = '' OR geo.country = '{Country}') -- and for country 
28  )
29SELECT
30  user_pseudo_id,
31  channel,
32  medium,
33  country,
34  event_name,
35  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', event_timestamp) AS event_timestamp,
36  SUM(revenue) AS total_revenue
37FROM
38  user_journeys
39GROUP BY
40  user_pseudo_id, channel, medium, country, event_name, event_timestamp
41ORDER BY
42  total_revenue DESC

‍User Experience and Report Customization

Here's what it looks like for the business user when they modify the report in the sidebar:

Because Parameters are inserted inside SQL itself before it runs (as compared to Filters that are applied on top of the query result), it enables the business users to get exactly the data they need, even if it was not specified in the output of the original query.

Filling keywords and fields in the form is enough to get the result, so they don't need to know or interact with SQL to make changes at any point.

Using parameters may be helpful to address questions related to specific dates or periods, dimensions such as geo or channels, including additional metrics, and so on.

Sharing and Access Management

Now we have a Data Mart that provides the report that can be easily adjusted to include additional columns or include or exclude extra metrics and dimensions. The only thing missing is an option for a business user to run the query on BigQuery on their own once they configure the report.

Usually, you would run the query for them and share the spreadsheet with the data.

But this also means that every time the business users need a change in the data or the schedule, they would need to reach out instead of composing the report they need.

Most likely, you are sharing the data in a spreadsheet because of two reasons:

a) Business users don't have access to BigQuery, so you export data for them. They don't really need to have the BQ access either - it's not their job to work with the data warehouse, after all.

b) Business users don't want or need to do anything with SQL. Not to mention that having them fiddling with SQL is an overhead for the data analyst. Nobody wants an endless stream of debug questions, right?

So, sharing a spreadsheet seems like a convenient way, until the business users need some adjustments.

Alternatively, you may simply share permission to update the report using the OWOX BI extension.

For a business user, it means that they will be able to apply parameters and filters, and set up their own update schedule. They would press "Run" and it will simply work. Whenever they need to create a new one or adjust the results, they may do it on their own.

What about SQL or access to BigQuery?

That's the awesome part. We've handled this for both analysts and business users:

• Reports extension will utilize the access that the analyst specifies when sharing the data mart.
• Business users with shared access won't be able to open the SQL in the Data Mart shared, so it remains intact. Business users won't even see the SQL tab at all.

However, the invited users would be able to configure their own set of parameters, filters, and refresh schedule (that works independently for every user), and get the report based on BigQuery data without the need to bother data analysts with quick adjustments - all built inside the tool.

‍Benefits of Sharing Data Marts with Business Users

In the example of our multichannel funnel report, we went from the data export that requires a data analyst to maintain to a data mart that business users can use to create multiple reports and customize ad hoc on their own, all without the need to access SQL or BigQuery.

In case the user needs to see the channel performance by specific country or region, compare reports by date, or analyze specific channels, all they need to do is to type that in the sidebar of the extension and hit "Run". If the data mart includes enough data, different teams may even use the same data mart to configure multiple reports that analyze the same data from a different angle.

This may save weeks of turnaround time and the need for multiple stakeholders to explain and follow up on the requirements.

Try it for free with OWOX Reports Extension.