Example queries for your data

Neelke Stadler Updated by Neelke Stadler

It is incredible what you learn about your users by looking at your data.

Your WhatsApp data can be used to understand your users and their behaviour to ultimately to improve your service, both online and offline. The analysis you can do is endless, but we share a couple of example queries to help you start.

You will find great resources online, and many of your questions on how to write specific queries to answer some of your burning questions are likely already answered by online communities such as Stack Overflow. A great starting point is BigQuery's Standard SQL documentation, specifically the Functions and Operators section.

A few tips

  • Create a new dataset in BigQuery, e.g. called Reporting, and save important query output that you would like to visualise to a dedicated table in your new dataset.
  • This way it is easy to visualise the output (e.g. daily counts) on Data Studio by using BigQuery and selecting the specific dataset and table as the data source.
  • BigQuery allows you to schedule a query refresh to ensure you have up to date data for your dashboard.

Example Queries

  • Unique users: Count the number of unique users that have engaged with your service since the start:
select count(distinct id) from `<your number>.chats`
  • Chat details: Get the latest chat details of a user without duplication:
select t.* 
from(
    select *, 
    RANK() OVER(PARTITION BY id ORDER BY updated_at DESC) as rank
    from `<your number>.chats`
    )t
WHERE rank = 1
  • Daily counts: Get the daily counts for active users (split between new and returning) and total messages (split between sent and received)
SELECT date(messages.inserted_at) AS day,
count(distinct chats.owner) as Daily_Active_Users,
count(distinct IF(date(chats.inserted_at) = date(messages.inserted_at), chats.owner, NULL)) as Daily_New_Users,
count(distinct IF(date(chats.inserted_at) <> date(messages.inserted_at), chats.owner, NULL)) as Daily_Returning_Users,
count(distinct messages.id) as Daily_Total_Messages,
count(distinct IF(messages.direction='inbound', messages.id, NULL)) as Daily_Received_Messages,
count(distinct IF(messages.direction='outbound', messages.id, NULL)) as Daily_Sent_Messages,
count(distinct messages.id)/count(distinct chats.owner) as Messages_per_User
FROM `<your number>.chats` as chats
LEFT JOIN `<your number>.messages` as messages ON messages.chat_id = chats.id
GROUP BY day
ORDER BY day desc

  • Content: Get the number of unique users that interacted with content on your service, in descending order.
SELECT faqs.question as content_name,
count(distinct chats.id) as user_count
From `<your address>.messages` as messages
LEFT JOIN `<your address>.chats` as chats ON messages.chat_id = chats.id
LEFT JOIN `<your address>.faqs` as faqs ON messages.faq_uuid = faqs.uuid
WHERE messages.direction = 'outbound'
and messages.faq_uuid is not null
and faqs.is_deleted = false
GROUP BY content_name
ORDER BY user_count desc

  • Contacts: Get the latest contact details you have for each of your users
select t.owner, t.generation, JSON_EXTRACT_SCALAR(t.fields, "$.whatsapp_profile_name") as whatsapp_profile_name, JSON_EXTRACT_SCALAR(t.fields, "$.whatsapp_id") as whatsapp_id
from(select distinct chats.owner,generation,fields, max(generation) over (partition by contact_details.contact_id) as max_generation
from `27600457382.chats` as chats
left join `27600457382.contact_details` as contact_details on chats.contact_id = contact_details.contact_id) t
where generation = max_generation

Was this article helpful?

How to top up your 'available amount' for conversations?

Insights

Contact