> ## Documentation Index
> Fetch the complete documentation index at: https://developers.stellar.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Creating Visualizations

> This page will show how you can create visualizations with Hubble and Google Looker Studio.

This page will show how you can create visualizations with Hubble and [Google Looker Studio](https://cloud.google.com/looker-studio?hl=en).

By the end of the tutorial you will have created two graphs that will help visualize temporary VS persistent contract data entry distribution and expiration.

![Demo](https://mintcdn.com/stellardevelopmentfoundation/nXKshiEJBuAoHF5T/img/hubble/hubble_visualization_demo.gif?s=a1dae26db197dcc6c7e9a1eff1df1f0c)

As you can see, persistent contract data entries account for roughly 25% of all contract data entries with the rest being temporary. There are also a lot more expired temporary contract data entries. This is expected because temporay entries cannot be bumped whereas persistent entries can be bumped/restored. For more information, you can read about [State Archival and the Contract Data Lifecycle](https://developers.stellar.org/docs/learn/fundamentals/contract-development/storage/state-archival.md#contract-data-type-descriptions).

## Prerequisites

1. Make sure you have connected to Hubble by following the instructions in the [Connecting](https://developers.stellar.org/docs/data/analytics/hubble/developer-guide/connecting-to-bigquery.md) page
2. You have access to [Google Looker Studio](https://cloud.google.com/looker-studio?hl=en)
3. You have read and understand the general [Best Practices](https://developers.stellar.org/docs/data/analytics/hubble/analyst-guide/optimizing-queries.md#best-practices) for querying BigQuery data

## Create a Report in Looker Studio

### Attach Data Sources to Looker Studio

1. Select `Create --> Data source`

![New Source](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/create-data-source.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=76a45fd6e0ffc196d590b36b1b97c380)

2. Find and select the `BigQuery Google Connector`

![Add BigQuery](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/bq-connector.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=4692913c7582cbb6c08bf5e37dd9a369)

3. Find the desired tables that you want to connect. For this example you will want to add a data source for:
   1. `crypto-stellar.crypto_stellar.contract_data`

![Contract Data](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/add-contract-data.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=d8e302bfe0b20db812e46206a0a633d4)

### Create a New Report (Dashboard)

1. Select `Create --> Report`

![Create Report](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/create-report.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=9739c6f4c354c0194e51a04ffe1b06f8)

2. Add your data sources from above

![Get Data](https://mintcdn.com/stellardevelopmentfoundation/nXKshiEJBuAoHF5T/img/hubble/select-data-sources.png?fit=max\&auto=format\&n=nXKshiEJBuAoHF5T\&q=85\&s=8c1bb0f82db4017a12fa53a0241cc084)

3. Insert a `Pie chart`

![Add A Pie Chart](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/add-pie-chart.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=6d539d2d67ea9b2617c31dc02e4215a9)

4. Choose `contract_data` as the `Data source`

![New Source](https://mintcdn.com/stellardevelopmentfoundation/nXKshiEJBuAoHF5T/img/hubble/pie-contract-data.png?fit=max\&auto=format\&n=nXKshiEJBuAoHF5T\&q=85\&s=de876a45e6091ce922a7c825d2d34749)

5. Choose `closed_at` as the `Date Range Dimension` and `contract_durability` as the `Dimension`

![Add Pie Chart](https://mintcdn.com/stellardevelopmentfoundation/nXKshiEJBuAoHF5T/img/hubble/pie-config.png?fit=max\&auto=format\&n=nXKshiEJBuAoHF5T\&q=85\&s=3edfbf5544c0bc16714cf25e54e7260a)

6. You should now have a pie chart showing the percentage of Temporary VS Persistent Contract Data Durability

![Results](https://mintcdn.com/stellardevelopmentfoundation/nXKshiEJBuAoHF5T/img/hubble/pie_chart_durability.png?fit=max\&auto=format\&n=nXKshiEJBuAoHF5T\&q=85\&s=282c2255b882a9cdd8cbc89659d4d955)

### Use Custom SQL to Create a Chart

1. In your report, click `Add Data` which will be near the bottom right of your window

![Click Button](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/click_add_data.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=6dc5eb9c10cb2a79f47dcd060f9570a2)

2. Select `BigQuery` and choose `CUSTOM QUERY` and select your desired `Billing Project` where the query will be charged

![Custom Query](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/add-custom-query.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=08b7160a37363b4942e4bd82688b2747)

3. Add the following query and click `Add`

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
-- Find the latest ledger sequence within Hubble.
-- This may be slightly behind the actual Stellar latest ledger
-- because Hubble is scheduled to run and insert data at 10 minute intervals
with latest_ledger_in_hubble as (
  select
    max(sequence) as latest_ledger_sequence
  from `crypto-stellar.crypto_stellar.history_ledgers`
),

-- Find all the ttl that have expired
expired_ttl as (
  select
    key_hash
    , live_until_ledger_seq
    -- Saving the date to aggregate on at the final step of the query
    , date(closed_at) as ledger_date
  from `crypto-stellar.crypto_stellar_dbt.ttl_current`
  where true
    -- Filter for expired entries only with the use of latest_ledger_sequence
    and live_until_ledger_seq < (select latest_ledger_sequence from latest_ledger_in_hubble)
)

-- Aggregate based on the month and contract durability type
select
  date_trunc(et.ledger_date, month) as month_agg
  , cd.contract_durability
  , count(1) as expired_entry_count
from expired_ttl as et
  join `crypto-stellar.crypto_stellar_dbt.contract_data_current` as cd
    on et.key_hash = cd.ledger_key_hash
where true
  -- Optionally filter for a specific date/date range
  and et.ledger_date between '2024-02-01' and '2024-10-31'
group by 1,2
order by 1 desc, 2
```

4. Insert `Column chart`

![Column Chart](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/add-column-chart.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=da752c64d18f4282e653098e913fa2b6)

5. Select the following:

* `BigQuery Custom SQL` as your `Data source`
* `month_agg` as the `Dimension`
* `contract_durability` as the `Breakdown Dimension`
* `expired_entry_count` as the `Metric`

![Configure](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/column-chart-config.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=51c40fcc66279747c6b19e8412c6987e)

6. You should now have a column chart (bar chart) showing the expired Soroban contract entries

![Chart](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/expired-entry-column-chart.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=21e01b971e704a630ff3c38875243930)

### Applying a Global Filter

1. Click `+Add quick filter` to apply a filter throughout the whole report

![Quick Filter](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/add-quick-filter.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=cb81e861cd55fbff69de077061afe2eb)

2. Select `contract_durability` to filter by `contract_durability` values

![Durability](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/filter-contract.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=e70a2507a417e158bd15f64047ecdb8d)

3. Select only `ContractDataDurabilityPersistent` and click `Apply`

![Persistent](https://mintcdn.com/stellardevelopmentfoundation/nXKshiEJBuAoHF5T/img/hubble/persistent-only.png?fit=max\&auto=format\&n=nXKshiEJBuAoHF5T\&q=85\&s=d821f635d7627947ae77e2cfd0309075)

4. Your charts should now be filtered and show only `ContractDataDurabilityPersistent` data

![Clean Report](https://mintcdn.com/stellardevelopmentfoundation/fqZrpgzoDdQoCWJz/img/hubble/filtered-report.png?fit=max\&auto=format\&n=fqZrpgzoDdQoCWJz\&q=85\&s=91727d1a991f6057370d3baa781805c1)