March 2023 Update: Diving into Looker Studio

4 min readMar 30, 2023

March [Bracket] Madness

We believe everyone should have easy access to their insights for decision-making, program iteration, and strategic planning — that’s our mission.

And that’s why working with Google Cloud Platform has been the best choice for us and our partners because Looker Studio (formerly Google Data Studio) makes building insightful, beautiful dashboards and reports so easy and cost-friendly. You might say, looking at the competition out there, there’s a clear winner:

March Madness: Visualization Tools Bracket:

Looker Studio beat the competition because it’s:

Included with the Google Cloud Platform services CTA provides at low-cost via PAD
It’s simple to use
It connects easily to Google Sheets
And it can easily be shared with non-licensed users.

We think those are some major points. You can read more about our approach to using Looker Studio here — and learn more on how to get started below!

CTA + Impact Action: Building the District Dashboard

Last year, Impact Action teamed up with CTA to build a powerful data infrastructure that would help Impact and their grantees make informed decisions in real-time — all without breaking the bank.

Impact and CTA worked together to move data into Progressive Action Database (PAD), CTA’s data warehouse. Using PAD’s easy-to-use tools, Impact was able to seamlessly blend voter file data, predictive models, demographics, and district information from seven battleground states into a single, easy-to-read report.

The result: their Districts Dashboard — an innovative tool that gave key stakeholders and decision-makers a clear view into election reporting and forecasts for critical races.

By using CTA’s user-friendly integrations and tools, Impact’s grantees could stay focused on their top priority: electing more progressives to state legislatures across the country. And their efforts paid off! Thanks to their data-driven approach, Impact’s grantees’ programs helped secure four State House and four State Senate majority wins!

Check out an example of the Districts Dashboard:

District Dasboard designed by the Impact Action team

Creating Dashboards in Looker Studio

Making use of the Google Sheets integration with BigQuery helps democratize the ability to create powerful visualizations, lessening the burden on technical development teams.

By building PAD on top of Google’s tooling, it allows users to use native integrations so that visualizing data is easy right out of the gate. Those integrations in Looker Studio can help anyone create dashboards to analyze, understand, and present your data.

Here’s how you use Looker Studio to create dynamic dashboards.

o connect Google Sheets to Looker Studio:

Login to Looker Studio
Click the ‘+Create’ button to add a new Data Source
This opens up the menu of integrations Looker Studio has available
Select Google Sheets and select the workbook and sheet(s) you’d like to use for building out your charts and dashboards.

And to load in Data Directly from BigQuery:

Login to Looker Studio
Click the ‘+Create’ button to add a new Data Source
This opens up the menu of integrations Looker Studio has available
Select BigQuery and select the Project, DataSet and Table you’d like to use for building out your charts and dashboards.

You’ve now integrated your Google Sheets and BigQuery Project with Looker Studio, all without writing any SQL code! With these data sources established, you’ll be able to retrieve all the data you need from your organization’s data warehouse in PAD, along with any additional data stored in Sheets, and visualize all aspects of your program in one centralized place.

Notes from BenDesk*

*Ben is our resident ZenDesk captain and manager of all help@ inquiries. We’re bringing you interesting inquiries from his inbox each month.

Question of the Month: How can I tell when my table was last updated in PAD?

BenDesk Answer: PAD lets you see a variety of information about your tables, including when they were updated, the row count, and storage use. To view this, navigate to the table you are interested in and click the ‘Details’ tab. The timestamp for when a table was last updated is listed as “Last modified” in the Table Info section. This is useful if you want to perform an ad-hoc check on a sync.

But if you’re interested in regularly monitoring the table or want to include any of this information in your reporting, it’s easier to query the “__TABLES__” view in PAD. This view contains one row for each table or view in your dataset and includes the same information as the BigQuery UI.

Below is an example query that will give you the creation timestamp, last updated timestamp, and row count of tables in a dataset. Please note that you’ll need to convert the timestamp using “TIMESTAMP_MILLIS()” so that it appears in a readable format:

SELECT
dataset_id,
table_id,
TIMESTAMP_MILLIS(creation_time) AS creation_time,
TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time,
row_count
FROM
`bigquery-public-data`.`census_bureau_usa`.__TABLES__

Do you have a question for BenDesk and the team? Partners can submit their asks to help@techallies.org.

What We’re Reading, Watching, & Listening To

Reading Klara and the Sun by Kazuo Ishiguro
Reading Six of Crows by Leigh Bardugo
Listening to The Final Revival of Opal and Nev by Dawnie Walton
Watching Shadow and Bone Season 2 on Netflix