вторник, 10 ноября 2015 г.

Adobe Mobile Service Implementation

Working as a digital intelligence analyst means that I should work with various data sources such as web analytical system or mobile applications trackers. In our company, we used Adobe SiteCatalyst and it allows us implement SDK to mobile apps and track lifecycle metrics such as launches, sessions and etc. Moreover, we can add custom variables and custom metrics.

Let's look closely on step by step manual how to start tracking mobile application with Adobe Mobile App Analytics.

1. We should create new reporting suite. In the marketing cloud go to Admin->Reporting Suites. Select Create New > Report Suite:
I reckon to create separate reporting suite for every app and os.
2. Now we can go to https://mobilemarketing.adobe.com and add new app. In order to add new app we should choose reporting suite, create name and choose type. If we already have id of app in the store, we can add app by finding it in the store:
3. Next step is very important, because we should prepare technical documentation for mobile developers. Fortunately, adobe documentation is very detail and we can find all information there. Let's look, how should we prepare all staff in order to start tracking app.  For example in my case, we already have application in google and apple stores but we want to create new release with adobe sdk:
There is full documentation for ios and android. After adding or creating apps in adobe, we can download config and library for our OS:
Config file and OS library we should send to our developers. 
4. In addition, we should think about acquisition, because it is very important to track acquisition channels and measure their performance. In order to start create acquisition links, we should add in our config information about acquisition. Just enable SDK Acquisition Options and don't forget make Referrer Timeout at least 5 second, or it won't be work like in our case:)
We can learn more about acquisition for IOS and Android.
5. Finally, we can add custom metrics and variables. Let's go to "Manage Variables & Metrics" and add addition variables and custom metrics. There are 3 tab:
  • Standard Variables & Metrics
  • Custom Variables
  • Custom Metrics
We can learn more about standard metrics and variables for IOS and Android. In order to understand,  how to add custom fields, we should consider our app as a collection of screen and we can define various events on particular screen with some attributes, for example  event "Successful registration of an account" with some variables like date and time of registration, account number, type of account and etc. In other words, we should take screen from app and right list of events and variables for every screen and give it to developers, in order they put it in code and then map it in  "Manage Variables & Metrics" menu:
In this example, I asked developer to add in code s.evar7 and s.evar31 and then mapped them.

After successful release, we can start to track our app and use default charts of adobe mobile analytics. Let's look on some of them:

1. Overview report, which show as Unique Users, First Launches (Installs), Launches and Custom Events. We can change it in any way. Be careful with crashes and crash rate, it can be wrong, better to discuss with mobile developers. 

2.  Retention profile is a part of cohort analyses and help us to analyze each particular cohort and their life time.

3. Version report - this one very useful in order to track bags after new release

4. Acquisition overview - here we can analyze our marketing campaigns. In order to track new channel, we should create new link. which we can add to the add, for example in Google Adwords. Unfortunately,  we can't do it for facebook and twitter, in order to fix this problem we should add facebook SDK or twitter SDK to the app or use adjust or appsflayer, because they are integrated with them.

Mobile Pirate Metric Framework

What is pirate metrics and why it calls pirate?

Pirate Metrics analyzes your business by tracking 5 key metrics, then helps you improve your product and increase your revenue. It’s the best analytics tool for your subscription business. AARRR!*
*pirates always scream AARRR!
Acquisition - users downloads mobile application, we have their attention
Activation - user performs some key activity that indicates a good first visit, i.e. user enjoys first visit, happy experience
Retention - user continues to do that key activity indicating they like your mobile app
Revenue - user pays you
Reengagement - users has left the product without activation or not converted to real without deposit

Currently we have to trackers:
  • Adjust, because it helps us to run campaigns in facebook and twitter
  • Adobe Mobile Analytics because it is part of Adobe SiteCatalyst
There is top architecture of solution:

