Satochan24 score 84
2016/02/09 17:47 投稿
| アクセスログの表から新規ユーザとリピーターの割合（月ごと）が知りたい |
| 【追記】アクセスログの表から新規ユーザとリピーターの割合（月ごと→週ごと）が知りたい |
| 2016/2/9追記 |
| 無理だったら全然いいのですが、 |
| 掲題の件、週ごとの集計は可能でしょうか？ |
| 週ごとというのは、2月第1週、第2週で集計します。 |
| 以前、ここで教えてもらった月ごとの集計SQLは、下記の通りです。 |
| いろいろ、部分的に分けて、MySQLで検証してみた感じでは、難しそうでした。 |
| ```ここに言語を入力 |
| SELECT (a.access_month) AS access, b.newer_count AS newer, a.all_count - b.newer_count AS repeater_count, a.all_count AS all_count |
| FROM ( |
| SELECT access_month, count( * ) AS all_count |
| FROM ( |
| SELECT DISTINCT user_id, date_format( access_day, '%Y年%m月' ) AS access_month |
| FROM access_record |
| WHERE access_type =101 |
| ) AS c |
| GROUP BY access_month)a |
| LEFT JOIN ( |
| SELECT access_month, count( * ) AS newer_count |
| FROM ( |
| SELECT user_id, min( date_format( access_day, '%Y年%m月' ) ) access_month |
| FROM access_record |
| GROUP BY user_id |
| ) AS d |
| GROUP BY access_month)b ON a.access_month = b.access_month |
| ORDER BY `a`.`access_month` DESC |
| ``` |
| （以前の質問内容） |
| アプリのアクセスログを取得している表があるのですが、 |
| この表から、新規ユーザとリピーターの割合を知りたいと思っています。また、取得したデータはPHPでWebページに |
| 表示させたいので、できれば１つの問い合わせで出力できるとページも作りやすいと思っています。 |
| 何とか、新規ユーザはカウントできたのですが、リピーターで苦戦しています。全体数をカウントして、新規ユーザを引けば、リピータになるのではと考えていますが・・・ |
| 何かカウントできる方法、あるいは、もっと別の方法で |
| 新規・リピータともカウントできるという方法があれば |
| 教えてください。 |
| 【アクセスログの表の一部】 |
| access_day | user_id | access_type |
| ---------------------------------- |
| 2016-01-15 | 1306 | 101 |
| 2016-01-15 | 1305 | 101 |
| 2015-12-28 | 1298 | 102 |
| 2015-12-28 | 1298 | 101 |
| ※表の説明 |
| ・access_type:101はアプリ最初のメニュー表示なので、 |
| アクセスした場合、必ず記録される。 |
| ・同じaccess_typeは同じuser_idからは1日1回しか記録されない。 |
| 【新規ユーザ取得のSQL】 |
| ・ユーザーごとの最小アクセス日、MIN(access_day)を取得すれば、それが新規ユーザのアクセス日だと考え、そのデータ数を月ごとにカウントしました。 |
| SELECT COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS 'this', |
| COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'one', |
| COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'two', |
| COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 3 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'three', |
| （一部省略） |
| COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 11 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'eleven', |
| COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 12 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'year' |
| FROM (SELECT a.user_id, MIN( a.access_day ) AS c |
| FROM access_record AS a |
| GROUP BY a.user_id |
| ) AS b |
| これで、 |
| 今月 1か月前 2ヶ月前 3ヶ月前 4ヶ月前 |
| 新規ユーザー 8 4 34 29 3 |
| のような表の形で取得できたので、PHPでWeb表示ができました。 |
| また、実際のデータを数えて、カウント数も相違ないことを確認しました。 |
| 【リピーター数取得のSQL】 |
| 次にリピータ数ですが、access_type:101は全てのユーザが記録されるので、101のみにしぼり、その月（例では12月）の全ユーザ数（重複なし）を出しました。 |
| 【12月全ユーザ数９】 |
| SELECT DISTINCT a.user_id |
| FROM access_record AS a |
| GROUP BY a.user_id, a.access_type, a.access_day |
| HAVING a.access_type =101 |
| AND date_format( a.access_day, '%Y-%m' ) = date_format( DATE_SUB( now( ) , INTERVAL 1 |
| MONTH ) , '%Y-%m' ) |
| これで9と表示できたので1ヶ月前の新規ユーザ数4を引けば、5がリピータ数になると考えました。 |
| 、1文で過去の月も表示できるよう、COUNT文の中に、条件を記載して、 |
| SELECT |
| COUNT( |
| CASE WHEN date_format( b.c, '%Y-%m' ) = date_format( DATE_SUB( now( ) , INTERVAL 1 |
| MONTH ) , '%Y-%m' ) |
| THEN 1 |
| ELSE NULL |
| END ) AS 'one' |
| FROM |
| ( |
| SELECT DISTINCT a.user_id, a.access_day AS c |
| FROM access_record AS a |
| GROUP BY a.user_id, a.access_type, a.access_day |
| HAVING a.access_type =101 |
| ) AS b |
| 上記を実行したら、予想（9）に反して、12月の101の全アクセス数の16が表示されました。 |
| 恐らく、内側のSELECTの中に、a.access_day AS cを入れたためだと思うのですが、ここに記載しないと外側のCOUNTで条件指定できなくて困っています。 |
| 何か対策、あるいは、もっと効率的に直接、リピータ数（そもそも新規ユーザ数も）を取得できる方法が |
| ありますでしょうか？ |
