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で条件指定できなくて困っています。
何か対策、あるいは、もっと効率的に直接、リピータ数(そもそも新規ユーザ数も)を取得できる方法が
ありますでしょうか?
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/01/15 07:32
2016/01/15 07:53
2016/01/19 05:34
2016/01/19 09:03
2016/01/19 09:46
2016/01/20 05:28
2016/01/21 03:47
2016/01/22 02:45
2016/01/25 06:00