Overview
A raw events table is long and thin. One row per event, an event name column, and a handful of detail columns. That shape is great for storage but awkward for a dashboard, because a board tile usually wants one row per date and one column per thing you are counting. A pivot table closes that gap.
Unlike a GA4 or CSV table, a pivot table has no external source. It reads from another Lens table that has already synced, then reshapes it. You choose which dimensions become rows, optionally add SQL-computed columns, and then define named metrics. Each metric is a conditional aggregation, which means you attach conditions that decide which source rows count toward that metric. The result is a wide, tidy table that boards and reports can query directly.
The classic use is splitting one event stream into many measures. A single Echo events table holds file_downloads, form_success, file_preview, and more, all mixed together. A pivot turns that into a table with one row per date and a clean column for each of those events, ready to chart side by side.
Pick a source table
A pivot reads from another Lens table, so the source must already exist and have synced successfully. If the source has never synced, the pivot has nothing to build from.
The source is usually a raw, event-level table such as an Echo events table. It should carry the dimensions you plan to filter and group on, for example eventName, element_text, or a page path. The richer the detail in the source, the more you can express in the pivot's metric conditions.
Create the pivot table
Open Lens > Tables from the left sidebar and click Add Table. Choose Pivot as the source type. The setup page changes to the pivot layout, and the breadcrumb reads "New Pivot Table".
Give the table a friendly name that says what it produces, such as "KPIs by Campaign" or "Metrics by Metro". Then use the Source Table selector to pick the table you want to reshape. Once a source is selected, the Group By, Derived Dimensions, and Pivot Metrics panels appear.
Choose Group By dimensions
Under Group By Dimensions, pick the dimensions that become rows in the output. Each unique combination of those values produces one row. Date is always included automatically, so at minimum every pivot is grouped by date.
Keep this list focused. If you group by date alone you get one row per day. Add a campaign dimension and you get one row per campaign per day. Every dimension you add multiplies the row count, so only group by what your board actually needs to break out.
Add derived dimensions (optional)
Derived Dimensions are SQL-computed columns built from the source data. A common example is a Month Name column derived from an event timestamp, so a board can label things by month without extra work. Each derived dimension becomes its own column and is added to the grouping.
Derived dimensions come from a curated expression library rather than free-form SQL, so you pick from a vetted list instead of writing your own. If you need an expression that is not in the list, ask a sysadmin to add it. This step is optional; many pivots need none.
Define pivot metrics
This is the heart of the pivot. Under Pivot Metrics, click Add Metric for each measure you want. Every metric becomes a column in the output, and you give it a clear name such as "File Downloads" or "Form Submits".
A metric's conditions filter which source rows contribute to it. A "File Downloads" metric might count rows where eventName equals file_downloads; a "Form Submits" metric might count rows where eventName equals form_success. Because the conditions are per metric, one source table can feed many columns, each measuring something different.
Think of each metric as a small question asked of the same data. The pivot runs all of them in one pass and lays the answers out as columns, which is exactly the shape a KPI row or a multi-series chart wants.
Save and build
Click Save to create the pivot table record. Then build it. Lens runs the pivot query against the source table and stores the result as its own ClickHouse table, so boards query the pivot directly without recomputing anything.
A pivot is a snapshot of its source at build time. Whenever the source table re-syncs with new data, rebuild the pivot so it picks up the change. You can do this manually, and the table view will tell you when a rebuild is due.
Conditional metrics are the whole point. Resist the urge to create separate source tables for each event type. Keep one rich events table and let a pivot carve it into as many columns as you need by giving each metric a different condition.
A pivot table is only as fresh as its source. If the source table syncs on a schedule, the pivot will drift behind until you rebuild it. For dashboards that must stay current, rebuild the pivot on the same cadence as the source.
Troubleshooting
The table view shows a stale-source warning
This means the source table has synced more recently than the pivot was last built. The pivot is showing older numbers. Rebuild the pivot to bring it level with the source. If this happens often, line up the pivot rebuild with the source's sync schedule.
A metric column is all zeros
The metric's conditions are matching no source rows. Check the condition values against the actual data in the source table. A frequent cause is an event name typo or a case mismatch, for example File_Downloads versus file_downloads. Open the source table view and confirm the exact values before fixing the condition.
The pivot reports its source table is missing
The source table was deleted or moved. The pivot view shows a notice with a link to edit the pivot configuration. Open the pivot, point it at a valid source table, and rebuild. Lens normally blocks deleting a table that a pivot depends on, so this usually means the dependency chain was broken in an unusual way.