I have created the api to fetch data from big-query into my nextjs 13 admin dashboard
while testing the data, I am getting 500 internal server error but data is getting printed in console
api/bigQuery/route.js
import { BigQuery } from "@google-cloud/bigquery";
//! Initialize BigQuery client
const bigquery = new BigQuery({
projectId: process.env.GCP_PROJECT_ID,
keyFilename: process.env.GCP_PRIVATE_KEY,
});
export const GET = async () => {
try {
console.log("Fetching data from BigQuery...");
const metrics = [
// ! Customer Demographics Dashboard
{
name: "Customer's Age Group",
query: `
SELECT
CASE
WHEN CustomerAge BETWEEN 1 AND 18 THEN '1-18'
WHEN CustomerAge BETWEEN 19 AND 27 THEN '19-27'
WHEN CustomerAge BETWEEN 28 AND 40 THEN '28-40'
WHEN CustomerAge BETWEEN 41 AND 54 THEN '41-54'
WHEN CustomerAge BETWEEN 55 AND 67 THEN '55-67'
ELSE '68+'
END AS CustomerAgeRange,
COUNT(*) AS CustomerCount
FROM NewMVP.all
GROUP BY CustomerAgeRange
ORDER BY CustomerAgeRange;
`,
},
{
name: "Customer's Gender",
query: `
SELECT
Gender as CustomerGender,
COUNT(*) AS CustomerCount
FROM goldengcsrec.custdatarec
GROUP BY CustomerGender
ORDER BY CustomerGender
`,
},
{
name: "Customer's Location",
query: `
SELECT
CustomerLocation,
COUNT(*) AS CustomerCount
FROM NewMVP.Customer
GROUP BY CustomerLocation
ORDER BY CustomerCount DESC
LIMIT 5;
`,
},
// { name: "Customer Income Level",
// query:``,
// }
// ! Customer Behavior Dashboard
// {
// name: "Foot Traffic Count - Monthly",
// query: `
// SELECT
// DATE_TRUNC(LastVisitDate, MONTH) AS MonthStart,
// COUNT(*) AS FootTrafficCount
// FROM NewMVP.Customer
// GROUP BY MonthStart
// ORDER BY MonthStart;
// `,
// },
{
name: "Foot Traffic by Time(days of week)",
query: `
SELECT
EXTRACT(DAYOFWEEK FROM LastVisitDate) AS DayOfWeek,
EXTRACT(HOUR FROM LastVisitTime) AS HourOfDay,
COUNT(*) AS FootTrafficCount
FROM NewMVP.CustomerData
GROUP BY DayOfWeek, HourOfDay
ORDER BY DayOfWeek, HourOfDay;
`,
},
{
name: "Average Spending",
query: `
SELECT
LoyaltyPointsMemberStatus,
AVG(AmountPaid) AS AvgSpendPerVisit
FROM NewMVP.Customer
GROUP BY LoyaltyPointsMemberStatus;
`,
},
//! Customer Reviews Dashboard
{
name: "Most Mentioned Dishes",
query: `
SELECT
MenuItemName,
COUNT(*) AS MentionFrequency
FROM NewMVP.Customer
GROUP BY MenuItemName
ORDER BY MentionFrequency DESC
LIMIT 7;
`,
},
{
name: "Review Trends",
query: `
SELECT
SUBSTR(CommentDate, 6, 2) AS Month,
COUNT(*) AS ReviewCount
FROM NewMVP.Customer
GROUP BY Month
ORDER BY Month;
`,
},
// !Loyalty Program Dashoard
{
name: "Number of Loyalty Members",
query: `
SELECT
LoyaltyPointsMemberStatus,
COUNT(*) AS TotalCustomer
FROM NewMVP.Customer
GROUP BY LoyaltyPointsMemberStatus
ORDER BY LoyaltyPointsMemberStatus;
`,
},
{
name: "Average Spend per Member",
query: `
SELECT
LoyaltyPointsMemberStatus,
AVG(AmountPaid) AS AvgSpendPerVisit
FROM NewMVP.Customer
GROUP BY LoyaltyPointsMemberStatus;
`,
},
{
name: "Loyalty Program Engagement",
query: `
SELECT
LoyaltyPointsMemberStatus,
SUM(LoyaltyPointsEarned) AS TotalPointsEarned
FROM NewMVP.Customer
GROUP BY LoyaltyPointsMemberStatus;
`,
},
// ! Customer Feedback Dashboard
// {
// name: "Survey Result",
// query: `
// SELECT
// LoyaltyPointsMemberStatus,
// SUM(LoyaltyPointsEarned) AS TotalPointsEarned
// FROM NewMVP.Customer
// GROUP BY LoyaltyPointsMemberStatus;
// `,
// },
// {
// name: "Improvment Area",
// query: `
// SELECT
// LoyaltyPointsMemberStatus,
// SUM(LoyaltyPointsEarned) AS TotalPointsEarned
// FROM NewMVP.Customer
// GROUP BY LoyaltyPointsMemberStatus;
// `,
// },
// {
// name: "Action Plan",
// query: `
// SELECT
// LoyaltyPointsMemberStatus,
// SUM(LoyaltyPointsEarned) AS TotalPointsEarned
// FROM NewMVP.Customer
// GROUP BY LoyaltyPointsMemberStatus;
// `,
// },
// {
// name: "Dine-In Experience Trends",
// query: `
// SELECT
// EXTRACT(YEAR FROM LastVisitDate ) AS Year,
// EXTRACT(MONTH FROM LastVisitDate ) AS Month,
// AVG(Rating) AS AverageSatisfactionScore
// FROM NewMVP.Customer
// GROUP BY Year, Month
// ORDER BY Year, Month;
// `,
// },
// ! Customer Rention Dashboard
];
const dataPromises = metrics.map(async (metric) => {
try {
const [rows] = await bigquery.query({ query: metric.query });
console.log(`Fetched data for ${metric.name}:`, rows);
return { [metric.name]: rows };
} catch (error) {
console.error(`Error fetching ${metric.name} data:`, error);
throw error;
}
});
const results = await Promise.all(dataPromises);
const responseData = results.reduce(
(acc, result) => ({ ...acc, ...result }),
{}
);
return {
statusCode: 200,
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify(responseData),
};
} catch (error) {
console.error("Error fetching data:", error);
return {
statusCode: 500,
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({ error: "Error fetching data from BigQuery" }),
};
}
};
Due to the 500 error I’m unable to get the in client-side
this is my client-side code
"use client";
import React, { useEffect, useState } from "react";
import { useTheme } from "@mui/material/styles";
import dynamic from "next/dynamic";
import BaseCard from "../../../shared/DashboardCard";
const Chart = dynamic(() => import("react-apexcharts"), { ssr: false });
const Gender = () => {
const theme = useTheme();
const primary = theme.palette.warning.dark;
const secondary = theme.palette.secondary.main;
const [loading, setLoading] = useState(true);
const [customerGenderData, setCustomerGenderData] = useState([]);
useEffect(() => {
fetch("/api/bigQuery")
.then((response) => response.json())
.then((data) => {
console.log("Fetched data:", data);
const genderData = data["Customer's Gender"];
setCustomerGenderData(genderData);
setLoading(false);
})
.catch((error) => {
console.error("Error fetching data:", error);
setLoading(false);
});
}, []);
const optionssalesoverview = {
grid: {
show: true,
position: "back",
padding: {
top: 0,
left: 0,
right: 0,
bottom: 0,
},
},
plotOptions: {
bar: {
horizontal: false,
columnWidth: "55%",
endingShape: "rounded",
distributed: true,
},
},
fill: {
type: "solid",
opacity: 1,
},
colors: [
"#003049",
"#d62828",
"#f77f00",
"#fcbf49",
"#eae2b7",
"#B38CB4",
"#5D2A42",
],
chart: {
type: "bar",
offsetX: -15,
toolbar: {
show: false,
},
fontFamily: "inherit",
},
dataLabels: {
enabled: true,
},
markers: {
size: 0,
},
legend: {
show: false,
},
xaxis: {
type: "category",
categories: customerGenderData.map((entry) => entry.CustomerGender),
title: {
text: "Gender Category",
style: {
cssClass: "grey--text lighten-2--text fill-color",
fontSize: "16px",
color: "#000",
},
},
labels: {
style: {
cssClass: "grey--text lighten-2--text fill-color",
fontSize: "16px",
},
},
},
yaxis: {
show: true,
tickAmount: 3,
title: {
text: "No. of Customer",
style: {
cssClass: "grey--text lighten-2--text fill-color",
fontSize: "16px",
color: "#000",
},
},
labels: {
style: {
cssClass: "grey--text lighten-2--text fill-color",
fontSize: "16px",
},
},
},
stroke: {
show: true,
width: 5,
lineCap: "butt",
colors: ["transparent"],
},
tooltip: {
theme: "dark",
},
};
return (
<BaseCard title="Gender Distribution">
{loading ? (
<p>Loading...</p>
) : (
<Chart
options={optionssalesoverview}
series={[
{ data: customerGenderData.map((entry) => entry.CustomerCount) },
]}
type="bar"
height="370px"
/>
)}
</BaseCard>
);
};
export default Gender;
What I should change in api to avoid 500 error
I have nextjs 13 admin dashboard with 40+ and 160+ charts, I have created an API to fetch required data for the chart which is stored in Big-Query. Currently I’m expecting to get 200 status code but I’m getting 500 internal server error code but data is getting printed in console
