質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.01%

【追記】アクセスログの表から新規ユーザとリピーターの割合(月ごと→週ごと)が知りたい

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,325

Satochan24

score 84

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で条件指定できなくて困っています。

何か対策、あるいは、もっと効率的に直接、リピータ数(そもそも新規ユーザ数も)を取得できる方法が
ありますでしょうか?

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

0

ご質問のSQLのサブクエリーは(access_dayではなく)月を返すようにし、その月をベースに集計すれば良いと思います。

  • 月ごとの新規ユーザー数
  • 月ごとの全アクセス数
    この2つの結果セットを外部結合することで「月ごとの全アクセス数-新規ユーザー数=リピーター数」になると思います。

Oracleですがご参考までに。
※「substr(access_day, 1, 7)」は「date_format( b.c, '%Y-%m' )」と同義です。

select a.access_month, b.newer_count, a.all_count - b.newer_count as repeater_count
from (
    select substr(access_day, 1, 7) as access_month, count(*) as all_count
    from access_record 
    where access_type = 101
    group by substr(access_day, 1, 7)
  ) a 
  left join ( 
    select access_month, count(*) as newer_count
    from ( 
        select user_id, min(substr(access_day, 1, 7)) access_month 
        from access_record 
        group by user_id
      ) 
    group by access_month
  ) b on a.access_month = b.access_month


分析関数を使えばもっとすっきりするかもしれません。


2016/01/21 追記
left joinより上の部分をdistinctにして重複ユーザーを除去しました。

select a.access_month, b.newer_count, a.all_count - b.newer_count as repeater_count
from (
    select access_month, count(*) as all_count
    from (
        select distinct user_id, substr(access_day, 1, 7) as access_month
        from access_record 
        where access_type = 101
    )
    group by access_month
  ) a
  以下同じ

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/01/21 12:47

    SQLを追記しました。distinctを使うと重複なしユーザー数は9を返します。
    (新規ユーザーは他の月のデータも必要なので確認はできませんが)

    キャンセル

  • 2016/01/22 11:45

    回答有難うございました。
    DISTINCTのSQLは後ほど、確認させて頂きたいと思いますが、
    今日、新たに、新規ユーザ数取得で、問題が発見されました。
    下記のSQLでMIN(access_day)で取得できるはずだったんですが、
    12月のデータは合ってたのですが、
    アクセスタイプの取得を開始したのが、8月からなので、
    普通に考えたら、8月はアクセス数全てが新規ユーザのはずだと思うのですが、
    実際には、59人中、17人が新規ユーザとなってしまいました。
    何か、原因、解決策等ありますでしょうか?
    (そもそもの、新規ユーザ取得SQLのロジックが破たんしているようで…)
    すみません。

    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 4 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'four',
    COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 5 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'five',
    COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 6 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'six',
    COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 7 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'seven',
    COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 8 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'eight',
    COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 9 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'nine',
    COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 10 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'ten',
    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

    キャンセル

  • 2016/01/25 15:00

    回答有難うございました。
    DISTINCTを使ったuser_idで、重複なしのカウントができました。
    表の形式が他の表とちがってしまいましたが、必要な情報は取得できるようになりました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.01%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる
  • トップ
  • PHPに関する質問
  • 【追記】アクセスログの表から新規ユーザとリピーターの割合(月ごと→週ごと)が知りたい