Modeling data for Funnel Chart

[corbusier]

Hi all, I am looking for a way to display a Conversion Funnel for our app. We have 7 different stages, and one user would have to go through the different stages in order to get to the end (sorry if I'm stating the obvious). 

Since we have multiple data sources, I've joined them all with a query that also pivots the data so it looks like this: 

App Version | User ID | Stage 1 timestamp | Stage 2 timestamp | Stage 3 timestamp  .... etc (with NULLs if the Stage doesn't exist). 

I see no way of using this data with the Funnel Chart. Should I model the data differently? We need the App Version to filter the report, but for the rest we just need the counts.

The second question is that the data isn't always reliable so you could have a User missing a couple of steps but still having Step 7 completed, for example. How do we deal with that? 

[v-huizhn-msft]

Hi @corbusier,

Have you resolved your problem? If it didn't, is there any chance to post your sample data or .pbix file for analysis? So that we can post solution which is close to your requirement. 

Best Regards,

Angelia

[corbusier]

Hi @v-huizhn-msft, I gave the layout of the data in the question, and would like to actually get the data from a working Funnel visualization so I can see what it looks like, then model my data to behave the same. If you would like to know what the data is like in the source, I can give you that: 

The main table contains the Started and Finished dates and current status of the User. We also have an Activity table that contains detailed activity of the User, from which we extract what is meaningful to us. Lastly, we also have an external Email service, from which we extract Clicks and Opens of the Emails that the user has received (one at minimum, two maximum).

[corbusier]

I was able to use the UNPIVOT function in SQL Server to get my data in the form that I needed it. I actually Googled "Funnel Chart Tableau" and the first blog had an example of how the data should look. 

http://www.evolytics.com/blog/tableau-201-how-to-make-funnel-charts/

The data should look like this: 

Gender	Step	Players
Female	1	371,393
Female	2	35,490
Female	3	36
Male	1	412,351
Male	2	36,741
Male	3	77

Whereas my data looked like this: 

Gender	Step 1 Players	Step 2 Players	Step 3 Players
Female	371,393	35,490	36
Male	412,351	36,741	77

UNPIVOT worked.

[v-huizhn-msft · Accepted solution]

Hi @corbusier,

I test it using your sample table.

Please replace the table header using 1,2 and 3. Right click the table->Power Query, select the 1,2 ,3 columns and click the unpivot columns(highlighted in red line).



Click the apply&close on home page, you will get expected result.

Best Regards,

Angelia

[corbusier]

Thank you, @v-huizhn-msft, I hadn't seen that option!

[LaurentCouartou]

You could add all your timestamps columns  (with a Count aggregation) to the Values role of your funnel chart, or even write a measure for every column. Did you try that?

Regarding the "missing steps", I would just go for a simple rule like "if we have a timestamp for step x, then we should have a timestamp for all previous steps", that I would enforce at the query level.