I collect data from adjust and adobe in redshift. Using Pentaho Data Integration, I run daily job which extract data from adjust table, which store in OLAP and in the same time run another job which take 2 files from adobe ftp, one for IOS and another for Android. All data goes to Redshift in staging are in separate table. In addition, I load data from DWH with deposits, costs and revenue amount.
Main challenge is to combine both trackers, because they have varius nature of data, various timezones and etc. I solved it for daily qty, using SQL. It is simple math. As etalon tracker I chose Adjsut, because it is more precise. Then we just calculate sum of installs per day for every channels and operationa system for Adjust. From Organic channel in adjust we should minus quanty of adobe install without adobe organic. As a result we get install by channels.

ETL process

I created 2 jobs. One for loading stg.stg_adobe and another one for loading stg.stg_adjust.

Loading stg_adjust

Run Pentaho DI

Pentaho home path: /home/dasboard/Downloads/data-integration
in order to start pentaha use terminal and run spoon.sh

Transformation 1: stg_adjust

This transformation extract data from mob.adjust for last 7 days and send it to s3://s3/webanalys/adjust_data in gzip format.

Transformation 2: sql_adjust

This transformation run sql queries:
--copy data from file to stg.stg_temp_adjsut

copy stg.stg_temp_adjust from 's3://webanalys/adjust_data'
delimiter '|'
timeformat 'YYYY-MM-DD HH24:MI:SS'
--delete data from stg.stg_adjust in order to write new fresh data
DELETE FROM stg.stg_adjust where stamp>= (SELECT MIN(stamp) FROM stg.stg_temp_adjust);
INSERT INTO stg.stg_adjust (SELECT * FROM stg.stg_temp_adjust);
--optimaze stg_adjsut table
analyze stg.stg_adjust;
vacuum stg.stg_adjust;

--delete from temp table all data
DELETE from stg.stg_temp_adjust;
vacuum stg.stg_temp_adjust;

Job: jb_adjsuttoAWS

Kitchen job

Pentaha allow as to schedule jobs via kitchen. Using crontab I run my job. Moreover, kitchen can perform only shell script. As a result I put my job in shell script:

/home/dasboard/Downloads/data-integration/kitchen.sh -file=/home/dasboard/Downloads/pentaho_di_files/jb_adjusttoAWS.kjb -level=Minimal

Loading stg_adobe

In the same time, we should load data from Adobe mobile to redshift in separate table.
By default adobe allow us to use "datawarehouse" option in order to send data to adobe FTP.

Using pebtaho data integration, I can go to FTP, grab file and put it in local folder, then unzip and start to transform it and then send to S3 in order to incrementaly load in staging table for adobe. There is a job:

Let's look on transformation, which transform csv file and send it to S3. The main problem of adobe datawarehouse it it date format, like "10 November, 2015". It is not so comfortable for database. That's why with pentaho I quickly fix it:

And finaly, I run copy command in redshift:
copy stg.stg_temp_adobe from 's3://webanalys/ios7days' 
credentials 'aws_access_key_id=<key>;aws_secret_access_key=<key>'
delimiter '|'
timeformat 'YYYY/MM/DD HH24:MI:SS'

DELETE FROM stg.stg_adobe where app_os='ios' and visit_date>= (SELECT MIN(visit_date) FROM stg.stg_temp_adobe WHERE app_os='ios');

INSERT INTO stg.stg_adobe (SELECT * FROM stg.stg_temp_adobe WHERE app_os='ios');
analyze stg.stg_adobe;
vacuum stg.stg_adobe;

DELETE  from stg.stg_temp_adobe where app_os='ios';
vacuum stg.stg_temp_adobe;

This example for IOS, the same is for android.

When we collect all data in stageing, we can build datamart in order to combine data from both trackers and enrich it with sales data.

Let's look on pirate metric, which I visualized with Tableau.

Dashboard consist of 4 tabs:

  • Acquisition - users downloads libertex
  • Activation - users enjoy first visit
  • Retention - users come back, invests multiple times
  • Revenue - user deposits real money

There are visualizations of main KPIs: