質問編集履歴

1 追記

Satochan24

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で条件指定できなくて困っています。
何か対策、あるいは、もっと効率的に直接、リピータ数(そもそも新規ユーザ数も)を取得できる方法が
ありますでしょうか?
  • PHP

    27398 questions

    PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

  • MySQL

    8084 questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る