Plugin to provide funnel analysis report

[wicked]

a plugin which tracks visitor behaviour for defined funnels where a funnel represents a serie of ordered pages a visitor has to browse / complete (for instance a checkout process).

Interesting metrics to know would be

• # of visitors starting the funnel
• continuing the to next step
• the (onsite) page they're going to when they step out of the funnel
• the page they use to reach the particular funnel step.

Funnel charts UI, see  http://peltiertech.com/WordPress/bad-graphics-funnel-chart/

[mandark]

this would be a great addition to piwik. it is one feature, google analytics has, but piwik doesnt. the ability to analyse a funnel-process is very important for a lot of companies. so this feature would make piwik even more attractive to them.

[vipsoft]

see also ticket #460

[thegrieve]

I'm currently attempting to make a plugin to this extent for my own purposes, but just thought I'd share a very simple SQL snippet that could be useful.

SELECT v.location_ip, a.name FROM `piwik_log_action` a, `piwik_log_visit` v, `piwik_log_link_visit_action` l WHERE (v.idvisit = (SELECT idvisit FROM `piwik_log_visit` ORDER BY idvisit DESC LIMIT 1) AND v.idvisit = l.idvisit) AND (l.idaction_url = a.idaction) ORDER BY l.idlink_va

This will retrieve the entire visitor path for the last visitor, this could be ammended to do what you actually want very easily.

I'll update again when I put an interface to what is currently a colletion of SQL snippets.

Ryan

[matt]

The bigger challenge of this ticket is to report on each specific subpath from a given page, but efficiently and with large data sets. Rather than path by visitor, we are interested in per page metric.

[thegrieve]

I understand the bigger scope here. I was providing the snippet as a general POC that the data is available in exisiting databases and can be mined retrospectively (an advantage over existing proprietary solutions), and also to provide a starting point for anyone requiring a small-scale quick solution via their own database.

My current development is able to retrieve statistics for any given path, (since this data is easily mined) on a per-visit/per-page basis but it is not efficient.. yet. I'm currently trying the possibility of archiving data relevant to pre-defined funnel rulesets on *.compute and then only having to retrieve the relatively small set of data for the period since last archive.

(An interesting side development of the way I'm currently looking at accessing data, is that a real-time web map, showing paths to from any given url and its "neighbours" would be a trivial development beyond this. If such a plugin doesnt currently exist.)

Ryan

[matt]

I think it's going to be very hard (too hard?) to scale Funnel analytics for Piwik. This would significantly slow down archiving for sure.

However real time visualization of paths on a map, if done well, would certainly be a killer :)

[crowbot]

Hi,

As part of my work at  http://mysociety.org I've started work on a plugin to produce a funnel report. The code is available here:

 http://github.com/mysociety/funnels

It's pretty early days, but it now allows you to define a series of funnel steps associated with a goal, and will track visits to those steps and visualize the results. It's developed against piwik 0.6.4. It takes quite a strict approach to the idea of a funnel at the moment, requiring that steps are visited consecutively in one visit in order for a funnel conversion to be recorded. Any feedback welcome!

[vipsoft]

crowbot: glad to see someone working on this. Can you post some screenshots?

The strict approach to a funnel is flawed because it's sequential and only represents the percentage not filtered out. (Technically, it's a sieve.) In reality, people may enter at different points along the path, or even skip steps. (This might even be viewed as a subset of click path analysis.)

License. We'll have to provide some license guidance if we include Funnels with the core Piwik distribution, but GNU Affero GPL is ok.

[crowbot]

Piwik funnel module screenshot

[crowbot]

Screenshot of creating/editing funnel

[crowbot]

vipsoft: I've uploaded a couple of screenshots as requested. I think you're right, the strict approach definitely has flaws. However, with entry and exit stats you do have some indication of where people are going when they are filtered out, and where they join (or rejoin) the funnel from. I'm going to gather some feedback from our team on whether this interpretation of what a funnel is is useful to them in practice, or if they'd prefer something more fuzzy.

[vipsoft]

Nice. Have you built any widgets, similar to the bar chart and multi-line (evolution) chart discussed in peltiertech blog? Such might be useful for the dashboard or a funnel overview screen.

[crowbot]

Not yet. Agreed that would be a nice addition for the overview.

[matt]

That looks really good and interesting :)

