funnel analysis in hadoop at etsy
Post on 12-Sep-2014
7.036 views
DESCRIPTION
As an ecommerce site with more than 800,000 different sellers, Etsy is particularly interested in understanding how shoppers find the items they seek. Part of this understanding involves attributing successful purchases to specific features on the site. This attribution model allows us to compare and refine Etsy’s features, but also provides valuable signals for A/B testing, search quality, and recommenders. However, the path to a successful handmade purchase often involves multiple features over the course of several visits. This talk will discuss the challenges of funnel analysis at Etsy and the corresponding deficiencies of several widely used web analytics tools. We’ll then dive into our event sequence matching tool, which we’ve successfully applied to hundreds of millions of visits in a single Hadoop job and is widely used across our big data stack. Finally, we’ll take a look at some of our applications of the tool and compare it to related work.TRANSCRIPT
![Page 1: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/1.jpg)
Funnel Analysis in Hadoop at Etsy
Steve Mardenfeld, Wil Stuckey, Matt Walker
Tuesday, March 12, 13
![Page 2: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/2.jpg)
Tuesday, March 12, 13
![Page 3: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/3.jpg)
Handmade MarketPlace
Tuesday, March 12, 13
![Page 4: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/4.jpg)
Tuesday, March 12, 13
![Page 5: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/5.jpg)
Data Driven Development
• Use data to make informed decisions
• Use data to evaluate the efficacy of our products
Tuesday, March 12, 13
![Page 6: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/6.jpg)
Funnel AnalysisFunnel Analysis
Tuesday, March 12, 13
![Page 7: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/7.jpg)
Registration Funnel
Tuesday, March 12, 13
![Page 8: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/8.jpg)
Registration Funnel
5 0 , 0 0 0 1 0 , 0 0 0
1 0 0 % 2 0 %
Tuesday, March 12, 13
![Page 9: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/9.jpg)
Registration Funnel
5 0 , 0 0 0 1 0 , 0 0 0
1 0 0 % 6 0 %4 5 , 0 0 0
9 0 %
1 5 , 0 0 0
3 3 %1 0 0 % 9 0 % 2 9 % 2 0 %
Tuesday, March 12, 13
![Page 10: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/10.jpg)
Funnels ++
• Funnels are more than just an optimization tool
• Use them to understand different pathways throughout our site
• Partition and compare these pathways by attributes
• A/B tests, categories, queries, cohorts
• Attribution model
Tuesday, March 12, 13
![Page 11: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/11.jpg)
Attribution Models
• Tie conversions and successes to specific products and actions
• Use to gain understanding of our users’ interaction with Etsy
• Help us to measure gains in A/B testing
• Easily compare different varieties of the same product
• Attribution techniques for internal and external attribution
Tuesday, March 12, 13
![Page 12: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/12.jpg)
Attributions Illustrated
Tuesday, March 12, 13
![Page 13: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/13.jpg)
Attributions Illustrated
• last product interaction - browse
Tuesday, March 12, 13
![Page 14: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/14.jpg)
Attributions Illustrated
• last product interaction - browse
• co-occurrence - search and browse
Tuesday, March 12, 13
![Page 15: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/15.jpg)
Attributions Illustrated
• last product interaction - browse
• co-occurrence - search and browse
• direct funnel attribution - search
Tuesday, March 12, 13
![Page 16: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/16.jpg)
Indirect Attribution Illustrated
Tuesday, March 12, 13
![Page 17: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/17.jpg)
Indirect Attribution Illustrated
• direct funnel attribution - shop home
Tuesday, March 12, 13
![Page 18: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/18.jpg)
Indirect Attribution Illustrated
• direct funnel attribution - shop home
• indirect funnel attribution - search
Tuesday, March 12, 13
![Page 19: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/19.jpg)
Aggregate Results
Search Clicked Listing Purchased5 0 , 0 0 0 2 5 , 0 0 0 5 , 0 0 0E v e n t s
Direct
Tuesday, March 12, 13
![Page 20: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/20.jpg)
Aggregate Results
Search Clicked Listing Purchased
Search Clicked Listing Shop Clicked Listing
5 0 , 0 0 0
5 0 , 0 0 0
2 5 , 0 0 0
2 5 , 0 0 0
5 , 0 0 0
1 0 , 0 0 0 5 , 0 0 0
E v e n t s
E v e n t s
Direct
IndirectPurchased
3 , 0 0 0
Tuesday, March 12, 13
![Page 21: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/21.jpg)
Aggregate Results
Search Clicked Listing Purchased
Search Clicked Listing Shop Clicked Listing
5 0 , 0 0 0
5 0 , 0 0 0
2 5 , 0 0 0
2 5 , 0 0 0
5 , 0 0 0
1 0 , 0 0 0 5 , 0 0 0
E v e n t s
E v e n t s
Search Clicked Listing5 0 , 0 0 0 2 5 , 0 0 0E v e n t s
Direct
Indirect
Combined
Purchased3 , 0 0 0
Purchased8 , 0 0 0
Tuesday, March 12, 13
![Page 22: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/22.jpg)
Segmenting Within Funnels
Search Clicked Listing5 0 , 0 0 0 2 0 , 0 0 0C o u n t s
Old AlgorithmPurchased
1 5 , 0 0 0
Search Clicked Listing1 0 0 , 0 0 0 6 0 , 0 0 0C o u n t s
New HotnessPurchased
1 5 , 0 0 0
Tuesday, March 12, 13
![Page 23: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/23.jpg)
Segmenting Within Funnels
Search Clicked Listing1 0 0 % 5 0 %S t e p
1 0 0 % 5 0 %To t a l
Old AlgorithmPurchased
4 0 %2 0 %
Search Clicked Listing1 0 0 % 6 0 %S t e p
1 0 0 % 6 0 %To t a l
New HotnessPurchased
2 5 %1 5 %
Tuesday, March 12, 13
![Page 24: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/24.jpg)
Segmenting Across Funnels
* Clicked Listing1 0 0 % 5 0 %S e a r c h
1 0 0 % 4 0 %B r o w s e
Purchased4 0 %3 0 %
1 0 0 % 6 0 %H o m e 3 6 %
1 0 0 % 6 2 %A c t i v i t y F e e d
1 0 0 % 4 7 %Ta s t e Te s t2 8 %3 1 %
1 0 0 % 4 5 %S e a r c h A d s 3 8 %
Tuesday, March 12, 13
![Page 25: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/25.jpg)
Democratized FunnelsHow do we make this awesomeness available for everyone?
Tuesday, March 12, 13
![Page 26: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/26.jpg)
Tuesday, March 12, 13
![Page 27: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/27.jpg)
Awesome infrastructure but...
• Must be an engineer to write your own queries
• Engineering resources become the bottleneck
• Hard to scale as the company grows
Tuesday, March 12, 13
![Page 28: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/28.jpg)
So we want to:
• Allow the data engineers to focus on higher priority things.
• Allow people to answer their own questions.
Tuesday, March 12, 13
![Page 29: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/29.jpg)
How do we do this?
Tuesday, March 12, 13
![Page 30: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/30.jpg)
The Path of Funnel Tools
google spreadsheets internal tools
• Funnel Cake
• Feature Funnel
Tuesday, March 12, 13
![Page 31: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/31.jpg)
Funnel Cake
Tuesday, March 12, 13
![Page 32: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/32.jpg)
Version 1
Tuesday, March 12, 13
![Page 33: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/33.jpg)
Real Time
Tuesday, March 12, 13
![Page 34: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/34.jpg)
Lets do it in PHP!
Tuesday, March 12, 13
![Page 35: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/35.jpg)
Why?
Tuesday, March 12, 13
![Page 36: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/36.jpg)
It’s the “Etsy Way”
• Already have existing infrastructure
• Operationally stable
• The same tools everyone else is using means a better adoption rate across Engineering
Tuesday, March 12, 13
![Page 37: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/37.jpg)
• Event Stream
• Code runs on every page view
• Simple matching system
• (ab)Used memcached as a temporary storage
• Rolled up to DB every min (near real time)
Tuesday, March 12, 13
![Page 38: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/38.jpg)
What happened?
Tuesday, March 12, 13
![Page 39: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/39.jpg)
Broader adoption!Over 75 Funnels were created
Tuesday, March 12, 13
![Page 40: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/40.jpg)
Shiny Visualized Results
Tuesday, March 12, 13
![Page 41: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/41.jpg)
• Funnels had to be setup ahead of time (no backfills)
• Reconciliation is hard
• Limited to events in our web clickstream (ios/android would be excluded)
• Scaling and Operational issues
• Difficult to maintain multiple stacks
Tuesday, March 12, 13
![Page 42: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/42.jpg)
Turns out that we don’t make Product decisions in real time
http://mcfunley.com/whom-the-gods-would-destroy-they-first-give-real-time-analytics
Tuesday, March 12, 13
![Page 43: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/43.jpg)
Version 2
Tuesday, March 12, 13
![Page 44: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/44.jpg)
Tuesday, March 12, 13
![Page 45: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/45.jpg)
Getting back on the elephant
• Able to carry over the User Interface from v1
• Standardized event sessionization
• Operationally supported infrastructure
• Nightly batch process
Tuesday, March 12, 13
![Page 46: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/46.jpg)
Feature Funnel
Tuesday, March 12, 13
![Page 47: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/47.jpg)
Attribution for ALL
Tuesday, March 12, 13
![Page 48: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/48.jpg)
Built in metrics
• Click through rate
• Listing impressions
• Purchase rate
• Purchase from shop rate
• Favorite from shop rate
• More...
-
Tuesday, March 12, 13
![Page 49: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/49.jpg)
Built in metrics
• Click through rate
• Listing impressions
• Purchase rate
• Purchase from shop rate
• Favorite from shop rate
• More...
- Direct Attribution
- Direct Attribution
- Direct Attribution
- Indirect Attribution
- Indirect Attribution
-
Tuesday, March 12, 13
![Page 50: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/50.jpg)
Segmentation
• Primarily used for A/B test analysis
• Arbitrary segmentation
Tuesday, March 12, 13
![Page 51: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/51.jpg)
How do we get it?
Tuesday, March 12, 13
![Page 52: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/52.jpg)
How do we get it?
select (clicks/visits * 100.0) as "CTR" from feature_funnel where event_type = 'search' and ab_test = 'sitewide' and epoch_s = 1361318400 and group_name = 'ALL_GROUPINGS';
Tuesday, March 12, 13
![Page 53: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/53.jpg)
The Future?
Tuesday, March 12, 13
![Page 54: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/54.jpg)
Features
• Eliminate the need for an engineer to write the queries
• Robust segmentation
• Not be limited to visit sessions
• Run Ad Hoc queries
Tuesday, March 12, 13
![Page 55: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/55.jpg)
Build your own Funnels
show the builder ui here
Tuesday, March 12, 13
![Page 56: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/56.jpg)
Mechanics of Funnel Analysis
Tuesday, March 12, 13
![Page 57: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/57.jpg)
Input
• Event sequences
• Typically visits to website
• Sessionization scheme left to developer
Tuesday, March 12, 13
![Page 58: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/58.jpg)
Tuesday, March 12, 13
![Page 59: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/59.jpg)
Funnel
• Search
• Listing
• Cart
Tuesday, March 12, 13
![Page 60: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/60.jpg)
Tuesday, March 12, 13
![Page 61: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/61.jpg)
Tuesday, March 12, 13
![Page 62: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/62.jpg)
Tuesday, March 12, 13
![Page 63: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/63.jpg)
Tuesday, March 12, 13
![Page 64: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/64.jpg)
Tuesday, March 12, 13
![Page 65: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/65.jpg)
Query
• Only discussed event types
• Funnel steps require additional constraints:
• Listing referred by search
• Added that listing to cart
Tuesday, March 12, 13
![Page 66: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/66.jpg)
Searchquery “dinosaur”
listing_ids 119469855, 90583707, ...
loc “http://www.etsy.com/search/handmade/patterns?q=dinosaur&order=most_relevant&view_type=gallery&ship_to=ZZ”
... ...
Tuesday, March 12, 13
![Page 67: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/67.jpg)
Listinglisting_id 119469855
ref “http://www.etsy.com/search/handmade/patterns?q=dinosaur&order=most_relevant&view_type=gallery&ship_to=ZZ”
shop_id 7415158
... ...
Tuesday, March 12, 13
![Page 68: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/68.jpg)
Cartadded_listing_id 119469855
cart_listing_ids 119469855, ...
cart_type guest
... ...
Tuesday, March 12, 13
![Page 69: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/69.jpg)
Query
• Listing referred by search
• Added that listing to cart
Tuesday, March 12, 13
![Page 70: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/70.jpg)
Search
query “dinosaur”
listing_ids 119469855, 90583707, ...
loc “http://www.etsy.com/search/handmade/patterns?q=dinosaur&order=most_relevant&view_type=gallery&ship_to=ZZ”
... ...
Tuesday, March 12, 13
![Page 71: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/71.jpg)
Listinglisting_id 119469855
ref “http://www.etsy.com/search/handmade/patterns?q=dinosaur&order=most_relevant&view_type=gallery&ship_to=ZZ”
shop_id 7415158
... ...
Tuesday, March 12, 13
![Page 72: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/72.jpg)
Listinglisting_id 119469855
ref “http://www.etsy.com/search/handmade/patterns?q=dinosaur&order=most_relevant&view_type=gallery&ship_to=ZZ”
shop_id 7415158
... ...
listing.ref == search.loc
Tuesday, March 12, 13
![Page 73: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/73.jpg)
Cartadded_listing_id 119469855
cart_listing_ids 119469855, ...
cart_type guest
... ...
Tuesday, March 12, 13
![Page 74: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/74.jpg)
Cartadded_listing_id 119469855
cart_listing_ids 119469855, ...
cart_type guest
... ...
cart.added_listing_id == listing.listing_id
Tuesday, March 12, 13
![Page 75: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/75.jpg)
Query
• Search
• Listing & Referred
• Cart & AddedListing
Tuesday, March 12, 13
![Page 76: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/76.jpg)
Pattern Matching
• Apply query to event sequence
• Select out tuples of matching events
Tuesday, March 12, 13
![Page 77: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/77.jpg)
What About Incomplete Funnels?
Tuesday, March 12, 13
![Page 78: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/78.jpg)
Tuesday, March 12, 13
![Page 79: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/79.jpg)
null
Tuesday, March 12, 13
![Page 80: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/80.jpg)
nullnull
Tuesday, March 12, 13
![Page 81: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/81.jpg)
Funnel Analysis
• Replace events with 1
• Replace nulls with 0
• Sum
Tuesday, March 12, 13
![Page 82: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/82.jpg)
null
nullnull
Tuesday, March 12, 13
![Page 83: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/83.jpg)
null
nullnull
1 1 1
11
1
Tuesday, March 12, 13
![Page 84: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/84.jpg)
0
00
1 1 1
11
1
Tuesday, March 12, 13
![Page 85: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/85.jpg)
123
Tuesday, March 12, 13
![Page 86: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/86.jpg)
min(1, 1)min(2, 1)min(3, 1)
Tuesday, March 12, 13
![Page 87: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/87.jpg)
111
Tuesday, March 12, 13
![Page 88: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/88.jpg)
What Do We Keep?
Tuesday, March 12, 13
![Page 89: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/89.jpg)
Tuesday, March 12, 13
![Page 90: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/90.jpg)
Search
query “dinosaur”
listing_ids 119469855, 90583707, ...
loc“http://www.etsy.com/search/handmade/patterns?q=dinosaur&order=most_relevant&view_type=gal
lery&ship_to=ZZ”
... ...
Listing
listing_id 119469855
ref“http://www.etsy.com/search/handmade/patterns?q=dinosaur&order=most_relevant&view_type=gal
lery&ship_to=ZZ”
shop_id 7415158
... ...
added_listing_id 119469855
cart_listing_ids 119469855, ...
cart_type guest
... ...
Cart
Tuesday, March 12, 13
![Page 91: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/91.jpg)
Tuesday, March 12, 13
![Page 92: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/92.jpg)
Segmenting Funnels
Tuesday, March 12, 13
![Page 93: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/93.jpg)
Tuesday, March 12, 13
![Page 94: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/94.jpg)
Search
query “dinosaur”
listing_ids 119469855, 90583707, ...
loc“http://www.etsy.com/search/handmade/patterns?q=dinosaur&order=most_relevant&view_type=gal
lery&ship_to=ZZ”
... ...
Listing
listing_id 119469855
ref“http://www.etsy.com/search/handmade/patterns?q=dinosaur&order=most_relevant&view_type=gal
lery&ship_to=ZZ”
shop_id 7415158
... ...
added_listing_id 119469855
cart_listing_ids 119469855, ...
cart_type guest
... ...
Cart
Tuesday, March 12, 13
![Page 95: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/95.jpg)
Listing
listing_id 119469855
ref“http://www.etsy.com/search/handmade/patterns?q=dinosaur&order=most_relevant&view_type=gal
lery&ship_to=ZZ”
shop_id 7415158
... ...
added_listing_id 119469855
cart_listing_ids 119469855, ...
cart_type guest
... ...
Cart
query
Tuesday, March 12, 13
![Page 96: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/96.jpg)
added_listing_id 119469855
cart_listing_ids 119469855, ...
cart_type guest
... ...
Cart
listing_idquery
Tuesday, March 12, 13
![Page 97: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/97.jpg)
query listing_id cart_type
Tuesday, March 12, 13
![Page 98: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/98.jpg)
Segmented Funnel Analysis
• Extract segmenting properties
• Compute indicators as before
• Group on segmenting properties and sum
Tuesday, March 12, 13
![Page 99: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/99.jpg)
MapReduce
• Work is done map-side
• Common first step in our jobs
• Expensive computation limited to first round mappers
Tuesday, March 12, 13
![Page 100: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/100.jpg)
Event Sequence Pattern Matching
Tuesday, March 12, 13
![Page 101: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/101.jpg)
Components
• Predicate: matches/rejects events
• Query: tuple of predicates
• Match: tuple of events
Tuesday, March 12, 13
![Page 102: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/102.jpg)
Match Predicates
• Select an event based on:
• Full event sequence
• Prior matched events
• Current candidate
Tuesday, March 12, 13
![Page 103: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/103.jpg)
Match Predicate DSL
• Combine predicates with logical operators
• val Query = Seq(Search, Listing & Referred, Cart & AddedListing)
Tuesday, March 12, 13
![Page 104: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/104.jpg)
Semantics
• Fixed number of events in match
• Arbitrary number of matches per sequence
• Collect and extend all partial matches
Tuesday, March 12, 13
![Page 105: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/105.jpg)
Tuesday, March 12, 13
![Page 106: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/106.jpg)
Search Listing Shop
Listing Cart Home
Search Listing Search
Tuesday, March 12, 13
![Page 107: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/107.jpg)
Search, Listing, Shop, Listing, Cart, Home, Search, Listing, Search
Tuesday, March 12, 13
![Page 108: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/108.jpg)
Root
Search
Query
Search, Listing, Shop, Listing, Cart, Home, Search, Listing, Search
Cart & AddedListing
Listing & Referred
Tuesday, March 12, 13
![Page 109: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/109.jpg)
Root
Search
Query
Search, Listing, Shop, Listing, Cart, Home, Search, Listing, Search
?
Cart & AddedListing
Listing & Referred
Tuesday, March 12, 13
![Page 110: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/110.jpg)
Root
SearchSearch
Query
Listing, Shop, Listing, Cart, Home, Search, Listing, Search
Cart & AddedListing
Listing & Referred
Tuesday, March 12, 13
![Page 111: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/111.jpg)
Root
SearchSearch
Query
Listing, Shop, Listing, Cart, Home, Search, Listing, Search
?
?
Cart & AddedListing
Listing & Referred
Tuesday, March 12, 13
![Page 112: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/112.jpg)
Root
Search
Listing
Search
Query
Shop, Listing, Cart, Home, Search, Listing, Search
Cart & AddedListing
Listing & Referred
Tuesday, March 12, 13
![Page 113: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/113.jpg)
Root
Search
Listing
Search
Query
Shop, Listing, Cart, Home, Search, Listing, Search
?
?
?
Cart & AddedListing
Listing & Referred
Tuesday, March 12, 13
![Page 114: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/114.jpg)
Root
Search
Listing
Search
Query
Listing, Cart, Home, Search, Listing, Search
Cart & AddedListing
Listing & Referred
Tuesday, March 12, 13
![Page 115: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/115.jpg)
Root
Search
Listing
Search
Query
Listing, Cart, Home, Search, Listing, Search
?
?
?
Cart & AddedListing
Listing & Referred
Tuesday, March 12, 13
![Page 116: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/116.jpg)
Root
Search
Listing
Search
Query
Cart, Home, Search, Listing, Search
Listing & Referred
Cart & AddedListing
Tuesday, March 12, 13
![Page 117: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/117.jpg)
Root
Search
Listing
Search
Query
Cart, Home, Search, Listing, Search
Listing & Referred
?
?
?
Cart & AddedListing
Tuesday, March 12, 13
![Page 118: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/118.jpg)
Root
Search
Listing
Cart
Search
Query
Home, Search, Listing, Search
Listing & Referred
Cart & AddedListing
Tuesday, March 12, 13
![Page 119: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/119.jpg)
Root
Search
Listing
Cart
Search
Query
Home, Search, Listing, Search
Listing & Referred
?
?
?
Cart & AddedListing
Tuesday, March 12, 13
![Page 120: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/120.jpg)
Root
Search
Listing
Cart
Search
Query
Search, Listing, Search
Listing & Referred
Cart & AddedListing
Tuesday, March 12, 13
![Page 121: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/121.jpg)
Root
Search
Listing
Cart
Search
Query
Search, Listing, Search
Listing & Referred
?
?
?
Cart & AddedListing
Tuesday, March 12, 13
![Page 122: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/122.jpg)
Root
Search Search
Listing
Cart
Search
Query
Listing, Search
Listing & Referred
Cart & AddedListing
Tuesday, March 12, 13
![Page 123: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/123.jpg)
Root
Search Search
Listing
Cart
Search
Query
Listing, Search
Listing & Referred
?
? ?
?
Cart & AddedListing
Tuesday, March 12, 13
![Page 124: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/124.jpg)
Root
Search Search
Listing Listing
Cart
Search
Query
Search
Listing & Referred
Cart & AddedListing
Tuesday, March 12, 13
![Page 125: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/125.jpg)
Root
Search Search
Listing Listing
Cart
Search
Query
Search
Listing & Referred
?
? ?
? ?
Cart & AddedListing
Tuesday, March 12, 13
![Page 126: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/126.jpg)
Root
Search Search Search
Listing Listing
Cart
Search
Query
Listing & Referred
Cart & AddedListing
Tuesday, March 12, 13
![Page 127: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/127.jpg)
null
nullnull
Tuesday, March 12, 13
![Page 128: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/128.jpg)
Tuesday, March 12, 13
![Page 129: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/129.jpg)
Tuesday, March 12, 13
![Page 130: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/130.jpg)
Match Tree
• Purely functional data structure
• Holds matched events and indices
• Match prefixes shared
Tuesday, March 12, 13
![Page 131: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/131.jpg)
Match Tree Algorithm
• Fold over sequence accumulating tree
• May extend any non-terminal node
• Each level in tree corresponds to predicate
Tuesday, March 12, 13
![Page 132: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/132.jpg)
Practicality
• Explodes, but
• Queries are constant length
• Sequences are bounded (visits)
• Predicates constrain growth
Tuesday, March 12, 13
![Page 133: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/133.jpg)
Summary
• Why funnels are interesting
• What we’ve built with them at Etsy
• Our approach to funnel analysis
Tuesday, March 12, 13
![Page 134: Funnel Analysis in Hadoop at Etsy](https://reader034.vdocuments.us/reader034/viewer/2022051322/5412da368d7f72174e8b45fd/html5/thumbnails/134.jpg)
Questions?
• Steve Mardenfeld
• Wil Stuckey @quiiver
• Matt Walker @data_daddy
Tuesday, March 12, 13