How I save my Strava activity

Last Sunday, when I reached my home after my bike ride I took my smartphone from my pocket to stop the activity and tried to put it back in it. But I missed the pocket and the phone fall (screen first) on the sidewalk. I was able to turn on/off the screen but the touch sensor was not responding so the phone was unusable.
Breaking a phone is always something annoying but when it contains your longest bike ride statistics not yet synchronized, it's worst.

I then spend some time to think about how I could access to the detail of my activity. I was only able to see the distance and duration on the lock screen, but that was not enough for me.

The easiest solution

While I was complaining talking about my broken screen and unsynchronized Strava activity on IRC, a friend (Edouard), suggests me to install a remote control software to access the phone screen via the computer. That idea was great and worked well.

For that I connected my phone via USB to my computer and used Vysor Chrome extension and adb. That's probably doable with any similar tools, but they can require to install an app on the device.

If the device is not Internet connected (like my phone that I used only to record bike rides), it may be necessary to enable WiFi with "svc wifi enable" command in adb shell.
If the phone is locked, it can be necessary to unlock it via adb: "input keyevent 66 && input text XXX && input keyevent 66" (XXX is the password, 66 is the key code for Enter).

The geek solution

When I thought about that issue, my first idea was to analyze files used by Strava on Android and my last option was dumping the memory to try to find some data related to my activity.

First thing first, I started looking for files on the file system of my phone where I can find data related to my Strava activity.

root@mako:/ # find . 2> /dev/null -name *strava*

A search like the previous one was able to list many files:

./storage/emulated/0/Android/data/com.strava
./storage/emulated/0/Android/data/com.strava/cache/cache_vts_labl_com.strava.m
./storage/emulated/0/Android/data/com.strava/cache/cache_vts_com.strava.m
./storage/emulated/0/Android/data/com.strava/cache/cache_vts_inaka_com.strava.m
./storage/emulated/0/Android/data/com.strava/cache/cache_vts_com.strava.0
./storage/emulated/0/Android/data/com.strava/cache/cache_vts_inaka_com.strava.0
./storage/emulated/0/.estrongs/.app_icon_back/ver/com.strava_419
./storage/emulated/0/.estrongs/.app_icon_back/ver/com.strava_512
./storage/emulated/0/.estrongs/.app_icon_back/ver/com.strava_516
./storage/emulated/0/.estrongs/.app_icon_back/com.strava.png

[...]

./data/data/com.strava/shared_prefs/com.strava.preference.excludeFromBackup.xml

./data/data/com.strava/shared_prefs/com.strava.preference.userPreferences.xml

./data/data/com.strava/shared_prefs/com.strava_preferences.xml

./data/data/com.strava/shared_prefs/apptimizecom.strava.xml

./data/data/com.strava/files/DATA_disk_creation_time_vts_labl_com.strava

./data/data/com.strava/files/DATA_disk_creation_time_vts_com.strava

./data/data/com.strava/files/DATA_disk_creation_time_vts_inaka_com.strava

./data/data/com.strava/files/event_store_v2_com.strava

./data/data/com.strava/files/DATA_ServerControlledParametersManager.data.com.strava

./data/data/com.strava/databases/strava

./data/data/com.strava/databases/strava-journal

./data/app/com.strava-1

./data/dalvik-cache/profiles/com.strava

./data/media/0/Android/data/com.strava

I started to analyze the cache directory. Each activity recorded by Strava created few files in that cache directory. Here is how the content looks like for 3 activities:

root@mako:/storage/self/primary/Android/data/com.strava/cache #  ls -l
-rw-rw---- u0_a111  sdcard_rw    76181 2016-07-21 20:02 cache_bd.0
-rw-rw---- u0_a111  sdcard_rw    17540 2016-07-24 08:33 cache_bd.1
-rw-rw---- u0_a111  sdcard_rw    49152 2016-07-24 08:33 cache_bd.m
-rw-rw---- u0_a111  sdcard_rw    22528 2016-07-19 19:49 cache_its.m
-rw-rw---- u0_a111  sdcard_rw    22528 2016-07-19 19:49 cache_its_ter.m
-rw-rw---- u0_a111  sdcard_rw    23257 2016-07-21 20:24 cache_r.0
-rw-rw---- u0_a111  sdcard_rw    32768 2016-07-21 20:24 cache_r.m
-rw-rw---- u0_a111  sdcard_rw  4061958 2016-07-24 08:33 cache_vts_com.strava.0
-rw-rw---- u0_a111  sdcard_rw    40960 2016-07-24 08:33 cache_vts_com.strava.m
-rw-rw---- u0_a111  sdcard_rw     2827 2016-07-21 20:02 cache_vts_inaka_com.strava.0
-rw-rw---- u0_a111  sdcard_rw    40960 2016-07-21 20:02 cache_vts_inaka_com.strava.m
-rw-rw---- u0_a111  sdcard_rw    27648 2016-07-19 19:49 cache_vts_labl_com.strava.m
drwxrwx--x u0_a111  sdcard_rw          2016-07-19 16:53 debug

These files contain probably some interesting data, but nothing understandable without in-depth analysis of the app.

The find command also return a SQLite database (./data/data/com.strava/databases/strava) which is easier to understand and contain a lot of information.