Can you please post the current status of the code (after maybe a quick review as per coding standards  http://dev.piwik.org/trac/wiki/CodingStandard if you have time). It would be great to look at the code and give some feedback early in the process, so we can hopefuly include this in piwik core in the future? Cheers! great work

[vipsoft]

I'm going to try and review it this week.

Silly question: does a funnel path have to be associated with a goal?

[vipsoft]

I'll try to find more time for review, but from a cursory examination:

• this nested loop would concern me re: the impact on tracker performance in recordFunnelSteps:

  // pseudo-code
  foreach($funnels as $funnel) {
      foreach($steps as $step) {
          if($step['url'] == $actionUrl || $step['name'] == $actionName) {
              SELECT ...
              INSERT ...
          }
      }
  }
  

• not obvious from the funnel definition page that 'Name' corresponds to action name, and that a "match" for a particular step is either by url or action name; personally, I expected Name to be a free-form description of the step
• answering my question in comment:18, there should be some help message on the funnel definition page that the goal is implicitly the last step; the dropdown list should exclude manually triggered goals as these have no associated URL
• API.php: getFunnels, getFunnelsByGoal: should check has view access
• unit test fails because piwik_funnel table hasn't been created

  Exception: Test_Piwik_Funnels -> test_addFunnel -> Unexpected exception of type [Zend_Db_Statement_Exception] with message [SQLSTATE[42S02]: Base table or view not found: 1146 Table 'piwik_tests.piwik_funnel' doesn't exist] in [/path/to/piwik/libs/Zend/Db/Statement/Pdo.php line 234]
  

• integration tests fail once plugin is activated (i.e., no expected results)

[crowbot]

Replying to vipsoft:

I'll try to find more time for review, but from a cursory examination: [...]

OK, that gives me a few improvements to be going on with :)

[vipsoft]

recordFunnelSteps() might benefit from the tracker cache to replace the call to Funnels_API getFunnels():

• The tracker cache are files in tmp/cache/tracker to reduce the number of SQL queries by the tracker.
• In plugins/SitesManager/SitesManager.php, recordWebsiteDataInCache() hooks on "Common.fetchWebsiteAttributes" to cache site data. The funnel steps for each site could also be saved this way.
• In API.php, any update of the site table is followed by a call to Piwik_Common::regenerateWebsiteCacheAttributes().
• recordFunnelSteps would call Piwik_Common::getCacheWebsiteAttributes( $idSite ) to access the tracker cache (which may already be loaded at this point), thus avoiding a SELECT during tracking.

[vipsoft]

I'm falling asleep, but I want to record some thoughts re: the nested foreach loop:

• if steps are unique, INSERT IGNORE could replace SELECT + INSERT; then inner could "break" after the INSERT
• alternately, might reduce to a single loop by flipping the funnel data structure around to quickly lookup url/name-to-funnel(s)

[ibmed]

Sorry if I'm posting this in the wrong place:

Is it possible to add manual goals tracking support to the plugin? Some actions just can't be tracked as URLs.

[matt]

crowbot, it would be great to work together to put this plugin into Piwik core.

Do you have a more recent version than the one vipsoft reviewed?

I would like to do a review, but maybe you are making some changes at the moment. Please let me know if you would like a review now or a bit later. Thanks!

[crowbot]

Replying to matt:

crowbot, it would be great to work together to put this plugin into Piwik core. Do you have a more recent version than the one vipsoft reviewed? I would like to do a review, but maybe you are making some changes at the moment. Please let me know if you would like a review now or a bit later. Thanks!

Hi matt,

I did start work on replacing the tracker loop with an "INSERT IGNORE" statement - but I haven't really tested it at all - I'll try and get that version checked in in the next week or so - so a bit later would be great!

Thanks

[matt]

crowbot, just one requirement before the review: could you please change the license to GPL to ensure we can safely include it in the Piwik core? It seems it is currently under AGPL but unfortunately this is not compatible with GPL. Thanks!

[crowbot]

Replying to matt:

crowbot, just one requirement before the review: could you please change the license to GPL to ensure we can safely include it in the Piwik core? It seems it is currently under AGPL but unfortunately this is not compatible with GPL. Thanks!

As far as I'm aware, AGPL3 and GPL3 should be compatible:

 http://www.gnu.org/licenses/gpl-faq.html#v3Notwithstanding

Can you tell me where the problem arises? Thanks!

[vipsoft]

(This'll be terse...I'm afk an using my iphone.)

the licenses are compatible to the extent that we can redistribute gpl and agpl code together, but is problematic if copying/pasting code between the two (eg as in refactoring).

I jotted some notes in #1826 to document the effect of the "network interaction" and "corresponding source" clauses, but will have to revise the text given the expanded scope, ie a request by the js tracking code to the piwik server (and thus, tracker plugins) qualifies as "network interaction"; also "corresponding source" means all modified source, not just those changes upon which a customized Funnels plugin might depend on. This changes the intent behind the decision to use the GPL (instead of AGPL).

For inclusion in core, I would prefer GPLv3 or later, so that there's less licensing confusion.

[crowbot]

Replying to vipsoft:

(This'll be terse...I'm afk an using my iphone.)

Thanks for that clarification. I wrote the plugin in the course of my job at mySociety so I'll need to check that the relicensing will be OK. I'll do that now.

[matt]

Thanks for checking crowbot. We believe this plugin is a great candidate for future inclusion in core and it would be really nice to work together on including it.

Of course, we would be happy if you write a blog post about how the plugin and include a link to your company's website if it helps.

[crowbot]

Replying to matt:

Thanks for checking crowbot. We believe this plugin is a great candidate for future inclusion in core and it would be really nice to work together on including it. Of course, we would be happy if you write a blog post about how the plugin and include a link to your company's website if it helps.

OK, license change done.

[vipsoft]

This is a brain dump, eg new reports / enhancements to this plugin.

• page views -> unique visits -> user sessions / unique visitors -> ROI / conversion tracking -> click paths / funnels -> propensity to repurchase

[matt]

As an update. I took some time to brainstorm the Funnel plugin, and I think we can do it with much better performance, no new table, but schema updates to existing tables. It is quite some work to rewrite though so I won't suggest this in the code review. I think the easiest is to keep maintaining this plugin for the time being as a Third Party plugin, then we can do the rewrite and reuse some code from this plugin (For example, the Report HTML/CSS which looks great).

To explain a bit my ideas in the implementation:

• introduce limitation of number of Goals per website (eg. 10 goals)
• for each goal, add funnel_first_step_1, funnel_last_step_1, funnel_ref_idaction_1, funnel_exit_idaction_1 in the log_visit table (_1 means for Goal id=1)

Then in Tracker, the funnel IDs would be updated on each page view, processing at each page view if the visitor enters/exits the funnel, and keeping track of where it enters from and where it exits from (similar to a mini visit inside a visit).

so I think tracking funnel could be done without any extra DB lookup, which would be great. Also during Archiving, it would be fast since all the fields to build the funnel report would be in the log_visit table and integers.

Hopefully one day we can implement this solution in core, in the meantime, this plugin is great for most users :)

