The above screenshot is the first results from processing the WordCamp Toronto 2018 Survey that many Meetup and Toronto WP users may have filled in. This survey is intended to help identify what are current topics of interest to WordPress users for the upcoming TO WordCamp 2018 but also to provide Toronto WordPress meetup organizers with a list of possible lecture topics for the next year.
The report page shows a comparison for each of the 36 topic questions. The first column is the tabulation of responses by all survey responders. Then in the green column is shown responses filtered for business developers. Finally, the light blue column shows responses by WP Newbies. The results are under analysis by the WordCamp Toronto 2018 Organizers and will form the principal recommendation for lecture topics for the conference. To see all four reports on users preferred topics, visit the following website. Meanwhile, this post describes the 3 day challenge in processing this survey data.
The Processing Challenge
As many users know, tabular data processing can be a formidable task in WordPress. Yes, with bolstered AJAX and Rest API routines plus two much improved plugins for handling MySQL data tables with TablePress and WPDataTables. Database table processing has improved in WordPress. The overall goal was to process the data into summary tabulations for each topic which could be cross filtered for three groups of responders: all survey takers, business developers, and WordPress newbie users. In addition simple charting was also envisioned but proved very difficult due to the spreadsheet bias towards numeric data.
At the outset, the real challenge was getting the survey data cleanly handed-off between the various apps: the first step was from Google Forms to Google Sheet starting the processing journey.
Except for a snafu for two fields at the outset, filling in the Google Forms has proved simple. In turn Google Forms delivers a Google Spreadsheet like so:
So Google Sheet is able to chart the data [trick is to point to the full column or row of data] but to do cross tabulations of the data proved elusive in Google Sheets. Google Sheet docs simply dry up. Also Pivot tables in Google Sheet also proved vexing as seen below:
So now our goal was to download the Google Sheet into an Excel .xlsx file in anticipation of moving that Excel data into a MySQL file in order to do the cross tabulations and get a more formatted pivot table view. So now it is off to Excel.
Google Sheets to Excel Transfer
Fortunately, the transfer from Google Sheet to Microsoft Excel was easy. However, once in Excel the results were mixed as seen in the screenshot:
First the Spider charts and Pie charts simply did not make sense. But the Pivot table worked – sort of. But editing the Excel pivot table was forbidden. So now the work in Excel would be to prepare for the transfer from Excel to the WordPress MySQL database so cross tabulations and data formatting could be done.
Here there was quite a lot of clean-up work required in Excel. Filling in blank fields, correcting some entries, but the most time consuming was to simplify all the 50 column headers so they could become legitimate sql column names. Fortunately, Excel did not get in the way on doing this clean-up. So now it is off to WordPress MySQL database so we can get cross tabulations and formatted pivot table reports.
Excel Spreadsheet Transfer to WordPress MySQL Database
I used the WPDataTables plugin to implement the transfer from Excel Spreadsheet to WordPress database. This time the transfer incurred some hiccups because I had missed some illegal characters in column names. But credit WPDatables for having easy methods for getting data into WordPress:
WPDatatables is the one of the best plugins for getting, editing and displaying database tables in WordPress. But it’s Generate a query to a MySQL database module that fell short with too many steps for the 150 different tables needed in the analysis of the WordCamp Survey data. So it was off to the handy SQL Reports plugin. for the cross tabulation and report formatting.
SQL Reports Delivers Crosstabulated Results
WPDatables can do the needed queries but it is much faster to do the cross tabulations in SQL Reports as seen in this screenshot:
The same WHERE clause is used in 36 SQL Reports, the topic name changes for each report. That is the advantage of SQL Reports, it is easy to cut and paste the topic name into the SQL. Ditto for adding the formating sub-selection as seen in the screenshot below:
The whole advantage of SQL Reports is that users can cut and paste snippets of code into the existing SQL statement change the Report Name [right side of the screen shot] the click the Update Report button just above the Report name field – and voila, you have created the new report. Do this 107 more times and the Comparison of Survey Results is finished.
Astra Theme and Elementor Page Builder Wrapup
What made a big difference in completing the report in a timely fashion was Astra Theme that provided full width layout with no header coupled with the Elementor PageBuilder. Elementor allowed me to create a new menu with the 4 posts as menu items. As well doing the layout of reports to a 3 column layout was expedited with row duplication and then changing the shortcode for each column which was easy to do. 36 reports done in about 1/2 hour. So this is the saga of creating Word Camp 2018 Survey Results.
Lessons Learned About DIY Development
As a freelance WordPress Developer I have encountered the hazards of developing for DIY Clients and Web Agencies. These users are committed to WordPress for its DIY reputation – simple, attractive, low cost, short timeline is their driving mantra. They are certainly aware that Web development is undergoing rapid change and increasing coding complexity. But they The problem occurs when the results appear simple but the path to getting those “simple” results is twisting as in this case of this simple cross tabulation for survey results. A number of nogo paths with Google Sheet, Microsoft Excel and even WPDataables had to be abandoned.
The problem arises when the DIY users are not interested and “do not want to know about the full stack complexities” involved in their tasks or project. I know of Web Designers and DIY users who proudly profess their ignorance of HTML5, CSS and certainly JavaScript, PHP plus SQL. And do not mention SPA, AMP, PWA, RestAPI, headless CMS, Kafka plus other edge Web development technologies to them. Yet all of these technologies are being absorbed into the WordPress ecosystem right away, right now.
Now fortunately for this project, the WordCamp Organizing Committee is full stack aware and fairly immune to DIY short sightedness. But in recent contracts, I have learned the value of Caveat Emptor – not only beware of the Web Developer you work with but also the DIY client. Make sure they are have necessary full stack awareness for the project you will be teaming up with them on.