Website analytics

A new page is now available that has been in development lately of a different nature to the existing content on the website. We use the privacy friendly open source website analytics platform Matomo that is hosted on-premises where we fully own the data that allows us to understand the usage and interest in this service we provide.

This website analytics data have been used to determine whether there is a correlation between the visits are made to the website compared to amount of the rainfall at the time and to test whether visitor activity is stronger during wetter weather. From this it have been discovered that at this time that while a lot of visits are made when there is no rainfall, when expressed in percentage terms that there is a general trend in more interest in the website during wetter weather, whilst visits across the range of rainfall totals are very consistent.

This information of visits by rainfall have been published on the new page as four charts using this information calculated from our databases. In addition to this, more standard summarised data over time of the number of visits, the type of devices used and the visits made by returning and frequently returning visitors. On that page are explanations of the definitions relating to this data and also there are key metric for various periods to provide a quick summary.

Thank you to all those who have shown an interest in this website and the many who have come back to this website many times. I have been quite surprised at the strong visitor activity that has occurred and there is clearly an interest on local real-time weather data. This website is in continued development with completed new works announced on this blog in addition to updates to the website that are mentioned on our Website Info page.

The below information is provided to share how this page is put together at this time for anyone who want to have more understanding of this. Both the analytics data and weather data are stored in MySQL databases, which are both used to create four database tables containing the data for the four graphs for the visits by rainfall. These calculations use table joins and appropriate aggregations to produce the data to allow for a fair comparison. But because these are quite complex calculations the database queries are not executed when a graph is viewed as retrieving the current data will add significant time to load the graph.

So given that this data doesn’t change too quickly, a SQL procedure is executed once a month to update the data for when someone visits the page. The SQL code used in that procedure is similar to the below example. The methodology used is that a table is created to calculate the amount of rainfall in the previous 24 hours for each hour during the past year, of which this data is not readily available in the database. This is derived by first calculating the rain for each hour, then the cumulative rainfall during the last year for each hour and then calculate the amount of rainfall for each hour compared to the same time in the previous day.

