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ページに |
表示させたいので、できれば1つの問い合わせで出力できるとページも作りやすいと思っています。 |
何とか、新規ユーザはカウントできたのですが、リピーターで苦戦しています。全体数をカウントして、新規ユーザを引けば、リピータになるのではと考えていますが・・・ |
何かカウントできる方法、あるいは、もっと別の方法で |
新規・リピータともカウントできるという方法があれば |
教えてください。 |
【アクセスログの表の一部】 |
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月全ユーザ数9】 |
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で条件指定できなくて困っています。 |
何か対策、あるいは、もっと効率的に直接、リピータ数(そもそも新規ユーザ数も)を取得できる方法が |
ありますでしょうか? |