SQL request to get daily stats

  • Resolved stanxxx

    (@stanxxx)


    5 months, 1 week ago

    Hello!

    We would like to write a script to update a db table every day to get the daily stats, for our client. Reading the stats day by day on the site is resources consuming.

    Can you tell me what would be the SQL request to get the daily stats (only the total views by day)?

    Thank you!

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author Ben Sibley

    (@bensibley)

    Hi there,

    That’s a good question. We have an API function called iawp_analytics() that will make this easy: https://independentwp.com/knowledgebase/developer/developer-api/. It can get stats from any date range, so you could loop over the last 30 days and call this function for each day.

    Thread Starter stanxxx

    (@stanxxx)

    Hello, thank you! But we prefer run independent scripts on another server for stats, that concern a few “big” sites.

    There is something I don’t understand: for example for June 10th, on a smaller site:

    SELECT COUNT(*) from wp_independent_analytics_views WHERE viewed_at LIKE ‘%2024-06-10%’

    SQL routine returns 16162 but dashboard shows 16444.

    Same for all dates, on various sites.

    Where does this difference come from ?

    Thank you !

    Plugin Author Ben Sibley

    (@bensibley)

    There could be a timezone difference between the server where the query is made and the site’s WP timezone. We also apply some filters behind the scenes on certain menus, like excluding views in the Geographic report where a location wasn’t found.

Viewing 3 replies - 1 through 3 (of 3 total)
  • You must be logged in to reply to this topic.