Once we have that data then table joins are used to merge the analytics data to this rainfall data for each of the four tables. Finally at the end the rainfall data is deleted, as the data is no longer needed.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateTbls_recent_rainfall_by_visits`()

BEGIN 
DECLARE exit handler for SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,
@errno = MYSQL_ERRNO,
@text = MESSAGE_TEXT;
SET
  @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT
  @full_error;
END;
/* create table of rainfall in past 24 hours for each hour for past year - used for each of the following 4 create tables */
DROP TABLE IF EXISTS last_24_hrs_rain_past_year;
CREATE TABLE last_24_hrs_rain_past_year (
  updated_time DATETIME NOT NULL,
  to_date DATETIME NOT NULL,
  from_date DATETIME NOT NULL,
  rain_in_last_24_hours FLOAT(5, 1) NULL,
  cumulative_rain FLOAT(5, 1) NULL,
  PRIMARY KEY (to_date)
)
SELECT
  DATE_ADD(UTC_TIMESTAMP(), INTERVAL 10 HOUR) AS updated_time,
  ROUND(HourlyCumulativeRain_a.cumulative_rain, 1) AS cumulative_rain,
  HourlyCumulativeRain_a.date_hour AS to_date,
  HourlyCumulativeRain_b.date_hour AS from_date,
  ROUND(
    (
      HourlyCumulativeRain_a.cumulative_rain - HourlyCumulativeRain_b.cumulative_rain
    ),
    1
  ) AS rain_in_last_24_hours
FROM
  (
   /* Query to return the cumulative rainfall for each hour for the past year plus 1 day and for the TO_DATE field of the outer query */
    SELECT
      hour AS date_hour,
      ROUND((@CumlativeRain: = @CumlativeRain + rain_hour), 1) as cumulative_rain
    FROM
      (
        SELECT
          hour,
          SUM(rain_since_midnight_hour) as rain_hour
        FROM
          (
            SELECT
              LogDateTime AS hour,
              IF(
                RainSinceMidnight > 0,
                RainSinceMidnight - @lastValue,
                0
              ) AS rain_since_midnight_hour,
              @lastValue: = RainSinceMidnight,
              @CumlativeRain: = 0
            FROM
              weatherdata.Monthly m,
              (
                SELECT
                  @lastValue: = 0
              ) SQLVars
            WHERE
              LogDateTime >= (CURDATE() - INTERVAL 1 YEAR - INTERVAL 1 DAY)
            GROUP BY
              YEAR(m.LogDateTime),
              MONTH(m.LogDateTime),
              DAY(m.LogDateTime),
              HOUR(m.LogDateTime),
              MINUTE(m.LogDateTime)
            ORDER BY
              (LogDateTime)
          ) as m1
        GROUP BY
          YEAR(hour),
          MONTH(hour),
          DAY(hour),
          HOUR(hour)
      ) as m2
    GROUP BY
      hour
    ORDER BY
      hour
  ) AS HourlyCumulativeRain_a
  LEFT JOIN (
   /* Query to return the cumulative rainfall for each hour for the past year plus 1 day for the FROM_DATE field of the outer query */
    SELECT
      hour AS date_hour,
      ROUND((@CumlativeRain: = @CumlativeRain + rain_hour), 1) as cumulative_rain
    FROM
      (
        SELECT
          hour,
          SUM(rain_since_midnight_hour) as rain_hour
        FROM
          (
            SELECT
              LogDateTime AS hour,
              IF(
                RainSinceMidnight > 0,
                RainSinceMidnight - @lastValue,
                0
              ) AS rain_since_midnight_hour,
              @lastValue: = RainSinceMidnight,
              @CumlativeRain: = 0
            FROM
              weatherdata.Monthly m,
              (
                SELECT
                  @lastValue: = 0
              ) SQLVars
            WHERE
              LogDateTime >= (CURDATE() - INTERVAL 1 YEAR - INTERVAL 1 DAY)
            GROUP BY
              YEAR(m.LogDateTime),
              MONTH(m.LogDateTime),
              DAY(m.LogDateTime),
              HOUR(m.LogDateTime),
              MINUTE(m.LogDateTime)
            ORDER BY
              (LogDateTime)
          ) as m1
        GROUP BY
          YEAR(hour),
          MONTH(hour),
          DAY(hour),
          HOUR(hour)
      ) as m2
    GROUP BY
      hour
    ORDER BY
      hour
  ) AS HourlyCumulativeRain_b ON /* Join the inner queries to find the rainfall in the previous 24 hours for each hour */
  HourlyCumulativeRain_a.date_hour - INTERVAL 1 DAY = HourlyCumulativeRain_b.date_hour
WHERE
  HourlyCumulativeRain_b.cumulative_rain IS NOT NULL;
/* end of create table */

  -- query1 to create table -average daily rain by number of visitors
  DROP TABLE IF EXISTS avg_daily_rain_by_visits;
CREATE TABLE avg_daily_rain_by_visits (
    updated_time DATETIME NOT NULL,
    visit_time_count SMALLINT NOT NULL,
    visit_total SMALLINT NOT NULL,
    rain_in_last_24_hours SMALLINT NULL,
    PRIMARY KEY (rain_in_last_24_hours)
  )
SELECT
  updated_time,
  COUNT(visit_time_hour) AS visit_time_count,
  SUM(visit_count) AS visit_total,
  rain_in_last_24_hours
FROM
  (
    SELECT
      last_24_hrs_rain_past_year.updated_time,
      DATE_ADD(
        DATE_FORMAT(
          matomo.mtys_log_link_visit_action.server_time,
          "%Y-%m-%d %H"
        ),
        INTERVAL 10 HOUR
      ) AS visit_time_hour,
      COUNT(
        DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
      ) AS visit_count,
      ROUND(
        AVG(last_24_hrs_rain_past_year.rain_in_last_24_hours),
        0
      ) AS rain_in_last_24_hours
    FROM
      matomo.mtys_log_link_visit_action
      JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = YEAR(last_24_hrs_rain_past_year.to_date)
      AND MONTH(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = MONTH(last_24_hrs_rain_past_year.to_date)
      AND DAY(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = DAY(last_24_hrs_rain_past_year.to_date)
      AND HOUR(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = HOUR(last_24_hrs_rain_past_year.to_date)
    WHERE
      matomo.mtys_log_link_visit_action.server_time >= (
        weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
      )
      AND last_24_hrs_rain_past_year.rain_in_last_24_hours <> 0
    GROUP BY
      YEAR(matomo.mtys_log_link_visit_action.server_time),
      MONTH(matomo.mtys_log_link_visit_action.server_time),
      DAY(matomo.mtys_log_link_visit_action.server_time)
  ) AS Data
GROUP BY
  rain_in_last_24_hours;

/* query2 to create table - average hourly rain by number of visitors */
  DROP TABLE IF EXISTS avg_hourly_rain_by_visits;
CREATE TABLE avg_hourly_rain_by_visits (
    updated_time DATETIME NOT NULL,
    visit_time_count SMALLINT NOT NULL,
    visit_total SMALLINT NOT NULL,
    rain_in_last_24_hours SMALLINT NULL,
    PRIMARY KEY (rain_in_last_24_hours)
  )
SELECT
  updated_time,
  COUNT(visit_time_hour) AS visit_time_count,
  SUM(visit_count) AS visit_total,
  rain_in_last_24_hours
FROM
  (
    SELECT
      last_24_hrs_rain_past_year.updated_time,
      DATE_ADD(
        DATE_FORMAT(
          matomo.mtys_log_link_visit_action.server_time,
          "%Y-%m-%d %H"
        ),
        INTERVAL 10 HOUR
      ) AS visit_time_hour,
      COUNT(
        DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
      ) AS visit_count,
      ROUND(
        AVG(last_24_hrs_rain_past_year.rain_in_last_24_hours),
        0
      ) AS rain_in_last_24_hours
    FROM
      matomo.mtys_log_link_visit_action
      JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = YEAR(last_24_hrs_rain_past_year.to_date)
      AND MONTH(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = MONTH(last_24_hrs_rain_past_year.to_date)
      AND DAY(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = DAY(last_24_hrs_rain_past_year.to_date)
      AND HOUR(
        DATE_ADD(
          matomo.mtys_log_link_visit_action.server_time,
          INTERVAL 10 HOUR
        )
      ) = HOUR(last_24_hrs_rain_past_year.to_date)
    WHERE
      matomo.mtys_log_link_visit_action.server_time >= (
        weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
      )
      AND last_24_hrs_rain_past_year.rain_in_last_24_hours <> 0
    GROUP BY
      YEAR(matomo.mtys_log_link_visit_action.server_time),
      MONTH(matomo.mtys_log_link_visit_action.server_time),
      DAY(matomo.mtys_log_link_visit_action.server_time),
      HOUR(matomo.mtys_log_link_visit_action.server_time)
  ) AS Data
GROUP BY
  rain_in_last_24_hours;

/* query3 to create table - grouped hourly rainfall by visitors */
  DROP TABLE IF EXISTS grouped_hourly_rain_by_visits;
CREATE TABLE grouped_hourly_rain_by_visits (
    updated_time DATETIME NOT NULL,
    rain_in_last_24_hours_grouped VARCHAR(10),
    visit_time_count SMALLINT NOT NULL,
    visitor_total SMALLINT NOT NULL,
    visitor_percent FLOAT(5, 4) NULL,
    PRIMARY KEY (rain_in_last_24_hours_grouped)
  )
SELECT
  updated_time,
  rain_in_last_24_hours_grouped,
  visit_time_count,
  visitor_total,
  visitor_total / total_visitors.visitor_grand_total AS visitor_percent
FROM
  /* take the visitor and recent rainfall values and bin into the standard rainfall groups */
  (
    SELECT
      updated_time,
      COUNT(visit_time_hour) AS visit_time_count,
      SUM(visitor_count) AS visitor_total,
      CASE
        WHEN rain_in_last_24_hours = 0 THEN 0
        WHEN rain_in_last_24_hours = 0.2 THEN 0.2
        WHEN rain_in_last_24_hours BETWEEN 0.2
        AND 1 THEN '0.4 to <1'
        WHEN rain_in_last_24_hours BETWEEN 1
        AND 5 THEN '1 to <5'
        WHEN rain_in_last_24_hours BETWEEN 5
        AND 10 THEN '5 to <10'
        WHEN rain_in_last_24_hours BETWEEN 10
        AND 20 THEN '10 to <20'
        WHEN rain_in_last_24_hours BETWEEN 20
        AND 50 THEN '20 to <50'
        WHEN rain_in_last_24_hours BETWEEN 50
        AND 100 THEN '50 to <100'
        WHEN rain_in_last_24_hours >= 100 THEN '>=100'
        ELSE ROUND(rain_in_last_24_hours, 0)
      END AS rain_in_last_24_hours_grouped,
      rain_in_last_24_hours
    FROM
      (
        /* Inner query to join visitor information to rainfall in the preceding 24 hours for the past year. */
        SELECT
          weatherdata.last_24_hrs_rain_past_year.updated_time AS updated_time,
          DATE_ADD(
            DATE_FORMAT(
              matomo.mtys_log_link_visit_action.server_time,
              "%Y-%m-%d %H"
            ),
            INTERVAL 10 HOUR
          ) AS visit_time_hour,
          COUNT(
            DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
          ) AS visitor_count,
          last_24_hrs_rain_past_year.rain_in_last_24_hours
        FROM
          matomo.mtys_log_link_visit_action
          LEFT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = YEAR(last_24_hrs_rain_past_year.to_date)
          AND MONTH(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = MONTH(last_24_hrs_rain_past_year.to_date)
          AND DAY(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = DAY(last_24_hrs_rain_past_year.to_date)
          AND HOUR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = HOUR(last_24_hrs_rain_past_year.to_date)
        WHERE
          matomo.mtys_log_link_visit_action.server_time >= (
            weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
          )
        GROUP BY
          YEAR(matomo.mtys_log_link_visit_action.server_time),
          MONTH(matomo.mtys_log_link_visit_action.server_time),
          DAY(matomo.mtys_log_link_visit_action.server_time),
          HOUR(matomo.mtys_log_link_visit_action.server_time)
      ) AS Data1
    GROUP BY
      rain_in_last_24_hours_grouped
  ) AS Data2 /* Cross Join query to find the total number of visits. This is used in the outer SELECT to calculate the % distribution of visits by grouped rainfall amounts */
  CROSS JOIN (
    SELECT
      SUM(visitor_count) AS visitor_grand_total
    FROM
      (
        SELECT
          COUNT(
            DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
          ) AS visitor_count
        FROM
          matomo.mtys_log_link_visit_action
          LEFT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = YEAR(last_24_hrs_rain_past_year.to_date)
          AND MONTH(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = MONTH(last_24_hrs_rain_past_year.to_date)
          AND DAY(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = DAY(last_24_hrs_rain_past_year.to_date)
          AND HOUR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = HOUR(last_24_hrs_rain_past_year.to_date)
        WHERE
          matomo.mtys_log_link_visit_action.server_time >= (
            weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR
          )
        GROUP BY
          YEAR(matomo.mtys_log_link_visit_action.server_time),
          MONTH(matomo.mtys_log_link_visit_action.server_time),
          DAY(matomo.mtys_log_link_visit_action.server_time),
          HOUR(matomo.mtys_log_link_visit_action.server_time)
      ) AS Data
  ) as total_visitors
GROUP BY
  rain_in_last_24_hours_grouped
ORDER BY
  rain_in_last_24_hours;

/* query4 to create table - visitor engagement by grouped daily rainfall */
  /* Query to return the % visitors engagement by rainfall amount joining weatherdata to analytics data */
  DROP TABLE IF EXISTS visit_activity_by_grouped_daily_rain;
CREATE TABLE visit_activity_by_grouped_daily_rain (
    updated_time DATETIME NOT NULL,
    rain_in_last_24_hours_grouped VARCHAR(10) NOT NULL,
    rain_in_last_24_hours_count SMALLINT NOT NULL,
    visit_time_count SMALLINT NULL,
    visitor_total SMALLINT NOT NULL,
    visitor_percent_of_grouped_rain FLOAT(5, 4) NOT NULL,
    PRIMARY KEY (rain_in_last_24_hours_grouped)
  )
SELECT
  updated_time,
  rain_in_last_24_hours_grouped,
  rain_in_last_24_hours_count,
  visit_time_count,
  visitor_total,
  ROUND(visit_time_count / rain_in_last_24_hours_count, 3) AS visitor_percent_of_grouped_rain
FROM
  /* take the visitor and recent rainfall values and bin into the standard rainfall groups */
  (
    SELECT
      updated_time,
      COUNT(rain_time_hour) AS rain_in_last_24_hours_count,
      COUNT(visit_time_hour) AS visit_time_count,
      SUM(visitor_count) AS visitor_total,
      IF(
        rain_in_last_24_hours = 0,
        0,
        IF(
          rain_in_last_24_hours = 0.2,
          0.2,
          IF(
            rain_in_last_24_hours BETWEEN 0.2
            AND 1,
            '0.4 to <1',
            IF(
              rain_in_last_24_hours BETWEEN 1
              AND 5,
              '1 to <5',
              IF(
                rain_in_last_24_hours BETWEEN 5
                AND 10,
                '5 to <10',
                IF(
                  rain_in_last_24_hours BETWEEN 10
                  AND 20,
                  '10 to <20',
                  IF(
                    rain_in_last_24_hours BETWEEN 20
                    AND 50,
                    '20 to <50',
                    IF(
                      rain_in_last_24_hours BETWEEN 50
                      AND 100,
                      '50 to <100',
                      IF(
                        rain_in_last_24_hours BETWEEN 100
                        AND 1000,
                        '>100',
                        ROUND(rain_in_last_24_hours, 0)
                      )
                    )
                  )
                )
              )
            )
          )
        )
      ) as rain_in_last_24_hours_grouped,
      rain_in_last_24_hours
    FROM
      (
        /* Inner query to join visitor information to average rainfall grouped by day by rainfall in the preceding 24 hours for the past year */
        SELECT
          last_24_hrs_rain_past_year.updated_time AS updated_time,
          DATE_ADD(
            DATE_FORMAT(
              last_24_hrs_rain_past_year.to_date,
              "%Y-%m-%d %H"
            ),
            INTERVAL 10 HOUR
          ) AS rain_time_hour,
          DATE_ADD(
            DATE_FORMAT(
              matomo.mtys_log_link_visit_action.server_time,
              "%Y-%m-%d %H"
            ),
            INTERVAL 10 HOUR
          ) AS visit_time_hour,
          CASE
            WHEN COUNT(
              DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
            ) >= 1 THEN COUNT(
              DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)
            )
            ELSE 0
          END AS visitor_count,
          AVG(
            weatherdata.last_24_hrs_rain_past_year.rain_in_last_24_hours
          ) AS rain_in_last_24_hours
        FROM
          matomo.mtys_log_link_visit_action
          RIGHT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = YEAR(last_24_hrs_rain_past_year.to_date)
          AND MONTH(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = MONTH(last_24_hrs_rain_past_year.to_date)
          AND DAY(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = DAY(last_24_hrs_rain_past_year.to_date)
          AND HOUR(
            DATE_ADD(
              matomo.mtys_log_link_visit_action.server_time,
              INTERVAL 10 HOUR
            )
          ) = HOUR(last_24_hrs_rain_past_year.to_date)
        WHERE
          last_24_hrs_rain_past_year.to_date >= (CURDATE() - INTERVAL 1 YEAR)
        GROUP BY
          YEAR(last_24_hrs_rain_past_year.to_date),
          MONTH(last_24_hrs_rain_past_year.to_date),
          DAY(last_24_hrs_rain_past_year.to_date)
      ) AS Data
    GROUP BY
      rain_in_last_24_hours_grouped
  ) AS DATA
GROUP BY
  rain_in_last_24_hours_grouped
ORDER BY
  rain_in_last_24_hours;

/* finally delete the rainfall table initially created as we don't need that data now */
  DROP TABLE IF EXISTS last_24_hrs_rain_past_year;
END$$
DELIMITER ;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `CreateTbls_recent_rainfall_by_visits`()

BEGIN

DECLARE exit handler for SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,

@errno = MYSQL_ERRNO,

@text = MESSAGE_TEXT;

SET

@full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);

SELECT

@full_error;

END;

/* create table of rainfall in past 24 hours for each hour for past year - used for each of the following 4 create tables */

DROP TABLE IF EXISTS last_24_hrs_rain_past_year;

CREATE TABLE last_24_hrs_rain_past_year (

updated_time DATETIME NOT NULL,

to_date DATETIME NOT NULL,

from_date DATETIME NOT NULL,

rain_in_last_24_hours FLOAT(5, 1) NULL,

cumulative_rain FLOAT(5, 1) NULL,

PRIMARY KEY (to_date)

)

SELECT

DATE_ADD(UTC_TIMESTAMP(), INTERVAL 10 HOUR) AS updated_time,

ROUND(HourlyCumulativeRain_a.cumulative_rain, 1) AS cumulative_rain,

HourlyCumulativeRain_a.date_hour AS to_date,

HourlyCumulativeRain_b.date_hour AS from_date,

ROUND(

(

HourlyCumulativeRain_a.cumulative_rain - HourlyCumulativeRain_b.cumulative_rain

) AS rain_in_last_24_hours

FROM

(

/* Query to return the cumulative rainfall for each hour for the past year plus 1 day and for the TO_DATE field of the outer query */

SELECT

hour AS date_hour,

ROUND((@CumlativeRain: = @CumlativeRain + rain_hour), 1) as cumulative_rain

FROM

(

SELECT

hour,

SUM(rain_since_midnight_hour) as rain_hour

FROM

(

SELECT

LogDateTime AS hour,

IF(

RainSinceMidnight > 0,

RainSinceMidnight - @lastValue,

) AS rain_since_midnight_hour,

@lastValue: = RainSinceMidnight,

@CumlativeRain: = 0

FROM

weatherdata.Monthly m,

(

SELECT

@lastValue: = 0

) SQLVars

WHERE

LogDateTime >= (CURDATE() - INTERVAL 1 YEAR - INTERVAL 1 DAY)

GROUP BY

YEAR(m.LogDateTime),

MONTH(m.LogDateTime),

DAY(m.LogDateTime),

HOUR(m.LogDateTime),

MINUTE(m.LogDateTime)

ORDER BY

(LogDateTime)

) as m1

GROUP BY

YEAR(hour),

MONTH(hour),

DAY(hour),

HOUR(hour)

) as m2

GROUP BY

hour

ORDER BY

hour

) AS HourlyCumulativeRain_a

LEFT JOIN (

/* Query to return the cumulative rainfall for each hour for the past year plus 1 day for the FROM_DATE field of the outer query */

SELECT

hour AS date_hour,

ROUND((@CumlativeRain: = @CumlativeRain + rain_hour), 1) as cumulative_rain

FROM

(

SELECT

hour,

SUM(rain_since_midnight_hour) as rain_hour

FROM

(

SELECT

LogDateTime AS hour,

IF(

RainSinceMidnight > 0,

RainSinceMidnight - @lastValue,

) AS rain_since_midnight_hour,

@lastValue: = RainSinceMidnight,

@CumlativeRain: = 0

FROM

weatherdata.Monthly m,

(

SELECT

@lastValue: = 0

) SQLVars

WHERE

LogDateTime >= (CURDATE() - INTERVAL 1 YEAR - INTERVAL 1 DAY)

GROUP BY

YEAR(m.LogDateTime),

MONTH(m.LogDateTime),

DAY(m.LogDateTime),

HOUR(m.LogDateTime),

MINUTE(m.LogDateTime)

ORDER BY

(LogDateTime)

) as m1

GROUP BY

YEAR(hour),

MONTH(hour),

DAY(hour),

HOUR(hour)

) as m2

GROUP BY

hour

ORDER BY

hour

) AS HourlyCumulativeRain_b ON /* Join the inner queries to find the rainfall in the previous 24 hours for each hour */

HourlyCumulativeRain_a.date_hour - INTERVAL 1 DAY = HourlyCumulativeRain_b.date_hour

WHERE

HourlyCumulativeRain_b.cumulative_rain IS NOT NULL;

/* end of create table */

-- query1 to create table -average daily rain by number of visitors

DROP TABLE IF EXISTS avg_daily_rain_by_visits;

CREATE TABLE avg_daily_rain_by_visits (

updated_time DATETIME NOT NULL,

visit_time_count SMALLINT NOT NULL,

visit_total SMALLINT NOT NULL,

rain_in_last_24_hours SMALLINT NULL,

PRIMARY KEY (rain_in_last_24_hours)

)

SELECT

updated_time,

COUNT(visit_time_hour) AS visit_time_count,

SUM(visit_count) AS visit_total,

rain_in_last_24_hours

FROM

(

SELECT

last_24_hrs_rain_past_year.updated_time,

DATE_ADD(

DATE_FORMAT(

matomo.mtys_log_link_visit_action.server_time,

"%Y-%m-%d %H"

INTERVAL 10 HOUR

) AS visit_time_hour,

COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

) AS visit_count,

ROUND(

AVG(last_24_hrs_rain_past_year.rain_in_last_24_hours),

) AS rain_in_last_24_hours

FROM

matomo.mtys_log_link_visit_action

JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = YEAR(last_24_hrs_rain_past_year.to_date)

AND MONTH(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = MONTH(last_24_hrs_rain_past_year.to_date)

AND DAY(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = DAY(last_24_hrs_rain_past_year.to_date)

AND HOUR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = HOUR(last_24_hrs_rain_past_year.to_date)

WHERE

matomo.mtys_log_link_visit_action.server_time >= (

weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR

)

AND last_24_hrs_rain_past_year.rain_in_last_24_hours <> 0

GROUP BY

YEAR(matomo.mtys_log_link_visit_action.server_time),

MONTH(matomo.mtys_log_link_visit_action.server_time),

DAY(matomo.mtys_log_link_visit_action.server_time)

) AS Data

GROUP BY

rain_in_last_24_hours;

/* query2 to create table - average hourly rain by number of visitors */

DROP TABLE IF EXISTS avg_hourly_rain_by_visits;

CREATE TABLE avg_hourly_rain_by_visits (

updated_time DATETIME NOT NULL,

visit_time_count SMALLINT NOT NULL,

visit_total SMALLINT NOT NULL,

rain_in_last_24_hours SMALLINT NULL,

PRIMARY KEY (rain_in_last_24_hours)

)

SELECT

updated_time,

COUNT(visit_time_hour) AS visit_time_count,

SUM(visit_count) AS visit_total,

rain_in_last_24_hours

FROM

(

SELECT

last_24_hrs_rain_past_year.updated_time,

DATE_ADD(

DATE_FORMAT(

matomo.mtys_log_link_visit_action.server_time,

"%Y-%m-%d %H"

INTERVAL 10 HOUR

) AS visit_time_hour,

COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

) AS visit_count,

ROUND(

AVG(last_24_hrs_rain_past_year.rain_in_last_24_hours),

) AS rain_in_last_24_hours

FROM

matomo.mtys_log_link_visit_action

JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = YEAR(last_24_hrs_rain_past_year.to_date)

AND MONTH(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = MONTH(last_24_hrs_rain_past_year.to_date)

AND DAY(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = DAY(last_24_hrs_rain_past_year.to_date)

AND HOUR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = HOUR(last_24_hrs_rain_past_year.to_date)

WHERE

matomo.mtys_log_link_visit_action.server_time >= (

weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR

)

AND last_24_hrs_rain_past_year.rain_in_last_24_hours <> 0

GROUP BY

YEAR(matomo.mtys_log_link_visit_action.server_time),

MONTH(matomo.mtys_log_link_visit_action.server_time),

DAY(matomo.mtys_log_link_visit_action.server_time),

HOUR(matomo.mtys_log_link_visit_action.server_time)

) AS Data

GROUP BY

rain_in_last_24_hours;

/* query3 to create table - grouped hourly rainfall by visitors */

DROP TABLE IF EXISTS grouped_hourly_rain_by_visits;

CREATE TABLE grouped_hourly_rain_by_visits (

updated_time DATETIME NOT NULL,

rain_in_last_24_hours_grouped VARCHAR(10),

visit_time_count SMALLINT NOT NULL,

visitor_total SMALLINT NOT NULL,

visitor_percent FLOAT(5, 4) NULL,

PRIMARY KEY (rain_in_last_24_hours_grouped)

)

SELECT

updated_time,

rain_in_last_24_hours_grouped,

visit_time_count,

visitor_total,

visitor_total / total_visitors.visitor_grand_total AS visitor_percent

FROM

/* take the visitor and recent rainfall values and bin into the standard rainfall groups */

(

SELECT

updated_time,

COUNT(visit_time_hour) AS visit_time_count,

SUM(visitor_count) AS visitor_total,

CASE

WHEN rain_in_last_24_hours = 0 THEN 0

WHEN rain_in_last_24_hours = 0.2 THEN 0.2

WHEN rain_in_last_24_hours BETWEEN 0.2

AND 1 THEN '0.4 to <1'

WHEN rain_in_last_24_hours BETWEEN 1

AND 5 THEN '1 to <5'

WHEN rain_in_last_24_hours BETWEEN 5

AND 10 THEN '5 to <10'

WHEN rain_in_last_24_hours BETWEEN 10

AND 20 THEN '10 to <20'

WHEN rain_in_last_24_hours BETWEEN 20

AND 50 THEN '20 to <50'

WHEN rain_in_last_24_hours BETWEEN 50

AND 100 THEN '50 to <100'

WHEN rain_in_last_24_hours >= 100 THEN '>=100'

ELSE ROUND(rain_in_last_24_hours, 0)

END AS rain_in_last_24_hours_grouped,

rain_in_last_24_hours

FROM

(

/* Inner query to join visitor information to rainfall in the preceding 24 hours for the past year. */

SELECT

weatherdata.last_24_hrs_rain_past_year.updated_time AS updated_time,

DATE_ADD(

DATE_FORMAT(

matomo.mtys_log_link_visit_action.server_time,

"%Y-%m-%d %H"

INTERVAL 10 HOUR

) AS visit_time_hour,

COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

) AS visitor_count,

last_24_hrs_rain_past_year.rain_in_last_24_hours

FROM

matomo.mtys_log_link_visit_action

LEFT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = YEAR(last_24_hrs_rain_past_year.to_date)

AND MONTH(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = MONTH(last_24_hrs_rain_past_year.to_date)

AND DAY(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = DAY(last_24_hrs_rain_past_year.to_date)

AND HOUR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = HOUR(last_24_hrs_rain_past_year.to_date)

WHERE

matomo.mtys_log_link_visit_action.server_time >= (

weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR

)

GROUP BY

YEAR(matomo.mtys_log_link_visit_action.server_time),

MONTH(matomo.mtys_log_link_visit_action.server_time),

DAY(matomo.mtys_log_link_visit_action.server_time),

HOUR(matomo.mtys_log_link_visit_action.server_time)

) AS Data1

GROUP BY

rain_in_last_24_hours_grouped

) AS Data2 /* Cross Join query to find the total number of visits. This is used in the outer SELECT to calculate the % distribution of visits by grouped rainfall amounts */

CROSS JOIN (

SELECT

SUM(visitor_count) AS visitor_grand_total

FROM

(

SELECT

COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

) AS visitor_count

FROM

matomo.mtys_log_link_visit_action

LEFT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = YEAR(last_24_hrs_rain_past_year.to_date)

AND MONTH(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = MONTH(last_24_hrs_rain_past_year.to_date)

AND DAY(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = DAY(last_24_hrs_rain_past_year.to_date)

AND HOUR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = HOUR(last_24_hrs_rain_past_year.to_date)

WHERE

matomo.mtys_log_link_visit_action.server_time >= (

weatherdata.last_24_hrs_rain_past_year.updated_time - INTERVAL 1 YEAR

)

GROUP BY

YEAR(matomo.mtys_log_link_visit_action.server_time),

MONTH(matomo.mtys_log_link_visit_action.server_time),

DAY(matomo.mtys_log_link_visit_action.server_time),

HOUR(matomo.mtys_log_link_visit_action.server_time)

) AS Data

) as total_visitors

GROUP BY

rain_in_last_24_hours_grouped

ORDER BY

rain_in_last_24_hours;

/* query4 to create table - visitor engagement by grouped daily rainfall */

/* Query to return the % visitors engagement by rainfall amount joining weatherdata to analytics data */

DROP TABLE IF EXISTS visit_activity_by_grouped_daily_rain;

CREATE TABLE visit_activity_by_grouped_daily_rain (

updated_time DATETIME NOT NULL,

rain_in_last_24_hours_grouped VARCHAR(10) NOT NULL,

rain_in_last_24_hours_count SMALLINT NOT NULL,

visit_time_count SMALLINT NULL,

visitor_total SMALLINT NOT NULL,

visitor_percent_of_grouped_rain FLOAT(5, 4) NOT NULL,

PRIMARY KEY (rain_in_last_24_hours_grouped)

)

SELECT

updated_time,

rain_in_last_24_hours_grouped,

rain_in_last_24_hours_count,

visit_time_count,

visitor_total,

ROUND(visit_time_count / rain_in_last_24_hours_count, 3) AS visitor_percent_of_grouped_rain

FROM

/* take the visitor and recent rainfall values and bin into the standard rainfall groups */

(

SELECT

updated_time,

COUNT(rain_time_hour) AS rain_in_last_24_hours_count,

COUNT(visit_time_hour) AS visit_time_count,

SUM(visitor_count) AS visitor_total,

IF(

rain_in_last_24_hours = 0,

IF(

rain_in_last_24_hours = 0.2,

0.2,

IF(

rain_in_last_24_hours BETWEEN 0.2

AND 1,

'0.4 to <1',

IF(

rain_in_last_24_hours BETWEEN 1

AND 5,

'1 to <5',

IF(

rain_in_last_24_hours BETWEEN 5

AND 10,

'5 to <10',

IF(

rain_in_last_24_hours BETWEEN 10

AND 20,

'10 to <20',

IF(

rain_in_last_24_hours BETWEEN 20

AND 50,

'20 to <50',

IF(

rain_in_last_24_hours BETWEEN 50

AND 100,

'50 to <100',

IF(

rain_in_last_24_hours BETWEEN 100

AND 1000,

'>100',

ROUND(rain_in_last_24_hours, 0)

)

) as rain_in_last_24_hours_grouped,

rain_in_last_24_hours

FROM

(

/* Inner query to join visitor information to average rainfall grouped by day by rainfall in the preceding 24 hours for the past year */

SELECT

last_24_hrs_rain_past_year.updated_time AS updated_time,

DATE_ADD(

DATE_FORMAT(

last_24_hrs_rain_past_year.to_date,

"%Y-%m-%d %H"

INTERVAL 10 HOUR

) AS rain_time_hour,

DATE_ADD(

DATE_FORMAT(

matomo.mtys_log_link_visit_action.server_time,

"%Y-%m-%d %H"

INTERVAL 10 HOUR

) AS visit_time_hour,

CASE

WHEN COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

) >= 1 THEN COUNT(

DISTINCT(matomo.mtys_log_link_visit_action.idvisitor)

)

ELSE 0

END AS visitor_count,

AVG(

weatherdata.last_24_hrs_rain_past_year.rain_in_last_24_hours

) AS rain_in_last_24_hours

FROM

matomo.mtys_log_link_visit_action

RIGHT JOIN weatherdata.last_24_hrs_rain_past_year ON YEAR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = YEAR(last_24_hrs_rain_past_year.to_date)

AND MONTH(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = MONTH(last_24_hrs_rain_past_year.to_date)

AND DAY(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = DAY(last_24_hrs_rain_past_year.to_date)

AND HOUR(

DATE_ADD(

matomo.mtys_log_link_visit_action.server_time,

INTERVAL 10 HOUR

)

) = HOUR(last_24_hrs_rain_past_year.to_date)

WHERE

last_24_hrs_rain_past_year.to_date >= (CURDATE() - INTERVAL 1 YEAR)

GROUP BY

YEAR(last_24_hrs_rain_past_year.to_date),

MONTH(last_24_hrs_rain_past_year.to_date),

DAY(last_24_hrs_rain_past_year.to_date)

) AS Data

GROUP BY

rain_in_last_24_hours_grouped

) AS DATA

GROUP BY

rain_in_last_24_hours_grouped

ORDER BY

rain_in_last_24_hours;

/* finally delete the rainfall table initially created as we don't need that data now */

DROP TABLE IF EXISTS last_24_hrs_rain_past_year;

END$$

DELIMITER ;

The other graphs that don’t compare the rainfall by visits use php scripts to run a query to retrieve the necessary information from the database and insert that data into arrays in a format that the Highcharts graphs can use. This is quite similar to the other graphs on the website. The Javascript code for the highcharts is heavily based on the graphs elsewhere on this website, which is accessible through viewing the page source code. From the Javascript code the code of the php scripts used as the data input can be accessed by appending a ?view=sce to the url.

The summary statistics at the top of the page uses data from a php script that contains a series of variables whose values are assigned from the result of SQL queries. That php script is used as an php include in the web page like this: include './utils/SQL-queries/analyticsData.php'; and the variables in that script are used within the table such as: <?php echo $visits7day ?>

Similarly a php script is used to return a variable for populating the update time for the visits by rainfall data. This is quite simple in that it returns the distinct updated_time field of the one of the database tables containing this data and the query is formatted in php to show the date in the required date format.

These are the SQL statements to calculate the summary statistics in the table:

/* construct a new query for visits and visitors in last 24 hrs */
	SELECT
					COUNT(DISTINCT(idvisit)) AS visits,
					COUNT(DISTINCT(idvisitor)) AS visitors
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );
	
/* construct a new query for visits in last 7 days */
				SELECT
					COUNT(DISTINCT(idvisit)) AS visits,
					COUNT(DISTINCT(idvisitor)) AS visitors
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );


/*construct a new query for visits in last 30 days*/
				SELECT
					COUNT(DISTINCT(idvisit)) AS visits,
					COUNT(DISTINCT(idvisitor)) AS visitors
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 MONTH );

/*construct a new query for visits -all time*/
				SELECT
					COUNT(DISTINCT(idvisit)) AS visits,
					COUNT(DISTINCT(idvisitor)) AS visitors
				FROM
					mtys_log_link_visit_action;

/*construct a new query for page views in last 24 hrs*/
				SELECT
					COUNT(idvisitor) AS page_views
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );

/* construct a new query for page views in last 7 days */
				SELECT
					COUNT(idvisitor) AS page_views
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );
 

/* construct a new query for page views in last 30 days*/
				SELECT
					COUNT(idvisitor) AS page_views
				FROM
					mtys_log_link_visit_action
				WHERE 
					DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 MONTH );

/* construct a new query for page views -all time*/
				SELECT
					COUNT(idvisitor) AS page_views
				FROM
					mtys_log_link_visit_action;

/* construct a new query for return visits in last 24 hrs*/
				SELECT
					COUNT(DISTINCT idvisit) AS return_visits
				FROM
					mtys_log_visit
				WHERE
					visitor_returning <> 0 
					AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );

/* construct a new query for return visits in last 7 days */
			SELECT
				COUNT(DISTINCT idvisit) AS return_visits
			FROM
				mtys_log_visit
            WHERE
            	visitor_returning <> 0 
                AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );

/* construct a new query for return visits in last 30 days */
			SELECT
				COUNT(DISTINCT idvisit) AS return_visits
			FROM
				mtys_log_visit
            WHERE
            	visitor_returning <> 0 
                AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 30 DAY );

/* construct a new query for return visits -all time*/
			SELECT
				COUNT(DISTINCT idvisit) AS return_visits
			FROM
				mtys_log_visit
            WHERE
            	visitor_returning <> 0;


/* construct a new query for frequent return visits in last 24 hrs*/
			/*To return the number of regular visitors and the number of visits*/
			SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits
			FROM(
				-- inner query to get all regular visitors  
				SELECT
						idvisitor AS visitors,
						count(idvisitor) as count_visits
				FROM
						mtys_log_visit
				WHERE
						visitor_returning > 0 
						AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY )
						AND visitor_days_since_last <=30
				GROUP BY 
						idvisitor
				) AS Data
				/* subquery to return just the visitors that have contributed 2 visits */
			WHERE count_visits >= 2;

/* construct a new query for frequent return visits in last 7 days*/
			/*-- To return the number of regular visitors and the number of visits*/
			SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits
			FROM(
				-- inner query to get all regular visitors  
				SELECT
						idvisitor AS visitors,
						count(idvisitor) as count_visits
				FROM
						mtys_log_visit
				WHERE
						visitor_returning > 0 
						AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY )
						AND visitor_days_since_last <=30
				GROUP BY 
						idvisitor
				) AS Data
				/* subquery to return just the visitors that have contributed 4 visits*/
			WHERE count_visits >= 4;
	
/* construct a new query for frequent return visits in last 30 days*/
			/*To return the number of regular visitors and the number of visits*/
			SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits
			FROM(
				/* inner query to get all regular visitors  */
				SELECT
						idvisitor AS visitors,
						count(idvisitor) as count_visits
				FROM
						mtys_log_visit
				WHERE
						visitor_returning > 0 
						AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 30 DAY )
						AND visitor_days_since_last <=30
				GROUP BY 
						idvisitor
				) AS Data
				/* subquery to return just the visitors that have contributed to 5 visits*/
			WHERE count_visits >= 5;

	
	
/* construct a new query for frequent return visits - all time*/
			/* To return the number of regular visitors and the number of visits*/
			SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits
			FROM(
				/* inner query to get all regular visitors */ 
				SELECT
						idvisitor AS visitors,
						count(idvisitor) as count_visits
				FROM
						mtys_log_visit
				WHERE
						visitor_returning > 0
						AND visitor_days_since_last <=30
				GROUP BY 
						idvisitor
				) AS Data
				/* subquery to return just the visitors that have contributed to 1 % of the total frequent return visitor numbers */
			WHERE count_visits >= (SELECT 
										0.01*COUNT(idvisitor) 
								   FROM 
										mtys_log_visit 
								   WHERE
										visitor_returning > 0 
										AND visitor_days_since_last <=30);

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

/* construct a new query for visits and visitors in last 24 hrs */

SELECT

COUNT(DISTINCT(idvisit)) AS visits,

COUNT(DISTINCT(idvisitor)) AS visitors

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );

/* construct a new query for visits in last 7 days */

SELECT

COUNT(DISTINCT(idvisit)) AS visits,

COUNT(DISTINCT(idvisitor)) AS visitors

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );

/*construct a new query for visits in last 30 days*/

SELECT

COUNT(DISTINCT(idvisit)) AS visits,

COUNT(DISTINCT(idvisitor)) AS visitors

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 MONTH );

/*construct a new query for visits -all time*/

SELECT

COUNT(DISTINCT(idvisit)) AS visits,

COUNT(DISTINCT(idvisitor)) AS visitors

FROM

mtys_log_link_visit_action;

/*construct a new query for page views in last 24 hrs*/

SELECT

COUNT(idvisitor) AS page_views

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );

/* construct a new query for page views in last 7 days */

SELECT

COUNT(idvisitor) AS page_views

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );

/* construct a new query for page views in last 30 days*/

SELECT

COUNT(idvisitor) AS page_views

FROM

mtys_log_link_visit_action

WHERE

DATE_ADD(server_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 MONTH );

/* construct a new query for page views -all time*/

SELECT

COUNT(idvisitor) AS page_views

FROM

mtys_log_link_visit_action;

/* construct a new query for return visits in last 24 hrs*/

SELECT

COUNT(DISTINCT idvisit) AS return_visits

FROM

mtys_log_visit

WHERE

visitor_returning <> 0

AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY );

/* construct a new query for return visits in last 7 days */

SELECT

COUNT(DISTINCT idvisit) AS return_visits

FROM

mtys_log_visit

WHERE

visitor_returning <> 0

AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY );

/* construct a new query for return visits in last 30 days */

SELECT

COUNT(DISTINCT idvisit) AS return_visits

FROM

mtys_log_visit

WHERE

visitor_returning <> 0

AND DATE_ADD(visit_last_action_time,INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 30 DAY );

/* construct a new query for return visits -all time*/

SELECT

COUNT(DISTINCT idvisit) AS return_visits

FROM

mtys_log_visit

WHERE

visitor_returning <> 0;

/* construct a new query for frequent return visits in last 24 hrs*/

/*To return the number of regular visitors and the number of visits*/

SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits

FROM(

-- inner query to get all regular visitors

SELECT

idvisitor AS visitors,

count(idvisitor) as count_visits

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 1 DAY )

AND visitor_days_since_last <=30

GROUP BY

idvisitor

) AS Data

/* subquery to return just the visitors that have contributed 2 visits */

WHERE count_visits >= 2;

/* construct a new query for frequent return visits in last 7 days*/

/*-- To return the number of regular visitors and the number of visits*/

SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits

FROM(

-- inner query to get all regular visitors

SELECT

idvisitor AS visitors,

count(idvisitor) as count_visits

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 7 DAY )

AND visitor_days_since_last <=30

GROUP BY

idvisitor

) AS Data

/* subquery to return just the visitors that have contributed 4 visits*/

WHERE count_visits >= 4;

/* construct a new query for frequent return visits in last 30 days*/

/*To return the number of regular visitors and the number of visits*/

SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits

FROM(

/* inner query to get all regular visitors */

SELECT

idvisitor AS visitors,

count(idvisitor) as count_visits

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) >= (CURDATE() - INTERVAL 30 DAY )

AND visitor_days_since_last <=30

GROUP BY

idvisitor

) AS Data

/* subquery to return just the visitors that have contributed to 5 visits*/

WHERE count_visits >= 5;

/* construct a new query for frequent return visits - all time*/

/* To return the number of regular visitors and the number of visits*/

SELECT COUNT(visitors) AS visitors, SUM(count_visits) as visits

FROM(

/* inner query to get all regular visitors */

SELECT

idvisitor AS visitors,

count(idvisitor) as count_visits

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND visitor_days_since_last <=30

GROUP BY

idvisitor

) AS Data

/* subquery to return just the visitors that have contributed to 1 % of the total frequent return visitor numbers */

WHERE count_visits >= (SELECT

0.01*COUNT(idvisitor)

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND visitor_days_since_last <=30);

And these are the SQL statements to return the data for the graphs excluding the visits by rainfall which while a php script also retrieves that data, those queries are very basic because the data is already pre-generated.

/* construct a new query for visits by month*/
			SELECT
				UNIX_TIMESTAMP(server_time),
				COUNT(DISTINCT(idvisit)) AS visits,
				COUNT(idvisitor) AS page_views,
				COUNT(DISTINCT(idvisitor)) AS visitors
			FROM
				mtys_log_link_visit_action
			GROUP BY
				YEAR(server_time),
				MONTH(server_time);
	
/* construct a new query for monthly visits by device type */
			SELECT UNIX_TIMESTAMP(date_format(visit_time, "%Y-%m-01")) as visit_time, desktop_visit, mobile_visit, tablet_visit, mobile_percent
			FROM (
				SELECT  server_time as visit_time,     
						COUNT(DISTINCT CASE WHEN config_device_type = 0 THEN mtys_log_link_visit_action.idvisit END) AS desktop_visit,         
						COUNT(DISTINCT CASE WHEN config_device_type = 1 THEN mtys_log_link_visit_action.idvisit END) AS mobile_visit,         
						COUNT(DISTINCT CASE WHEN config_device_type = 2 THEN mtys_log_link_visit_action.idvisit END) AS tablet_visit,                                       
						ROUND(COUNT(DISTINCT CASE WHEN config_device_type = 1 THEN mtys_log_link_visit_action.idvisit END) / COUNT(DISTINCT mtys_log_link_visit_action.idvisit) *100,1) AS mobile_percent 

				FROM mtys_log_visit INNER JOIN mtys_log_link_visit_action ON mtys_log_visit.idvisitor = mtys_log_link_visit_action.idvisitor 
				GROUP BY YEAR(visit_time), MONTH(visit_time)
				) AS Data
			GROUP BY YEAR(visit_time), MONTH(visit_time);


/*construct a new query for monthly return and frequent return visits*/
				SELECT 	UNIX_TIMESTAMP(date_format(visit_last_action_time, "%Y-%m-01")) AS visit_last_action_time,
				COUNT(DISTINCT idvisitor) as visitors,
				SUM(visits) AS visits
				FROM(
					SELECT 	*
						FROM(
							SELECT
								DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) AS visit_last_action_time,
								idvisitor,
								COUNT( idvisitor) AS visits
							FROM
									mtys_log_visit
							WHERE
									visitor_returning > 0 
									AND visitor_days_since_last <=30
							GROUP BY year(visit_last_action_time), month(visit_last_action_time), idvisitor
							ORDER BY `visits`  DESC
							) AS Visits
					WHERE visits >= 5
				)AS visits_summ
			GROUP BY year(visit_last_action_time), month(visit_last_action_time);

/* construct a new query for visits by month*/

SELECT

UNIX_TIMESTAMP(server_time),

COUNT(DISTINCT(idvisit)) AS visits,

COUNT(idvisitor) AS page_views,

COUNT(DISTINCT(idvisitor)) AS visitors

FROM

mtys_log_link_visit_action

GROUP BY

YEAR(server_time),

MONTH(server_time);

/* construct a new query for monthly visits by device type */

SELECT UNIX_TIMESTAMP(date_format(visit_time, "%Y-%m-01")) as visit_time, desktop_visit, mobile_visit, tablet_visit, mobile_percent

FROM (

SELECT server_time as visit_time,

COUNT(DISTINCT CASE WHEN config_device_type = 0 THEN mtys_log_link_visit_action.idvisit END) AS desktop_visit,

COUNT(DISTINCT CASE WHEN config_device_type = 1 THEN mtys_log_link_visit_action.idvisit END) AS mobile_visit,

COUNT(DISTINCT CASE WHEN config_device_type = 2 THEN mtys_log_link_visit_action.idvisit END) AS tablet_visit,

ROUND(COUNT(DISTINCT CASE WHEN config_device_type = 1 THEN mtys_log_link_visit_action.idvisit END) / COUNT(DISTINCT mtys_log_link_visit_action.idvisit) *100,1) AS mobile_percent

FROM mtys_log_visit INNER JOIN mtys_log_link_visit_action ON mtys_log_visit.idvisitor = mtys_log_link_visit_action.idvisitor

GROUP BY YEAR(visit_time), MONTH(visit_time)

) AS Data

GROUP BY YEAR(visit_time), MONTH(visit_time);

/*construct a new query for monthly return and frequent return visits*/

SELECT UNIX_TIMESTAMP(date_format(visit_last_action_time, "%Y-%m-01")) AS visit_last_action_time,

COUNT(DISTINCT idvisitor) as visitors,

SUM(visits) AS visits

FROM(

SELECT *

FROM(

SELECT

DATE_ADD(visit_last_action_time, INTERVAL 10 HOUR) AS visit_last_action_time,

idvisitor,

COUNT( idvisitor) AS visits

FROM

mtys_log_visit

WHERE

visitor_returning > 0

AND visitor_days_since_last <=30

GROUP BY year(visit_last_action_time), month(visit_last_action_time), idvisitor

ORDER BY `visits` DESC

) AS Visits

WHERE visits >= 5

)AS visits_summ

GROUP BY year(visit_last_action_time), month(visit_last_action_time);

Want to share this?

One thought on “Website analytics”

Rebecca says:

Leave a Reply Cancel Reply