1 / 17

WEBINAR Data Mining & Direct Access

WEBINAR Data Mining & Direct Access. Agenda. Overview Data Structure Data Mining Events Users Direct Access Connecting Querying Events Querying Users Joining between Events and Users Questions…. Overview. Overview.

chancellor-vance
Télécharger la présentation

WEBINAR Data Mining & Direct Access

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. WEBINARData Mining & Direct Access

  2. Agenda • Overview • Data Structure • Data Mining • Events • Users • Direct Access • Connecting • Querying Events • Querying Users • Joining between Events and Users • Questions…

  3. Overview

  4. Overview Aggregated Data- Counts of events, users, sessions, missions, revenue, items etc..- Fixed set of Dimensions and Measures

  5. Overview Aggregated Data- Counts of events, users, sessions, missions, revenue, items etc..- Fixed set of Dimensions and Measures RawData- Not aggregated, contains all events and parameters- Stored in an HP Vertica Analytics Database- Very fast ad hock queries against any events or parameters - Event Data retained for 30 days be default (can be extended on a per game basis)- User Metrics retained for life

  6. Overview Aggregated Data- Counts of events, users, sessions, missions, revenue, items etc..- Fixed set of Dimensions and Measures RawData- Not aggregated, contains all events and parameters- Stored in an HP Vertica Analytics Database- Very fast ad hock queries against any events or parmaters - Event Data retained for 30 days be default (can be extended on a per game basis)- User Metrics retained for life Archives - All event data is archived to Amazon S3 so you can download and run longer map reduce queries.

  7. Data Structure • Events • Stored in a single wide table • One row per event there are exceptions! more on them later… • One column per parameter • So, simplistically there is a row for each event you send and it contains columns for every possible parameter the game can send. • New columns will be added to the events table automatically as you add new parameters to your event schema.

  8. Data Structure • Events • Stored in a single wide table • One row per event there are exceptions! more on them later… • One column per parameter • So, simplistically there is a row for each event you send and it contains column for every possible parameter the game can send. • New columns will be added to the events table automatically as you add new parameters to your event schema. • - The exceptions, • Some events can span multiple rows (transaction, levelUp etc...) • Some columns are automatically populated by deltaDNA

  9. Data Structure • User Metrics • Each row contains metrics for a user including • Generic Metrics – automatically calculated totalDaysPlayed, totalRealCurrencySpent … • Counts of events eventGameStartedCount, eventLevelUpCount … • Parameter Metrics • Auto fieldMissionNameLast, fieldDeviceTypeLast, fieldUserXPLast … • User Defined. Any String or Integer Game Parameter. Set parameters as metrics in Game Parameters tool.(Warning, don’t just set everything to be a metric, it will just bloat your user metrics table and slow down your direct access queries) • Multiple rows per user, use effective date to get the most recent • Updated every night in the early hours (UTC). This is an archive for you to run queries on, Segmentaion, Targeting etc.. Work against a live, in memory version of this that has real-time accuracy. • Be aware, some metrics are relative to the user_last_seen_date, e.g. daysPlayedLast7Days . We are looking to change this at some point to make re-evaluation of inactive users easier.

  10. Data Mining – Events • Moves Remaining on “First Time User Forest #2”

  11. Data Mining – Events • Moves Remaining on “First Time User Forest #3”

  12. Data Mining – Events • Ratio of mission events on the first 5 missions

  13. Data Mining – Events • Ratio of mission events on the first 5 missions 47% failure rate, mission too difficult!

  14. Data Mining – Users • Moves Remaining by mission

  15. Direct Access - Connecting • Direct Access Documentationhttp://docs.deltadna.com/direct-sql-access/

  16. Direct Access - Queries • Event Query • User Metrics Query -- Event Mining Query SELECTmovesRemaining, count(eventDate) FROMevents_live WHEREmissionName= 'First Time User Forest #2' ANDeventName = 'missionCompleted' GROUPBYmovesRemaining ORDERBYmovesRemainingdesc; -- User Mining Query select * fromuser_metrics_dev whereeffective_date = CURRENT_DATE limit 20;

  17. Direct Access - Queries • Advanced Query with JOIN between events and user metrics SELECTe.missionName, count(e.missionName) FROMevents_live e JOINuser_metrics_live m ONe.userID = m.user_id WHEREeventName = 'missionFailed' ANDe.missionNameLIKE'First Time User Forest%' ANDm.fieldLivesBalanceLast = 0 ANDm.effective_date = CURRENT_DATE GROUPBYe.missionName ORDERBYe.missionName; First Time User Forest #3 is causing abandonmentIt has the highest number of players where it is The last mission they play, and fail and have no lives left

More Related