[vipsoft]

My thought is that if we support multiple goal conversions before per visit, then Funnels and Goals should be more loosely coupled. This could then be expanded (subclassed?) for clickpath analysis and split a/b testing.

[nm]

Is anyone using this on piwik 1.1.1?

After installing, I'm just getting a blank page below the "Funnels Management" text.

[tyroden]

I installed it on 1.1.1 and found that as well. I have multiple websites tracking in my Piwik, and I can no longer select websites on the All Websites link at the top as the page is blank there too. Any ideas folks?

[crowbot]

Replying to tyroden:

I installed it on 1.1.1 and found that as well. I have multiple websites tracking in my Piwik, and I can no longer select websites on the All Websites link at the top as the page is blank there too. Any ideas folks?

Looks like the plugin needs some updating to work with piwik 1.1.1. I'll take a look at it next week.

[crowbot]

I've made a fix to the plugin and it's now working for me under 1.1.1.

[fbrem]

Replying to crowbot:

I've made a fix to the plugin and it's now working for me under 1.1.1.

crowbot, i cannot confirm that. after activating the plugin no statistic data is loaded in all pages (Dashboard, Visitors etc.) Maybe this is just with my installation, can anyone confirm that?

but thank you anyway for your effort.

[vipsoft]

fbrem: did you delete the files in tmp/cache/tracker/* ?

[fbrem]

vipsoft: jep, i did. Is the plugin working with your piwik installation?

[ksuther]

Has anyone been using this with 1.2.1? I was able to fix a couple of the runtime errors I encountered, but the funnels don't actually seem to be working.

[antlee]

Replying to ksuther:

Has anyone been using this with 1.2.1? I was able to fix a couple of the runtime errors I encountered, but the funnels don't actually seem to be working.

I'm trying this fork on 1.2.1:  https://github.com/msteffeck/funnels

[jj01]

Has anyone had any further luck with this plugin?

I am running 1.4 and get this error when I create a funnel: Fatal error: Call to undefined method Piwik_ArchiveProcessing_Day::queryConversionsBySegment() in /var/www/html/mobileab/httpdocs/piwik/plugins/Funnels/Funnels.php on line 175

The link for the above fork seems to be broken - it would be a great feature to have working

[jess]

For those interested, I have forked mysociety's Funnels plugin and it is now working with Piwik v1.5, with the following enhancements:

• Funnels can be defined by page title as well as URL
• A match doesn't have to be exact, it can now be; exact, 'contains', or a regular expression (the same as for Goals)
• A match can be case sensitive or not
• A manually triggered goal will now be correctly tracked as a funnel conversion for known visitors
• Can trigger a goal conversion by URL, such as:  http://piwik.example.com/piwik.php?token_auth=[Super_User_Auth_Token]&cid=[HEX_Id_of_Visitor_to_log_goal_hit_for]&idsite=[site_containing_goal]&idgoal=[id_of_goal]

Limitations:

• I haven't looked into the speed issue when archiving (inner loop with SELECT INSERT).
• There are no tests (Haven't looked into writing tests in Piwik yet).
• UI has been hacked a little so it's a bit less elegant than mysociety's original version

 https://github.com/jesstelford/funnels

If you have any feedback or want to help out (especially in the area of writing tests), feel free to fork the repo and send me a pull request with your commits :)

[kaystrobach]

will this plugin be part of the official 1.7 release? - (would like it ;) ) thanks kay