Create a funnel analysis tool with Redshift and Power BI in 5 minutes

If you're not collecting events from your product, get started right away!

Events are a great way to collect behavioral data on how your users use your data: what paths they take, what errors they encounter, how long something takes etc. When you have events, there isn't a lot you cannot analyze.


But once you start collecting events, you might get a little overwhelmed with analyzing those events. How about creating everyone in your company a nice funnel analysis tool?

I like simple solutions. I also like self-service analytics. This tool takes 5 minutes to set up, is quite powerful and can be used by anyone in your company. The way this funnel tool works is very simple:

• Choose a mandatory event
• Choose from a list of events to compare the first event to

Disclaimer: this simple tool will not consider the order of the events, but I did say it's a simple tool, didn't I?

We'll use Redshift with PowerBI, as it allows for Direct Query (which is especially useful when you might have millions of event records). Let's assume your event is a table in Redshift with (at least) the following columns:

1. A name of the event (e.g. "Logged In")
2. An owner for the event (e.g. a user identifier)

That's it. That's all you need. Sure, if you have stuff such as timestamps, you can take this further. But we don't need that for now. Let's create a couple views:

CREATE VIEW vw_event_name_owner AS SELECT DISTINCT event_name, owner FROM event;
CREATE VIEW vw_event_owner AS SELECT DISTINCT owner FROM event;

Be sure to pull these in with the Redshift connector, using Live Query.

The first, vw_event_name, we will use to select the mandatory event. We will map that to the second view, vw_event_name_owner, with a many-to-one two-way filter. We will then use the vw_event_owner to filter our event data, leaving only those events that have been produced by owners who have also produced the event we have selected in vw_event_name. Here's the relationship diagram.

Now, put a single-choice drop-down filter from the vw_event_name_owner -view and a multi-select filter from your event table. With any luck you should have something like this:

Voilà! You can publish it for others to use on PowerBI cloud. Once PowerBI cloud is provided with Redshift credentials and assuming your enterprise gateway is set up, the report will have a live connection to Redshift.