Database description

This is a listing of the tables present in this SQLite database with a short description of their content when I was able to understand it. I didn't try to analyze everything as I mainly focus of the unsynchronized activity.

- activities: Information of your user's friends activities
- activities_unsynced: Unsynchronized activities
- android_metadata: Only store the language
- annual_progress_goals: Annual total distance for running biking and swimming of user's friends
- athlete_clubs
- athlete_contact
- athlete_stats: Number of activities, distance, duration and elevation gain for running, biking and swimming for recent activities, for the year and since the user uses the app for him and all his friends. NB: the numbers can be not accurate (doesn't seem to be updated frequently)
- athletes: Details about the athlete profiles visited included the profile of the user (with more details than the other athletes)
- challenge_leaderboards: Summary of the leaderboards for each challenge subscribed by the user
- challenge_participants: Information about which user's friend is participating to which challenge
- challenges: Information about the challenges available
- clubs
- comments
- dorado_impression: A unique URL to a 1x1 gif used to track something (probably Premium ads printing)
- facebook_search: Information about the user's friends found via Facebook
- feed_entries: Information about activities shown in the app (via clubs, challenges for exemple)
- followers: Information about athletes following the user
- followings: Information about the athletes followed by athletes in the 'athletes' table
- froutes
- gear: List of gear (shoes, bikes) and distance traveled with
- kudos
- live_activities
- live_activities_points
- live_athletes
- live_events
- live_location_activities_gson
- live_matches
- live_tracking_contacts
- notification_settings
- notifications: Information about app notifications
- progress_goals: Progress goals about athletes from 'athletes' table
- promo_overlay
- related_activites
- routes: Route information when they have been printed on screen (for any kind of user)
- rts_logs: Geolocalisation data for unsynchronized activities
- segments: Information about activities segments
- sensor_datum: Information ('relative_altitude', 'pause_type') for unsynchronized activities
- streams                                                                                      
- training_videos
- unsynced_photos
- waypoints: Details (latitude, longitude, altitude, speed, elapsed time, speed) for unsynchronized activities
- zones

Let's get my data back

The previous tables listing shows some interesting tables, like "activities_unsynced" which contains some meta data about all unsynchronized activities:

sqlite> select * from activities_unsynced;
id|updated_at|json
3|1469393124028|{"activity_id":0,"auto_pause_enabled":false,"commute":false,"distance":84499.02644972557,"elapsed_time":0,"end_timestamp":0,"guid":"REDACTED","is_private":false,"live_activity_id":0,"row_id":3,"m_end_battery_level":-1.0,"m_initial_elevation":94.42694,"m_screen_on":false,"m_screen_on_start":53574812,"m_screen_on_time":723567,"m_screen_timer_invalid":false,"m_start_battery_level":0.99,"sync_state":"UNFINISHED","manual":false,"name":"2016-07-24","photos":[],"route_id":-1,"sensor_averages":{"1":{"a":10000,"b":0,"c":0.0,"e":true},"0":{"a":10000,"b":0,"c":0.0,"e":true},"10":{"a":10000,"b":0,"c":0.0,"e":true},"18":{"a":10000,"b":0,"c":0.0,"e":true}},"should_facebook_share":false,"start_timestamp":1469374573560,"type":"Ride","video_view_id":-1,"workout_type":-1}

Interesting data include:
- distance
- elapsed time
- start timestamp
- end timestamp
- guid (unique ID of the activity)
- name
- type
- synchronization state

NB: The dot in the timestamp is missing (1469374573560 -> 1469374573.560) and the distance is recorded in meters (even if the application is configured to use miles).


An other interesting table is "rts_logs" as it contains few GPS coordinates of the user during its activity:

sqlite> select * from "rts_logs";
id|updated_at|activity_guid|json
76|-1|REDACTED|{"m_activity_guid":"REDACTED","m_log_json":"{\"latlng\":[37.74XXXXXX,-122.43XXXXXX],\"timestamp\":1469374596336,\"failure_code\":1001,\"failure_status_code\":-1}","m_type":"request"}
[...]
sqlite> select count(id) from rts_logs;
count(id)
67

This table can be used to track the user using latitude, longitude and timestamp but 67 records for a 84km/52miles ride, it's not that much.

Hopefully another table contains more detailed information about the activity, and that table is "waypoints":

sqlite> select * from waypoints;

ride_id|pos|timestamp|latiude|longitude|altitude|h_accuracy|v_accuracy|command|speed|bearing|device_time|filtered|elapsed_time|distance

REDACTED|0|1469374597000|37.74XXXXXX|-122.43XXXXXX|18.60XXXXXXXXXXX|9.0|||7.0|86.3000030517578|1469374596293|0|10241|0.0

[...]

REDACTED|17356|1469393123000|37.74XXXXXX|-122.43XXXXXX|34.X|3.0|||0.0|0.0|1469393123969|1|15800446|84499.0264497256

sqlite> select count() from waypoints;

count()

17357

We now have 17357 GPS coordinates to recreate the activity map (using OpenStreetMap or Google Maps) and do some statistics!

I hope that this understanding of the Strava SQLite database can be useful for any person in a similar situation as me (broken phone with unsynchronized activity) or for forensics analysis (lot of GPS data in it)!