Making Quick Pivot Tables off your SQL results
You've crafted your SQL to return data exactly in the format you want. The next step is to present an analysis on the data. You could export the data as a CSV to create formulas in a spreadsheet – but, organizing and arranging data to show a summary would take hours and is error-prone. This is why we have pivot tables built into Silota.
A pivot table is a special type of summary table that’s unique to spreadsheets like Excel. Pivot tables are great for summarizing values in a table because they do their magic without making you create formulas to perform calculations. Pivot tables let you play around with the organization and arrangement of the summarized data. It’s this capability of changing the arrangement of summarized data on the fly by simply rotating row and column headings that give pivot tables its name.
Key components of a pivot table
Given a database table with the following schema:
CREATE TABLE adwords_keyword_performance ( date_d DATE, keyword VARCHAR(32), impressions INTEGER, clicks INTEGER, conversions INTEGER )
SELECT statement to get your total performance for a day:
SELECT date_d, SUM(impressions), SUM(clicks), SUM(conversions) FROM adwords_keyword_performance GROUP BY 1 ORDER BY 1
You might have questions about your data, for example:
- keywords with the most impressions
- days with the most impressions
- total impressions for a day
All of these questions can be answered by a pivot table.
Your first pivot table
If you want to create a table for the sum of impressions by keyword, a pivot on the keyword column will generate the following table:
This table is a lot easier to consume and visualize.
Pivot tables in Silota
Once your query is ready, head over to the 'Series' and simply pick the columns you want to pivot on.
Once you've picked a column to pivot on "segment", you are free to customize any of the generated segments. You can change chart types, change colors assigned or hide them completely.
Your resulting chart:
Can you do this in regular SQL?
Yes, but your queries will be messy:
SELECT date_d, SUM(CASE WHEN keyword = 'keyword_1' THEN impressions ELSE 0 END) as keyword_1, SUM(CASE WHEN keyword = 'keyword_2' THEN impressions ELSE 0 END) as keyword_2, SUM(CASE WHEN keyword = 'keyword_3' THEN impressions ELSE 0 END) as keyword_3 FROM adwords_keyword_performance GROUP BY 1 ORDER BY 1
You'll be tasked with keeping this query updated as you add new keywords.