# Content Report ### report_10_Post-episode Activity Completion Ratio (episode based) `Overall` #### steps to generate report: * query daily_usage_video_tracking table from database ![table preview](https://lh3.googleusercontent.com/pw/ACtC-3dEyqwXwt666yBWAYgxcXWIac5-jTYXlbwnEfRFHRcxsjAQNSQcq8RJ4u9DF5TJP_nNdPDoDKH4sT2fs5mzxf5sDpsdBw3x_IpjfYF7m8smBKd09L74LvJBBsNmbpqHi3UD1cfIgKwlkHUXaA_orDLh=w340-h170-no ) * query activity_tracking table get the all completed activities that related to the episode ![enter image description here](https://lh3.googleusercontent.com/dGGiSm9497RJVDiWbcEGbAoB5OqEb6zLsrXKDnbEEIz92itgrJU_OKWGocjK-7ob7FkrDQKgGCqjPxF1WRWOCaOdZujzV4VyTlsi0b6UlHnhj4SXaBiLKvX4oMRiolnKUVMaYCt2b3MUg7bqfaupKy_9S2GskiXxfPaXdgM6vhv5PLsLOP2Dq9yzAnEzSFt5h0p3NN6LRyc10dzl9hIe7Dh7kBDSX5h4Et1Nhw8DHIq0vxmiPKwgRVI8bxCEU2bJH90g_zEjnjdc4VB7fc13i-e8YZyt99AR7aupD_FG9dd4ULPOs_mUcaAO3TlPqVW-YwfrGJnkYXvdASKRiVRUOP1YUoiRnGI5roVwciGzpB0zaZq7gX7jRmBPp1_EjSs7v2BJuKJ_shs7kTHBky8WAAY5t29FPwwjof6jev9Xvwr97JMfqc9UPYTnAvI-DLfb8G1lUdJ2yPu_de7-KDn5eTxRETSPDlVXXWWp4aAt7tAyNjndfrY64f7RqbHXtefkIPWyUqoo9ScxcYGKEBPqS4xEkBILWXMS-UySHMJaLjktTOLJ_sX9PLQp16tI9P24HJdpBO332A26ECzj2e721_MUW_f518HYZegHhqYPr_h1k7bQHqUYZl55zcDXp3RgotdQCHQs4a2y8ZR5VOrWG_GtHmK2Zh97oGoG46iVi1kH7TxujmQHTwHMvzSfVA=w357-h159-no?authuser=3) * inner join between the tracking video and activity that are in the same day #### warning * i used all videos with music because there is no activity completed for a video with no music tell now ### report11 Post-episode Activity Completion (child ages based)`Overall` #### steps to generate report: * follow the steps of the previous report but add child column and group by it. * and generate these results ![enter image description here](https://lh3.googleusercontent.com/0w60FA_zoo6cXBOE8k1e9_hrPjzjU27IlzCl_n7cGfyCy2tfq69ks10futP5eIGN_v2wpcBqtlHOfOTrrmgbz4YMAPsL0CiiSgiGajrL2D6l__U_LCHxvGXF1x5VY5yPte40mM0q6I0og4rpxpS-3juRs7D4JjQfysyMYmcwycoAVuC2QnZiYm8Q3E3zsoDIOaz-YjV6PGh7vdA4SS1WaTTr3yNdMlWMnKfBxDutgSQZHigG6NKgcyMh4UwOz5V_ptc_iIMhald1Ni_5l-grLxHigeB63776nG57yn95nKyLR5fha07uDitrikRr-97zJCgzCm_niTLKdXAphmmzbHqTJ_34MRa2aGAuFXcJBuwnCz2I0pfgiijXnSkeQS3pn0w7ByO0H6e7xvTEN3_tikJ1hcZqa9jm9Z5_-aM0ESAyyw0i17pFqeC89Z4XCK-s64PRnvedFaO9fM_mmaugFFaYMnMHUAf2JZ4TDr1Q1DMPhXTIyfXnKY47oAhHZz2CjdUCsiOfHFVT9ij2NvIy-d3PX8SDBCQRJhjfyjGzzlsIkHUd4hW47WM1wXgCgIzAEe4k0KmfmSBl77jdDJ4JV1H8UUzvLCcDpnsXLRREEOde_XZJ55XAuuJrbw-eOABESimOt1vVBdfPYEot5H5yFtcoDm6enWJv9K2P6VgWn1_1lPxIijLa-Fg1B0ku5w=w246-h341-no?authuser=3) ### report 13 Intro Video Watching Percentage`Perodic` #### steps to generate the report: * query BigQuery to get users who access the intro-video * get duration of the session till end #### warning * we cant get exact time on specific screen. so we include the splash screen with the session ### report 18 jsoor Vistors `Perodic` #### steps to generate report * query BigQuery to get event_count and unique users for all users who accessed jsoor screens * we identified Jsoor screen by `content-dir-home` tag * Outcome ![enter image description here](https://lh3.googleusercontent.com/RpSL9YexFHte1LvSo_aJ44MFfNQeMVDWWBMVKEsdpAR38yOD_dVGczPfCTyEO5c_FSRiwZUtjPS69JSNx5dNCZzFyN4MdBky3XLuDCfPJk3-uODefuLEAXR1ulZsO3by7tLykwyNqsZQTaxAxtw21oS7N0-4RWuYixThfvDH2ZOHaStye4yOD6WVnmAGE2385hW08z-Dxpn2JzY_Ew7rUU_MdALDLKGVv9E6UAwermzGD2_ECFULot1Mnc7WT7NV-qjqxx4qhuMx2DG0NtwVtJUROJPzB-vKhPEomjUyUyh19u1nsxwzB189cX6zPWKPdB2IMhhXoHKLnpESQhyzw75Uuu_da1HTwLGEMoUoGTuCebXS4spIYP3xDWGTWQsJzj3lKwIHNlmiKsjbdQmK-2-J7wshDTMDcvpHr2o9M5alKBsQ43C2oyDnG42oPqkLTdiTtA23ntWl8Awn35RFl5su4Z0qK7Ga24dnrwQWgZDEN0Ok7kNrxDrrkucmayjFgdopTEskKYiA5x_ECFtT0M8e032Xi2mc0-4xk3UNW-7KDuM1ycSALKokxcLK2himpfqzdx7r2JOCnyZT44LxicEb7sOCUT4zFSKmJvYmTbRoioOGfTI37_5b4oN-sYMuu2CHLAuB8gLoZMaP7T_rmssT9wubIiefe97a9coPQJxR3XCYrA5v7q8xdSxqvQ=w408-h38-no?authuser=3) ### report 29 free/paid episodes most/least watched`Perodic` #### steps to generate report * query the database for each video type and merge with daily_usage_video_tracking * collect count of watches ,unique children who viewed this video, free or paid option * Outcome * ![enter image description here](https://lh3.googleusercontent.com/sXwuWfZvMzQoYYszcrTfJDGU102bjReAaYpyY3XCWi4NMiI2EVB2qRFjKrDxmpdKvDjW0rXr8HlbutqDdjS2_Foa6PEq2JYtbU5rI-FHkyBGI281fIkuDsQVjP-FCh8Hp6IdUN8Tu9fVJcuj7y9xeIIjjUXF691qOiqHjjx4voHNM2zbVSnUzvcSETIw5jYly1MPWaQQW1E0d8HArHLxqHwBFRckEPY5rt_VMG0lTMzCWjLWieoC_l7zmkMCmpnCa5fr_Wen81MzFl9B4KOBOkuEpLvcJ5YjM_DvGjj4zW2BfAQJiZ1CnbELsM0X0COJzOerpgxuzh1DhjPWvCbyt5r9p73JfSktZfFJ6INghH2Vzi7vGsNLS6UxS297NwsUbubuM04FJawc1Chm0AEZfTXXV9xArlEYJiEVIiLonnxXAGLMM0LBvWDfBkGBtmn8rR1OetkeT7nIhE6ZrcMcWTcxdDtfHDDK4JNpnH1Yg0JeVXVHn4REQeuPm2CTxCownBlZSgsnDkEl1wCdMncLFNpxIfMwYlfNtpikEIPlPDgZSwwPGJODEsBekk0d8qLq3lG12cPIPK-o7K2Hc6C-2a6PBA_qh6a5EmQ-O6vfw_OC6YzuP8S8S26cOtvjuhk7AqBWa0lGEqGzDsrSwdmNSqupAPXfprg2OO8s6y2wdVQF_-hBuflzL5hCMcXX9Q=w824-h334-no?authuser=3) ### report 30 Screens Events Count`Perodic` #### steps to generate report * query BigQuery to get each event_name ,event_count ,unique_users_count. * outcome ![enter image description here](https://lh3.googleusercontent.com/9VjeV1G0HYJGXZUIlEYO7LAoeasVLlQOdphxHM1ezdfDoK2noT-oRDygmAQDqAHtBkIKMl6l4tjqInYkWKdckOvcnMKy65BLnD78GFCRSzEMsEettP4YIg4IKCCeVgXeW7Zg9UCapod23v5-7jVegdQ-7OYyYjoo_1izMVl7n_-C0VnYsRPlopsIX7CTcXktIPFRfjecYvM4sGMe7hHuG5oi38s8xKJYBFjrIg35gu6WHRmIP17NbR_OsmPCbZWU9VnUiJlNLRO6ppQmUsS2bj2KJrVRD-EWvto2NK4mi0KmcjJKFbPEVmz6rkKXPKxgyQCumKd8zJzdxrTp973gsAS6-A8LsLkbKurxz_L1nwBiUyh1B86f8Ml_l58jlJvNlPYlkEvP00HSBa4Ro3Va--F8mSMsmLW5_OIhbR0dSRio9jTqRde2udH0Umjwh5cp8IMt2Ti47oqV-dKcvFgtZchw3xdr7yBIBuvHPGIewgmWlyvPavaLGVaMl99i2Ih06SS_6TOxJxGfgQzZJNKkgd8blXd12KG7LgA4NnIiVYfbL0bC59RC4zpyLmoY1uK0cpnk_M3-Jj7NXvYBMlBBVaNlGvqWqAF0hALhVThPU_udZR-s9kY0j8HyPOf9yaA9BN30E7_3aJqSL554uNPZxxhFRulghdyeC9vIsfbfr0dbNdEZvbP8EiH5u7QA_Q=w644-h401-no?authuser=3) ## Music Preferance ### steps to generate this report * query user table with music preferance table * get the phone number * get country key * than query BigQuery to get the advertising_id