Gergő Tisza has uploaded a new change for review. https://gerrit.wikimedia.org/r/160779
Change subject: Add relative timeseries ...................................................................... Add relative timeseries Add a relative timeline which looks like the absolute one but shows per-step survival rates and makes it easier to glimpse trends. Also rename the old "relative" to "overview" and make some tiny cosmetic fixes. Change-Id: I72fa2d446927f3ddcf00f3f0f25ea14163320123 Mingle: https://wikimedia.mingle.thoughtworks.com/projects/multimedia/cards/862 --- M build-uw-funnel-tsvs M uw-funnel/absolute.sql A uw-funnel/overview.sql M uw-funnel/relative.sql 4 files changed, 59 insertions(+), 23 deletions(-) git pull ssh://gerrit.wikimedia.org:29418/analytics/multimedia refs/changes/79/160779/1 diff --git a/build-uw-funnel-tsvs b/build-uw-funnel-tsvs index 009de29..b2ecd0e 100755 --- a/build-uw-funnel-tsvs +++ b/build-uw-funnel-tsvs @@ -16,8 +16,7 @@ tsvpath=$TSV_DIR/uw-funnel-relative.tsv echo "Updating UploadWizard relative funnel data..." -# limn can't handle headers; survival rate is meaningless for the last step -$MYSQL_CMD < $sqlfilepath | tail -n+2 | head -n-1 > $tsvpath +$MYSQL_CMD < $sqlfilepath > $tsvpath mv -f $tsvpath $PUBLIC_DIR/upload-wizard-funnel-relative.tsv sqlfilepath="$CHECKOUT_DIR/uw-funnel/absolute.sql" @@ -27,4 +26,12 @@ $MYSQL_CMD < $sqlfilepath > $tsvpath mv -f $tsvpath $PUBLIC_DIR/upload-wizard-funnel-absolute.tsv +sqlfilepath="$CHECKOUT_DIR/uw-funnel/overview.sql" +tsvpath=$TSV_DIR/uw-funnel-overview.tsv +echo "Updating UploadWizard funnel overview data..." + +# limn can't handle headers; survival rate is meaningless for the last step +$MYSQL_CMD < $sqlfilepath | tail -n+2 | head -n-1 > $tsvpath +mv -f $tsvpath $PUBLIC_DIR/upload-wizard-funnel-overview.tsv + echo " Done!" diff --git a/uw-funnel/absolute.sql b/uw-funnel/absolute.sql index 7e2ece4..8f5b103 100644 --- a/uw-funnel/absolute.sql +++ b/uw-funnel/absolute.sql @@ -7,8 +7,11 @@ SUM(CASE WHEN event_step = 'thanks' THEN 1 ELSE 0 END) AS 'thanks' FROM UploadWizardStep_8851805 -WHERE timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) -AND timestamp < TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) +WHERE + timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) + AND timestamp < TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) GROUP BY datestring -ORDER BY datestring ASC \ No newline at end of file +ORDER BY + datestring ASC +; \ No newline at end of file diff --git a/uw-funnel/overview.sql b/uw-funnel/overview.sql new file mode 100644 index 0000000..a2287df --- /dev/null +++ b/uw-funnel/overview.sql @@ -0,0 +1,27 @@ +SET + @survivors = NULL -- holds number of users reaching the next step +; + +SELECT + step, + @survivors / survivors AS relative, + @survivors := survivors AS absolute +FROM ( + SELECT + event_step AS step, + COUNT(*) AS survivors + FROM + UploadWizardStep_8851805 + WHERE + timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) + GROUP BY + event_step + ORDER BY + -- we need to calculate (users reaching next step / users reaching current step) + -- but there is no way to know abou the next row, so we reverse the ordering + -- here, then reverse it again in the outer select + survivors ASC +) uploadwizard_funnel_data +ORDER BY + absolute DESC +; diff --git a/uw-funnel/relative.sql b/uw-funnel/relative.sql index d732bce..3c7070c 100644 --- a/uw-funnel/relative.sql +++ b/uw-funnel/relative.sql @@ -1,26 +1,25 @@ -SET - @survivors = NULL -- holds number of users reaching the next step -; - SELECT - step, - @survivors / survivors AS relative, - @survivors := survivors AS absolute + datestring, + file_abs / tutorial_abs AS tutorial, + deeds_abs / file_abs AS file, + details_abs / deeds_abs AS deeds, + thanks_abs / details_abs AS details FROM ( SELECT - event_step AS step, - COUNT(*) AS survivors + CONCAT(SUBSTRING(timestamp, 1, 4), '-', SUBSTRING(timestamp, 5, 2), '-', SUBSTRING(timestamp, 7, 2)) AS datestring, + SUM(CASE WHEN event_step = 'tutorial' THEN 1 ELSE 0 END) AS tutorial_abs, + SUM(CASE WHEN event_step = 'file' THEN 1 ELSE 0 END) AS file_abs, + SUM(CASE WHEN event_step = 'deeds' THEN 1 ELSE 0 END) AS deeds_abs, + SUM(CASE WHEN event_step = 'details' THEN 1 ELSE 0 END) AS details_abs, + SUM(CASE WHEN event_step = 'thanks' THEN 1 ELSE 0 END) AS thanks_abs FROM UploadWizardStep_8851805 - WHERE timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) + WHERE + timestamp >= TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) + AND timestamp < TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) GROUP BY - event_step - ORDER BY - -- we need to calculate (users reaching next step / users reaching current step) - -- but there is no way to know abou the next row, so we reverse the ordering - -- here, then reverse it again in the outer select - survivors ASC -) uploadwizard_funnel_data + datestring +) uw_funnel_absolute ORDER BY - absolute DESC + datestring ASC ; -- To view, visit https://gerrit.wikimedia.org/r/160779 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I72fa2d446927f3ddcf00f3f0f25ea14163320123 Gerrit-PatchSet: 1 Gerrit-Project: analytics/multimedia Gerrit-Branch: master Gerrit-Owner: Gergő Tisza <[email protected]> _______________________________________________ MediaWiki-commits mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
